メインコンテンツへスキップ
メインコンテンツへスキップ

BigQuery から ClickHouse へのデータ読み込み

このガイドは ClickHouse Cloud およびセルフホスト型 ClickHouse v23.5+ に対応しています。

このガイドでは、BigQuery から ClickHouse にデータを移行する方法を説明します。

まずテーブルを Google のオブジェクトストレージ (GCS) にエクスポートし、その後そのデータを ClickHouse Cloud にインポートします。これらの手順は、BigQuery から ClickHouse に移行したい各テーブルごとに繰り返す必要があります。

ClickHouse へのデータエクスポートにはどのくらい時間がかかりますか？​

BigQuery から ClickHouse へのデータエクスポートにかかる時間は、データセットのサイズによって異なります。参考として、このガイドを使用した場合、4TB のパブリック Ethereum データセットを BigQuery から ClickHouse にエクスポートするのに、約 1 時間かかります。

テーブル行数エクスポートされたファイル数データサイズBigQuery エクスポートスロット時間ClickHouse インポート
blocks16,569,4897314.53GB23 secs37 min15.4 secs
transactions1,864,514,4145169957GB1 min 38 sec1 day 8hrs18 mins 5 secs
traces6,325,819,30617,9852.896TB5 min 46 sec5 days 19 hr34 mins 55 secs
contracts57,225,83735045.35GB16 sec1 hr 51 min39.4 secs
合計8.26 billion23,5773.982TB8 min 3 sec> 6 days 5 hrs53 mins 45 secs

テーブルデータを GCS にエクスポートする​

この手順では、BigQuery SQL ワークスペース を使用して SQL 文を実行します。ここでは、EXPORT DATA ステートメントを使用して、mytable という BigQuery テーブルを GCS のバケットにエクスポートします。

DECLARE export_path STRING;
DECLARE n INT64;
DECLARE i INT64;
SET i = 0;

-- n は x 十億行に対応する値に設定することを推奨します。例えば 50 億行の場合は n = 5 とします
SET n = 100;

WHILE i < n DO
  SET export_path = CONCAT('gs://mybucket/mytable/', i,'-*.parquet');
  EXPORT DATA
    OPTIONS (
      uri = export_path,
      format = 'PARQUET',
      overwrite = true
    )
  AS (
    SELECT * FROM mytable WHERE export_id = i
  );
  SET i = i + 1;
END WHILE;

上記のクエリでは、BigQuery テーブルを Parquet データ形式 にエクスポートしています。また、uri パラメータには * 文字を含めています。これにより、エクスポートするデータが 1GB を超える場合に、出力が複数ファイルに分割され、それぞれに数値の接尾辞が連番で付与されます。

この方法には、いくつかの利点があります。

  • Google は、1 日あたり最大 50TB までを無料で GCS にエクスポートできるようにしています。ユーザーは GCS ストレージ分のみを支払うだけで済みます。
  • エクスポートは自動的に複数のファイルを生成し、それぞれを最大 1GB のテーブルデータに制限します。これは、インポートを並列化できるため ClickHouse にとって有利です。
  • 列指向フォーマットである Parquet は、標準で圧縮されており、BigQuery によるエクスポートおよび ClickHouse によるクエリが高速であるため、より優れたデータ交換形式です。

GCS から ClickHouse へのデータインポート​

エクスポートが完了したら、このデータを ClickHouse のテーブルにインポートできます。以下のコマンドを実行するには、ClickHouse SQL console か clickhouse-client を使用できます。

まず ClickHouse でテーブルを作成しておく必要があります。

-- BigQuery のテーブルに STRUCT 型の列が含まれている場合、この設定を有効化する必要があります
-- その列を ClickHouse の Nested 型の列にマッピングするためです
SET input_format_parquet_import_nested = 1;

CREATE TABLE default.mytable
(
        `timestamp` DateTime64(6),
        `some_text` String
)
ENGINE = MergeTree
ORDER BY (timestamp);

テーブルを作成したら、クラスタ内に複数の ClickHouse レプリカがある場合は、エクスポート処理を高速化するために parallel_distributed_insert_select 設定を有効にしてください。ClickHouse ノードが 1 つだけの場合は、この手順はスキップして構いません。

SET parallel_distributed_insert_select = 1;

最後に、INSERT INTO SELECT コマンド を使用して、GCS から ClickHouse テーブルにデータを挿入します。このコマンドは、SELECT クエリの結果に基づいてテーブルにデータを挿入します。

INSERT するデータを取得するには、s3Cluster 関数 を使用して GCS バケットからデータを読み取ります。GCS は Amazon S3 と相互運用可能なため、これが利用できます。ClickHouse ノードが 1 台だけの場合は、s3Cluster 関数の代わりに s3 テーブル関数 を使用できます。

INSERT INTO mytable
SELECT
    timestamp,
    ifNull(some_text, '') AS some_text
FROM s3Cluster(
    'default',
    'https://storage.googleapis.com/mybucket/mytable/*.parquet.gz',
    '<ACCESS_ID>',
    '<SECRET>'
);

上記のクエリで使用されている ACCESS_ID と SECRET は、GCS バケットに関連付けられた HMAC キー です。

注記
NULL を取り得るカラムをエクスポートする場合は ifNull を使用する

上記のクエリでは、ClickHouse テーブルにデフォルト値付きでデータを挿入するために、some_text カラムに対して ifNull 関数 を使用しています。ClickHouse 側のカラムを Nullable にすることもできますが、パフォーマンスに悪影響を与える可能性があるため推奨されません。

別の方法として、SET input_format_null_as_default=1 を設定すると、対応するカラムにデフォルト値が指定されている場合、欠損値や NULL 値はそれぞれのカラムのデフォルト値で置き換えられます。

データ エクスポートの成功を確認する​

データが正しく挿入されたかを確認するには、新しいテーブルに対して SELECT クエリを実行してみてください。

SELECT * FROM mytable LIMIT 10;

追加の BigQuery テーブルをエクスポートするには、各テーブルごとに上記の手順を繰り返します。

参考資料とサポート​

このガイドに加えて、ClickHouse を使って BigQuery を高速化する方法と、インクリメンタルインポートを処理する方法 を解説したブログ記事もあわせてご覧いただくことをおすすめします。

BigQuery から ClickHouse へのデータ転送に問題が発生した場合は、[email protected] までお気軽にお問い合わせください。