![cover](https://img-blog.csdnimg.cn/3590f4302bce4aff9f88dccef20dfc56.jpeg)
在 ClickHouse 中处理更新和删除
ClickHouse为不同的情况提供了多种强大的方式来有效地更新和删除数据
本文字数:9491;估计阅读时间:24 分钟
作者:ClickHouse中国
Meetup活动:
首届ClickHouse官方Meetup活动正式开放报名,文末扫码报名!
作为世界上最快的实时分析数据库,许多 ClickHouse 的工作负载涉及大量的一次性写入数据,且并不经常修改数据(例如,由 IoT 设备生成的遥测事件,或电商网站产生的客户点击)。虽然这些数据通常是不可变的,但作为那些在分析过程中提供上下文的数据集(例如,基于设备或客户 ID 的查找表),可能就需要修改了。
在历史上,根据您不同的目标和性能需求,ClickHouse 提供了多种更新和删除数据的方法。本文的其余部分描述了每种方法及其权衡考虑,以及与轻量级删除相关的一些最新进展,它们解决了一些常见的挑战。我们推荐使用最佳实践,并在选择方法时,重点考虑那些重要的权衡因素。
在处理数据前,请确认“更新”是否是您解决问题的最佳方法。例如,对于不经常更改的数据,版本化数据可能是更好的选择。在存储效率和查询性能方面,ClickHouse 是排名第一的分析型数据库,因此在许多情况下,仅保存数据的多个版本,而不是更新原始数据,可能会更好。
轻量级删除
轻量级删除被公认为:从 ClickHouse 中删除数据的首选和最有效的方式。通过 DELETE FROM table 语法,用户可以指定一个条件,用来删除特定的行,如下所示:
DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2
默认情况下,此操作是异步的,除非将 mutations_sync 设置为1(参见下文)。执行删除时,ClickHouse 为每一行保存一个掩码,表示在 _row_exists 列中是否已删除它。随后的查询则排除了那些被删除的行,如下所示。
在内部,ClickHouse 将数据排序为多个部分(part),每个部分都包含列数据文件和索引。常规的合并周期负责组合(合并)和重写这些部分。这确保随着插入更多的数据,文件的数量并不会继续增长,也能保持快速的查询。这些合并考虑了轻量级删除,在新形成的部分中排除了标记为删除的行。
在 22.8 中发布的,截至写作时仍处于实验阶段,轻量级删除有望在接下来的某个版本中成为生产就绪功能。在此之前,使用轻量级删除需要设置 allow_experimental_lightweight_delete=true 的参数。
用户应该意识到,通过依赖正常的后台合并周期,最终行只会从磁盘上被删除。虽然从搜索结果中排除了这些行,但这些行会一直驻留在磁盘上,直到它们的部分(part)被合并。合并发生的时间是不确定的。这有几个含义:
-
空间节省不会像通过变异(mutations)发出的删除那样,立即的生效-请参见下文。如果空间节省至关重要,例如磁盘空间不足,请考虑使用变异。
-
由于无法保证删除,需要遵守合规性要求的用户可能希望使用变异来确保数据被删除掉。
轻量级删除操作的成本取决于 WHERE 子句中匹配的行数和当前部分(part)中数据的数量。当匹配的行数较少时,此操作将最为有效。用户还应该意识到,轻量级删除在 Wide 部分上表现最好,在这里,列数据文件是分开存储的,而 Compact 部分则使用单个文件用于所有列数据。前者允许将掩码 _row_exists 作为单独的文件存储,从而允许它独立于其他列进行写入。通常,在插入后会形成 Compact 的部分。一旦部分超过某个大小(例如,由于合并),就会使用 Wide 格式。对于大多数工作负载,这不应该是一个问题。
最后,请注意,轻量级删除使用到了,我们下面要描述的相同的变异(Mutation)队列和后台线程。我们建议您在(https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals)查看文档,以获取有关内部实现的更多详细信息。
变异 - Mutations
使用 Mutations 更新数据更新
ClickHouse 表中的数据,使用 ALTER...UPDATE 语句是最简单的用法。
ALTER TABLE table
UPDATE col1 = 'Hi' WHERE col2 = 2
此查询将根据给定的过滤条件更新表 table 中的 col1 。
与某些数据库不同,ClickHouse 的 ALTER UPDATE 语句默认是异步的。这意味着更新是在后台进行的,您将不会立即在表上看到更新的效果。这种更新表的过程称为 mutations。
这里需要注意的重要一点是,更新数据是一个重型查询,因为 ClickHouse 必须做很多工作,用来优化存储和处理结果。一个 mutation 操作强制所有包含那些要删除的行的数据部分被重写,当形成新部分时排除目标行。这可能会导致相当大的 I/O 和集群开销,所以使用时要小心,或者考虑下面讨论的其他方法。
使用 mutation 删除数据
像更新一样,删除也可以通过 mutation 进行,并为轻量级删除提供了一种替代方式。在大多数情况下,由于重写所有列的mutation成本,轻量级删除更适合于数据删除。更具体地说,与轻量级删除不同的是:所有列都将被重写,而不仅仅是一个 _row_exists 掩码列。
但是,考虑到轻量级删除的“最终从磁盘删除数据”的属性,用户可能更喜欢这种基于mutation的方法,以实现确保磁盘空间的节省。此外,当用户需要确保数据从磁盘中删除时,例如由于合规性原因,这种方法可能是更合适的。
ALTER TABLE table
DELETE WHERE col2 = 3
此查询中, col2 值为 3 的所有行都会被删除。与其他mutation类似,删除也默认是异步的。可以使用上面描述的相同的 mutations_sync 参数设置来使其同步。
检查mutation进度
由于mutation是异步运行的,可以通过 system.mutations 表进行监控。这允许用户按其需要:检查表上特定mutation 的进度。
SELECT
command,
is_done
FROM system.mutations
WHERE table = 'tablename'
┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 2 │ 1 │
│ UPDATE col1 = 'All hi' WHERE col2 > 0 │ 0 │
└───────────────────────────────────────┴─────────┘
如果特定mutation的 is_done 值为 0 ,则仍在执行。突变是为每个表部分执行的,其中mutation部分立即变得可用。
同步更新
对于需要同步更新的用户,可以将 mutations_sync 参数的值设置为 1 (或者如果我们还想等到所有副本都更新了,就设置为 2 ):
SET mutations_sync = 1
现在,我们的更新查询将等待mutation 的完成:
ALTER TABLE table
UPDATE col1 = 'bye' WHERE col2 > 0
0 rows in set. Elapsed: 1.182 sec.
请注意,当 ClickHouse 等待后台的mutation完成时,这个查询花费了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';
ClickHouse 不允许您更新整个表,因为更新是重型操作。强制 ClickHouse 接受此操作的一种方法是使用always-true的过滤器:
ALTER TABLE table
UPDATE col1 = 'bye' WHERE true
但是,更优的方法是创建一个新列,新值作为默认值,然后切换旧列和新列。例如:
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;
我们使用 col1_new 列的默认值,来指定我们要用作更新值的内容。这是安全的,而且效率更高,因为我们在这里跳过了重型的mutation操作。
使用 JOINs 更新和删除
有时,我们需要基于关系删除或更新行,因此我们必须联接表格。这在 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;
这两个表格的区别在于 logins 表格只存储每个会话的单一事件。假设在某个时刻,我们决定在 logins 表格中添加 session_id 列:
ALTER TABLE logins
ADD COLUMN `session_id` UInt64
现在我们需要使用 user_id 和 time 的 JOIN,将 logins.session_id 列与 pageviews 表的相应值进行更新:
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─┐
│ 2 │ 2023-01-09 12:23:16 │ 2 │ 2023-01-09 12:23:16 │ 2752888102 │
│ 1 │ 2023-01-09 13:23:16 │ 1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘
首先,我们需要创建并填充一个特殊的 Join 表:
CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews
此表格允许我们使用 joinGet 函数,在执行更新查询时基于 JOIN 获取值:
ALTER TABLE logins
UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 0
我们可以看到,logins 表格已经相应地使用 JOIN 更新了:
SELECT * FROM logins
┌─user_id─┬────────────────time─┬─session_id─┐
│ 2 │ 2023-01-09 12:23:16 │ 2752888102 │
│ 1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴────────────┘
因为我们通过添加 session_id 列更改了 logins 表格,所以一旦完成更改,我们可以删除 pageviews_join 表(在删除之前,确保检查 system.mutations 表):
DROP TABLE pageviews_join
同样的方法可以用于删除数据,使用轻量级或基于突变的删除方法。
高效删除大块数据
如果我们需要删除大量数据,用户可以分区一个表格,以便可以根据需要删除分区。这是一个轻量级的操作。假设我们有以下表格:
CREATE TABLE hits
(
`project` String,
`url` String,
`time` DateTime,
`hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)
通过按 project 列分区此表,我们可以通过删除整个分区来删除具有特定 project 值的行。让我们删除 project = c 的所有内容:
ALTER TABLE hits
DROP PARTITION 'c'
这里, c 是我们想要删除的 project 列的值:
在 system.parts 表中可以找到可用的分区列表:
SELECT partition
FROM system.parts
WHERE table = 'hits'
┌─partition─┐
│ c │
│ a │
│ b │
└───────────┘
我们还可以使用 DETACH 和 ATTACH 语句在表格之间移动分区(例如,如果我们想要将数据移动到 trash 表格而不是删除它)。
在您的 DDL 中设置分区时,要注意避免场景的陷阱:按具有高基数的列,或表达式分区。这可能会导致创建许多部分(part),从而导致性能问题。
定期删除旧数据
在时间序列数据的情况下,我们可能想要定期删除过时的旧数据。ClickHouse为这个具体的用例提供了TTL功能。这需要配置一个表,并指定我们想要删除哪些数据,以及何时删除。假设我们想从 hits 表中删除一个月之前的数据:
ALTER TABLE hits
MODIFY TTL time + INTERVAL 1 MONTH
这里我们要求ClickHouse删除所有 time 列值早于当前时间一个月的行。TTL也可以设置在列上,在一段时间后重置其值为默认值。通过按日期分区,四舍五入为适当的时间单位(例如,天),可以使此操作更为高效。当执行TTL规则时,ClickHouse将自动以最有效的方式删除数据。再次强调,表不应该按时间列的高基数(例如,毫秒粒度)进行分区,以避免高产生大量的部分(part)。对于大多数TTL操作,通常按天或月进行分区就足够了。
使用 CollapsingMergeTree 更新和删除
如果我们必须经常更新单个行,我们可以使用 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)
特殊的 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);
现在我们的表中有如下的一行数据:
SELECT *
FROM article_reads
┌─user_id─┬─article_id─┬─read_to─┬──────────read_start─┬────────────read_end─┬─sign─┐
│ 1 │ 12 │ 0 │ 2023-01-06 15:20:32 │ 2023-01-06 15:20:32 │ 1 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘
一分钟后,当用户阅读了文章的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);
带有 sign=-1 的第一行用于告诉ClickHouse它应该删除现有的行(基于 ORDER BY 元组的值 - read_start , article_id 和 user_id 列)。第二个插入的行(带有 sign=1 )是带有 read_to 列的新行,该列设置为新值 70 。
由于数据更新是在后台发生的,最终结果的一致性,因此我们应该在 sign 列上进行过滤以获得正确的结果:
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)─┐
│ 12 │ 1 │ 2023-01-06 15:21:59 │ 70 │
└────────────┴─────────┴─────────────────────┴──────────────┘
1 row in set. Elapsed: 0.004 sec.
CollapsingMergreTree 引擎现在将在后台高效地删除已取消的行,所以我们不必手动删除它们。您可以在此处(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)找到使用 CollapsingMergeTree 引擎的其他示例。
使用版本控制和ReplacingMergeTree进行Upsert
对于更复杂的情况,我们可能希望使用基于ReplacingMergeTree引擎的版本控制。这个引擎实现了在其他数据库管理系统中称为 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)
请注意特殊的 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);
这里,随着阅读进度的跟踪,我们增加了 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─┐
│ 1 │ 12 │ 80 │ 2023-01-06 15:23:10 │ 5 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘
或者我们可以使用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─┐
│ 1 │ 12 │ 80 │
└─────────┴────────────┴─────────┘
同样,我们不需要关心旧版本的移除 - 这由ClickHouse在后台自动完成。
总结
在分析环境中更新和删除数据可能会很有挑战性,并且会显著影响数据库性能。为了解决这个问题,ClickHouse为不同的情况提供了多种强大的方式来有效地更新和删除数据:
-
通过 DELETE FROM 语法的轻量级删除来从ClickHouse中删除数据。这是删除数据的最有效方法,只要不需要立即节省磁盘空间,并且用户可以容忍已删除的数据“存在”在磁盘上。
-
在需要立即节省磁盘空间的情况下,通过 ALTER…DELETE 进行基于Mutation的删除。例如,合规性要求需要保证从磁盘上移除数据。
-
在不规则和不频繁的更改的情况下,使用 ALTER…UPDATE 进行基于Mutation的更新。
-
使用TTL根据日期/时间进行定期移除(过时的)数据,
-
使用CollapsingMergeTree频繁地更新或删除单个行。
-
使用ReplacingMergeTree实现基于版本控制的upsert(插入/更新)。
-
定期删除大块数据时删除分区。
-
创建新列(并删除旧列)也可能是更新整个表的更有效的方法。
Meetup 活动报名通知
好消息:ClickHouse Beijing User Group第1届 Meetup 已经开放报名了,人数限制在 50 人,请大家赶紧扫码报名。
本活动是由 ClickHouse 中国官方组织的线下技术交流活动。ClickHouse 的全球官方用户社区已经在 MeetUp网站上的 25 个城市组织了 meetup 活动,其中官方首届北京 Meetup 活动即将于 11 月 4 日举行。
-
沙龙时间:2023年11月4日
-
沙龙地点:北京市海淀区北四环西路56号辉煌时代大厦 3层WeWork
-
面向人群:ClickHouse 技术爱好者、开发者、运维、架构师等
联系我们
手机号:13910395701
邮箱:Tracy.Wang@clickhouse.com
满足您所有的在线分析列式数据库管理需求
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)