从 BigQuery 加载数据到 ClickHouse

本指南与 ClickHouse 云兼容，并适用于自托管 ClickHouse v23.5+。

本指南演示如何将数据从 BigQuery 迁移到 ClickHouse。

我们首先将表导出到 Google 的对象存储 (GCS)，然后将这些数据导入到 ClickHouse 云。对于希望从 BigQuery 导出到 ClickHouse 的每个表，都需要重复这些步骤。

将数据导出到 ClickHouse 需要多长时间？

从 BigQuery 导出数据到 ClickHouse 取决于数据集的大小。作为比较，使用本指南将 4TB 的公共以太坊数据集 从 BigQuery 导出到 ClickHouse 大约需要一个小时。

表	行	导出文件	数据大小	BigQuery 导出	时隙时间	ClickHouse 导入
块	16,569,489	73	14.53GB	23 秒	37 分钟	15.4 秒
交易	1,864,514,414	5169	957GB	1 分 38 秒	1 天 8 小时	18 分 5 秒
跟踪	6,325,819,306	17,985	2.896TB	5 分 46 秒	5 天 19 小时	34 分 55 秒
合约	57,225,837	350	45.35GB	16 秒	1 小时 51 分钟	39.4 秒
总计	82.6 亿	23,577	3.982TB	8 分 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;

-- We recommend setting n to correspond to x billion rows. So 5 billion rows, 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 允许每天免费导出高达 50TB 的数据到 GCS。用户只需支付 GCS 存储费用。
• 导出会自动生成多个文件，每个文件最多包含 1GB 的表数据。这对 ClickHouse 很有利，因为它允许并行导入。
• Parquet 作为一种面向列的格式，代表了一种更好的交换格式，因为它本身就是压缩的，并且 BigQuery 导出和 ClickHouse 查询的速度更快

2. 从 GCS 将数据导入到 ClickHouse

导出完成后，我们可以将这些数据导入到 ClickHouse 表中。您可以使用 ClickHouse SQL 控制台 或 clickhouse-client 执行以下命令。

您必须首先在 ClickHouse 中 创建您的表

-- If your BigQuery table contains a column of type STRUCT, you must enable this setting
-- to map that column to a ClickHouse column of type 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 节点，则可以跳过此步骤

SET parallel_distributed_insert_select = 1;

最后，我们可以使用 INSERT INTO SELECT 命令 将数据从 GCS 插入到我们的 ClickHouse 表中，该命令根据 SELECT 查询的结果将数据插入到表中。

要检索要 INSERT 的数据，我们可以使用 s3Cluster 函数 从我们的 GCS 存储桶中检索数据，因为 GCS 与 Amazon S3 互操作。如果您只有一个 ClickHouse 节点，则可以使用 s3 表函数 而不是 s3Cluster 函数。

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 密钥。

导出可空列时使用 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 时遇到问题，请随时联系我们，邮箱地址为 [email protected]。