从 BigQuery 迁移到 ClickHouse Cloud

为什么使用 ClickHouse Cloud 而不是 BigQuery？

TLDR：因为对于现代数据分析，ClickHouse 比 BigQuery 更快、更便宜、更强大

将数据从 BigQuery 导入 ClickHouse Cloud

数据集

作为一个示例数据集，用于展示从 BigQuery 迁移到 ClickHouse Cloud 的典型过程，我们使用记录在 此处 的 Stack Overflow 数据集。该数据集包含从 2008 年到 2024 年 4 月发生在 Stack Overflow 上的每一个 post、vote、user、comment 和 badge。此数据的 BigQuery 模式如下所示

对于希望将此数据集填充到 BigQuery 实例中以测试迁移步骤的用户，我们已在 GCS 存储桶中以 Parquet 格式提供了这些表的数据，并且创建和加载 BigQuery 表的 DDL 命令可在 此处 获取。

迁移数据

BigQuery 和 ClickHouse Cloud 之间的数据迁移分为两种主要的工作负载类型

• 初始批量加载和定期更新 - 必须迁移初始数据集，并以设定的间隔进行定期更新，例如每天。这里的更新是通过重新发送已更改的行来处理的 - 通过可以用于比较的列（例如，日期）来识别。删除是通过定期重新加载整个数据集来处理的。
• 实时复制或 CDC - 必须迁移初始数据集。此数据集的更改必须以接近实时的速度反映在 ClickHouse 中，仅可接受几秒钟的延迟。这实际上是一个 变更数据捕获 (CDC) 过程，其中 BigQuery 中的表必须与 ClickHouse 同步，即 BigQuery 表中的插入、更新和删除必须应用于 ClickHouse 中的等效表。

通过 Google Cloud Storage (GCS) 进行批量加载

BigQuery 支持将数据导出到 Google 的对象存储 (GCS)。对于我们的示例数据集

1. 将 7 个表导出到 GCS。可以在 此处 获取命令。

2. 将数据导入 ClickHouse Cloud。为此，我们可以使用 gcs 表函数。DDL 和导入查询可在 此处 获取。请注意，由于 ClickHouse Cloud 实例由多个计算节点组成，因此我们不是使用 gcs 表函数，而是使用 s3Cluster 表函数。此函数也适用于 gcs 存储桶，并且 利用 ClickHouse Cloud 服务的全部节点 并行加载数据。

这种方法具有许多优点

• BigQuery 导出功能支持用于导出数据子集的过滤器。
• BigQuery 支持导出到 Parquet、Avro、JSON 和 CSV 格式以及多种 压缩类型 - ClickHouse 支持所有这些格式。
• GCS 支持 对象生命周期管理，允许在将数据导出并导入 ClickHouse 后，在指定的时间段后删除数据。
• Google 允许每天免费导出最多 50TB 的数据到 GCS。用户只需支付 GCS 存储费用。
• 导出会自动生成多个文件，将每个文件限制为最多 1GB 的表数据。这对于 ClickHouse 有益，因为它允许并行化导入。

在尝试以下示例之前，我们建议用户查看 导出所需的权限 和 位置建议，以最大限度地提高导出和导入性能。

通过计划查询进行实时复制或 CDC

变更数据捕获 (CDC) 是保持两个数据库中的表同步的过程。如果需要实时处理更新和删除，这将变得更加复杂。一种方法是简单地使用 BigQuery 的 计划查询功能 安排定期导出。如果您可以接受数据插入 ClickHouse 的延迟，那么这种方法易于实施和维护。示例在 此博客文章 中提供。

设计模式

我们建议从 BigQuery 迁移的用户阅读 ClickHouse 中数据建模的指南。本指南使用相同的 Stack Overflow 数据集，并探讨了使用 ClickHouse 功能的多种方法。

坚持这一原则，我们专注于主要的 posts 表。BigQuery 的模式如下所示

