• 卢经理 151 5399 4549
    扫一扫,加我咨询
扫码关注我们
在 ClickHouse 中处理更新和删除以及 Upserts
发布时间:2023-09-26

作为世界上最快的实时分析数据库,ClickHouse 的许多工作负载都涉及大量的数据,这些数据只写一次,不经常修改(例如,物联网设备产生的遥测事件或电子商务网站产生的客户点击)。虽然这些通常是不可变的,但在分析期间提供上下文的其他关键数据集(例如,基于设备或客户 ID 的信息查找表)可能需要修改。

根据你的目标和性能要求,ClickHouse 有多种更新和删除数据的方法。本文的其余部分将描述每种方法及其优缺点,以及解决一些常见挑战的轻量级删除的一些最新进展。我们推荐最佳实践,并强调在考虑一种方法时需要考虑的一些重要事项。

在继续之前,确定更新是否是解决问题的最佳方法。例如,对于不经常更改的数据,对数据进行版本控制可能是更好的选择。在存储效率和查询性能方面,ClickHouse 是排名第一的分析数据库,所以在许多情况下,仅仅保存多个版本的数据而不是更新可能会更好。

轻量级删除

轻量级删除代表首选和最有效的方式从 ClickHouse 删除数据。通过 DELETE FROM 表语法,用户可以指定一个条件来删除特定的行,如下所示:

  • https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals
DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2
  • 1.

默认情况下,该操作是异步的,除非 mutations_sync 设置为 1(见下文)。执行删除时,ClickHouse 会为每一行保存一个掩码,在 _row_exists 列中表示是否被删除。随后的查询依次排除这些已删除的行,如下所示。

图片图片

在内部,ClickHouse 将数据分成几个部分,每个部分包含列数据文件和索引。常规的合并周期负责组合(合并)和重写这些部分。这确保了文件的数量不会随着插入更多数据而继续增长,从而保持查询的速度。这些合并考虑轻量级删除,不包括那些在新形成的部分中标记为要删除的行。

图片图片

它于 22.8 发布,在撰写本文时仍处于试验阶段,轻量级删除将在下一个版本中成为生产就绪。在此之前,使用轻量级删除需要设置allow_experimental_lightweight_delete=true。

用户应该意识到,依靠正常的后台合并周期,行最终只会从磁盘中删除。虽然从搜索结果中排除,但这些行将驻留在磁盘上,直到它们的部分被合并。这种情况发生所需的时间是不确定的。这有几个含义:

  • 节省的空间不会像通过突变发出删除那样直接——见下文。如果节省空间非常重要,例如磁盘空间不足,请考虑使用突变。
  • 由于不能保证删除,有遵从性需求的用户可能希望使用突变来确保删除数据。

轻量级删除操作的成本取决于 WHERE 子句中匹配行的数量和当前数据部分的数量。当匹配少量行时,此操作将是最有效的。用户还应该意识到,轻量级删除在宽部分(列数据文件单独存储)和紧凑部分(所有列数据使用单个文件)上表现最好。前者允许将掩码 _row_exists 存储为一个单独的文件,从而允许它独立于其他列进行写入。通常,紧凑的零件将在插入后成形。一旦部分超过一定的大小(例如,由于合并),就使用宽格式。对于大多数工作负载,这不应该是一个问题。

  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-data-storage

最后,请注意,轻量级删除使用与我们下面描述的相同的突变队列和后台线程。关于内部实现的更多细节,我们推荐使用这里的文档。

  • https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals

突变

使用突变更新数据

更新 ClickHouse 表中的数据最简单的方法是使用 ALTER…UPDATE 语句。

  • https://clickhouse.com/docs/en/sql-reference/statements/alter/update
ALTER TABLE table
    UPDATE col1 = 'Hi' WHERE col2 = 2
  • 1.
  • 2.

该查询将使用给定的过滤器更新表 table 上的 col1。

与一些数据库不同,ClickHouse 的 ALTER UPDATE 语句默认情况下是异步的。这意味着更新发生在后台,您不会立即对表产生影响。这个更新表的过程称为突变。

  • https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations

图片图片

这里需要注意的是,更新数据是一个繁重的查询,因为 ClickHouse 必须做大量的工作来优化存储和处理。突变操作强制重写包含要删除的行的所有数据部分,在形成新部分时排除目标行。这可能会导致相当大的 I/O 和集群开销,因此要谨慎使用它,或者考虑下面讨论的替代方案。

使用突变删除数据

