メインコンテンツまでスキップ
メインコンテンツまでスキップ

NYPD Complaint Data

Tab区切り値、またはTSVファイルは一般的であり、ファイルの最初の行にフィールド見出しを含む場合があります。ClickHouseはTSVを取り込み、ファイルを取り込まずにTSVをクエリすることもできます。このガイドでは、これらの2つのケースの両方をカバーします。CSVファイルをクエリまたは取り込む必要がある場合は、同じ手法が機能し、単にフォーマット引数でTSVをCSVに置き換えるだけです。

このガイドを進める中で、以下を行います：

  • 調査：TSVファイルの構造と内容をクエリします。
  • 対象のClickHouseスキーマを決定：適切なデータ型を選び、既存のデータをそれらの型にマッピングします。
  • ClickHouseテーブルを作成。
  • データを前処理してストリーミングし、ClickHouseに送信します。
  • ClickHouseに対していくつかのクエリを実行します。

このガイドで使用されるデータセットは、NYCオープンデータチームから提供されており、「ニューヨーク市警察（NYPD）に報告されたすべての有効な重罪、軽罪、違反事件に関するデータ」が含まれています。執筆時点で、データファイルのサイズは166MBですが、定期的に更新されています。

出典：data.cityofnewyork.us 利用規約： https://www1.nyc.gov/home/terms-of-use.page

前提条件​

このガイドで説明されているコマンドに関する注意​

このガイドには2種類のコマンドがあります：

  • 一部のコマンドはTSVファイルをクエリしており、これらはコマンドプロンプトで実行されます。
  • 残りのコマンドはClickHouseをクエリしており、これらはclickhouse-clientまたはPlay UIで実行されます。
注記

