高度なチュートリアル

Overview

ニューヨーク市のタクシーサンプルデータセットを使用して、ClickHouseでデータを取り込み、クエリする方法を学習します。

Prerequisites

このチュートリアルを完了するには、稼働中のClickHouseサービスへのアクセスが必要です。手順については、クイックスタートガイドを参照してください。

新しいテーブルを作成する

New York City のタクシーデータセットには、数百万件のタクシー乗車に関する詳細が含まれており、チップ額、通行料、支払い種別などのカラムがあります。このデータを保存するためのテーブルを作成します。

1. SQL コンソールに接続します:

   • ClickHouse Cloud の場合は、ドロップダウンメニューからサービスを選択し、左側のナビゲーションメニューから SQL Console を選択します。
   • セルフマネージドの ClickHouse の場合は、https://_hostname_:8443/play の SQL コンソールに接続します。詳細は ClickHouse 管理者に確認してください。

2. default データベース内に、次の trips テーブルを作成します:

   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;
   

データセットを追加する

テーブルを作成したので、S3 内の CSV ファイルから New York City のタクシーデータを追加します。

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

   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
   

2. INSERT が完了するまで待ちます。150 MB のデータをダウンロードするのに少し時間がかかることがあります。

3. 挿入が完了したら、成功していることを確認します:

   SELECT count() FROM trips
   

   このクエリは 1,999,657 行を返すはずです。

データを分析する

データを分析するためにいくつかのクエリを実行します。以下の例を確認するか、独自の SQL クエリを試してください。

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

  SELECT round(avg(tip_amount), 2) FROM trips
  
  期待される出力

  ┌─round(avg(tip_amount), 2)─┐
  │                      1.68 │
  └───────────────────────────┘
  

• 乗客数に基づいて平均運賃を計算します:

  SELECT
      passenger_count,
      ceil(avg(total_amount),2) AS average_total_amount
  FROM trips
  GROUP BY passenger_count
  
  想定される出力

  passenger_count は 0 から 9 までの範囲です:

  ┌─passenger_count─┬─average_total_amount─┐
  │               0 │                22.69 │
  │               1 │                15.97 │
  │               2 │                17.15 │
  │               3 │                16.76 │
  │               4 │                17.33 │
  │               5 │                16.35 │
  │               6 │                16.04 │
  │               7 │                 59.8 │
  │               8 │                36.41 │
  │               9 │                 9.81 │
  └─────────────────┴──────────────────────┘
  

• 地区ごとの日別の乗車回数を計算します:

  SELECT
      pickup_date,
      pickup_ntaname,
      SUM(1) AS number_of_trips
  FROM trips
  GROUP BY pickup_date, pickup_ntaname
  ORDER BY pickup_date ASC
  
  期待される出力

  ┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
  │  2015-07-01 │ Brooklyn Heights-Cobble Hill                             │              13 │
  │  2015-07-01 │ Old Astoria                                              │               5 │
  │  2015-07-01 │ Flushing                                                 │               1 │
  │  2015-07-01 │ Yorkville                                                │             378 │
  │  2015-07-01 │ Gramercy                                                 │             344 │
  │  2015-07-01 │ Fordham South                                            │               2 │
  │  2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy                   │             621 │
  │  2015-07-01 │ Park Slope-Gowanus                                       │              29 │
  │  2015-07-01 │ Bushwick South                                           │               5 │
  

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

  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
  
  想定される出力

  ┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
  │   1.9600000381469727 │                  8 │                  1 │      1 │        27511 │
  │                    0 │                 12 │                  2 │      1 │        27500 │
  │    0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │     60 │         1439 │
  │    0.902499997522682 │ 11.270625001192093 │            1.95625 │    160 │         1438 │
  │   0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │    133 │         1437 │
  │   0.9682692398245518 │ 14.134615384615385 │  2.076923076923077 │    104 │         1436 │
  │   1.1022105210705808 │ 13.778947368421052 │  2.042105263157895 │     95 │         1435 │
  

• 各地域ごとのピックアップ件数を、1日の時間帯別に表示します:

  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
  
  期待される出力

  ┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
  │ Airport                                                  │           0 │    3509 │
  │ Airport                                                  │           1 │    1184 │
  │ Airport                                                  │           2 │     401 │
  │ Airport                                                  │           3 │     152 │
  │ Airport                                                  │           4 │     213 │
  │ Airport                                                  │           5 │     955 │
  │ Airport                                                  │           6 │    2161 │
  │ Airport                                                  │           7 │    3013 │
  │ Airport                                                  │           8 │    3601 │
  │ Airport                                                  │           9 │    3792 │
  │ Airport                                                  │          10 │    4546 │
  │ Airport                                                  │          11 │    4659 │
  │ Airport                                                  │          12 │    4621 │
  │ Airport                                                  │          13 │    5348 │
  │ Airport                                                  │          14 │    5889 │
  │ Airport                                                  │          15 │    6505 │
  │ Airport                                                  │          16 │    6119 │
  │ Airport                                                  │          17 │    6341 │
  │ Airport                                                  │          18 │    6173 │
  │ Airport                                                  │          19 │    6329 │
  │ Airport                                                  │          20 │    6271 │
  │ Airport                                                  │          21 │    6649 │
  │ Airport                                                  │          22 │    6356 │
  │ Airport                                                  │          23 │    6016 │
  │ Allerton-Pelham Gardens                                  │           4 │       1 │
  │ Allerton-Pelham Gardens                                  │           6 │       1 │
  │ Allerton-Pelham Gardens                                  │           7 │       1 │
  │ Allerton-Pelham Gardens                                  │           9 │       5 │
  │ Allerton-Pelham Gardens                                  │          10 │       3 │
  │ Allerton-Pelham Gardens                                  │          15 │       1 │
  │ Allerton-Pelham Gardens                                  │          20 │       2 │
  │ Allerton-Pelham Gardens                                  │          23 │       1 │
  │ Annadale-Huguenot-Prince's Bay-Eltingville               │          23 │       1 │
  │ Arden Heights                                            │          11 │       1 │
  

