从 BigQuery 导出到 ClickHouse

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

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

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

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

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

表	行数	已导出的文件	数据大小	BigQuery 导出	Slot 时间	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 秒

将表数据导出到 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 查询的速度更快

从 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 值都将被其各自列的默认值替换，如果已指定这些默认值。

测试成功的数据导出

要测试您的数据是否正确插入，只需在新表上运行一个 SELECT 查询

SELECT * FROM mytable LIMIT 10;

要导出更多 BigQuery 表，只需对每个其他表重复上述步骤即可。

进一步阅读和支持

除了本指南，我们还建议阅读我们的博客文章，该文章展示了 如何使用 ClickHouse 加速 BigQuery 以及如何处理增量导入。

如果您在将数据从 BigQuery 传输到 ClickHouse 时遇到问题，请随时通过 [email protected] 与我们联系。