BigQueryからClickHouse Cloudへの移行

なぜClickHouse Cloudを選ぶべきか？

TL;DR: ClickHouseは、現代のデータ分析においてBigQueryよりも速く、安価で、より強力だからです。

BigQueryからClickHouse Cloudへデータを読み込む

データセット

BigQueryからClickHouse Cloudへの典型的な移行を示すためのサンプルデータセットとして、Stack Overflowデータセットを使用します。このデータセットには、2008年から2024年4月までにStack Overflowで発生したすべての post、vote、user、comment、および badge が含まれています。このデータのBigQueryスキーマは以下に示されています。

このデータセットをBigQueryインスタンスに格納して移行手順をテストしたいユーザーのために、GCSバケットにParquet形式でこれらのテーブルのデータを提供しています。BigQueryでテーブルを作成しデータをロードするDDLコマンドはこちらから入手できます。

データの移行

BigQueryとClickHouse Cloud間でのデータの移行は、主に2つのワークロードタイプに分類されます：

• 初回の一括ロードと定期的な更新 - 初回データセットを移行し、定期的な更新を設定された間隔で行う必要があります（例：毎日）。ここでの更新は、変更された行を再送信することで処理されます（例：日付など比較に使用できるカラムによって特定されます）。削除はデータセットの完全な定期的再読み込みによって処理されます。
• リアルタイムレプリケーションまたはCDC - 初回データセットを移行する必要があり、このデータセットの変更は数秒の遅延でClickHouseに反映される必要があります。これは効果的にChange Data Capture (CDC)プロセスであり、BigQueryのテーブルとClickHouseを同期させる必要があります。つまり、BigQueryテーブルでの挿入、更新、および削除を同等のClickHouseテーブルに適用する必要があります。

Google Cloud Storage (GCS)を介した一括ロード

BigQueryは、Googleのオブジェクトストレージ（GCS）へのデータのエクスポートをサポートしています。サンプルデータセットについては：

1. 7つのテーブルをGCSにエクスポートします。そのためのコマンドはこちらで入手できます。

2. データをClickHouse Cloudにインポートします。その際、gcsテーブル関数を使用します。DDLおよびインポートクエリはこちらで入手できます。ClickHouse Cloudインスタンスは複数のコンピュートノードで構成されているため、gcsテーブル関数の代わりに、s3Clusterテーブル関数を使用しています。この関数はgcsバケットでも動作し、ClickHouse Cloudサービスのすべてのノードを利用してデータを並行してロードすることができます。

このアプローチにはいくつかの利点があります：

• BigQueryのエクスポート機能はデータのサブセットをエクスポートするためのフィルタをサポートしています。
• BigQueryは、Parquet、Avro、JSON、CSV形式およびいくつかの圧縮タイプへのエクスポートをサポートしており、すべてClickHouseに対応しています。
• GCSはオブジェクトライフサイクル管理をサポートしており、ClickHouseにエクスポートおよびインポートされたデータを指定された期間後に削除することができます。
• Googleは、1日あたり最大50TBをGCSに無料でエクスポートすることを許可しています。ユーザーはGCSのストレージにのみ料金を支払います。
• エクスポートは自動的に複数のファイルを生成し、各ファイルを最大1GBのテーブルデータに制限します。これはClickHouseにとって有益で、インポートを並行化することができます。

次の例を試す前に、ユーザーはエクスポートに必要な権限およびローカリティの推奨事項を確認し、エクスポートおよびインポートのパフォーマンスを最大化することをお勧めします。

スケジュールクエリを介したリアルタイムレプリケーションまたはCDC

Change Data Capture（CDC）は、2つのデータベース間でテーブルを同期させるプロセスです。更新と削除がリアルタイムで処理される必要がある場合、これはかなり複雑です。一つのアプローチは、BigQueryのスケジュールクエリ機能を使用して定期的なエクスポートをスケジュールすることです。ClickHouseへのデータ挿入にある程度の遅延を受け入れられるのであれば、このアプローチは実装とメンテナンスが容易です。具体例としてはこのブログ投稿があります。

スキーマの設計

Stack Overflowデータセットには、関連するテーブルがいくつか含まれています。最初にプライマリテーブルの移行に焦点を当てることをお勧めします。これは必ずしも最も大きなテーブルではなく、むしろ最も多くの分析クエリが受信されると予想されるテーブルです。これにより、主要なClickHouseの概念に慣れることができます。このテーブルは、追加のテーブルが追加されることでClickHouseの機能を最大限に活用し、最適なパフォーマンスを得るために再モデル化が必要となる場合があります。このモデリングプロセスについては、データモデリングドキュメントで詳しく説明しています。