CREATE TABLE stackoverflow.posts (
    id INTEGER,
    posttypeid INTEGER,
    acceptedanswerid STRING,
    creationdate TIMESTAMP,
    score INTEGER,
    viewcount INTEGER,
    body STRING,
    owneruserid INTEGER,
    ownerdisplayname STRING,
    lasteditoruserid STRING,
    lasteditordisplayname STRING,
    lasteditdate TIMESTAMP,
    lastactivitydate TIMESTAMP,
    title STRING,
    tags STRING,
    answercount INTEGER,
    commentcount INTEGER,
    favoritecount INTEGER,
    conentlicense STRING,
    parentid STRING,
    communityowneddate TIMESTAMP,
    closeddate TIMESTAMP
);

优化类型

应用 此处描述 的过程会产生以下模式

CREATE TABLE stackoverflow.posts
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

我们可以使用简单的 INSERT INTO SELECT 填充此表，使用 gcs 表函数 读取导出的数据。请注意，在 ClickHouse Cloud 上，您还可以使用与 gcs 兼容的 s3Cluster 表函数 在多个节点上并行化加载。

INSERT INTO stackoverflow.posts SELECT * FROM gcs( 'gs://clickhouse-public-datasets/stackoverflow/parquet/posts/*.parquet', NOSIGN);

我们的新模式不保留任何空值。上述插入会将这些隐式转换为各自类型的默认值 - 整数为 0，字符串为空值。ClickHouse 还会自动将任何数字转换为目标精度。

ClickHouse 主键有什么不同？

如 此处 所述，与 BigQuery 一样，ClickHouse 不会强制执行表主键列值的唯一性。

类似于 BigQuery 中的聚类，ClickHouse 表的数据按主键列排序后存储在磁盘上。查询优化器利用此排序顺序来防止重新排序、最大限度地减少连接的内存使用量并为 limit 子句启用短路。与 BigQuery 相比，ClickHouse 会根据主键列自动创建 （稀疏）主索引。此索引用于加速包含主键列过滤器的所有查询。具体来说

• 内存和磁盘效率对于 ClickHouse 经常使用的规模至关重要。数据以称为分块的块写入 ClickHouse 表，并应用规则以在后台合并这些分块。在 ClickHouse 中，每个分块都有自己的主索引。当分块合并时，合并的分块的主索引也会合并。请注意，这些索引不是为每一行构建的。相反，分块的主索引每组行都有一个索引条目 - 这种技术称为稀疏索引。
• 稀疏索引是可能的，因为 ClickHouse 将分块的行按指定的键排序后存储在磁盘上。与其直接定位单个行（如基于 B 树的索引），稀疏主索引允许它快速（通过在索引条目上进行二进制搜索）识别可能匹配查询的行组。定位的可能匹配的行组然后并行流式传输到 ClickHouse 引擎以查找匹配项。这种索引设计允许主索引很小（它完全适合主内存），同时仍然可以显着加快查询执行时间，尤其是在数据分析用例中常见的范围查询。

ClickHouse 中选择的主键将确定索引以及数据写入磁盘的顺序。因此，它可以极大地影响压缩级别，进而影响查询性能。导致大多数列的值以连续顺序写入的排序键将允许所选的压缩算法（和编解码器）更有效地压缩数据。

表中的所有列都将根据指定的排序键的值进行排序，无论它们是否包含在键本身中。例如，如果使用 CreationDate 作为键，则所有其他列中的值的顺序将对应于 CreationDate 列中的值的顺序。可以指定多个排序键 - 这将以与 SELECT 查询中的 ORDER BY 子句相同的语义进行排序。

选择排序键

有关选择排序键的考虑因素和步骤，以 posts 表为例，请参阅 此处。

数据建模技术

我们建议从 BigQuery 迁移的用户阅读 ClickHouse 中数据建模的指南。本指南使用相同的 Stack Overflow 数据集，并探讨了使用 ClickHouse 功能的多种方法。

分区

