将数据从 BigQuery 加载到 ClickHouse

如何将数据从 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 秒

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 十亿行。例如 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 允许每天免费将最多 50TB 的数据导出到 GCS。用户只需支付 GCS 存储费用。
   • 导出会自动生成多个文件，并将每个文件的表数据限制在最多 1GB。这对 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 节点，则可以跳过此步骤：

   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

   在上述查询中，我们对 some_text 列使用了 ifNull 函数，以便在向 ClickHouse 表中插入数据时使用默认值。你也可以将 ClickHouse 中的列设为 Nullable，但不建议这样做，因为可能会对性能造成负面影响。

   或者，你也可以 SET input_format_null_as_default=1，这样任何缺失值或 NULL 值都会被替换为其对应列的默认值 (如果已为这些列指定默认值) 。

3. ​

   测试数据是否已成功导出

   若要验证数据是否已成功插入，只需对新表运行一条 SELECT 查询：

   SELECT * FROM mytable LIMIT 10;
   

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

​

延伸阅读与支持

除本指南外，我们还建议阅读我们的这篇博客文章，其中介绍了如何使用 ClickHouse 加速 BigQuery，以及如何处理增量导入。

如果您在将数据从 BigQuery 传输到 ClickHouse 的过程中遇到问题，欢迎随时通过 [e-mail] 联系我们。