clickhouse中update/delete的使用之mutation

　　Clickhouse是个分析型数据库。这种场景下，数据一般是不变的，因此Clickhouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。

1.Clickhouse通过alter方式实现更新、删除，它把update、delete操作叫做mutation(突变)。

　　语法为：

ALTER TABLE [db.]table DELETE WHERE filter_expr
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

　　那么，mutation与标准的update、delete有什么区别呢？

　　标准SQL的更新、删除操作是同步的，即客户端要等服务端反回执行结果（通常是int值）；而Clickhouse的update、delete是通过异步方式实现的，当执行update语句时，服务端立即反回，但是实际上此时数据还没变，而是排队等着。它们是异步后台进程，类似于MergeTree表中的合并，用于生成新的“变异”版本的部件。

　　1)该查询删除表中此表达式取非零值的行。

　　2)此查询将指定列的值更新为行中对应的表达式的值，这些表达式的值filter_expr取非零值。使用运算符将值转换为列类型CAST。不支持更新用于计算主键或分区键的列。

支持子查询但是不支持直接的delete /update join 动作。

一个查询可以包含多个用逗号分隔的命令。

　　大多数ALTER TABLE查询仅支持*MergeTree表以及Merge和Distributed

　　1）对于*MergeTree表，突变通过重写整个数据部分来执行。没有原子性 - 部件一旦准备好就被变异部件替换，并且SELECT在变异期间开始执行的查询将看到来自已经变异部件的数据以及来自尚未变异部件的数据。

　　2）突变完全按其创建顺序排序，并按该顺序应用于每个部分。突变也通过INSERT INTO查询部分排序：在提交突变之前插入到表中的数据将被突变，之后插入的数据不会被突变。请注意，突变不会以任何方式阻止插入。

　　3）添加突变条目后，突变查询立即返回（如果复制表到 ZooKeeper，对于非复制表 - 到文件系统）。突变本身使用系统配置文件设置异步执行。要跟踪突变的进度，您可以使用该system.mutations表。即使重新启动 ClickHouse 服务器，成功提交的变更仍将继续执行。一旦提交，就无法回滚突变，但如果突变由于某种原因被卡住，可以通过KILL MUTATION查询取消它。

　　4）完成突变的条目不会立即删除（保留条目的数量由finished_mutations_to_keep存储引擎参数确定）。旧的突变条目被删除。

　　5）对于非复制表，所有ALTER查询都是同步执行的。对于复制的表，查询只是将适当操作的指令添加到ZooKeeper，并且操作本身会尽快执行。但是，查询可以等待在所有副本上完成这些操作。对于所有ALTER查询，您可以使用replication_alter_partitions_sync设置来设置等待。您可以使用replication_wait_for_inactive_replica_timeout设置指定等待非活动副本执行所有ALTER查询的时间（以秒为单位）。！！！对于所有ALTER查询，如果replication_alter_partitions_sync = 2某些副本不活动超过replication_wait_for_inactive_replica_timeout设置中指定的时间，UNFINISHED则抛出异常。

2.查看mutation队列

　　那么，怎么查看数据是否更新完成了呢？

　　可以通过system.mutations表查看相关信息：

SELECT
    database,
    table,
    command,
    create_time,
    is_done
FROM system.mutations
LIMIT 10

┌─database─┬─table─────────────────┬─command─────────────────────────────────────────────────────────────────────────────┬─────────create_time─┬─is_done─┐
│ app      │ scene_model           │ UPDATE status = '2' WHERE id = '208209306'                                          │ 2020-03-30 15:38:58 │       1 │
│ app      │ scene_model           │ UPDATE status = '2' WHERE id = '100000004'                                          │ 2020-03-30 15:40:00 │       1 │
│ app      │ scene_model           │ UPDATE status = '2' WHERE id = '100000004'                                          │ 2020-03-30 15:41:09 │       1 │
│ app      │ user_model            │ UPDATE name = 'zhuweiming' WHERE id = '0000000047fd31e40147fd3477cc0000'            │ 2020-03-19 18:34:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000900') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000901') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000902') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000903') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000904') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
│ app      │ work_statistics_total │ UPDATE pv = 10000, uv = 10000 WHERE (id = '1000905') AND (product = 'tracker_view') │ 2020-03-31 14:45:59 │       1 │
└──────────┴───────────────────────┴─────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴─────────┘

database: 库名
table: 表名
command: 更新/删除语句
create_time: mutation任务创建时间，系统按这个时间顺序处理数据变更
is_done: 是否完成，1为完成，0为未完成

　　除了上述的，还有一些其他的字段，详见官方文档。

　　通过以上信息，可以查看当前有哪些mutation已经完成，is_done为1即表示已经完成。

3.Mutation具体过程

　　首先，使用where条件找到需要修改的分区；
　　然后，重建每个分区，用新的分区替换旧的，分区一旦被替换，就不可回退；

　　对于每个分区，可以认为是原子性的；但对于整个mutation，如果涉及多个分区，则不是原子性的。

4.注意事项

　　1）更新功能不支持更新有关主键或分区键的列

　　2）更新操作没有原子性，即在更新过程中select结果很可能是一部分变了，一部分没变，从上边的具体过程就可以知道

　　3）更新是按提交的顺序执行的

　　4）更新一旦提交，不能撤销，即使重启clickhouse服务，也会继续按照system.mutations的顺序继续执行

　　5）已完成更新的条目不会立即删除，保留条目的数量由finished_mutations_to_keep存储引擎参数确定。超过数据量时旧的条目会被删除

　　6）更新可能会卡住，比如update intvalue='abc'这种类型错误的更新语句执行不过去，那么会一直卡在这里，此时，可以使用KILL MUTATION来取消，语法：

kill mutation where database='app' and table='test'

-- database、table是system.mutations表中的字段

　　当执行删除更新失败的时候需注意：对于mutations执行失败的话，先去查询system.mutations的任务，查看is_done=0的数据，并且看下latest_fail_reason的原因。如果发现问题不是sql的原因，则删除mutations再尝试执行删除或者更新，如果是sql或者其他原因，查询找到原因解决再执行删除或者更新。

7）ClickHouse 中的 mutation 操作(update/delete) 默认是异步执行的, 这会导致一种情况的出现: 删除的数据在一段时间内还能查询到. 在非事务性的使用场景中这个设置可以加快处理速度, 并且不会影响后来数据的添加, 但在要求事务性的使用场景中(比如新增数据依赖历史数据), 这个设置会导致后加的数据出现错误.

　　解决方法：在 ClickHouse 的配置文件中加入 mutation_sync 参数, 令其等于 1 或 2

　　mutation_sync 参数默认为 0, 也就是 mutation 操作异步执行, 设为 1 会在当前机器上阻塞直到操作完成, 设为 2 会一直阻塞直到操作在所有数据副本上执行完成, 主要用于集群。

　　修改 mutation_sync 这个参数的方法很简单, 如果只是为了临时修改一下, 在启动客户端命令时加入 -mutations_sync=1 就可以了.

　　如果想持久化这个修改, 在 /etc/clickhouse-server/users.xml 这个配置文件中 profile 的 default 设置(对所有继承default设置的用户生效)或者特定用户(只对特定用户生效)的配置信息中加入 <mutations_sync>2</mutations_sync> 即可