跳至主要内容

去重策略

**去重**是指**删除数据集中的重复行**的过程。在 OLTP 数据库中,由于每行都有唯一的 主键,因此可以轻松完成此操作,但代价是插入速度会变慢。每个插入的行都需要先进行搜索, 然后如果找到,则需要进行替换。

ClickHouse 在数据插入方面速度很快。存储文件是不可变的,ClickHouse 在插入行之前不会检查 现有主键,因此去重需要花费更多精力。这也意味着去重不是立即发生的,而是**最终**的,这有一些副作用。

  • 在任何时间点,您的表中仍然可能存在重复项(具有相同排序键的行)。
  • 实际删除重复行的操作发生在合并部分期间。
  • 您的查询需要允许存在重复项的可能性。
Cassandra logoClickHouse 提供有关去重和许多其他主题的免费培训。 删除和更新数据培训模块 是一个不错的起点。

去重选项

ClickHouse 使用以下表引擎实现去重。

  1. ReplacingMergeTree 表引擎:使用此表引擎,在合并期间会删除具有相同排序键的重复行。 ReplacingMergeTree 是模拟 upsert 行为(您希望查询返回最后插入的行)的不错选择。

  2. 折叠行:CollapsingMergeTreeVersionedCollapsingMergeTree 表引擎使用一种逻辑,其中现有行被 “取消”,然后插入新行。它们比 ReplacingMergeTree 更复杂,但您的查询和聚合更容易编写, 不用担心数据是否已合并。当您需要频繁更新数据时,这两种表引擎很有用。

我们在下面介绍了这两种技术。有关更多详细信息,请查看我们免费的点播 删除和更新数据培训模块

使用 ReplacingMergeTree 进行 Upsert

让我们看一个简单的示例,其中一个表包含 Hacker News 评论,其中 views 列表示评论的查看次数。 假设我们在发布文章时插入新行,并在查看次数增加时每天 upsert 一行,其中包含查看次数的总数。

CREATE TABLE hackernews_rmt (
id UInt32,
author String,
comment String,
views UInt64
)
ENGINE = ReplacingMergeTree
PRIMARY KEY (author, id)

让我们插入两行。

INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 0),
(2, 'ch_fan', 'This is post #2', 0)

要更新 views 列,请插入具有相同主键的新行(注意 views 列的新值)。

INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 100),
(2, 'ch_fan', 'This is post #2', 200)

表现在有 4 行。

SELECT *
FROM hackernews_rmt
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 0 │
│ 1 │ ricardo │ This is post #1 │ 0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘

输出中上面的单独框演示了幕后的两个部分 - 此数据尚未合并,因此重复行尚未删除。 让我们在 SELECT 查询中使用 FINAL 关键字,这将导致对查询结果进行逻辑合并。

SELECT *
FROM hackernews_rmt
FINAL
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘

结果只有 2 行,并且最后插入的行是返回的行。

注意

如果您只有少量数据,使用 FINAL 可以正常工作。如果您处理的是大量数据,使用 FINAL 可能不是最佳选择。让我们讨论一个更好的选择,用于查找列的最新值…

避免使用 FINAL

让我们再次更新两行唯一行的 views 列。

INSERT INTO hackernews_rmt VALUES
(1, 'ricardo', 'This is post #1', 150),
(2, 'ch_fan', 'This is post #2', 250)

表现在有 6 行,因为实际合并尚未发生(只有我们在使用 FINAL 时进行的查询时合并)。

SELECT *
FROM hackernews_rmt
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 200 │
│ 1 │ ricardo │ This is post #1 │ 100 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 0 │
│ 1 │ ricardo │ This is post #1 │ 0 │
└────┴─────────┴─────────────────┴───────┘
┌─id─┬─author──┬─comment─────────┬─views─┐
│ 2 │ ch_fan │ This is post #2 │ 250 │
│ 1 │ ricardo │ This is post #1 │ 150 │
└────┴─────────┴─────────────────┴───────┘