如果您熟悉 BigQuery，您将熟悉表分区概念，该概念通过将表划分为更小、更易于管理的部分（称为分区）来增强大型数据库的性能和可管理性。可以通过指定列上的范围（例如，日期）、定义的列表或通过键的哈希来实现此分区。这允许管理员根据特定标准（例如日期范围或地理位置）组织数据。

分区有助于通过启用更快的通过分区修剪和更有效的索引进行数据访问来提高查询性能。它还有助于维护任务，例如备份和数据清除，通过允许对单个分区而不是整个表进行操作。此外，分区可以通过跨多个分区分配负载来显着提高 BigQuery 数据库的可扩展性。

在 ClickHouse 中，分区是在最初定义表时通过 PARTITION BY 子句指定的。此子句可以包含任何列/列上的 SQL 表达式，其结果将定义将行发送到哪个分区。

数据部分在磁盘上的每个分区中逻辑关联，并且可以独立查询。对于下面的例子，我们使用表达式 toYear(CreationDate) 按年份对 posts 表进行分区。当行插入到 ClickHouse 时，将针对每一行评估此表达式 - 然后将行路由到由此产生的属于该分区的分区中的新数据部分。

CREATE TABLE posts
(
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
...
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)

应用

ClickHouse 中的分区与 BigQuery 中的分区具有相似的应用，但有一些细微的差别。更具体地说

• 数据管理 - 在 ClickHouse 中，您应该主要将分区视为一种数据管理功能，而不是查询优化技术。通过基于键逻辑分离数据，可以独立地对每个分区进行操作，例如删除。这允许您在时间上有效地在 存储层级 之间移动分区，从而移动子集，或者 过期数据/从集群中高效删除。例如，下面我们删除 2008 年的帖子

SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'

┌─partition─┐
│ 2008      │
│ 2009      │
│ 2010      │
│ 2011      │
│ 2012      │
│ 2013      │
│ 2014      │
│ 2015      │
│ 2016      │
│ 2017      │
│ 2018      │
│ 2019      │
│ 2020      │
│ 2021      │
│ 2022      │
│ 2023      │
│ 2024      │
└───────────┘

17 rows in set. Elapsed: 0.002 sec.

ALTER TABLE posts
(DROP PARTITION '2008')

Ok.

0 rows in set. Elapsed: 0.103 sec.

• 查询优化 - 虽然分区可以帮助提高查询性能，但这在很大程度上取决于访问模式。如果查询仅针对几个分区（理想情况下是一个），则性能可能会提高。只有当分区键不在主键中并且您正在按其进行过滤时，这通常才有用。但是，需要覆盖许多分区的查询可能比不使用分区时表现更差（因为分区可能会导致更多的数据部分）。如果分区键已经是主键中的早期条目，那么针对单个分区的优势将变得更不明显甚至不存在。如果每个分区中的值是唯一的，分区也可以用于 优化 GROUP BY 查询。但是，通常，您应该优化主键，并且仅在访问模式访问特定可预测的子集（例如，按天分区，大多数查询在过去一天内）时才将分区视为查询优化技术。

建议

您应该将分区视为一种数据管理技术。当需要从集群中过期数据时，尤其是在使用时间序列数据时，例如，最旧的分区可以 直接删除。

重要提示：确保您的分区键表达式不会导致高基数集，即应避免创建超过 100 个分区。例如，不要按高基数列（如客户端标识符或名称）对数据进行分区。相反，将客户端标识符或名称作为 ORDER BY 表达式中的第一列。

在内部，ClickHouse 为插入的数据创建部分。随着插入更多数据，部分的数量会增加。为了防止部分数量过多，这会降低查询性能（因为有更多文件需要读取），部分会在后台异步过程中合并在一起。如果部分数量超过 预配置的限制，则 ClickHouse 会在插入时抛出异常，作为 “部分过多”错误。这在正常操作下不应发生，仅当 ClickHouse 配置错误或使用不当（例如，许多小插入）时才会发生。由于部分是独立地按分区创建的，因此增加分区数量会导致部分数量增加，即它是分区数量的倍数。因此，高基数分区键可能导致此错误，应避免。

