ClickHouse增加删除更新操作

前面我们已经介绍过 ClickHouse 是列式存储数据库，并且是按照有序存储、且按照索引粒度建立稀疏索引，所以 ClickHouse 是不擅长做 update/delete 操作的，对于需要经常变化的数据，也不建议使用clickhouse。但是并不是说clickhouse就不能更新数据，clickhouse提供了一种基于alter语句的“突变”（mutations）操作来实现更新/删除操作。在使用mutations操作之前需要注意：

1. mutations操作需要重置分区，是一种“很重”的操作，更适用于操作批量数据，最好是直接删除分区。如果是删除分区操作，应直接drop分区。
2. clickhouse不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚。即使ClickHouse服务器重新启动，成功提交的mutations操作也将继续执行，但是如果mutations由于某种原因被卡住了，可以用 KILL MUTATION 语句取消它。
3. mutations操作的执行是一个异步的后台过程，语句被提交之后就会立即返回，但并不表示数据已经被删除，会有一个后台进程去删除数据操作，具体数据什么时候删除，可以在系统表 system.mutations 查看，system.mutations 表记录了 mutations操作执行信息，is_done = 1表示删除完成。
4. mutations操作是按照队列顺序执行的，虽然是异步的，但是在提交mutations操作之后插入的数据不会被影响。
5. 已完成更新的条目不会立即删除，保留条目的数量由finished_mutations_to_keep存储引擎参数确定，超过数据量时旧的条目会被删除。
6. 对于非副本表，所有ALTER查询都是同步执行的。对于副本表，查询只是向ZooKeeper添加适当操作的指令，操作本身会尽快执行，可以通过replication_alter_partitions_sync设置控制执行等待，如果为0表示不等待，如果为1表示只等待自己执行(默认，即一个)，如果为2表示需要等待所有节点完成。另外还可以通过replication_wait_for_inactive_replica_timeout参数设置等待时间，0表示不等待，负整数表示无限制等待，正整数表示等待秒数。如果 replication_alter_partitions_sync = 2，某些副本ALTER操作超过 replication_wait_for_inactive_replica_timeout 时间，则会抛出 UNFINISHED 异常。
7. ALTER TABLE … UPDATE|DELETE 语句默认都是异步执行的，但是可以通过 mutations_sync 参数控制，为0表示异步（默认），为1表示等待当前节点的所有mutations完成，为2表示等待所有副本节点执行完成。

1. UPDATE 语句

语法格式如下，和标准SQL语法相似：

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

Examples：

-- 使用字典查找更新
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';

-- 对于分片表，可以在CLUSTER上执行 mutations，旧版本不支持
ALTER TABLE clicks ON CLUSTER main_cluster UPDATE click_count = click_count / 2 
WHERE visitor_id ILIKE '%robot%';
1
2
3
4
5
6
7
8
9
10
11

注意：

• 上面例子中的更新分片表在旧版本中是不支持的，只能更新本地表数据。如果没有做DDL语句同步的话，需要在分布式表的所有本地表对应的节点上分别执行 mutations 操作。
• 不能更新主键或者 ORDER BY 字段。

如果只对某个分区做UPDATE操作，除了在WHERE语句中指定条件外，还可以直接指定分区：

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] 
WHERE filter_expr;

-- Example
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2;
1
2
3
4
5

2. DELETE 语句

语法格式如下，和标准SQL语法相似：

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

Examples：

-- 删除记录
ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277);

-- 删除分片表数据
ALTER TABLE clicks ON CLUSTER main_cluster WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573';
1
2
3
4
5

注意：

• 旧版本分片表同样不支持DELETE操作。
• 如果是删除全表数据，应使用 TRUNCATE TABLE，效率会更高。

如果只对某个分区做DELETE操作，除了在WHERE语句中指定条件外，还可以直接指定分区：

ALTER TABLE [db.]table DELETE [IN PARTITION partition_id] WHERE filter_expr;

-- Example
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2;
1
2
3
4

如果删除整个分区数据，应该直接使用drop分区操作，参考第5节。

3. TRUNCATE 语句

TRUNCATE TABLE 适用于删除全表数据的情况，而且效率比DELETE更高。语法格式如下，高版本同样支持 ON CLUSTER 子句：

TRUNCATE TABLE [<database].]<table>
1

需要注意的是在一些低版本clickhouse中，直接对分片表使用TRUNCATE TABLE语句或许不会报错，但是数据并没有被删掉，依然需要对本地表执行操作。

4. DROP 语句

可以删除数据库、表、字典等。语法格式如下：