この原則に従って、主要な posts テーブルに焦点を当てます。このテーブルのBigQueryスキーマは以下に示されています：

型の最適化

ここで説明されているプロセスを適用すると、以下のスキーマが得られます：

このテーブルに簡単なINSERT INTO SELECTを使用して、gcsからエクスポートされたデータを読み込むことができます。ClickHouse Cloudでは、gcs互換のs3Clusterテーブル関数を使用して、複数のノードでのロードを並行化できます：

新しいスキーマでは、nullを保持していません。上記の挿入は、暗黙的に各型のデフォルト値（整数の場合は0、文字列の場合は空の値）に変換されます。ClickHouseはまた、あらゆる数値をそのターゲット精度に自動的に変換します。

ClickHouseの主キーはどのように異なるか？

ここで説明されているように、BigQueryと同様に、ClickHouseはテーブルの主キー列値の一意性を強制しません。

BigQueryのクラスターと同様に、ClickHouseのテーブルのデータは主キー列によってディスクに順序付けられます。このソート順は、クエリオプティマイザによって利用され、再ソートを防ぎ、結合に必要なメモリ使用量を最小化し、制限句のショートサーキットを可能にします。 BigQueryとは異なり、ClickHouseは主キー列値に基づいてsparseな主インデックスを自動的に作成します。このインデックスは、主キー列にフィルタを含むすべてのクエリの実行を高速化するために使用されます。具体的には：

• メモリとディスクの効率は、ClickHouseがしばしば使用されるスケールでは最も重要です。データは、パーツとして知られるチャンクでClickHouseテーブルに書き込まれ、バックグラウンドでのマージに関するルールが適用されます。ClickHouseでは、各パーツには独自の主インデックスがあります。パーツがマージされると、マージされたパーツの主インデックスもマージされます。これらのインデックスは、各行ごとには作成されません。代わりに、パーツの主インデックスは、行のグループごとに1つのインデックスエントリを持ちます。この技術はスパースインデクシングと呼ばれます。
• スパースインデクシングが可能なのは、ClickHouseが指定されたキーによってディスク上の行を順序付けて格納しているからです。単一の行を直接見つけるのではなく（B-Treeベースのインデックスのように）、スパース主インデックスは、インデックスエントリ上での二分探索を介してクエリに一致する可能性のある行のグループを迅速に特定できるようにします。特定された可能性のある一致行のグループは、並行してClickHouseエンジンにストリーミングされて一致を見つけます。このインデックス設計により、主インデックスは小さく（メインメモリに完全に収まる）なりながら、クエリの実行時間を大幅に短縮することができます。特にデータ分析のユースケースで典型的な範囲クエリに対しては、さらにその効果が顕著です。詳細については、この詳細なガイドをお勧めします。

ClickHouseで選択された主キーは、インデックスだけでなく、データがディスクに書き込まれる順序も決定します。これにより、圧縮レベルに劇的に影響を与え、クエリパフォーマンスに影響を与えることがあります。ほとんどのカラムの値が連続して書き込まれる順序を引き起こす順序キーは、選択された圧縮アルゴリズム（およびコーデック）がデータをより効果的に圧縮できるようにします。

テーブル内のすべてのカラムは、指定された順序キーの値に基づいてソートされます。これには、それ自体に含まれているかどうかにかかわらず。たとえば、CreationDateがキーとして使われる場合、他のすべてのカラムの値の順序はCreationDateカラムの値の順序に対応します。複数の順序キーを指定することができます。これにより、SELECTクエリのORDER BY句と同じセマンティクスで順序付けられます。

順序キーの選択

順序キーを選定する際の考慮事項とステップについては、postsテーブルを例にこちらをご覧ください。

データモデリング技術

BigQueryから移行するユーザーは、ClickHouseにおけるデータモデリングガイドを読むことをお勧めします。このガイドでは、同じStack Overflowデータセットを使用し、ClickHouseの機能を活用した複数のアプローチを探ります。

パーティション

BigQueryユーザーは、テーブルをパーティションに分割することで大規模データベースのパフォーマンスと管理性を向上させるテーブルパーティショニングの概念に慣れているでしょう。このパーティショニングは、指定されたカラムに基づいて範囲（例：日付）を使用するか、定義済みリスト、またはキーにハッシュを使用して達成できます。これにより、管理者は日付範囲や地理的位置などの特定の基準に基づいてデータを整理できます。

