更新和删除 ClickHouse 数据

尽管 ClickHouse 专为高容量分析工作负载而设计，但在某些情况下，可以修改或删除现有数据。这些操作被标记为“mutation”，并使用 ALTER TABLE 命令执行。您还可以使用 ClickHouse 的轻量级删除功能 DELETE 行。

提示

如果您需要执行频繁的更新，请考虑在 ClickHouse 中使用重复数据删除，它允许您更新和/或删除行，而无需生成 mutation 事件。

更新数据

使用 ALTER TABLE...UPDATE 命令更新表中的行

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression> 是满足 <filter_expr> 条件的列的新值。<expression> 必须与列的数据类型相同，或者可以使用 CAST 运算符转换为相同的数据类型。<filter_expr> 应该为数据的每一行返回一个 UInt8（零或非零）值。多个 UPDATE <column> 语句可以在一个 ALTER TABLE 命令中组合，并用逗号分隔。

示例:

像这样的 mutation 允许使用字典查找更新替换 visitor_ids

ALTER TABLE website.clicks UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id) WHERE visit_date < '2022-01-01'

2.   Modifying multiple values in one command can be more efficient than multiple commands:

 ```sql
 ALTER TABLE website.clicks
 UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
 WHERE visit_date < '2022-01-01'

Mutation 可以在分片表上 ON CLUSTER 执行

ALTER TABLE clicks ON CLUSTER main_cluster
UPDATE click_count = click_count / 2
WHERE visitor_id ILIKE '%robot%'

注意

无法更新作为主键或排序键一部分的列。

删除数据

使用 ALTER TABLE 命令删除行

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

<filter_expr> 应该为数据的每一行返回一个 UInt8 值。

示例

删除列值在值数组中的任何记录

ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)

此查询会更改什么？

ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

注意

要删除表中的所有数据，使用 TRUNCATE TABLE [<database].]<table> 命令更有效。此命令也可以在 ON CLUSTER 上执行。

查看 DELETE 语句文档页面以获取更多详细信息。

轻量级删除

删除行的另一个选项是使用 DELETE FROM 命令，这被称为轻量级删除。删除的行会立即标记为已删除，并将自动从所有后续查询中过滤掉，因此您不必等待部件合并或使用 FINAL 关键字。数据清理在后台异步发生。

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

例如，以下查询从 hits 表中删除 Title 列包含文本 hello 的所有行

DELETE FROM hits WHERE Title LIKE '%hello%';

关于轻量级删除的一些注意事项

此功能仅适用于 MergeTree 表引擎系列。
轻量级删除默认是异步的。将 mutations_sync 设置为 1 以等待一个副本处理该语句，并将 mutations_sync 设置为 2 以等待所有副本。

更新数据​

删除数据​

轻量级删除​

更新数据

删除数据

轻量级删除