从 Postgres 到 ClickHouse：数据建模指南

admin 发表于 2024-10-15 10:36

专栏

上个月，我们收购了专注于 Postgres CDC 的 PeerDB。PeerDB 使得数据从 Postgres 复制到 ClickHouse 变得既快速又简单。PeerDB 的用户经常会问，在数据复制到 ClickHouse 后，如何进行数据建模以充分发挥 ClickHouse 的优势。

这一问题的产生源于 ClickHouse 和 Postgres 在数据建模上的差异。两者分别为各自特定的工作负载量身打造——Postgres 是事务型（OLTP）数据库，而 ClickHouse 则是专注于分析的列式（OLAP）数据库。本指南将帮助来自 Postgres 的用户掌握在 ClickHouse 中关键的数据建模概念。这是该系列博客的第一篇，更多内容将在未来发布。

ReplacingMergeTree 表引擎

PeerDB 使用 ReplacingMergeTree 引擎将 PostgreSQL 表映射到 ClickHouse。ClickHouse 在追加写入的场景下表现最佳，并不推荐频繁的 UPDATE 操作，而 ReplacingMergeTree 正是专为此类需求而设计的。

ReplacingMergeTree 支持既包含数据导入又包含数据修改的工作负载。每个表都是追加写入的，用户的更新会作为带有版本号的 INSERT 记录。ReplacingMergeTree 引擎在后台自动处理行数据的去重和合并。这是 ClickHouse 能够在实时数据导入方面表现出色的关键原因之一。

在 PeerDB 中，Postgres 的 INSERT 和 UPDATE 操作会以不同版本（使用 _peerdb_version）的新行形式写入 ClickHouse。ReplacingMergeTree 表引擎会定期通过排序键（ORDER BY 列）在后台处理去重，保留最新版本的行。而 PostgreSQL 的 DELETE 操作则会作为标记为已删除的新行（通过 _peerdb_is_deleted 列）进行处理。下方代码片段展示了 ClickHouse 中 public_goals 表的目标表定义。

clickhouse-cloud :) SHOW CREATE TABLE public_goals;CREATE TABLE peerdb.public_goals(    `id` Int64,    `owned_user_id` String,    `goal_title` String,    `goal_data` String,    `enabled` Bool,    `ts` DateTime64(6),    `_peerdb_synced_at` DateTime64(9) DEFAULT now(),    `_peerdb_is_deleted` Int8,    `_peerdb_version` Int64)ENGINE = SharedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _peerdb_version)PRIMARY KEY idORDER BY idSETTINGS index_granularity = 8192

你可能仍然会看到重复的行数据——该如何处理？

ReplacingMergeTree 在后台异步去重，但无法保证完全消除重复数据。因此，查询时你可能会遇到同一行或主键存在不同版本的重复情况。这是正常现象。要去除重复数据，你可以考虑以下几种方法：

在查询中使用 FINAL

ClickHouse 提供了一个独特的修饰符 FINAL，它可以在查询时进行去重（合并行数据）。这种去重操作发生在过滤（WHERE 子句）之后，聚合（GROUP BY）之前。

过去人们担心 FINAL 会影响查询性能。虽然 FINAL 确实对性能有一定影响，但 ClickHouse 的最新版本对其进行了显著优化。因此，你可以放心使用 FINAL 子句，并观察查询性能。以下是使用 FINAL 子句的示例：

SELECT owner_user_id, COUNT(*) FROM goals FINAL WHERE enabled = true GROUP BY owner_user_id;

使用 argMax 函数在查询时去重

ClickHouse 提供了 argMax 函数，用于在查询时动态去除重复行。尤其在需要根据版本号或时间戳保留最新记录时，它非常有用。

例如，对于 peerdb.public_goals 表，如果 id 是主键，_peerdb_version 记录版本号，你可以使用 argMax 选出每个 id 下版本号最高的行。这种方式可以在不改变底层数据的前提下去除重复项。接着，你可以对去重后的结果集执行子查询来进行聚合分析。以下是使用 argMax 的示例查询。

SELECT    owned_user_id,    COUNT(*) AS active_goals_count,    MAX(ts) AS latest_goal_timeFROM(    SELECT        id,        argMax(owned_user_id, _peerdb_version) AS owned_user_id,        argMax(goal_title, _peerdb_version) AS goal_title,        argMax(goal_data, _peerdb_version) AS goal_data,        argMax(enabled, _peerdb_version) AS enabled,        argMax(ts, _peerdb_version) AS ts,        argMax(_peerdb_synced_at, _peerdb_version) AS _peerdb_synced_at,        argMax(_peerdb_is_deleted, _peerdb_version) AS _peerdb_is_deleted,        max(_peerdb_version) AS _peerdb_version    FROM peerdb.public_goals    WHERE enabled = true    GROUP BY id) AS deduplicated_goalsGROUP BY owned_user_id;