-- 删除数据库，先删除库中所有表，再删除数据库
DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster];

-- 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster];

-- 删除字段
DROP DICTIONARY [IF EXISTS] [db.]name;

-- 删除视图，也可以通过 DROP TABLE 删除
DROP VIEW [IF EXISTS] [db.]name [ON CLUSTER cluster];
1
2
3
4
5
6
7
8
9
10
11

5. 列操作语句

语法格式如下，支持添加、删除、重命名字段，清楚列数据，添加注释等，可以通过逗号分隔执行多个列操作：

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...
1

5.1 新增列

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST]
1

新增列默认是放在最后一列，AFTER 子句可以指定在某个列之后，FIRST 子句表示放在第一列。新增列数据默认都是对应类型的默认值（或者指定的默认值）。
Example:

ALTER TABLE alter_test ADD COLUMN Added1 UInt32 FIRST;
ALTER TABLE alter_test ADD COLUMN Added2 UInt32 AFTER NestedColumn;
1
2

5.2 删除列

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name
1

如果列被物化视图引用，则不能被删除。
Example:

ALTER TABLE visits DROP COLUMN browser
1

5.3 重命名列

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] RENAME COLUMN [IF EXISTS] name to new_name
1

不能对 ORDER BY or PRIMARY KEY 字段RENAME，否则报错。
Example:

ALTER TABLE visits RENAME COLUMN webBrowser TO browser
1

5.4 清除列数据

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
1

清除指定分区上的指定列数据为默认值。
Example:

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple();
ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902;
1
2

5.5 添加列注释

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'Text comment'
1

每一列可以有一个注释。如果该列已有注释，则新注释将覆盖以前的注释。表字段注释可以通过 DESCRIBE TABLE 语句查询。
Example:

ALTER TABLE visits COMMENT COLUMN browser 'The table shows the browser used for accessing the site.'
1

5.6 修改列属性

语法格式如下：

ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST];
ALTER TABLE [db].name [ON CLUSTER cluster] ALTER COLUMN [IF EXISTS] name TYPE [type] [default_expr] [codec] [TTL] [AFTER name_after | FIRST];
1
2

修改列语句支持MODIFY / ALTER关键词，可以修改列类型、列顺序、TTL、压缩算法、默认值等。在更改类型时，将对值进行转换，就像对它们应用toType函数一样，需要花费较多时间。如果只更改默认表达式，则查询不会执行任何复杂的操作，而且几乎可以立即完成。
Example:

-- 更改字段类型
ALTER TABLE visits MODIFY COLUMN browser Array(String)
1
2

5.7 删除列属性

语法格式如下：

ALTER TABLE table_name MODIFY column_name REMOVE property;
1

可删除列默认值、压缩算法、注释、TTL等。
Example:

-- 删除TTL
ALTER TABLE table_with_ttl MODIFY COLUMN column_ttl REMOVE TTL;
1
2

6. 分区操作语句

支持卸载、删除、加载、拷贝、移动、备份等操作。

6.1 卸载分区

语法格式如下：

ALTER TABLE table_name DETACH PARTITION|PART partition_expr
1

卸载分区的意思是把分区数据移动到 detached 目录，相当于数据从表中被删除，但是没有完全删除，还可以通过 ATTACH 语句加载分区，恢复数据，类似于我们电脑的回收站机制。
Example:

ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0';
1
2

对于副本表，DETACH 语句会被同步到所有副本节点上执行，但是 DETACH 语句本身只能在leader节点上执行，可以通过 system.replicas 表查询leader副本节点，也可以对所有副本执行 DETACH 语句，非leader副本会抛出异常。

6.2 加载分区

语法格式如下：

ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
1

从 detached 目录中加载分区或部分数据，可以是已卸载的分区数据或者是复合格式的数据文件。
Examples:

ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;
1
2

对于副本表，该操作同样会同步到所有副本执行，如果当前副本 detached 目录没有复合条件的数据，而其他副本节点有，则当前副本会从其他副本下载数据，所以如果是外部数据，放置在任何一个副本节点上即可。

6.3 删除分区

语法格式如下：

ALTER TABLE table_name DROP PARTITION|PART partition_expr
1

删除分区数据并不会立即被删除，首先把分区标记为 inactive（非活跃），然后大约在10分钟内完全删除数据。在副本表上执行时，会删除所有副本分区。
Example:

ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0';
1
2

6.4 删除已卸载的分区

语法格式如下：

ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr
1

6.5 复制分区

语法格式如下：

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1
1