パーティショニングは、パーティションプルーニングを通じてより迅速なデータアクセスを可能にし、効率的なインデックス作成によってクエリパフォーマンスを改善するのに役立ちます。また、個々のパーティションではなく、テーブル全体ではなく、バックアップやデータ削除などのメンテナンスタスクを実施できるようになります。さらに、パーティショニングは、複数のパーティション間で負荷を分散させることで、BigQueryデータベースのスケーラビリティを大幅に向上させることができます。

ClickHouseでは、テーブルが最初に定義される際にPARTITION BY句を介してパーティショニングが指定されます。この句には、いかなるカラムのSQL式も含めることができ、その結果が行が送信されるパーティションを定義します。

データパーツは、ディスク上の各パーティションに論理的に関連づけられ、独立してクエリを実行できます。以下の例では、CreationDateを使用して年ごとにpostsテーブルをパーティショニングします。この式は、ClickHouseへの行の挿入において、各行に対して評価されます。その結果、新たに追加されたデータパーツがそのパーティションに属する形でルーティングされます。

アプリケーション

ClickHouseのパーティショニングには、BigQueryと同様のアプリケーションがありますが、いくつかの微妙な違いがあります。具体的には：

• データ管理 - ClickHouseでは、ユーザーは主にパーティショニングをデータ管理機能と考えるべきです。キーに基づいて論理的にデータを分離することで、各パーティションは独立して操作できます（例：削除）。これにより、ユーザーはパーティションを効率的に移動し、サブセットをストレージ階層間で移動したり、データを期限切れにしたり/クラスタから効率的に削除したりできます。例えば、2008年の投稿を削除する操作は以下のように行います：

• クエリ最適化 - パーティションはクエリパフォーマンスを助ける可能性がありますが、これはアクセスパターンに大きく依存します。クエリが只に数個のパーティション（理想的には1つ）のみを対象にする場合、パフォーマンスは向上する可能性があります。しかし、パーティショニングキーが主キーに含まれている場合、その利点はほとんど存在しなくなります。特に多くのパーティションを対象とする必要があるクエリは、パーティショニングされていない場合よりもパフォーマンスが低下する可能性があります。また、パーティショニングは、GROUP BYクエリの最適化にも利用できる可能性があります。

推奨事項

ユーザーは、データ管理技術としてパーティショニングを考慮すべきです。特に、時系列データを扱う場合、クラスターからデータを期限切れにする必要がある場合に理想的です。例えば、最も古いパーティションは単純に削除することができます。

重要: パーティショニングキーの式が高いカーディナリティのセットを生成しないことを確認してください。すなわち、100以上のパーティションを作成することは避けるべきです。例えば、クライアント識別子や名前のような高いカーディナリティのカラムでデータをパーティショニングしないでください。代わりに、ORDER BY式の最初のカラムにクライアント識別子または名前を指定します。

内部的に、ClickHouseは挿入されたデータに対してパーツを作成します。データが挿入されるにつれて、パーツの数が増加します。クエリパフォーマンスが低下するほどの過剰なパーツの数を回避するために、パーツはバックグラウンドの非同期プロセスで統合されます。事前に設定された制限を超えると、ClickHouseは挿入時に"too many parts"エラーをスローします。これは通常の操作では発生せず、ClickHouseが正しく設定されていないか、誤って使用されている場合にのみ発生します（例：小さな挿入が多すぎる）。パーツは独立してパーティションごとに作成されるため、パーティションの数が増えるとパーツの数も増加します。高カーディナリティのパーティショニングキーは、このエラーを引き起こす可能性があるため、避けるべきです。

マテリアライズドビューとプロジェクション

ClickHouseのプロジェクションの概念により、ユーザーはテーブルに複数のORDER BY句を指定できます。

ClickHouseデータモデリングでは、マテリアライズドビューを使用して、集計を事前計算し、行を変換し、異なるアクセスパターンに対するクエリを最適化する方法を探ります。この場合、マテリアライズドビューが異なる順序キーを持つターゲットテーブルに行を送信する例を提供しました。

以下のクエリを考えます。

このクエリでは、UserIdが順序キーでないため、90百万行すべてをスキャンする必要があります（迅速に行われるとはいえ）。以前は、PostIdのルックアップとして動作するマテリアライズドビューを使用してこの問題を解決しました。同じ問題はプロジェクションでも解決できます。以下のコマンドでは、ORDER BY user_idのためのプロジェクションを追加します。