使用窗口函数

你也可以利用 ClickHouse 的窗口函数，通过在每个 id 分区中选出 _peerdb_version 最高的行来实现去重。下面是一个示例：

SELECT    owned_user_id,    COUNT(*) AS active_goals_count,    MAX(ts) AS latest_goal_timeFROM(    SELECT        *,        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn    FROM peerdb.public_goals    WHERE enabled = true) AS ranked_goalsWHERE rn = 1GROUP BY owned_user_id;

通过视图简化去重

将去重逻辑封装在视图中，使得 BI 工具能够更方便地查询最新数据。例如，在视图中使用窗口函数，只保留每行的最新版本：

CREATE VIEW goals ASSELECT * FROM(    SELECT        *,        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn    FROM peerdb.public_goals    WHERE enabled = true) WHERE rn = 1;

SELECT    owned_user_id,    COUNT(*) AS active_goals_count,    MAX(ts) AS latest_goal_timeFROM goalsGROUP BY owned_user_id;

可为空列

对于习惯使用 Postgres 的用户来说，ClickHouse 的一个特别之处在于，它不会存储列的 NULL 值，除非你明确将列类型定义为 Nullable。例如，对于日期列，ClickHouse 会将 1970-01-01 作为默认值，而不是存储 NULL，这可能让人意外。这是因为存储 NULL 值会影响 ClickHouse 作为列式数据库的查询性能。因此，ClickHouse 需要用户显式定义 Nullable 类型。

在 PeerDB 中，我们引入了一个名为 PEERDB_NULLABLE 的设置。当设置为 true 时，该功能会在复制过程中自动检测 Postgres 中的可为空列，并在 ClickHouse 中将其标记为 Nullable。这样你在复制过程中无需手动定义 Nullable 类型。有关此功能的更多信息，请查看以下 PR。

数据类型

ClickHouse 提供了丰富的数据类型，从数字、文本、时间戳、日期、数组，到新引入的 JSON 类型。在大多数情况下，Postgres 中的数据类型可以直接存储到 ClickHouse，而无需进行复杂的转换。

以下是我们在 PeerDB 中从 Postgres 复制数据到 ClickHouse 时使用的数据类型对照表，供参考。

排序键

什么是排序键？

在 ClickHouse 中，选择合适的排序键对查询性能至关重要。排序键由创建表时的 ORDER BY 子句定义，类似于 Postgres 中的索引，但其专为分析型工作负载进行优化。与 Postgres 中使用的 B-tree 树索引不同，ClickHouse 使用稀疏索引：

数据按排序键排序：
排序键确保磁盘上的数据按照指定的列进行排序。
这种排序有助于提升数据压缩效果，因为相似的值会集中存储在一起。
排序键还生成稀疏索引：
排序键还会生成稀疏索引，仅存储列的范围，每个条目指向一组已排序的行。
这保持了索引的小巧，使 ClickHouse 能够通过二进制搜索快速定位相关行组，从而高效执行查询。
你可以在此处阅读更多相关内容【https://clickhouse.com/docs/en/migrations/postgresql/designing-schemas#primary-ordering-keys-in-clickhouse】。

你可以将排序键与 Postgres 中的 BRIN 索引类比，但在 ClickHouse 中，数据会通过异步合并部分自动按排序键排序，无需在数据导入时手动排序。

如何选择合适的排序键

选择排序键时，优先考虑在查询的 WHERE 子句中最常使用的列，并根据列基数（即唯一值的数量）升序排列——从具有最少不同值的列开始。这样能够优化数据压缩与查询性能。有关该主题的更多深入探讨，请参考此处的详细指南【https://clickhouse.com/docs/en/data-modeling/schema-design#choosing-an-ordering-key】。

主键 (PRIMARY KEY) 与排序键 (Ordering Key) 的区别

在 public_goals 表的定义中，你可能会注意到它设置了主键 (PRIMARY KEY)。那么，主键与排序键 (Ordering Key) 之间的区别是什么呢？让我们深入探讨它们的不同之处：

如果定义了主键 (PRIMARY KEY)，它将指定稀疏索引中的列，而 ORDER BY 子句则决定数据在磁盘上的排序方式。此外，主键和排序键也用于 ReplacingMergeTree 引擎的去重操作。
如果没有明确指定主键 (PRIMARY KEY)，则排序键 (Ordering Key) 会自动作为主键，定义稀疏索引中的列。

注意：主键 (PRIMARY KEY) 中的列应该在排序键 (Ordering Key) 中优先排列。这可以确保索引和物理数据的排序一致，从而减少不必要的数据扫描，提升查询性能。

主键与排序键不同的应用场景