与其使用 FINAL,不如使用一些业务逻辑 - 我们知道 views 列始终在增加,因此我们可以使用 max 函数在按所需列分组后选择具有最大值的行。

SELECT
id,
author,
comment,
max(views)
FROM hackernews_rmt
GROUP BY (id, author, comment)
┌─id─┬─author──┬─comment─────────┬─max(views)─┐
│ 2 │ ch_fan │ This is post #2 │ 250 │
│ 1 │ ricardo │ This is post #1 │ 150 │
└────┴─────────┴─────────────────┴────────────┘

如上面查询所示进行分组实际上可能比使用 FINAL 关键字更高效(在查询性能方面)。

我们的 删除和更新数据培训模块 扩展了此示例,包括如何使用 ReplacingMergeTreeversion 列。

使用 CollapsingMergeTree 频繁更新列

更新列涉及删除现有行并用新值替换它。正如您已经看到的那样,ClickHouse 中的这种操作会 *最终* 发生 - 在合并期间。如果您有很多行要更新,实际上避免使用 ALTER TABLE..UPDATE 可能会更高效,而是将新数据与现有数据一起插入。我们可以添加一个列,用于表示数据是否过期或新数据… 实际上有一个表引擎已经非常出色地实现了这种行为,特别是考虑到它会自动为您删除过期数据。让我们看看它的工作原理。

假设我们使用外部系统跟踪 Hacker News 评论的查看次数,并且每隔几个小时,我们将数据推送到 ClickHouse 中。我们希望删除旧行,并且新行代表每个 Hacker News 评论的新状态。我们可以使用 CollapsingMergeTree 来实现这种行为。

让我们定义一个表来存储查看次数。

