跳到主要内容
跳到主要内容

去重策略 (使用 CDC)

更新和删除从 Postgres 复制到 ClickHouse 的操作会由于其数据存储结构和复制过程而导致 ClickHouse 中出现重复行。此页面涵盖了为什么会发生这种情况以及在 ClickHouse 中处理重复数据的策略。

数据如何被复制？​

PostgreSQL 逻辑解码​

ClickPipes 使用 Postgres 逻辑解码 来实时获取 Postgres 中的更改。Postgres 中的逻辑解码过程使得像 ClickPipes 这样的客户端能够以人类可读的格式接收更改，即一系列的 INSERT、UPDATE 和 DELETE 操作。

ReplacingMergeTree​

ClickPipes 使用 ReplacingMergeTree 引擎将 Postgres 表映射到 ClickHouse。ClickHouse 在仅追加的工作负载下表现最佳，并且不推荐频繁进行 UPDATE 操作。在这里，ReplacingMergeTree 特别强大。

使用 ReplacingMergeTree，更新被建模为带有行的新版本（_peerdb_version）的插入操作，而删除则被建模为带有新版本和 _peerdb_is_deleted 标记为 true 的插入操作。ReplacingMergeTree 引擎在后台去重/合并数据，并为给定的主键（id）保留最新版本的行，从而能够高效处理作为版本化插入的 UPDATE 和 DELETE。

以下是 ClickPipes 执行的 CREATE Table 语句的示例，创建 ClickHouse 中的表。

示例​

下面的插图展示了使用 ClickPipes 在 PostgreSQL 和 ClickHouse 之间同步 users 表的基本示例。

步骤 1 显示了 PostgreSQL 中 2 行的初始快照，以及 ClickPipes 将这 2 行加载到 ClickHouse 的初始操作。可以观察到，这两行都被原样复制到 ClickHouse 中。

步骤 2 显示了在 users 表上的三个操作：插入一条新行、更新一条已有行和删除另一条行。

步骤 3 显示了 ClickPipes 如何将 INSERT、UPDATE 和 DELETE 操作作为版本化插入复制到 ClickHouse。UPDATE 表现为带有 ID 为 2 的行的新版本，而 DELETE 表现为 ID 为 1 的新版本，其 _is_deleted 标记为 true。正因为如此，ClickHouse 比 PostgreSQL 多出三行。

因此，运行一个简单的查询，如 SELECT count(*) FROM users; 可能会在 ClickHouse 和 PostgreSQL 中产生不同的结果。根据 ClickHouse 合并文档，过时的行版本会在合并过程中最终被丢弃。然而，这种合并的时机是不可预测的，这意味着 ClickHouse 中的查询在合并发生之前可能返回不一致的结果。

我们如何确保 ClickHouse 和 PostgreSQL 中的查询结果相同？

使用 FINAL 关键字去重​

在 ClickHouse 查询中去重数据的推荐方法是使用 FINAL 修饰符。这确保只返回去重后的行。

让我们看看如何将其应用于三个不同的查询。

请注意以下查询中的 WHERE 子句，用于过滤出已删除的行。

  • 简单计数查询：统计帖子数量。

这是您可以运行的最简单查询，用于检查同步是否顺利。这两个查询应该返回相同的计数。

  • 带 JOIN 的简单聚合：前 10 名用户，查看次数最多。

这是对单个表进行聚合的示例。在这里有重复项会严重影响求和函数的结果。

FINAL 设置​

您可以使用 FINAL 设置 来自动将其应用于查询中的所有表，而不是在查询中的每个表名称后添加 FINAL 修饰符。

此设置可以针对单个查询或整个会话应用。

行策略​

隐藏冗余的 _peerdb_is_deleted = 0 过滤器的一个简单方法是使用 ROW 策略。下面是一个示例，创建一个行策略以在对表 votes 的所有查询中排除已删除的行。

行策略适用于用户和角色的列表。在此示例中，它适用于所有用户和角色。可以将其调整为仅适用于特定用户或角色。

像 Postgres 一样查询​

将分析数据集从 PostgreSQL 迁移到 ClickHouse 通常需要修改应用查询以考虑数据处理和查询执行的差异。

本节将探讨在保持原始查询不变的同时去重数据的技巧。

视图​

视图 是隐藏查询中的 FINAL 关键字的绝佳方式，因为它们不存储任何数据，只是在每次访问时从另一张表读取数据。

以下是为 ClickHouse 中数据库的每个表创建视图的示例，包含 FINAL 关键字和过滤已删除行的条件。

然后，我们可以使用与 PostgreSQL 相同的查询来查询这些视图。

可刷新物化视图​

另一种方法是使用 可刷新物化视图，可以让您安排查询执行以去重行并将结果存储在目标表中。在每次安排的刷新时，目标表将被最新的查询结果替换。

此方法的一个关键优势是，带有 FINAL 关键字的查询仅在刷新期间运行一次，消除了后续查询对目标表使用 FINAL 的需求。

然而，一个缺点是目标表中的数据仅会和最近的刷新保持同步。尽管如此，对于许多用例，几分钟到几小时的刷新间隔可能已经足够。

然后，您可以正常查询 deduplicated_posts 表。