与更新一样,删除也可以通过突变进行,并提供轻量级删除的另一种选择。在大多数情况下,由于重写所有列的突变成本,轻量级删除更适合于数据删除。更具体地说,与轻量级删除不同,所有列都被重写,而不仅仅是一个 _row_exists 掩码列。

然而,考虑到轻量级删除的 “最终从磁盘删除数据” 属性,用户可能更喜欢这种基于突变的方法来实现有保证的磁盘空间节省。此外,当用户需要保证从磁盘中删除数据时,例如由于遵从性原因,这种方法是合适的。

ALTER TABLE table
    DELETE WHERE col2 = 3
  • 1.
  • 2.

在此查询中,删除 col2 值为 3 的所有行。与其他修改类似,默认情况下,删除也是异步的。可以使用上述相同的 mutations_sync 设置使其同步。

检查突变进展

由于突变是异步运行的,因此可以通过 system.mutations 表进行监测。这允许用户需要检查他们在表上的特定突变的进度。

SELECT
    command,
    is_done
FROM system.mutations
WHERE table = 'tablename'

┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 21 │
│ UPDATE col1 = 'All hi' WHERE col2 > 00 │
└───────────────────────────────────────┴─────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

如果 is_done 的值对于特定的突变是 0,那么它仍然在执行。对每个表部分执行突变,突变后的部分立即可用:

图片图片

同步更新

对于需要同步更新的用户,mutations_sync 参数可以设置为 1(或者 2,如果我们也想等到所有副本也被更新):

  • https://clickhouse.com/docs/en/operations/settings/settings/#mutations_sync
SET mutations_sync = 1
  • 1.

现在我们的更新查询将等待突变完成:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec.
  • 1.
  • 2.
  • 3.
  • 4.

注意,当 ClickHouse 等待后台突变完成时,这个查询花了 1 秒钟的时间。注意,此参数也适用于轻量级删除。

更新整个表

在某些情况下,用户需要更新整个列的值。最初,用户可能会尝试使用不带 WHERE 子句的 ALTER TABLE 查询来实现这一点。然而,这是失败的,如下所示:

ALTER TABLE table UPDATE col1 = 'bye';

Syntax error: failed at position 38 (end of query):
ALTER TABLE table UPDATE col1 = 'bye';
  • 1.
  • 2.
  • 3.
  • 4.

ClickHouse 不会让你更新整个表,因为更新是繁重的。强迫 ClickHouse 接受此操作的一种方法是使用始终为真过滤器:

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true
  • 1.
  • 2.

然而,更优的方法是创建一个新列,将新值作为默认值,然后在新旧列之间切换。例如:

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';

ALTER TABLE table
    RENAME COLUMN col1 TO col1_old,
    RENAME COLUMN col1_new TO col1,
    DROP COLUMN col1_old;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

我们使用 col1_new 列的默认值来指定要使用的更新值。这是安全且高效得多的,因为我们跳过了这里的重突变操作。

使用 JOIN 进行更新和删除

有时,我们需要根据关系删除或更新行; 因此,我们必须 join 表。在 ClickHouse 中,使用 Join 表引擎和 joinGet 函数可以最好地实现这一点。假设我们有两个表-一个与所有的页面浏览量和另一个其他所有的登录跟踪:

CREATE TABLE pageviews
(
    `user_id` UInt64,
    `time` DateTime,
    `session_id` UInt64
)
ENGINE = MergeTree
ORDER BY time;

CREATE TABLE logins
(
    `user_id` UInt64,
    `time` DateTime
)
ENGINE = MergeTree
ORDER BY time;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

这两个表之间的区别在于,logins 表每个会话只存储一个事件。假设在某个时间点,我们决定将 session_id 列添加到 logins 表中:

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64
  • 1.
  • 2.

我们现在需要使用 user_id 和 time 上的 JOIN,用 pageviews 表中的相应值更新 logins.session_id 列:

SELECT *
FROM logins AS l
JOIN pageviews AS p ON (p.user_id = l.user_id) AND (p.time = l.time)

┌─user_id─┬────────────────time─┬─p.user_id─┬──────────────p.time─┬─session_id─┐
│       22023-01-09 12:23:1622023-01-09 12:23:162752888102 │
│       12023-01-09 13:23:1612023-01-09 13:23:164135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

首先,我们需要创建并填充一个特殊的 Join 表:

CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews
  • 1.
  • 2.
  • 3.
  • 4.

该表将允许我们在执行更新查询时使用 joinGet 函数来基于 JOIN 获取值:

ALTER TABLE logins
    UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 0
  • 1.
  • 2.

我们可以看到,logins 表被相应的更新了 JOIN:

SELECT * FROM logins

┌─user_id─┬────────────────time─┬─session_id─┐
│       22023-01-09 12:23:162752888102 │
│       12023-01-09 13:23:164135462640 │
└─────────┴─────────────────────┴────────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

因为我们已经通过添加 session_id 列更改了 logins 表,所以我们可以在更改完成后 DROP pageviews_join 表(删除之前请检查 system.mutations 表以确定):

DROP TABLE pageviews_join
  • 1.

同样的方法也可以用于通过轻量级或基于突变的删除来删除数据。

高效删除大块数据块

如果我们必须删除大的数据块,用户可以对表进行分区,以便根据需要删除分区。这是一个轻量级操作。假设我们有以下表:

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

通过 project 列对该表进行分区,我们可以通过删除整个分区来删除具有特定 project 值的行。让我们删除所有带有 project = c 的内容:

ALTER TABLE hits
    DROP PARTITION 'c'
  • 1.
  • 2.

这里,c 是我们想要删除的 project 列值:

图片图片

可用分区的列表可以在 system.parts 表中找到:

SELECT partition
FROM system.parts
WHERE table = 'hits'

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

我们还可以使用 DETACH 和 ATTACH 语句在表之间移动分区(例如,如果我们想将数据移动到 trash 表而不是删除它)。

在 DDL 中设置分区时,要注意按具有高基数的列或表达式进行分区的常见缺陷。这可能导致创建许多部件,从而导致性能问题。

定期删除旧数据

对于时间序列数据,我们可能希望定期删除过时的数据。ClickHouse 对于这个确切的用例具有 TTL 特性。这需要配置一个表,并指定我们希望删除哪些数据以及何时删除。假设我们想从 hits 表中删除超过一个月的数据:

  • https://docs.google.com/document/d/1CAmuBror9fGLZwFCiBUH5rt89LKq-ylA1Lpa6-FrJns/edit
  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl
ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH
  • 1.
  • 2.

在这里,我们要求 ClickHouse 删除当前时间的时间列值超过一个月的所有行。还可以在列上设置 TTL,以便在一段时间后将其值重置为默认值。通过按日期进行分区,四舍五入到适当的时间单位,例如,days,可以使该操作更有效。在执行 TTL 规则时,ClickHouse 将以最有效的方式自动删除数据。同样,表不应该按照高基数(例如毫秒粒度)的时间列进行分区,以避免高部分计数。通常按天或月划分对于大多数 TTL 操作来说就足够了。

  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-column-ttl

使用 CollapsingMergeTree 删除和更新

如果我们必须频繁地更新单个行,我们可以使用 CollapsingMergeTree 引擎来有效地管理数据更新。

  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/