物化视图与投影

ClickHouse 的投影概念允许您为表指定多个 ORDER BY 子句。

在 ClickHouse 数据建模 中，我们探讨了如何在 ClickHouse 中使用物化视图来预计算聚合、转换行以及优化不同访问模式的查询。对于后者，我们 提供了一个示例，其中物化视图将行发送到与接收插入的原始表具有不同排序键的目标表。

例如，考虑以下查询

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
   │ 0.18181818181818182 │
   └─────────────────────┘
--highlight-next-line
1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.

由于 UserId 不是排序键，因此此查询需要扫描所有 9000 万行（尽管速度很快）。以前，我们使用充当 PostId 查找表的物化视图来解决此问题。可以使用投影来解决相同的问题。下面的命令添加一个带有 ORDER BY user_id 的投影。

ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)

ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id

请注意，我们必须先创建投影，然后将其物化。后者命令导致数据以两种不同的顺序存储在磁盘上两次。数据插入时，也可以在创建数据时定义投影，如下所示，并自动维护。

CREATE TABLE comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String),
    --highlight-begin
    PROJECTION comments_user_id
    (
    SELECT *
    ORDER BY UserId
    )
    --highlight-end
)
ENGINE = MergeTree
ORDER BY PostId

如果通过 ALTER 命令创建投影，则在发出 MATERIALIZE PROJECTION 命令时，创建是异步的。您可以使用以下查询确认此操作的进度，等待 is_done=1。

SELECT
    parts_to_do,
    is_done,
    latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')

   ┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │           1 │       0 │                    │
   └─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.003 sec.

如果我们重复上面的查询，我们可以看到性能得到了显着提高，但代价是额外的存储空间。

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘
--highlight-next-line
1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.

使用 EXPLAIN 命令，我们还确认投影用于服务此查询

EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

    ┌─explain─────────────────────────────────────────────┐
 1. │ Expression ((Projection + Before ORDER BY))         │
 2. │   Aggregating                                       │
 3. │   Filter                                            │
 4. │           ReadFromMergeTree (comments_user_id)      │
 5. │           Indexes:                                  │
 6. │           PrimaryKey                                │
 7. │           Keys:                                     │
 8. │           UserId                                    │
 9. │           Condition: (UserId in [8592047, 8592047]) │
10. │           Parts: 2/2                                │
11. │           Granules: 2/11360                         │
    └─────────────────────────────────────────────────────┘

11 rows in set. Elapsed: 0.004 sec.

何时使用投影

投影对于新用户来说是一个有吸引力的功能，因为它们会在插入数据时自动维护。此外，查询可以只发送到单个表，在可能的情况下利用投影来加快响应时间。

这与物化视图相反，在物化视图中，用户必须根据过滤器选择适当的优化目标表或重写查询。这给用户应用程序带来更大的压力，并增加了客户端的复杂性。

尽管具有这些优势，但投影也具有一些固有的局限性，您应该注意，因此应该谨慎部署。有关更多详细信息，请参见 “物化视图与投影”

我们建议在以下情况下使用投影

• 需要对数据进行完全重新排序。虽然投影中的表达式理论上可以使用 GROUP BY，但物化视图更有效地维护聚合。查询优化器也更有可能利用使用简单重新排序的投影，即 SELECT * ORDER BY x。您可以在此表达式中选择一小组列以减少存储空间。
• 用户对相关的存储空间增加和两次写入数据的开销感到满意。测试对插入速度的影响并 评估存储开销。

将 BigQuery 查询重写为 ClickHouse

以下提供了一些比较 BigQuery 和 ClickHouse 的示例查询。此列表旨在演示如何利用 ClickHouse 功能来显着简化查询。这些示例使用完整的 Stack Overflow 数据集（截至 2024 年 4 月）。

具有 10 个以上问题的用户，获得最多浏览量