在一些场景中，主键和 ORDER BY 子句的列可能不完全相同。例如，如果你的查询大多基于 customer_id 进行过滤而非 id，这时你可以在 customer_id 上设置主键 (PRIMARY KEY)，而 ORDER BY 子句中包括 customer_id 和 id。这样可以确保稀疏索引在查询时更紧凑高效，并且通过 id 进行数据去重，确保数据不会丢失。

注意：与 PostgreSQL 中的主键不同，PostgreSQL 的主键通过 B-tree 索引确保数据唯一性，而在 ClickHouse 中，主键并不强制唯一性。它主要用于指定哪些列应该被纳入稀疏索引中。

修改排序键 (Ordering Key)

在 ClickHouse 中，选择合适的排序键对于查询性能至关重要，因为它相当于查询数据时的索引。默认情况下，PeerDB 会将 PostgreSQL 的主键 (PRIMARY KEY) 用作 ClickHouse 表的排序键 (Ordering Key)，但你可以通过以下方法进行更改：

使用物化视图 (Materialized Views)

通过物化视图，你可以创建一个适用于特定工作负载的表，使用不同的排序键。在定义排序键时，确保主键 (PRIMARY KEY) 列位于排序键的末尾。这是因为 ReplacingMergeTree 引擎在去重时依赖 ORDER BY 子句的顺序，包含主键可以确保数据不会丢失。

CREATE MATERIALIZED VIEW goals_mvENGINE = ReplacingMergeTree(_peerdb_version)ORDER BY (enabled, ts, id)  POPULATE ASSELECT * FROM peerdb.public_goals;

注意：在创建物化视图后，务必按照前述处理重复数据的步骤进行操作，以确保在查询时正确去重。

预先定义目标表及所需排序键

如果需要更改排序键，可以预先定义一个包含目标排序键的新表，并将其替换现有表。具体步骤如下：

1. 创建虚拟镜像：首先在 PeerDB 中创建一个虚拟镜像，它将生成一个具有正确元数据列和数据类型的默认表。

2. 创建新表并设置排序键：基于 PeerDB 创建的表定义一个新表，包含你需要的排序键。在排序键中，确保将主键列放在末尾以确保正确的去重。

CREATE TABLE public_events_new AS public_eventsENGINE = ReplacingMergeTree(_peerdb_version)ORDER BY (user_id,id);

3. 删除旧表：将旧表删除以腾出空间。

DROP TABLE public_events;

4. 重命名新表：将新表命名为原表的名称。

RENAME TABLE public_events_new TO public_events;

5. 配置镜像指向新表：通过调整配置，使 PeerDB 的镜像指向新表。PeerDB 在后台使用 CREATE TABLE IF NOT EXISTS 命令，确保数据继续写入新表。

处理 DELETE 操作

正如前面提到的，PostgreSQL 中的 DELETE 操作会以标记为已删除的行（通过 _peerdb_is_deleted 列）形式同步到 ClickHouse。如果你希望在查询中排除这些已删除的数据，可以基于 _peerdb_is_deleted 列在 ClickHouse 中创建行级别的策略 (row-level policy)。例如：

CREATE ROW POLICY policy_name ON table_nameFOR SELECT USING _peerdb_is_deleted = 0;

这个策略会确保在查询表数据时，只有 _peerdb_is_deleted 等于 0 的行是可见的。

总结

希望这篇博客对你有所帮助。我尽量涵盖了从 PostgreSQL 迁移到 ClickHouse 过程中常见的数据建模挑战。在接下来的博客中，我将深入探讨更高级的话题，例如如何进行表连接 (joins) 和编写高效的 SQL 查询等。如果你有兴趣尝试 PeerDB 和 ClickHouse，并开始从 PostgreSQL 复制数据到 ClickHouse，请查看下面的链接，或者直接联系我们！

1. 免费试用 ClickHouse Cloud【https://clickhouse.com/docs/en/cloud-quick-start】

2. 免费试用 PeerDB Cloud【https://auth.peerdb.cloud/en/signup?glxid=81340839-0371-47e4-aea0-f0b994d2c85d&pagePath=%2Fblog%2Fpostgres-to-clickhouse-data-modeling-tips&origPath=%2Fblog%2Fhow-to-learn-clickhouse-and-become-a-certified-clickhouse-developer&experiments=mktg-website-nav-cta-btn%3A0%2Cmktg-website-rockset-eyebrow%3A0】

3. Postgres 到 ClickHouse 复制文档【https://docs.peerdb.io/mirror/cdc-pg-clickhouse】

4. 直接与 PeerDB 团队联系【https://www.peerdb.io/sign-up】

阿里云 ClickHouse企业版服务试用

轻松节省30%云资源成本？阿里云数据库ClickHouse架构全新升级，推出和原厂独家合作的ClickHouse企业版，在存储和计算成本上带来双重优势，现诚邀您参与100元指定规格测一个月的活动，了解详情：https://t.aliyun.com/Kz5Z0q9G