Set up docs MCP概要
ニューヨーク市のタクシーのサンプルデータセットを使用して、ClickHouse でデータを取り込み、クエリする方法を学びます。
前提条件
このチュートリアルを完了するには、稼働中の ClickHouse サービスにアクセスできる必要があります。手順については、クイックスタートガイドを参照してください。
新しいテーブルを作成する
New York Cityのタクシーデータセットには、数百万件のタクシー乗車記録に関する詳細が含まれています。チップ額、通行料、支払いタイプなどのカラムが含まれます。このデータを格納するテーブルを作成します。
-
SQL Console に接続します。
- ClickHouse Cloud の場合は、ドロップダウンメニューからサービスを選択し、左側のナビゲーションメニューで SQL Console を選択します。
- セルフマネージド ClickHouse の場合は、
https://_hostname_:8443/playの SQL Console に接続します。詳細については、ClickHouse 管理者に確認してください。
-
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 のタクシーデータを追加します。
-
次のコマンドは、S3 上の 2 つのファイル
trips_1.tsv.gzとtrips_2.tsv.gzから約 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 -
INSERTが完了するまで待ちます。150 MB のデータのダウンロードには少し時間がかかる場合があります。 -
挿入が完了したら、正しく実行されたことを確認します。
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 │ └─────────────────┴──────────────────────┘ -
地区ごとの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想定される出力
┌─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 │ -
各地域の乗車件数を時間帯別に表示します。
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 │
-
ラガーディア空港または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 は、ニューヨーク市の地区ごとに 1 行を持つ CSV ファイルに基づいています。
各 neighborhood は、ニューヨーク市の 5 つの borough (Bronx、Brooklyn、Manhattan、Queens、Staten Island) の名称と、Newark Airport (EWR) に対応付けられています。
以下は、使用するCSVファイルの内容を表形式で抜粋したものです。ファイル内の LocationID カラムは、trips テーブルの pickup_nyct2010_gid および dropoff_nyct2010_gid カラムに対応します:
| LocationID | 行政区 | ゾーン | service_zone |
|---|---|---|---|
| 1 | EWR | ニューアーク空港 | EWR |
| 2 | クイーンズ | ジャマイカ湾 | 区ゾーン |
| 3 | ブロンクス | アラートン／ペルハム・ガーデンズ | 区ゾーン |
| 4 | マンハッタン | アルファベット・シティ | イエローゾーン |
| 5 | スタテンアイランド | アーデンハイツ | ボロゾーン |
- 次の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())-
動作を確認します。次のクエリは、各 neighborhood につき 1 行、合計 265 行を返すはずです。
SELECT * FROM taxi_zone_dictionary -
dictGet関数 (またはそのバリエーション) を使用して、Dictionary から値を取得します。Dictionary の名前、取得する値、キー (この例ではtaxi_zone_dictionaryのLocationIDカラム) を指定します。たとえば、次のクエリは、JFK空港に対応する
LocationID132 の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. -
dictHas関数を使用して、Dictionary にキーが存在するかどうかを確認できます。たとえば、次のクエリは1(ClickHouse では "true") を返します。SELECT dictHas('taxi_zone_dictionary', 132) -
次のクエリは、4567 が Dictionary 内の
LocationIDに存在しない値であるため、0 を返します。SELECT dictHas('taxi_zone_dictionary', 4567) -
クエリで
dictGet関数を使用して borough の名前を取得します。例：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 空港で終了する区ごとのタクシー乗車記録数を集計します。結果は次のようになります。乗車場所の地域が不明な乗車記録がかなり多いことに注目してください。
┌─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 テーブルと結合するクエリをいくつか作成します。
-
まず、上記の空港クエリと同様の動作をする単純な
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.)
- このクエリは、チップ額が最も高い 1,000 件の乗車記録の行を返した後、各行を 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についてさらに詳しく学べます。
- ClickHouseのプライマリインデックス入門: ClickHouseがクエリ時に関連データを効率的に特定するために、スパースプライマリインデックスをどのように使用するかを学びます。
- 外部データソースを統合する: ファイル、Kafka、PostgreSQL、データパイプラインなど、データソースのインテグレーションオプションを確認します。
- ClickHouseでデータを可視化する: お好みのUI/BIツールをClickHouseに接続します。
- SQLリファレンス: ClickHouseで利用可能な、データの変換、処理、分析のためのSQL関数を参照します。