高度なチュートリアル
概要
New York Cityのタクシー例データセットを使用して、ClickHouseにデータを取り込み、クエリを実行する方法を学びます。
事前条件
このチュートリアルを完了するには、稼働中のClickHouseサービスへのアクセスが必要です。手順については、クイックスタートガイドを参照してください。
新しいテーブルを作成する
New York Cityのタクシーデータセットには、チップの金額、通行料、支払い方法などを含む、何百万件ものタクシーライドの詳細が含まれています。このデータを格納するテーブルを作成します。
-
SQLコンソールに接続します:
- ClickHouse Cloudの場合、ドロップダウンメニューからサービスを選択し、次に左側のナビゲーションメニューからSQLコンソールを選択します。
- セルフマネージドのClickHouseの場合は、
https://_hostname_:8443/play
でSQLコンソールに接続します。詳細についてはClickHouse管理者に確認してください。
-
default
データベースに次のtrips
テーブルを作成します:
データセットを追加する
テーブルを作成したので、S3にあるCSVファイルからNew York Cityのタクシーデータを追加します。
- 次のコマンドは、S3にある2つの異なるファイル
trips_1.tsv.gz
とtrips_2.tsv.gz
から、約2,000,000行をtrips
テーブルに挿入します:
-
INSERT
が完了するまで待ちます。150MBのデータがダウンロードされるまでにはしばらく時間がかかる場合があります。 -
挿入が完了したら、正しく動作したことを確認します:
このクエリは1,999,657行を返すはずです。
データを分析する
データを分析するためにいくつかのクエリを実行します。以下の例を探索するか、自分のSQLクエリを試してみてください。
- 平均チップ金額を計算します:
期待される出力
- 乗客数に基づく平均コストを計算します:
期待される出力
passenger_count
は0から9の範囲です:
- 地域ごとの1日のピックアップ数を計算します:
期待される出力
- 各旅行の長さを分単位で計算し、旅行の長さで結果をグループ化します:
期待される出力
- 時間帯ごとの地域ごとのピックアップ数を表示します:
期待される出力
- LaGuardiaまたはJFK空港へのライドを取得します:
期待される出力
辞書を作成する
辞書は、メモリ内に格納されたキーとバリューのペアのマッピングです。詳細については、辞書を参照してください。
ClickHouseサービス内のテーブルに関連付けられた辞書を作成します。テーブルと辞書は、New York Cityの各地域で1行を含むCSVファイルに基づいています。
地域は、New York Cityの5つの区(Bronx、Brooklyn、Manhattan、Queens、Staten Island)とニューワーク空港(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 |
- 次のSQLコマンドを実行し、
taxi_zone_dictionary
という名前の辞書を作成し、S3のCSVファイルから辞書をポピュレートします。ファイルのURLはhttps://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv
です。
LIFETIME
を0に設定すると、S3バケットへの不要なトラフィックを避けるために自動更新が無効になります。その他の場合は、異なる設定にすることができます。詳細については、LIFETIMEを使用した辞書データの更新を参照してください。
- 正しく動作したことを確認します。次のクエリは265行を返すはずです。これはそれぞれの地域1行に相当します:
-
dictGet
関数(またはそのバリエーション)を使用して、辞書から値を取得します。辞書の名前、取得したい値、およびキー(この例ではtaxi_zone_dictionary
のLocationID
列)を渡します。たとえば、次のクエリは、
LocationID
が132であるBorough
を返します(これはJFK空港に対応します):
JFKはQueensにあります。値を取得するための時間はほぼ0です:
dictHas
関数を使用して、辞書にキーが存在するかどうかを確認します。たとえば、次のクエリは1
を返します(ClickHouseでは「true」です):
- 次のクエリは0を返します。なぜなら4567は辞書の
LocationID
の値ではないからです:
- クエリ内で borough の名前を取得するために
dictGet
関数を使用します。例えば:
このクエリは、LaGuardiaまたはJFK空港で終了するタクシーライドの数を各 borough ごとに合計します。結果は次のようになり、ピックアップ地域が不明の旅行がいくつかあることに注意してください:
ジョインを実行する
taxi_zone_dictionary
をtrips
テーブルとジョインするクエリを書きます。
- 前述の空港クエリと同様に機能するシンプルな
JOIN
から始めます:
応答はdictGet
クエリと同じになります:
上記のJOIN
クエリの出力は、dictGetOrDefault
を使用した前のクエリと同じです(ただし、Unknown
値は含まれていません)。背後では、ClickHouseは実際にtaxi_zone_dictionary
辞書のdictGet
関数を呼び出していますが、JOIN
構文はSQL開発者にはより馴染みがあります。
- このクエリは、最も高いチップ金額を持つ1000件の旅行の行を返し、各行を辞書と内部ジョインします:
一般的に、ClickHouseではSELECT *
をあまり使用しないようにしています。実際に必要なカラムだけを取得するべきです。
次のステップ
以下のドキュメントでClickHouseについてさらに学びます:
- ClickHouseの主インデックスの紹介:ClickHouseがクエリの際に関連データを効率的に見つけるためにスパース主インデックスをどのように使用するかを学びます。
- 外部データソースを統合する:ファイル、Kafka、PostgreSQL、データパイプラインなどを含むデータソースの統合オプションを確認します。
- ClickHouseでデータを可視化する:お気に入りのUI/BIツールをClickHouseに接続します。
- SQLリファレンス:データを変換、処理、分析するためにClickHouseで使用可能なSQL関数を参照します。