注意: 最初にプロジェクションを作成し、次にそれをマテリアライズする必要があります。後者のコマンドは、データがディスクに2つの異なる順序で2回保存されることを引き起こします。データ作成時にプロジェクションを定義することもできます。そうすることで、データが挿入されるたびに自動的に維持されます。

プロジェクションをALTERコマンドで作成した場合、作成時は非同期で、MATERIALIZE PROJECTIONコマンドが発行された際に作成されます。ユーザーは、次のクエリでこの操作の進捗を確認し、is_done=1になるまで待つことができます。

上記のクエリを繰り返すと、ストレージの追加でパフォーマンスが大幅に改善されていることがわかります。

EXPLAINコマンドを使用して、このクエリがプロジェクションを使用したことも確認できます。

プロジェクションを使用するタイミング

プロジェクションは、新しいユーザーにとって魅力的な機能で、データが挿入される際に自動的に維持されます。さらに、クエリは単一のテーブルに送信され、プロジェクションが可能な限り活用され、応答時間を短縮します。

これは、ユーザーが適切な最適化されたターゲットテーブルを選択する必要があるマテリアライズドビューとは対照的です。これにより、ユーザーアプリケーションに対する重要性が増し、クライアント側の複雑さも増します。

これらの利点にもかかわらず、プロジェクションにはいくつかの固有の制限があります。ユーザーはこれらを把握し、慎重に展開する必要があります：

• プロジェクションは、ソーステーブルと（隠された）ターゲットテーブルに異なるTTLを使用することを許可しません。マテリアライズドビューは異なるTTLを許可します。
• プロジェクションは、（隠された）ターゲットテーブルに対してoptimize_read_in_orderを現在サポートしていません。
• プロジェクションを持つテーブルに対しては、軽量削除や更新がサポートされていません。
• マテリアライズドビューはチェーン化できます。1つのマテリアライズドビューのターゲットテーブルが別のマテリアライズドビューのソーステーブルになることが可能ですが、これはプロジェクションでは不可能です。
• プロジェクションは結合をサポートしませんが、マテリアライズドビューはサポートします。
• プロジェクションはフィルタ (WHERE句)をサポートしませんが、マテリアライズドビューはサポートします。

プロジェクションを使用することをお勧めするのは以下の場合です：

• データの完全な再配置が必要な場合。プロジェクションの式は理論的にはGROUP BYを使うことができますが、集計を維持する場合はマテリアライズドビューの方が効果的です。クエリオプティマイザが、単純な再配置を利用したプロジェクションを利用する可能性が高くなります。つまり、SELECT * ORDER BY xとすることです。この式で列のサブセットを選択して、ストレージフットプリントを削減できます。
• ユーザーが、ストレージフットプリントの増加とデータの二重書き込みのオーバーヘッドを快く受け入れられる場合。挿入速度への影響をテストし、ストレージのオーバーヘッドを評価してください。

BigQueryクエリをClickHouseで書き換える

以下に、BigQueryとClickHouseを比較した例のクエリを示します。このリストは、ClickHouse機能を活用してクエリを大幅に簡素化する方法を示すことを目的としています。ここでの例は、完全なStack Overflowデータセット（2024年4月まで）を使用しています。

（質問が10件以上の）最も多くのビューを受けたユーザー:

BigQuery

ClickHouse

最も多くのビューを受けたタグは:

BigQuery

ClickHouse

集計関数

可能な限り、ユーザーはClickHouseの集計関数を活用すべきです。以下に、argMax関数を使用して、毎年最も視聴された質問を計算する例を示します。

BigQuery

ClickHouse

Conditionals and Arrays

条件付きおよび配列関数は、クエリを大幅に簡素化します。以下のクエリは、2022年から2023年にかけて最大の割合で増加したタグ（出現回数が10000を超えるもの）を計算します。条件付き、配列関数の活用、HAVINGおよびSELECT句でのエイリアス再利用のおかげで、次の ClickHouse クエリは簡潔です。

BigQuery

ClickHouse

これで、BigQuery から ClickHouse に移行するユーザー向けの基本ガイドは終了です。BigQuery から移行するユーザーは、ClickHouse でのデータモデリング ガイドを読むことをお勧めします。これにより、ClickHouse の高度な機能についてさらに学ぶことができます。