高度なチュートリアル
概要
ニューヨーク市のタクシーの例データセットを使用して、ClickHouseでデータを取り込んでクエリを実行する方法を学びます。
前提条件
このチュートリアルを完了するには、稼働中のClickHouseサービスにアクセスできる必要があります。手順については、クイックスタートガイドを参照してください。
新しいテーブルを作成する
ニューヨーク市のタクシーデータセットには、チップ額、料金、支払いタイプなどのカラムを含む何百万ものタクシーライドに関する詳細情報が含まれています。このデータを格納するためのテーブルを作成します。
- SQLコンソールに接続します:
- ClickHouse Cloudの場合、ドロップダウンメニューからサービスを選択し、左のナビゲーションメニューからSQLコンソールを選択します。
- セルフマネージドのClickHouseの場合、
https://_hostname_:8443/play
でSQLコンソールに接続します。詳細はClickHouse管理者に確認してください。
default
データベースに次のtrips
テーブルを作成します:
データセットを追加する
テーブルを作成したので、S3のCSVファイルからニューヨーク市のタクシーデータを追加します。
-
次のコマンドは、S3の2つの異なるファイル
trips_1.tsv.gz
とtrips_2.tsv.gz
からtrips
テーブルに約2,000,000行を挿入します: -
INSERT
が完了するのを待ちます。150MBのデータをダウンロードするには少し時間がかかる場合があります。 -
挿入が完了したら、正常に処理されたかを確認します:
データを分析する
データを分析するためにいくつかのクエリを実行します。以下の例を探求するか、独自のSQLクエリを試してみてください。
- 平均チップ額を計算します:
- 乗客数に基づく平均コストを計算します:
- 地域ごとの日別ピックアップ数を計算します:
- 各旅行の長さを分単位で計算し、旅行の長さで結果をグループ化します:
- 各地域の時間帯ごとのピックアップ数を表示します:
- ラガーディアまたはJFK空港への乗車を取得します:
辞書を作成する
辞書は、メモリ内に保存されるキーと値のペアのマッピングです。詳細については、辞書を参照してください。
ClickHouseサービス内のテーブルに関連付けられた辞書を作成します。 このテーブルと辞書は、ニューヨーク市の各地区に対する行を含むCSVファイルに基づいています。
地域は、ニューヨーク市の五つの区(ブロンクス、ブルックリン、マンハッタン、クイーンズ、スタテンアイランド)、およびニューワーク空港(EWR)にマッピングされています。
使用するCSVファイルの抜粋をテーブル形式で示します。ファイル内のLocationID
カラムは、trips
テーブルのpickup_nyct2010_gid
およびdropoff_nyct2010_gid
カラムにマッピングされます:
LocationID | Borough | Zone | service_zone |
---|---|---|---|
1 | EWR | ニューワーク空港 | EWR |
2 | クイーンズ | ジャマイカ湾 | Boro Zone |
3 | ブロンクス | アラートン/ペルハムガーデンズ | Boro Zone |
4 | マンハッタン | アルファベットシティ | Yellow Zone |
5 | スタテンアイランド | アーデンハイツ | 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に設定すると、不要なトラフィックを避けるために自動更新が無効になります。その他のケースでは、異なる設定を行うことができます。詳細については、LIFETIMEを使用した辞書データの更新を参照してください。
-
正常に動作したか確認します。次のクエリは265行、つまり各地域に対して1行を返すはずです:
-
dictGet
関数(またはそのバリエーション)を使用して、辞書から値を取得します。辞書の名前、取得したい値、およびキー(この例ではtaxi_zone_dictionary
のLocationID
カラム)を渡します。例えば、次のクエリは
LocationID
が132のBorough
を返します。これはJFK空港に対応します: -
dictHas
関数を使用して、キーが辞書に存在するかどうかを確認します。例えば、次のクエリは1
(ClickHouseでは「真」を意味します)を返します: -
次のクエリは0を返します。なぜなら4567は辞書の
LocationID
の値ではないからです: -
クエリ内で辞書の名前を取得するために
dictGet
関数を使用します。例:
ジョインを実行する
taxi_zone_dictionary
をtrips
テーブルと結合するクエリをいくつか書きます。
- 前の空港クエリと同様に機能するシンプルな
JOIN
から始めます: - このクエリは、チップ額が最も高い1000件の旅行に対して、各行を辞書と内部結合します:
次のステップ
以下のドキュメントを使ってClickHouseについてさらに学びます:
- ClickHouseにおける主インデックスの導入: ClickHouseがクエリ中に関連するデータを効率的に見つけるためにスパース主インデックスをどのように使用するかを学びます。
- 外部データソースの統合: ファイル、Kafka、PostgreSQL、データパイプラインなど、データソースの統合オプションを確認します。
- ClickHouseでのデータの可視化: お気に入りのUI/BIツールをClickHouseに接続します。
- SQLリファレンス: データを変換、処理、分析するためにClickHouseで使用できるSQL関数をブラウズします。