このガイドの例では、TSVファイルを${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsvに保存したと仮定しています。必要に応じてコマンドを調整してください。

TSVファイルに慣れる​

ClickHouseデータベースで作業を始める前に、データに慣れてください。

ソースTSVファイルのフィールドを見る​

これはTSVファイルをクエリするコマンドの例ですが、まだ実行しないでください。

サンプル応答

ヒント

通常、上記のコマンドは、入力データのどのフィールドが数値で、どのフィールドが文字列、どのフィールドがタプルであるかを教えてくれます。これは常に当てはまるわけではありません。ClickHouseは数十億のレコードを含むデータセットと共に使用されることが多いため、スキーマを推測するために既定で100行を検査します。これは、数十億行を解析してスキーマを推測するのを避けるためです。以下の応答は、あなたが見るものと一致しないかもしれません。なぜならデータセットは毎年数回更新されているからです。データ辞書を見れば、CMPLNT_NUMがテキストとして指定されているのがわかり、数値ではありません。推論のデフォルトが100行であるのをSETTINGS input_format_max_rows_to_read_for_schema_inference=2000に上書きすることで、内容をより良く把握できます。

注：バージョン22.5以降、デフォルトはスキーマ推論のために25,000行になっていますので、古いバージョンを使用している場合や、25,000行以上のサンプリングが必要な場合にのみ設定を変更してください。

コマンドプロンプトでこのコマンドを実行してください。ダウンロードしたTSVファイルのデータをクエリするためにclickhouse-localを使用します。

結果：

この時点で、TSVファイルのカラムがデータセットのウェブページのこのデータセットのカラムセクションで指定されている名前とタイプと一致するか確認する必要があります。データ型はあまり具体的ではなく、すべての数値フィールドはNullable(Float64)に設定され、他のすべてのフィールドはNullable(String)です。データを格納するためにClickHouseテーブルを作成する際、より適切でパフォーマンスの良い型を指定できます。

適切なスキーマを決定する​

フィールドに使用すべき型を判断するためには、データがどのようになっているかを知る必要があります。たとえば、フィールドJURISDICTION_CODEは数値ですが、UInt8にするべきか、Enumにするべきか、またはFloat64が適切でしょうか？

結果：

クエリの応答は、JURISDICTION_CODEがUInt8に適していることを示しています。

同様に、いくつかのStringフィールドを見て、それらがDateTimeまたはLowCardinality(String)フィールドに適しているかどうかを確認します。

たとえば、フィールドPARKS_NMは「発生地点のNYC公園、遊び場、または緑地の名称（適用される場合。州立公園は含まれません）」と記述されています。ニューヨーク市の公園の名前はLowCardinality(String)に適しているかもしれません：

結果：

いくつかの公園の名前を見てみましょう：

結果：

執筆時点のデータセットには、PARK_NM列に数百の異なる公園と遊び場しかありません。この数は、LowCardinalityにおける推奨値である10,000以上の異なる文字列を下回る小さな数です。

DateTimeフィールド​

データセットのこのカラムセクションに基づいて、報告されたイベントの開始および終了のための日時フィールドがあります。CMPLNT_FR_DTおよびCMPLT_TO_DTの最小値と最大値を見れば、フィールドが常に埋まっているかどうかを判断できます：

結果：

結果：

結果：

結果：

プランを立てる​

上記の調査に基づいて：

  • JURISDICTION_CODEはUInt8型にキャストすべきです。
  • PARKS_NMはLowCardinality(String)にキャストすべきです。
  • CMPLNT_FR_DTとCMPLNT_FR_TMは常に埋まっている（恐らくデフォルトの時刻00:00:00を含む）。
  • CMPLNT_TO_DTとCMPLNT_TO_TMは空であるかもしれません。
  • 日付と時刻はソースの異なるフィールドに保存されている。
  • 日付はmm/dd/yyyy形式。
  • 時間はhh:mm:ss形式。
  • 日付と時間はDateTime型に結合できます。
  • 1970年1月1日以前の日付がいくつか存在するため、64ビットDateTimeが必要です。
注記

型に変更を加えるべき点は他にも多くあります。それらはすべて、同じ調査手順に従うことでわかります。フィールド内の異なる文字列の数、数値の最小値と最大値を調べ、決定を下してください。以下のガイドに示されるテーブルスキーマには、多くの低いカーディナリティ文字列と符号なし整数フィールドが含まれ、非常に少ない浮動小数点数が含まれます。

日付と時間フィールドを結合する​

日付と時間フィールドCMPLNT_FR_DTとCMPLNT_FR_TMをDateTimeにキャストできる単一のStringに結合するには、次の2つのフィールドを結合演算子CMPLNT_FR_DT || ' ' || CMPLNT_FR_TMで結合します。CMPLNT_TO_DTとCMPLNT_TO_TMフィールドも同様に処理されます。

結果：

日付と時間のStringをDateTime64型に変換する​

ガイドの前の方で、TSVファイルには1970年1月1日以前の日付があることがわかっているため、日付には64ビットDateTime型が必要になります。また、日付はMM/DD/YYYYからYYYY/MM/DDフォーマットに変換する必要があります。これらの両方はparseDateTime64BestEffort()で実行できます。

2行目と3行目には前のステップからの結合が含まれ、4行目と5行目は文字列をDateTime64に解析します。苦情の終了時間は必ずしも存在するわけではないため、parseDateTime64BestEffortOrNullが使用されます。

結果：

注記

上記のように1925と表示される日付は、データのエラーによるものです。オリジナルデータには、1019から1022の年に日付があるいくつかのレコードがあり、それは2019から2022であるべきです。これらは64ビットDateTimeで保存されるため、1925年1月1日として保持されています。

テーブルを作成する​

上記で決定したカラムに対するデータ型は、以下のテーブルスキーマに反映されます。また、テーブルに使用するORDER BYおよびPRIMARY KEYについても決定する必要があります。ORDER BYまたはPRIMARY KEYのいずれかは必ず指定しなければなりません。以下は、ORDER BYに含めるカラムを決定するためのガイドラインであり、この文書の最後の次のステップセクションに詳細情報があります。

Order ByとPrimary Keyの句​

  • ORDER BYのタプルには、クエリフィルターで使用されるフィールドを含めるべきです。
  • ディスク上の圧縮を最大化するために、ORDER BYのタプルはカーディナリティの昇順で並べるべきです。
  • もし存在する場合、PRIMARY KEYタプルはORDER BYタプルのサブセットでなければなりません。
  • ORDER BYのみが指定されている場合、同じタプルがPRIMARY KEYとして使用されます。
  • プライマリキーインデックスは、指定された場合にPRIMARY KEYタプルを使用して作成され、それ以外の場合はORDER BYタプルを使用して作成されます。
  • PRIMARY KEYインデックスは、主メモリに保持されます。

データセットを見て、クエリで回答される可能性のある質問を考えた場合、私たちはニューヨーク市の5つの区で報告されている犯罪の種類に着目することになるかもしれません。これらのフィールドは、ORDER BYに含めることができます：

カラム説明（データ辞書から）
OFNS_DESCキーコードに対応する犯罪の説明
RPT_DT警察に報告された日付
BORO_NM事件が発生した区の名前

3つの候補カラムのカーディナリティをTSVファイルにクエリしてみましょう：

結果：

カーディナリティ別に並べると、ORDER BYは次のようになります：

注記

以下のテーブルは、より読みやすいカラム名を使用します。上記の名前は、

とマッピングされます。

データ型に対する変更とORDER BYタプルを組み合わせることで、このテーブル構造が得られます：

テーブルのプライマリキーを検出する​

ClickHouseのsystemデータベース、特にsystem.tableには、作成したテーブルに関するすべての情報があります。このクエリはORDER BY（ソートキー）およびPRIMARY KEYを表示します：

応答

データを前処理してインポートする​

データの前処理にはclickhouse-localツールを使用し、アップロードにはclickhouse-clientを使用します。

clickhouse-localで使用する引数​

ヒント

table='input'は以下のclickhouse-localの引数に登場します。clickhouse-localは提供された入力（cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv）を受け取り、その入力をテーブルに挿入します。デフォルトでは、テーブル名はtableです。このガイドでは、データフローを明確にするためにテーブル名をinputに設定しています。clickhouse-localの最終引数は、テーブルから選択するクエリ（FROM input）で、これがclickhouse-clientにパイプされてNYPD_Complaintテーブルを埋めます。

データを検証する​

注記

データセットは年に1回以上変更されるため、あなたのカウントはこの文書にあるものと一致しないかもしれません。

クエリ：

結果：

ClickHouse内のデータセットのサイズは、元のTSVファイルのわずか12%です。元のTSVファイルのサイズとテーブルのサイズを比較します：

クエリ：

結果：

一部のクエリを実行する​

クエリ1. 月ごとの苦情数を比較する​

クエリ：

結果：

クエリ2. 区ごとの苦情の総数を比較する​

クエリ：

結果：

次のステップ​

ClickHouseにおけるスパースプライマリインデックスの実践的な紹介では、ClickHouseのインデックスが従来のリレーショナルデータベースと比較して異なる点、ClickHouseがスパースプライマリインデックスをどのように構築および使用するか、そしてインデクシングのベストプラクティスについて説明します。