7. LaGuardia 空港または JFK 空港行きの乗車データを取得します:

   SELECT
       pickup_datetime,
       dropoff_datetime,
       total_amount,
       pickup_nyct2010_gid,
       dropoff_nyct2010_gid,
       CASE
           WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
           WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
       END AS airport_code,
       EXTRACT(YEAR FROM pickup_datetime) AS year,
       EXTRACT(DAY FROM pickup_datetime) AS day,
       EXTRACT(HOUR FROM pickup_datetime) AS hour
   FROM trips
   WHERE dropoff_nyct2010_gid IN (132, 138)
   ORDER BY pickup_datetime
   
   期待される出力

   ┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
   │ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │         13.3 │                 -34 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │          6.8 │                  50 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │          4.8 │                -125 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │        14.72 │                -101 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │        39.34 │                  48 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │         9.95 │                 -93 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │         13.3 │                 -11 │                  138 │ LGA          │ 2015 │   1 │    0 │
   │ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │          6.3 │                 -94 │                  132 │ JFK          │ 2015 │   1 │    0 │
   │ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │        11.76 │                  37 │                  132 │ JFK          │ 2015 │   1 │    1 │
   

Dictionary を作成する

Dictionary はメモリに保存されたキーと値のペアのマッピングです。詳細については、Dictionaries を参照してください

ClickHouse サービス内のテーブルに関連付けられた Dictionary を作成します。 このテーブルと Dictionary は、New York City の各地区ごとに 1 行が含まれている CSV ファイルに基づいています。

近隣地域は、ニューヨーク市の5つの行政区(Bronx、Brooklyn、Manhattan、Queens、Staten Island)の名称、および Newark 空港 (EWR) にマッピングされています。

使用している 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

1. 次の SQL コマンドを実行すると、taxi_zone_dictionary という名前の Dictionary が作成され、S3 上の CSV ファイルから Dictionary にデータが取り込まれます。ファイルの 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())

注記

LIFETIME を 0 に設定すると、S3 バケットへの不要なトラフィックを回避するために自動更新が無効になります。その他の場合は、異なる設定を行うことができます。詳細については、LIFETIME を使用した Dictionary データの更新 を参照してください。

3. 正しく動作していることを確認します。次のクエリでは 265 行、つまり各地区ごとに 1 行が返されるはずです:

   SELECT * FROM taxi_zone_dictionary
   

4. Dictionary から値を取得するには、dictGet 関数（またはそのバリエーション）を使用します。Dictionary 名、取得したい値の属性名、およびキー（この例では taxi_zone_dictionary の LocationID カラム）を引数として渡します。

   たとえば、次のクエリは LocationID が 132 の Borough（JFK 空港に対応）を返します。

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

   JFK は Queens（クイーンズ区）にあります。値の取得にかかる時間がほぼ 0 であることを確認してください:

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

5. dictHas 関数を使用して、キーが Dictionary に存在するかどうかを確認します。たとえば、次のクエリは 1（ClickHouse では「true」とみなされます）を返します。

   SELECT dictHas('taxi_zone_dictionary', 132)
   

6. 次のクエリは、4567 が Dictionary の LocationID に存在しない値であるため、0 を返します:

   SELECT dictHas('taxi_zone_dictionary', 4567)
   

7. 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
   

   このクエリは、ラガーディア空港または JFK 空港で終了するタクシー乗車数を行政区ごとに集計します。結果は次のようになり、乗車地点（pickup）の地区情報が不明な乗車がかなり多いことに注目してください。

   ┌─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.)
   

結合を実行する

taxi_zone_dictionary と trips テーブルを結合するクエリをいくつか記述してください。

1. まず、前述の空港クエリと同様に動作するシンプルな 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 関数を呼び出していますが、JOIN 構文の方が SQL 開発者にとってよりなじみがあります。

2. このクエリは、チップ額が最も高い 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
   
   注記

   一般的に、ClickHouse では SELECT * を頻繁に使用することは避けてください。実際に必要なカラムだけを取得するようにしてください。

次のステップ

ClickHouse についてさらに学ぶには、以下のドキュメントを参照してください：

• ClickHouse におけるプライマリインデックス入門: ClickHouse がスパースなプライマリインデックスを使用して、クエリ時に関連するデータを効率的に特定する仕組みを解説します。
• 外部データソースとの統合: ファイル、Kafka、PostgreSQL、データパイプラインなどを含む、さまざまなデータソース統合オプションを確認します。
• ClickHouse でデータを可視化する: お好みの UI/BI ツールを ClickHouse に接続する方法を説明します。
• SQL リファレンス: データの変換、処理、分析に利用できる ClickHouse の SQL 関数を参照できます。