更新和删除ClickHouse数据
尽管ClickHouse专注于高容量分析工作负载,但在某些情况下,修改或删除现有数据是可能的。这些操作被标记为“mutations”,并使用ALTER TABLE
命令执行。您还可以使用ClickHouse的轻量级删除功能来DELETE
行。
如果需要频繁执行更新操作,请考虑在ClickHouse中使用去重,它允许您更新和/或删除行而不生成突变事件。
更新数据
使用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
命令中使用逗号分隔。
示例:
通过字典查找,这样的突变允许使用新的
visitor_ids
替换旧的visitor_ids
:ALTER TABLE website.clicks
UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
WHERE visit_date < '2022-01-01'一次修改多个值的命令可能比多个命令更有效:
ALTER TABLE website.clicks
UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
WHERE visit_date < '2022-01-01'可以在分片表上执行突变:
```sql
ALTER TABLE clicks ON CLUSTER main_cluster
UPDATE click_count = click_count / 2
WHERE visitor_id ILIKE '%robot%'
```note无法更新主键或排序键的列。
删除数据
使用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)
- 以下查询会修改什么?
```sql
ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'
```note要删除表中的所有数据,最好使用
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以等待所有副本。 - 此功能是实验性的,需要您将
allow_experimental_lightweight_delete
设置为true:
SET allow_experimental_lightweight_delete = true;