Skip to main content
Edit this page

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 秒37 分15.4 秒
transactions1,864,514,4145169957GB1 分 38 秒1 日 8 時間18 分 5 秒
traces6,325,819,30617,9852.896TB5 分 46 秒5 日 19 時間34 分 55 秒
contracts57,225,83735045.35GB16 秒1 時間 51 分39.4 秒
合計8.26 billion23,5773.982TB8 分 3 秒> 6 日 5 時間53 分 45 秒

1. GCS へのテーブルデータのエクスポート​

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

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

-- n を x billion 行に設定することを推奨します。例えば、5 billion 行なら 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 のテーブルデータに制限されます。これにより、Import 時に並列化が可能になるので ClickHouse にとって有益です。
  • Parquet は列指向フォーマットのため、圧縮されており、BigQuery がより速くエクスポートし、ClickHouse がより速くクエリ できるため、より良い交換フォーマットになります。

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

エクスポートが完了したら、このデータを ClickHouse テーブルにインポートすることができます。以下のコマンドを実行するには、ClickHouse SQL コンソール または 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;

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

データを 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 を使用

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

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

3. データエクスポートの成功確認​

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

SELECT * FROM mytable limit 10;

他の BigQuery テーブルをエクスポートするには、上記の手順を追加の各テーブルに対して繰り返してください。

さらに詳しい情報とサポート​

このガイドに加えて、ClickHouse を使用して BigQuery を高速化し増分インポートを扱う方法を示すブログ記事もお勧めです。

BigQuery から ClickHouse へのデータ転送に問題がある場合は、support@clickhouse.com までお問い合わせください。