ニューヨークのタクシーデータセットを用いた地理空間分析

2009年以降にニューヨーク市を出発地とした、タクシーおよび配車サービス車両（Uber、Lyft など）の数十億件の乗車記録からなるデータセットを使って ClickHouse を試してみましょう

このチュートリアルでは、ClickHouse を使って大量のデータに対して分析クエリを実行する方法を学びます。 あわせて、Dictionary を使ってデータを拡充する方法や、結合クエリの書き方も紹介します。

​

前提条件

このチュートリアルを進めるには、以下が必要です。

• ClickHouse Cloud アカウント (サインアップ時に 300 ドル分の無料クレジットが付与されます)
• ClickHouse Cloud サービス

1. テーブルを作成する

   このチュートリアルでは、ニューヨーク市のタクシーデータセットを使用します。このデータセットには数百万件のタクシー乗車に関する詳細情報が含まれており、チップ額、通行料、支払い方法などのカラムがあります。

   1. 左側のメニューから SQL console を選択します
   2. ホームアイコンの横にある + タブをクリックして、新しいクエリを作成します
   3. SQL エディタに次のクエリを入力し、Run をクリックします：

   Expandable

   CREATE TABLE trips
   (
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(pickup_date)
   ORDER BY pickup_datetime;
   
2. データを挿入する

   テーブルを作成したら、次に S3 上の CSV ファイルからニューヨーク市のタクシーデータを追加します。

   次のコマンドは、S3 上の 2 つのファイル trips_1.tsv.gz と trips_2.tsv.gz から、約 2,000,000 行を trips テーブルに挿入します。

   Expandable

   INSERT INTO trips
   SELECT * FROM s3(
       'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
       'TabSeparatedWithNames', "
       `trip_id` UInt32,
       `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
       `pickup_date` Date,
       `pickup_datetime` DateTime,
       `dropoff_date` Date,
       `dropoff_datetime` DateTime,
       `store_and_fwd_flag` UInt8,
       `rate_code_id` UInt8,
       `pickup_longitude` Float64,
       `pickup_latitude` Float64,
       `dropoff_longitude` Float64,
       `dropoff_latitude` Float64,
       `passenger_count` UInt8,
       `trip_distance` Float64,
       `fare_amount` Float32,
       `extra` Float32,
       `mta_tax` Float32,
       `tip_amount` Float32,
       `tolls_amount` Float32,
       `ehail_fee` Float32,
       `improvement_surcharge` Float32,
       `total_amount` Float32,
       `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
       `trip_type` UInt8,
       `pickup` FixedString(25),
       `dropoff` FixedString(25),
       `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
       `pickup_nyct2010_gid` Int8,
       `pickup_ctlabel` Float32,
       `pickup_borocode` Int8,
       `pickup_ct2010` String,
       `pickup_boroct2010` String,
       `pickup_cdeligibil` String,
       `pickup_ntacode` FixedString(4),
       `pickup_ntaname` String,
       `pickup_puma` UInt16,
       `dropoff_nyct2010_gid` UInt8,
       `dropoff_ctlabel` Float32,
       `dropoff_borocode` UInt8,
       `dropoff_ct2010` String,
       `dropoff_boroct2010` String,
       `dropoff_cdeligibil` String,
       `dropoff_ntacode` FixedString(4),
       `dropoff_ntaname` String,
       `dropoff_puma` UInt16
   ") SETTINGS input_format_try_infer_datetimes = 0
   

   データの挿入が完了するまで待ちます。約 150MB のデータがダウンロードされます。 挿入が完了したら、trips テーブルの行数を確認します。

   SELECT count() FROM trips
   

   結果は 1,999,657 行になるはずです

3. データを分析する

   データの読み込みが完了したら、クエリを実行してデータを分析できます。

   • チップの平均額を計算します:

     SELECT round(avg(tip_amount), 2) FROM trips
     

   • 乗客数ごとの平均料金を計算します:

     SELECT
         passenger_count,
         ceil(avg(total_amount),2) AS average_total_amount
     FROM trips
     GROUP BY passenger_count
     

   • 地区ごとの1日あたりの乗車数を計算します:

     SELECT
       pickup_date,
       pickup_ntaname,
       SUM(1) AS number_of_trips
     FROM trips
     GROUP BY pickup_date, pickup_ntaname
     ORDER BY pickup_date ASC
     

   • 各乗車記録の所要時間を分単位で計算し、所要時間ごとに結果をグループ化します:

     SELECT
       avg(tip_amount) AS avg_tip,
       avg(fare_amount) AS avg_fare,
       avg(passenger_count) AS avg_passenger,
       count() AS count,
       truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
     FROM trips
     WHERE trip_minutes > 0
     GROUP BY trip_minutes
     ORDER BY trip_minutes DESC
     

   • 各地区の乗車数を時間帯別に表示します:

     SELECT
         pickup_ntaname,
         toHour(pickup_datetime) as pickup_hour,
         SUM(1) AS pickups
     FROM trips
     WHERE pickup_ntaname != ''
     GROUP BY pickup_ntaname, pickup_hour
     ORDER BY pickup_ntaname, pickup_hour
     
4. 辞書を作成する

   次に、ニューヨーク市のすべての地区を収録した CSV ファイルをソースとして、ロケーション ID と NYC の区名を対応付ける taxi_zone_dictionary という名前の Dictionary (メモリ上に保持されるキー・バリューのペアのマッピング) を作成します。 これらのロケーション ID は、trips テーブルの pickup_nyct2010_gid カラムおよび dropoff_nyct2010_gid カラムに対応しています。

   以下は、使用する CSV ファイルの一部をテーブル形式で示したものです。ファイル内の LocationID カラムは、trips テーブルの pickup_nyct2010_gid カラムおよび dropoff_nyct2010_gid カラムに対応しています。

   LocationID	Borough	Zone	service_zone
   1	EWR	Newark Airport	EWR
   2	Queens	Jamaica Bay	Boro Zone
   3	Bronx	Allerton/Pelham Gardens	Boro Zone
   4	Manhattan	Alphabet City	Yellow Zone
   5	Staten Island	Arden Heights	Boro Zone

   次の SQL コマンドを実行して taxi_zone_dictionary という名前の Dictionary を作成し、S3 上の CSV ファイルからデータを投入します。ファイルの URL は https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv です。

   CREATE DICTIONARY taxi_zone_dictionary
   (
     `LocationID` UInt16 DEFAULT 0,
     `Borough` String,
     `Zone` String,
     `service_zone` String
   )
   PRIMARY KEY LocationID
   SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
   LIFETIME(MIN 0 MAX 0)
   LAYOUT(HASHED_ARRAY())
   

   ここでは、S3 バケットへの不要なトラフィックを避けるため、LIFETIME を 0 に設定して自動更新を無効にしています。用途によっては、別の値を設定してもかまいません。詳細については、LIFETIME を使用した Dictionary データの更新を参照してください。

   正しく動作したことを確認します。次のクエリは 265 行 (地区ごとに 1 行) を返すはずです。

   SELECT * FROM taxi_zone_dictionary
   
5. Dictionaryを使ってクエリを実行する

   Dictionary から値を取得するには、dictGet 関数 (またはそのバリエーション) を使用します。 Dictionary の名前、取得したい値、キー (この例では taxi_zone_dictionary の LocationID カラム) を渡すと、対応する値が返されます。 例えば、次のクエリは LocationID が 132 (JFK 空港に相当) の Borough を返します。

   SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)
   

   JFK はクイーンズにあります。値の取得にかかる時間が実質的に 0 であることに注目してください。

   ┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
   │ Queens                                          │
   └─────────────────────────────────────────────────┘
   
   1 rows in set. Elapsed: 0.004 sec.
   

   キーが Dictionary に存在するかどうかを確認するには、dictHas 関数を使用します。たとえば、次のクエリは 1 (ClickHouse では “true” を意味します) を返します。

   SELECT dictHas('taxi_zone_dictionary', 132)
   

   次のクエリでは、4567 は Dictionary 内の LocationID の値として存在しないため、0 が返されます。

   SELECT dictHas('taxi_zone_dictionary', 4567)
   

   クエリ内で区 (borough) の名前を取得するには、dictGet 関数を使用します。例：

   SELECT
     count(1) AS total,
     dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
   FROM trips
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY borough_name
   ORDER BY total DESC
   

   このクエリは、LaGuardia 空港または JFK 空港で降車したタクシーの乗車数を行政区 (borough) ごとに集計します。結果は次のとおりです。乗車地点の地区 (neighborhood) が不明な乗車記録がかなり多いことがわかります。

   ┌─total─┬─borough_name──┐
   │ 23683 │ Unknown       │
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)
   
6. 結合を実行する

   最後に、taxi_zone_dictionary と trips テーブルを結合するクエリをいくつか作成してみましょう。

   まずは、先ほどの空港に関するクエリと同様の処理を行う、シンプルな JOIN から始めます。

   SELECT
       count(1) AS total,
       Borough
   FROM trips
   JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
   WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
   GROUP BY Borough
   ORDER BY total DESC
   

   レスポンスは dictGet クエリの結果と同じです。

   ┌─total─┬─Borough───────┐
   │  7053 │ Manhattan     │
   │  6828 │ Brooklyn      │
   │  4458 │ Queens        │
   │  2670 │ Bronx         │
   │   554 │ Staten Island │
   │    53 │ EWR           │
   └───────┴───────────────┘
   
   6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)
   

   上記の JOIN クエリの出力は、dictGetOrDefault を使用した直前のクエリと同じであることがわかります (ただし、Unknown の値は含まれていません) 。 内部的には、ClickHouse は taxi_zone_dictionary Dictionary に対して dictGet 関数を呼び出していますが、SQL 開発者にとっては JOIN 構文のほうがなじみがあるでしょう。

   このクエリは、チップ額が最も高い 1000 件の乗車記録の行を返し、各行を Dictionary と内部結合します。

   SELECT *
   FROM trips
   JOIN taxi_zone_dictionary
   ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
   WHERE tip_amount > 0
   ORDER BY tip_amount DESC
   LIMIT 1000
   

   SELECT * を使うのではなく、クエリに必要なカラムだけを列挙することを推奨します。ClickHouse はデータをカラム単位で格納するため、選択するカラムを減らせば、読み取り・解凍・処理の対象となるデータ量をそのまま削減できます。

​

次のステップ

ClickHouse の詳細については、以下のドキュメントを参照してください。

• ClickHouse におけるプライマリインデックスの概要: ClickHouse がスパースプライマリインデックスを使用して、クエリ実行時に関連データを効率的に特定する仕組みを解説します。
• 外部データソースとの統合: ファイル、Kafka、PostgreSQL、データパイプラインなど、多様なデータソースとのインテグレーション方法を紹介します。
• ClickHouse のデータを可視化する: お好みの UI/BI ツールを ClickHouse に接続します。
• SQL リファレンス: データの変換、処理、分析に利用できる ClickHouse の SQL 関数を一覧で確認できます。