BigQuery

ClickHouse

SELECT
    OwnerDisplayName,
    sum(ViewCount) AS total_views
FROM stackoverflow.posts
WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING count() > 10
ORDER BY total_views DESC
LIMIT 5

   ┌─OwnerDisplayName─┬─total_views─┐
1. │ Joan Venge       │    25520387 │
2. │ Ray Vega         │    21576470 │
3. │ anon             │    19814224 │
4. │ Tim              │    19028260 │
5. │ John             │    17638812 │
   └──────────────────┴─────────────┘

5 rows in set. Elapsed: 0.076 sec. Processed 24.35 million rows, 140.21 MB (320.82 million rows/s., 1.85 GB/s.)
Peak memory usage: 323.37 MiB.

获得最多浏览量的标签

BigQuery

ClickHouse

-- ClickHouse
SELECT
    arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags,
    sum(ViewCount) AS views
FROM stackoverflow.posts
GROUP BY tags
ORDER BY views DESC
LIMIT 5

   ┌─tags───────┬──────views─┐
1. │ javascript │ 8190916894 │
2. │ python     │ 8175132834 │
3. │ java       │ 7258379211 │
4. │ c#         │ 5476932513 │
5. │ android    │ 4258320338 │
   └────────────┴────────────┘

5 rows in set. Elapsed: 0.318 sec. Processed 59.82 million rows, 1.45 GB (188.01 million rows/s., 4.54 GB/s.)
Peak memory usage: 567.41 MiB.

聚合函数

在可能的情况下，您应该利用 ClickHouse 聚合函数。下面展示了 argMax 函数 的用法，以计算每年浏览量最多的问题。

BigQuery

ClickHouse

-- ClickHouse
SELECT
    toYear(CreationDate) AS Year,
    argMax(Title, ViewCount) AS MostViewedQuestionTitle,
    max(ViewCount) AS MaxViewCount
FROM stackoverflow.posts
WHERE PostTypeId = 'Question'
GROUP BY Year
ORDER BY Year ASC
FORMAT Vertical

Row 1:
──────
Year:                    2008
MostViewedQuestionTitle: How to find the index for a given item in a list?
MaxViewCount:            6316987

Row 2:
──────
Year:                    2009
MostViewedQuestionTitle: How do I undo the most recent local commits in Git?
MaxViewCount:            13962748

...

Row 16:
───────
Year:                    2023
MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3?
MaxViewCount:            506822

Row 17:
───────
Year:                    2024
MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab"
MaxViewCount:            66975

17 rows in set. Elapsed: 0.225 sec. Processed 24.35 million rows, 1.86 GB (107.99 million rows/s., 8.26 GB/s.)
Peak memory usage: 377.26 MiB.

条件和数组

条件和数组函数使查询更加简单。以下查询计算了从 2022 年到 2023 年增长百分比最大的标签（出现次数超过 10000 次）。请注意，由于条件、数组函数以及在 HAVING 和 SELECT 子句中重用别名的能力，以下 ClickHouse 查询简洁明了。

BigQuery

ClickHouse

SELECT
    arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
    countIf(toYear(CreationDate) = 2023) AS count_2023,
    countIf(toYear(CreationDate) = 2022) AS count_2022,
    ((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5

┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js     │      13788 │      10520 │   31.06463878326996 │
│ spring-boot │      16573 │      17721 │  -6.478189718413183 │
│ .net        │      11458 │      12968 │ -11.644046884639112 │
│ azure       │      11996 │      14049 │ -14.613139725247349 │
│ docker      │      13885 │      16877 │  -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.096 sec. Processed 5.08 million rows, 155.73 MB (53.10 million rows/s., 1.63 GB/s.)
Peak memory usage: 410.37 MiB.

这结束了我们从 BigQuery 迁移到 ClickHouse 的基本指南。我们建议阅读有关 ClickHouse 数据建模 的指南，以了解更多关于高级 ClickHouse 功能的信息。