数据去重策略

数据去重是指删除数据集中的重复行的过程。在 OLTP 数据库中，这很容易完成，因为每一行都有一个唯一的主键——但这会以降低插入速度为代价。每个插入的行都需要首先被搜索，如果找到，则需要被替换。

ClickHouse 在数据插入方面以速度著称。存储文件是不可变的，ClickHouse 在插入行之前不检查是否存在主键——因此数据去重需要付出更多努力。这也意味着数据去重不是立即的——它是最终一致性的，这有一些副作用

在任何时刻，您的表仍然可能存在重复项（具有相同排序键的行）
重复行的实际删除发生在 parts 合并期间
您的查询需要考虑到可能存在重复项


	ClickHouse 提供关于数据去重和许多其他主题的免费培训。删除和更新数据培训模块是一个很好的起点。

数据去重选项

数据去重在 ClickHouse 中使用以下表引擎实现

ReplacingMergeTree 表引擎：使用此表引擎，具有相同排序键的重复行将在合并期间被删除。ReplacingMergeTree 是模拟 upsert 行为（您希望查询返回最后插入的行）的一个不错的选择。
折叠行：CollapsingMergeTree 和 VersionedCollapsingMergeTree 表引擎使用一种逻辑，其中现有行被“取消”，并且插入新行。它们比 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 │
└────┴─────────┴─────────────────┴───────┘

上面输出中单独的框演示了幕后的两个 parts - 此数据尚未合并，因此重复行尚未被删除。让我们在 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 关键字更有效（在查询性能方面）。

我们的删除和更新数据培训模块扩展了这个示例，包括如何将 version 列与 ReplacingMergeTree 一起使用。

使用 CollapsingMergeTree 频繁更新列

更新列涉及删除现有行并用新值替换它。正如您已经看到的，ClickHouse 中这种类型的 mutation 是最终发生的 - 在合并期间。如果您有很多行要更新，那么避免 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 的文档。

数据去重选项​

使用 ReplacingMergeTree 进行 Upsert 操作​

避免使用 FINAL​

使用 CollapsingMergeTree 频繁更新列​

来自多个线程的实时更新​

为什么我的行没有被去重？​