跳到主要内容
跳到主要内容
编辑此页面

从 BigQuery 加载数据到 ClickHouse

本指南与 ClickHouse Cloud 和自托管 ClickHouse v23.5+ 兼容。

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

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

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

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

表行数导出的文件数据大小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 秒
总计82.6 亿23,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;

-- 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 的数据，输出将被分片到多个文件中，并带有数字递增的后缀。

这种方法有许多优点

  • 谷歌允许每天免费导出高达 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] 联系我们。