CREATE TABLE hackernews_views (
id UInt32,
author String,
views UInt64,
sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PRIMARY KEY (id, author)

注意 hackernews_views 表有一个名为 sign 的 Int8 列,称为**符号**列。符号列的名称是任意的,但 Int8 数据类型是必需的,并且请注意列名已传递到 CollapsingMergeTree 表的构造函数中。

CollapsingMergeTree 表的符号列是什么?它表示行的*状态*,符号列只能是 1 或 -1。以下是它的工作原理。

  • 如果两行具有相同的主键(或如果排序顺序不同于主键,则为相同的主键排序顺序),但符号列的值不同, 那么最后插入的 +1 行将成为状态行,而其他行会相互抵消。
  • 相互抵消的行将在合并期间删除。
  • 没有匹配对的行将保留。

让我们在 hackernews_views 表中添加一行。由于它是该主键的唯一行,因此我们将它的状态设置为 1。

INSERT INTO hackernews_views VALUES
(123, 'ricardo', 0, 1)

现在假设我们想要更改 views 列。您插入两行:一行取消现有行,另一行包含行的最新状态。

INSERT INTO hackernews_views VALUES
(123, 'ricardo', 0, -1),
(123, 'ricardo', 150, 1)

表现在有 3 行,主键为 (123, 'ricardo')。

SELECT *
FROM hackernews_views
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 0 │ -1 │
│ 123 │ ricardo │ 150 │ 1 │
└─────┴─────────┴───────┴──────┘
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 0 │ 1 │
└─────┴─────────┴───────┴──────┘

注意添加 FINAL 会返回当前状态行。

SELECT *
FROM hackernews_views
FINAL
┌──id─┬─author──┬─views─┬─sign─┐
│ 123 │ ricardo │ 150 │ 1 │
└─────┴─────────┴───────┴──────┘

但当然,不建议对大型表使用 FINAL

注意

我们示例中为 views 列传递的值实际上并不需要,也不必与旧行的 views 的当前值匹配。事实上,您只需使用主键和 -1 就可以取消一行。

INSERT INTO hackernews_views(id, author, sign) VALUES
(123, 'ricardo', -1)

来自多个线程的实时更新

使用 CollapsingMergeTree 表,行使用符号列相互抵消,并且行的状态由最后插入的行决定。 但是,如果从不同的线程插入行,而这些行可能会以乱序插入,则这可能会成为问题。在这种情况下,使用 “最后” 行不起作用。

这就是 VersionedCollapsingMergeTree 派上用场的地方 - 它像 CollapsingMergeTree 一样折叠行, 但它不会保留最后插入的行,而是保留您指定版本列的最高值的行。

让我们看一个例子。假设我们想跟踪 Hacker News 评论的查看次数,并且数据经常更新。我们希望 报告使用最新值,而不强迫或等待合并。我们从类似于 CollapsedMergeTree 的表开始, 不同的是我们添加了一个列来存储行状态的版本。

CREATE TABLE hackernews_views_vcmt (
id UInt32,
author String,
views UInt64,
sign Int8,
version UInt32
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PRIMARY KEY (id, author)

注意该表使用 VersionsedCollapsingMergeTree 作为引擎,并传入**符号列**和**版本列**。以下是表的工作原理。

  • 它会删除每个具有相同主键和版本但符号不同的行对。
  • 行的插入顺序无关紧要。
  • 请注意,如果版本列不是主键的一部分,ClickHouse 会将其隐式地添加到主键中,作为最后一个字段。

在编写查询时,您使用相同的逻辑 - 按主键分组,并使用巧妙的逻辑来避免那些已被取消但尚未删除的行。让我们向 hackernews_views_vcmt 表添加一些行。

INSERT INTO hackernews_views_vcmt VALUES
(1, 'ricardo', 0, 1, 1),
(2, 'ch_fan', 0, 1, 1),
(3, 'kenny', 0, 1, 1)

现在我们更新其中两行并删除其中一行。要取消一行,请务必包含先前的版本号(因为它是主键的一部分)。

INSERT INTO hackernews_views_vcmt VALUES
(1, 'ricardo', 0, -1, 1),
(1, 'ricardo', 50, 1, 2),
(2, 'ch_fan', 0, -1, 1),
(3, 'kenny', 0, -1, 1),
(3, 'kenny', 1000, 1, 2)

我们将运行与之前相同的查询,该查询巧妙地根据符号列添加和减去值。

SELECT
id,
author,
sum(views * sign)
FROM hackernews_views_vcmt
GROUP BY (id, author)
HAVING sum(sign) > 0
ORDER BY id ASC

结果是两行。

┌─id─┬─author──┬─sum(multiply(views, sign))─┐
│ 1 │ ricardo │ 50 │
│ 3 │ kenny │ 1000 │
└────┴─────────┴────────────────────────────┘

让我们强制执行表合并。

OPTIMIZE TABLE hackernews_views_vcmt

结果中应该只有两行。

SELECT *
FROM hackernews_views_vcmt
┌─id─┬─author──┬─views─┬─sign─┬─version─┐
│ 1 │ ricardo │ 50 │ 1 │ 2 │
│ 3 │ kenny │ 1000 │ 1 │ 2 │
└────┴─────────┴───────┴──────┴─────────┘

当您希望在从多个客户端和/或线程插入行时实现去重时,VersionedCollapsingMergeTree 表非常有用。

为什么我的行没有去重?

插入的行可能没有去重的其中一个原因是,您在 INSERT 语句中使用了非幂等函数或表达式。例如,如果您使用 createdAt DateTime64(3) DEFAULT now() 列插入行,那么您的行将保证是唯一的,因为每行将为 createdAt 列具有唯一的默认值。MergeTree/ReplicatedMergeTree 表引擎将不知道去重这些行,因为每个插入的行将生成一个唯一的校验和。

在这种情况下,您可以为每批行指定您自己的 insert_deduplication_token,以确保同一批的多次插入不会导致相同行被重新插入。有关如何使用此设置的更多详细信息,请参阅 关于 insert_deduplication_token 的文档