数据去重策略

去重是指从数据集中删除重复行的过程。在 OLTP 数据库中，这很容易实现，因为每行都有唯一的主键，但这会导致插入速度变慢。每次插入行都需要先搜索，如果找到，就需要替换。

ClickHouse 是为数据插入速度而构建的。存储文件是不可变的，ClickHouse 在插入行之前不会检查是否存在主键，因此去重需要更多的工作。这也意味着去重不是立即进行的，它是最终的，这有一些副作用：

在任何时间点，您的表仍然可能有重复行（具有相同排序键的行）
实际删除重复行发生在合并部分时
您的查询需要允许重复的可能性


	ClickHouse 提供了关于去重和许多其他主题的免费培训。去重培训课程是一个很好的起点。

去重的选项

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

ReplacingMergeTree 表引擎：使用此表引擎，合并时会删除具有相同排序键的重复行。ReplacingMergeTree 是模拟 upsert 行为（您希望查询返回最后插入的行）的一个很好的选择。
折叠行：CollapsingMergeTree 和 VersionedCollapsingMergeTree 表引擎使用逻辑，其中现有行被“取消”，并插入新行。它们比 ReplacingMergeTree 更复杂，但您的查询和聚合可能更容易编写，而不必担心数据是否已经合并。当您需要频繁更新数据时，这两个表引擎非常有用。

我们将在下面详细介绍这两种技术。有关更多详细信息，请查看我们的免费的去重培训课程。

使用 ReplacingMergeTree 进行 Upserts

让我们看一个简单的例子，一个表包含 Hacker News 评论，其中有一个 views 列，表示评论被查看的次数。假设我们在发布文章时插入一行新数据，并在一天内插入一行新数据，如果值增加，则更新 views 列的总数：

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 行，返回的是最后插入的行。

note

使用 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 关键字更有效（从查询性能的角度）。FINAL 关键字会强制查询在单个线程中运行，而 GROUP BY 会并行执行。

我们的去重培训课程扩展了这个例子，包括如何在 ReplacingMergeTree 中使用 version 列。

使用 CollapsingMergeTree 频繁更新列

更新列涉及删除现有行并用新值替换它。如果您需要更新大量行，实际上避免使用 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 列，被称为sign列。sign 列的名称是任意的，但需要 Int8 数据类型，注意列名被传递给 CollapsingMergeTree 表的构造函数。

CollapsingMergeTree 表的 sign 列是什么？它表示行的状态，sign 列只能是 1 或 -1。这是它的工作原理：

如果两行具有相同的主键（或排序顺序，如果与主键不同），但 sign 不同，则最后插入的 +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。

note

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

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

多线程实时更新

对于 CollapsingMergeTree 表，行使用 sign 列相互取消，行的状态由最后插入的行确定。但是，如果您从不同线程插入行，行可以无序插入，使用“最后”行在这种情况下不起作用。

这就是 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 作为引擎，并传递了sign 列和version 列。这是表的工作原理：

它删除具有相同主键和版本但不同 sign 的行
插入行的顺序无关紧要
请注意，如果版本列不是主键的一部分，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)

我们将运行与之前相同的查询，它会根据 sign 列聪明地添加和减去值：

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 进行 Upserts​

避免使用 FINAL​

使用 CollapsingMergeTree 频繁更新列​

多线程实时更新​

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