假设我们有一个包含文章统计信息的表,用于跟踪每篇文章的阅读深度。我们希望用一行显示每个用户阅读每篇文章的深度。这里的挑战在于,我们必须在用户阅读文章时更新实际的阅读进度。让我们为数据创建一个表:

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_start` DateTime,
    `read_end` DateTime,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (read_start, article_id, user_id)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

特殊的 sign 列用于 CollapsingMergeTree,作为告诉 ClickHouse 我们想要更新特定行的一种方式。如果我们在 sign 列中插入 -1,整行就会被删除。如果我们插入一行 sign = 1,ClickHouse 将保留这一行。要更新的行是根据创建表时在 ORDER BY () DDL 语句中使用的排序键来标识的:

图片图片

为了满足排序键上的重复数据删除条件,我们必须为 read_start, article_id, user_id 列插入相同的值来更新一行。例如,当用户开始阅读一篇文章时,我们插入以下行:

INSERT INTO article_reads
            VALUES(1, 12, 0, now(), now(), 1);
  • 1.
  • 2.

现在我们在表中有了一行:

SELECT *
FROM article_reads

┌─user_id─┬─article_id─┬─read_to─┬──────────read_start─┬────────────read_end─┬─sign─┐
│       11202023-01-06 15:20:322023-01-06 15:20:321 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

一分钟后,当用户达到文章的 70% 时,我们插入以下 2 行:

INSERT INTO article_reads
            VALUES(1, 12, 0, '2023-01-06 15:20:32', now(), -1),
                  (1, 12, 70, '2023-01-06 15:20:32', now(), 1);
  • 1.
  • 2.
  • 3.

第一行的 sign=-1 是用来告诉 ClickHouse,它应该删除现有的行(基于值在 ORDER BY 元组- read_start, article_id 和 user_id 列)。第二个插入的行( sign=1 )是 read_to 列设置为新值 70 的新行。

由于数据更新是在后台进行的,因此最终的结果是一致的,我们应该对 sign 列进行过滤以获得正确的结果:

  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/#table_engine-collapsingmergetree-collapsing-algorithm
SELECT
    article_id,
    user_id,
    max(read_end),
    max(read_to)
FROM article_reads
WHERE sign = 1
GROUP BY
    user_id,
    article_id

┌─article_id─┬─user_id─┬───────max(read_end)─┬─max(read_to)─┐
│         1212023-01-06 15:21:5970 │
└────────────┴─────────┴─────────────────────┴──────────────┘

1 row in set. Elapsed: 0.004 sec.
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

现在,CollapsingMergreTree 引擎会在后台有效地从存储中删除被取消的行,所以我们不必手动删除它们。你可以在这里找到更多使用 CollapsingMergeTree 引擎的例子。

  • https://www.google.com/url?q=https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/%23example-of-use&sa=D&source=docs&ust=1678440218544759&usg=AOvVaw3vOSZ1Rbl24kvzHT48kuyp

使用版本控制和 ReplacingMergeTree 的 Upserts

对于更复杂的情况,我们可能希望使用基于 ReplacingMergeTree 引擎的版本控制。这个引擎实现了一种在其他dbms中执行UPSERT的有效方法,即使用一个特殊的版本列来跟踪应该删除哪些行。如果存在多个具有相同排序键的行,则只有具有最大版本的行保留在存储中,而其他行则被删除:

图片图片

对于前面的阅读文章的例子,我们可以使用下面的结构:

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_time` DateTime,
    `version` Int32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (article_id, user_id)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

请注意特殊的 version 数字列,ReplacingMergeTree 引擎将使用它来标记要删除的行。让我们模拟一个用户在 0 到 80% 的时间内阅读一篇文章:

INSERT INTO article_reads
           VALUES(1, 12, 0, '2023-01-06 15:20:32', 1),
                 (1, 12, 30, '2023-01-06 15:21:42', 2),
                 (1, 12, 45, '2023-01-06 15:22:13', 3),
                 (1, 12, 80, '2023-01-06 15:23:10', 4);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

这里,我们在跟踪读取进度时增加 version 列的值。删除行的过程也是通过正常的合并周期在后台执行的,所以我们需要根据查询时的最新版本进行过滤:

SELECT *
FROM article_reads
WHERE (user_id = 1) AND (article_id = 12)
ORDER BY version DESC
LIMIT 1

┌─user_id─┬─article_id─┬─read_to─┬───────────read_time─┬─version─┐
│       112802023-01-06 15:23:105 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

或者我们可以使用 LIMIT 1 BY 来获取最新版本的行列表:

SELECT
    user_id,
    article_id,
    read_to
FROM article_reads
ORDER BY version DESC
LIMIT 1 BY
    user_id,
    article_id

┌─user_id─┬─article_id─┬─read_to─┐
│       11280 │
└─────────┴────────────┴─────────┘
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

再次,我们不必关心旧版本的删除-这是由 ClickHouse 在后台自动完成。

总结

在分析环境中更新和删除数据可能具有挑战性,并且会极大地影响数据库性能。为了解决这个问题,ClickHouse 提供了多种强大的方法来更新和删除不同情况下有效的数据:

  • 轻量删除通过 DELETE FROM 语法删除数据从 ClickHouse。这是最有效的删除数据的方法,前提是不需要立即节省磁盘空间,并且用户可以容忍删除的数据“存在”在磁盘上。
  • 在需要立即节省磁盘空间的情况下,通过 ALTER…DELETE 进行基于突变的删除。例如,遵从性需求需要保证从磁盘中删除数据。
  • 在不规律和不频繁的变化情况下,使用 ALTER…UPDATE 进行基于突变的更新
  • 使用 TTLs 定期删除基于日期/时间的(过时的)数据;
  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
  • 使用 CollapsingMergeTree 频繁地更新或删除单个行。
  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/
  • 使用 ReplacingMergeTree 实现基于版本控制的 upsert(插入/更新)。
  • https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree/
  • 定期删除大数据块时删除分区。
  • https://clickhouse.com/docs/en/sql-reference/statements/alter/partition/#drop-partitionpart
  • 创建新列(并删除旧列)可能也是更新整个表的更有效的方法。

Refs

  • https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse