BigQuery から ClickHouse Cloud への移行

Why use ClickHouse Cloud over BigQuery?

TLDR: ClickHouseはモダンデータ分析において、BigQueryよりも速く、安く、より強力であるためです。

Loading data from BigQuery to ClickHouse Cloud

Dataset

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

このデータセットをテスト移行手順に従ってBigQueryインスタンスにポピュレートしたいユーザーのために、GCSバケットにParquet形式でテーブルのデータを提供しており、BigQueryでテーブルを作成しロードするためのDDLコマンドはこちらで入手可能です。

Migrating data

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

• 初期バルクロードと定期的な更新 - 初期データセットは移行され、例として定期的に（例えば、日次）更新されます。ここでの更新は、変更された行を再送信することで処理されます - 比較に使用できるカラム（例えば、日付）で識別されます。削除はデータセットの完全な定期的リロードによって処理されます。
• リアルタイム複製またはCDC - 初期データセットは移行されなければなりません。このデータセットの変更は、数秒の遅延でClickHouseに反映される必要があります。これは基本的にChange Data Capture (CDC)プロセスであり、BigQuery内のテーブルはClickHouseと同期される必要があります。すなわち、BigQueryテーブル内の挿入、更新、削除は、ClickHouseの同等のテーブルに適用されます。

Bulk loading via 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は、GCSに1日に最大50TBを無料でエクスポートできることを許可しています。ユーザーはGCSストレージの料金のみを支払います。
• エクスポートは自動的に複数のファイルを生成し、各ファイルを最大1GBのテーブルデータに制限します。これはClickHouseにとって有利であり、インポートを並列処理できるようにします。

以下の例を試す前に、ユーザーにはエクスポートに必要な権限とローカリティの推奨事項を確認することをお勧めします。

Real-time replication or CDC via scheduled queries

Change Data Capture (CDC)とは、2つのデータベース間でテーブルを同期するプロセスです。更新や削除をリアルタイムで処理するには、かなりの複雑さが伴います。一つのアプローチは、BigQueryのスケジュールクエリ機能を使用して定期的なエクスポートをスケジュールすることです。ClickHouseへのデータ挿入時にある程度の遅延を受け入れられるのであれば、このアプローチは実装と維持が簡単です。例はこのブログ記事に記載されています。

Designing Schemas

Stack Overflowデータセットには関連する複数のテーブルが含まれています。まず、主なテーブルの移行に焦点を当てることをお勧めします。このテーブルが必ずしも最大のテーブルである必要はありませんが、最も分析クエリを受けると予想されるテーブルであるのが理想です。これにより、主要なClickHouseの概念に慣れることができます。このテーブルは、追加のテーブルを追加することでClickHouseの機能を完全に活用し、最適なパフォーマンスを得るために再モデリングが必要になるかもしれません。このモデリングプロセスについては、データモデリングドキュメントで探ります。

この原則に従い、主なpostsテーブルに焦点を当てます。このテーブルのBigQueryスキーマは以下の通りです：

Optimizing types

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

このテーブルにデータをポピュレートするためには、GCSからエクスポートされたデータを読み取る簡単なINSERT INTO SELECTを使用できます。gcsテーブル関数を使用すると、ClickHouse Cloudでもgcs互換のs3Clusterテーブル関数を使用して、複数のノードにわたってロードを並列化できます：

新しいスキーマでは、nullを保持しません。上記の挿入は、各タイプのデフォルト値に暗黙的に変換します - 整数は0、文字列は空値に変換されます。ClickHouseはまた、自動的に数値をターゲット精度に変換します。

How are ClickHouse Primary keys different?

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

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

• メモリとディスク効率は、ClickHouseが使用される規模にとって極めて重要です。データは、パーツと呼ばれるチャンクでClickHouseテーブルに書き込まれ、バックグラウンドでパーツをマージするためのルールが適用されます。ClickHouseでは、各パートに独自の主インデックスがあります。パーツがマージされると、マージされたパートの主インデックスもマージされます。これらのインデックスは各行のために構築されるわけではありません。代わりに、パートの主インデックスは行のグループ毎に1つのインデックスエントリを持ち、この技術はスパースインデキシングと呼ばれます。
• スパースインデキシングが可能なのは、ClickHouseが指定されたキーによってディスク上にパートの行を順序付けて保存するためです。単一の行を直接特定するのではなく（Bツリーに基づいたインデックスのように）、スパースインデックスにより、インデックスエントリのバイナリ検索を介して、クエリに一致する可能性のある行のグループを迅速に特定できます。その後、特定された可能性のある一致する行のグループは、並列でClickHouseエンジンにストリーミングされ、一致を見つけます。このインデックス設計により、主インデックスは小さく保たれ（メインメモリに完全に収まる）、特にデータ分析用の範囲クエリに通常見られるクエリ実行時間を大幅に短縮します。詳細についてはこの詳細なガイドをお勧めします。

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

テーブル内のすべての列は、指定された順序付けキーの値に基づいてソートされます。キー自体に含まれているかどうかに関わらず、例えば、CreationDateがキーとして使用される場合、すべての他の列の値の順序はCreationDate列の値の順序に対応します。複数の順序付けキーを指定することができます - これはSELECTクエリのORDER BY句と同じ意味の順序を設定します。

Choosing an ordering key