从表table1中复制分区到表table2，两个表必须具有相同的表结构和分区键。

6.6 替换分区

语法格式如下：

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
1

从表table1中复制分区到表table2，并替换table2中已存在的同名分区，两个表必须具有相同的表结构和分区键。

6.7 移动分区

移动分区有两种情况：从一个表移动到另一个表、从一个表移动到磁盘。

(1) 从一个表移动到另一个表

语法格式如下：

ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest
1

从表table_source中移动分区到表table_dest，table_source中的分区会被删除，两个表必须具有相同的表结构、分区键、家族表引擎（区分副本和非副本表）以及相同的存储策略。

(2) 从一个表移动到磁盘

ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'
1

该操作不会同步到所有副本节点上，因为不同的副本可以有不同的存储策略。

6.8 备份分区

语法格式如下：

ALTER TABLE table_name FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']
1

此查询创建指定分区的本地备份。如果省略PARTITION子句，查询将立即创建所有分区的备份。该查询不会自动复制到所有副本节点上，只会对当前节点做备份，且只备份数据，不备份元数据信息，如果需要备份元数据，需要拷贝 /var/lib/clickhouse/metadata/database/table.sql 文件。

6.9 删除备份分区

语法格式如下：

ALTER TABLE 'table_name' UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name'
1

从磁盘中移除指定名称的备份分区。如果省略PARTITION子句，查询将立即删除所有分区的备份。

6.10 清除分区索引

语法格式如下：

ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr
1

6.11 设置分区表达式

几乎所有的分区操作都需要指定 partition_expr（分区表达式），但是我们在建表的时候分区定义可能是五花八门，可以是数值型字段、时间类型字段，或者字段的一部分等等，怎么确定分区表达式呢？

1. 通过 system.parts 表查询分区名称。
2. 按照建表语句格式指定，例如建表按照月份分区，那么 partition_expr 可以为：tuple(toYYYYMM(toDate(‘2019-01-25’))) 。
3. 通过分区ID指定，需要注意如果使用分区ID，语句中必须加ID关键字：ALTER TABLE visits DETACH PARTITION ID ‘201901’。

同样，OPTIMIZE 语句也支持指定分区：

OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;
1

7. SETTING 操作语句

在前面介绍MergeTree表原理和建表语句的文章中，我们介绍了建表语句中的 SETTINGS 选项，可以指定一系列参数。同样，这些参数也是可以更改的（只适用于MergeTree表）。语法格式如下，可以同时修改多个设置参数：

ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY|RESET SETTING ...
1

7.1 修改 SETTING

Example：

CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id;

ALTER TABLE example_table MODIFY SETTING max_part_loading_threads=8, max_parts_in_total=50000;
1
2
3

7.2 清除 SETTING

恢复SETTING项到默认值：

CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id
SETTINGS max_part_loading_threads=8;

ALTER TABLE example_table RESET SETTING max_part_loading_threads;
1
2
3
4

8. 跳数索引操作语句

clickhouse 除了主键中的稀疏索引以外，还支持二级索引（跳数索引），在前面介绍二级索引的文章中，我们介绍了二级索引的种类、原理，以及在建表语句中怎么创建二级索引。除了在建表语句中定义二级索引外，我们还可以在后期添加、删除二级索引。

语法格式如下：

-- 添加二级索引
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value [FIRST|AFTER name];

-- 删除索引
ALTER TABLE [db].name DROP INDEX name;

-- 重建索引，一般用于新增索引对历史数据生效
ALTER TABLE [db.]table MATERIALIZE INDEX name [IN PARTITION partition_name];
1
2
3
4
5
6
7
8

Example：

-- 对表skip_table的my_value列建立名为vix的set二级索引
ALTER TABLE skip_table ADD INDEX vix my_value TYPE set(100) GRANULARITY 2;
1
2

需要注意新建立的二级索引只对新增数据有效，所以是一个轻量级操作，如果想要对历史数据也有效，需要执行：

ALTER TABLE skip_table MATERIALIZE INDEX vix;
1

该操作需要对历史数据重建索引，所以是一个 mutation 操作。

二级索引操作语句会自动同步到所有副本节点上。

9. 约束操作语句

建表指定约束：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine
1
2
3
4
5
6
7

添加、删除约束：

ALTER TABLE [db].name ADD CONSTRAINT constraint_name CHECK expression;
ALTER TABLE [db].name DROP CONSTRAINT constraint_name;
1
2

约束操作语句对所有副本有效。添加大量的约束可能会对大型INSERT操作的性能产生影响。