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

NYPD苦情データ

タブ区切り値（TSV）ファイルは一般的であり、ファイルの最初の行にフィールドの見出しを含むことがあります。 ClickHouseはTSVを取り込むだけでなく、ファイルを取り込まずにTSVをクエリすることもできます。このガイドでは、これらの両方のケースをカバーします。 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の推奨事項に基づいており、LowCardinality(String)フィールドで1万を超えないようにすることが重要です。

DateTimeフィールド​

データセットのウェブページのこのデータセットのカラムセクションに基づき、報告されたイベントの開始と終了のためのデートおよび時間フィールドがあります。 CMPLNT_FR_DTおよびCMPLT_TO_DTの最小値と最大値を調べることで、フィールドが常に populatedされているかどうかを知ることができます。

結果:

結果:

結果:

結果:

計画を立てる​

上記の調査に基づいて：

  • 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が必要です
注記

もっと多くのタイプに変更を加える必要があります。すべての変更は、同じ調査手順に従うことで特定できます。フィールド内の異なる文字列の数、数値の最小値と最大値を確認し、決定を下してください。ガイドの後半に示されるテーブルスキーマは、多くの低カードinality文字列と符号なし整数フィールド、非常に少ない浮動小数点が含まれています。

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

日付フィールドCMPLNT_FR_DTと時間フィールドCMPLNT_FR_TMを連結して、DateTimeにキャストできる単一のStringにします。二つのフィールドは連結演算子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.tablesは、作成したテーブルに関するすべての情報を持っています。このクエリは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に設定して、データフローをわかりやすくします。最後の引数は、テーブルから選択するクエリ（FROM input）であり、これがclickhouse-clientにパイプされてNYPD_Complaintテーブルをポピュレートします。

データの検証​

注記

データセットは年に1回またはそれ以上変更されるため、カウントがこのドキュメントにあるものと一致しない場合があります。

クエリ:

結果:

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

クエリ:

結果:

いくつかのクエリを実行する​

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

クエリ:

結果:

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

クエリ:

結果:

次のステップ​

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