順序付けキーを選択する際の考慮事項と手順については、posts テーブルを例としてこちらを参照してください。

Data modeling techniques

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

Partitions

BigQueryユーザーは、テーブルをパーティショニングして、大規模データベースのパフォーマンスや管理性を向上させる概念に慣れているでしょう。これにより、テーブルが「パーティション」と呼ばれるより小さく管理しやすい部分に分割されます。このパーティショニングは、指定された列（例：日付）に基づく範囲、定義されたリスト、またはキーにハッシュを適用することで実現できます。これにより、管理者は特定の基準に基づいてデータを整理できます（例：日付範囲や地理的位置）。

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

ClickHouseでは、テーブルが初期に定義される際にPARTITION BY句を指定します。この句は、任意のカラムに対するSQL式を含むことができ、その結果が行が送られるパーティションを定義します。

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

Applications

ClickHouseにおけるパーティショニングの適用は、BigQueryと似ていますが、いくつかの微妙な違いがあります。具体的には：

• データ管理 - ClickHouseでは、ユーザーは主にパーティショニングをデータ管理機能とみなすべきです。キーに基づいてデータを論理的に分けることで、各パーティションに独立した操作（例：削除）が可能になります。これにより、パーティションを効率的に移動させ、ストレージ層間でサブセットを移動させることができます。例えば、以下のクエリは2008年の投稿を削除します：

• クエリ最適化 - パーティショニングはクエリパフォーマンスに貢献することができますが、これはアクセスパターンに大きく依存します。クエリが少数のパーティション（理想的には1つ）のみを対象とする場合、パフォーマンスが改善される可能性があります。これは通常、パーティショニングキーが主キーに含まれていない場合にのみ有用であり、それを基にフィルタリングを行います。ただし、多くのパーティションをカバーする必要のあるクエリは、パーティショニングを使用しない場合よりもパフォーマンスが低下する可能性があります（パーティショニングの結果、より多くのパーツが生じる可能性があるため）。特定のパーティションを対象とすることの利点は、パーティショニングキーが既に主キーの早期エントリーである場合、ほとんど存在しません。パーティショニングは、各パーティションの値がユニークである場合にGROUP BYクエリの最適化に使用できます。しかし一般的には、ユーザーは主キーが最適化されていることを確認し、アクセスパターンが特定の予測可能なサブセットにアクセスする特例のケースでのみパーティショニングをクエリ最適化技術として検討すべきです（例：日別のパーティションで、ほとんどのクエリが前日）。

Recommendations

ユーザーは、パーティショニングをデータ管理技術とみなすべきです。これは時間系列データを操作する際に、クラスタからデータを削除する必要がある場合に最適です（例：最も古いパーティションは単に削除できます）。

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

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

Materialized views vs projections

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

ClickHouseのデータモデリングでは、ClickHouseでマテリアライズドビューを使用して集計を事前計算し、行を変換し、異なるアクセスパターンに対するクエリを最適化する方法について探ります。後者に関しては、引数のある例を提供しており、マテリアライズドビューが、挿入を受け取る元のテーブルとは異なる順序付けキーを持つターゲットテーブルに行を送信します。

例えば、以下のクエリを考えてみましょう：

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

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

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

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

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

When to use projections

プロジェクションは新しいユーザーにとって魅力的な機能ですが、データが挿入されると自動的に維持されます。さらに、クエリはプロジェクションが可能な場合に利用される単一のテーブルに送信できます。

これは、マテリアライズドビューとは対照的で、ユーザーは適切な最適化ターゲットテーブルを選択するか、フィルターに応じてクエリを再作成する必要があります。これはユーザーのアプリケーションに対してより大きな重点を置き、クライアント側の複雑さを増加させます。

これらの利点にもかかわらず、プロジェクションにはユーザーが認識すべき固有の制限がありますので、慎重に適用する必要があります：

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

プロジェクションを使用する場合：

• データの完全な並べ替えが必要とされる場合。プロジェクション内の式は理論的にはGROUP BYを使用することができますが、マテリアライズドビューは集計を維持するためにより効果的です。また、クエリオプティマイザーはシンプルな並べ替えを使用するプロジェクションをより多く活用する傾向があります。すなわち、SELECT * ORDER BY xのように。ユーザーはこの式でストレージフットプリントを軽減するために列のサブセットを選ぶことができます。
• ユーザーが、ストレージフットプリントの増加とデータを2回書くことのオーバーヘッドを受け入れられる場合。挿入速度への影響をテストし、ストレージのオーバーヘッドを評価する必要があります。

Rewriting BigQuery queries in ClickHouse

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

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

BigQuery

ClickHouse

どのタグが最も多くのビューを受けるか:

BigQuery

ClickHouse

Aggregate functions

可能な場合は、ClickHouseの集約関数を利用すべきです。以下に、argMax関数を使用して、各年で最もビューされた質問を計算する例を示します。

BigQuery

ClickHouse

Conditionals and Arrays

条件および配列関数を使用すると、クエリが大幅に簡素化されます。以下のクエリは、2022年から2023年にかけて最大の割合の増加を持つタグ（10000回以上出現したタグ）を計算します。以下のClickHouseクエリは、条件、配列関数、およびHAVINGおよびSELECT句で別名を再利用する能力のおかげで簡潔です。

BigQuery

ClickHouse

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