ClickHouse系列教程 7. DML 相关操作介绍

1. 数据的写入

1. ClickHouse内部所有的数据操作都是面向Block数据块的，所以INSERT查询最终会将数据转换为Block数据块。

2. INSERT语句在单个数据块的写入过程中是具有原子性的。

3. 在默认的情况下，每个数据块最多可以写入1,048,576行数据（由max_insert_block_size参数控制）。

4. 如果一条INSERT语句写入的数据少于 max_insert_block_size 行，则这批数据的写入具有原子性，即要么全部成功，要么全部失败。

5. 需要注意的是，只有在 ClickHouse 服务端处理数据的时候才具有这种原子写入的特性，例如使用JDBC或者HTTP接口时。因为max_insert_block_size 参数在使用 CLI 命令行或者 INSERT SELECT 子句写入时是不生效的。

• 数据写入有三种方式

1. 使用VALUES格式的常规语法

INSERT INTO  -- 插入数据
  [db.] table_name -- 指定数据库与数据表
  [(c1,c2,c3…)]  -- 指定要插入数据的列
VALUES
(v11,v12,v13…), -- 插入的数据值
(v21,v22,v23…),
...

2. 使用指定格式的语法

INSERT INTO -- 插入数据
  [db.] table_name  -- 指定数据库与数据表
  [(c1,c2,c3…)] -- 指定要插入数据的列
FORMAT format_name -- 指定数据格式，支持的数据格式可以在下面文档查看
data_set -- 插入的数据集

ClickHouse 支持的输入/输出格式

3. 第三种是使用SELECT子句形式的语法：

INSERT INTO  -- 插入数据
[db.] table_name  -- 指定数据库与数据表
[(c1,c2,c3…)]  -- 指定要插入数据的列
SELECT ... -- 指定查询语句

• 写入数据示例

-- 创建表
CREATE TABLE IF NOT EXISTS insert_test(
    `id` Int8,
    `name` String,
    `insert_type` String
)ENGINE = MergeTree()
order by id;

-- 常规方式写入数据

INSERT INTO default.insert_test (id,name,insert_type) VALUES(1,'小明','insert'),(2,'小红','insert');

INSERT INTO default.insert_test  VALUES(3,'小丽','insert');

INSERT INTO default.insert_test (name,insert_type) VALUES('小雪','insert');

• csv 格式方式写入数据

-- 准备 2个 csv 格式文件，一个包含列名，一个不包含列名
[root@node3 opt]# cat data_without_title.csv 
4,xioaming,format_csv_without_title
5,xiaohong,format_csv_without_title

[root@node3 opt]# cat data_with_title.csv  # 包含列名
id,name,insert_type
6,xiox,format_csv_with_title
7,xiao,format_csv_with_title

-- 导入不包含表头的 csv 文件里的数据
cat data_without_title.csv | clickhouse-client --port=9977 --query " INSERT INTO default.insert_test FORMAT CSV "

-- 导入包含表头的 csv 文件里的数据
clickhouse-client --port=9977 --query="INSERT INTO default.insert_test FORMAT CSVWithNames" < format_csv_with_title.csv

• 使用 select 方式插入数据

insert into default.insert_test select 8,'小刚','select';

2. 修改与删除数据

ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为 Mutation 查询，它可以看作 ALTER 语句的变种。虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的 UPDATE 和 DELETE 来理解，我们必须清醒地认识到它的不同：

• Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；
• 它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；
• Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。

所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。

• DELETE语句的完整语法如下所示：

ALTER TABLE [db_name.] table_name DELETE WHERE filter_expr
-- 数据删除的范围由WHERE查询子句决定

-- 查看存储目录文件
[root@node3 insert_test]# cd /var/lib/clickhouse/data/default/insert_test

[root@node3 insert_test]# ll
总用量 4
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_1_1_0
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_2_2_0
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_3_3_0
drwxr-x--- 2 clickhouse clickhouse   6 4月  29 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 4月  29 10:53 format_version.txt

-- 删除 id =1 的数据
alter table default.insert_test delete where id = 1;

-- 这是一个异步的后台执行动作，但是由于演示的数据很少，DELETE操作给人的感觉和常用的OLTP数据库无异

-- 查看存储目录变化

[root@node3 insert_test]# ll
总用量 8
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_1_1_0
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_1_1_0_4
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_2_2_0
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_2_2_0_4
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_3_3_0
drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_3_3_0_4
drwxr-x--- 2 clickhouse clickhouse   6 4月  29 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 4月  29 10:53 format_version.txt
-rw-r----- 1 clickhouse clickhouse  81 4月  29 10:55 mutation_4.txt

-- 每个文件夹都增加了一个同名目录，末尾增加了一个 _4 的后缀
-- 增加了 mutation_4.txt 文件 记录了进行了哪些操作，与操作时间
[root@node3 insert_test]# cat mutation_4.txt
format version: 1
create time: 2021-04-29 10:55:01
commands: DELETE WHERE id = 1

-- 查看 system.mutations 操作记录表

SELECT database,table,mutation_id,block_numbers.number as num,is_done FROM system.mutations;

┌─database─┬─table───────────────┬─mutation_id────┬─num─┬─is_done─┐
│ db1      │ alter_add_column    │ mutation_2.txt │ [2] │       1 │
│ db1      │ alter_add_column    │ mutation_3.txt │ [3] │       1 │
│ db1      │ t_partition_default │ mutation_3.txt │ [3] │       1 │
│ default  │ insert_test         │ mutation_4.txt │ [4] │       1 │
└──────────┴─────────────────────┴────────────────┴─────┴─────────┘

至此，整个Mutation操作的逻辑就比较清晰了。

每执行一条ALTERDELETE语句，都会在mutations系统表中生成一条对应的执行计划，当is_done等于1时表示执行完毕。

与此同时，在数据表的根目录下，会以mutation_id为名生成与之对应的日志文件用于记录相关信息。

而数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，新目录的命名规则是在原有名称上加上system.mutations.block_numbers.number。

数据在重写的过程中会将需要删除的数据去掉。

旧的数据目录并不会立即删除，而是会被标记成非激活状态（active为0）。

等到MergeTree引擎的下一次合并动作触发时，这些非激活目录才会被真正从物理意义上删除。

数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除别无二致。

• 修改数据语法：

-- UPDATE支持在一条语句中同时定义多个修改字段，分区键和主键不能作为修改字段。
ALTER TABLE [db_name.]table_name UPDATE column1=expr1 [,...] WHERE filter_expr

ALTER TABLE default.insert_test UPDATE name='小明' where id=2;