clickhouse （⼆）删除更新⽅案
本⽂探讨clickhouse的1个经典问题：
如何模拟实现记录更新和删除效果？（因为clickhouse ⾃带的update/delete 实现极为低效）
跟着我的例⼦⾛吧。
创建数据库db2
CREATE DATABASE IF NOT EXISTS db2 ON CLUSTER mycluster
上述语句创建db2数据库，ON CLUSTER mycluster指定将该DDL操作⼴播到整个集的所有节点上。
创建商品表product
ON CLUSTER mycluster是说把这张表⼴播到所有节点上建⽴出来。
再说⼀下列：
id ：商品ID
name ：商品名
上述是业务字段，商品id是业务侧的主键。
sign和version是我们设计出来的控制字段，⽤来模拟update和delete操作，⽅案如下：
sign ：1表⽰upsert ，也就是插⼊或者更新；-1表⽰delete ，表⽰删除。
version ：版本号，要保证靠后发⽣的操作⽐先前发⽣的操作version 更⼤。
ReplicatedReplacingMergeTree(‘/clickhouse/tables/{shard}/db2/product’, ‘{replica}’, version)最后的version 是什么意思呢？
这⾥ReplacingMergeTree 是⼀种compaction 阶段能够对相同主键进⾏去重的引擎，当⼀个主键有多条记录时，version ⼤的被留下，其他被compaction 丢掉。
我们就是想要这样的效果，我们只关⼼同1个id 最新version 的数据内容~~~
光说还是不懂，下⾯我们就会进⼊演练，在此之前我们按常规流程创建出分布式表，后续只读写分布式表即可：
ON CLUSTER mycluster在所有node上创建了dis_product分布式表，对它的读取和写⼊都将是对集中所有product本地表的分布式处理。
模拟UPDATE/DELETE 的思路分析
假定我们是同步mysql的binlog，然后写⼊到clickhouse的dis_product表。
解析来的binlog 主要包含3个信息：
1，操作类型（INSERT/UPDATE/DELETE)
2，本次事务ID ，永远递增。
3，变化后的整⾏数据。
对于操作类型来说，INSERT/UPDATE我们都⽤sign=1统⼀为upsert操作，DELETE则⽤sign=-1表⽰删除。
事务ID恰好就可以⽤来作为version，表⽰数据变更的发⽣先后关系，对于同⼀个商品id我们只关⼼最新version的数据长什么样。
总结⼀下，
在clickhouse 中模拟UPDATE 和DELETE 的核⼼思路就是：将UPDATE 和DELETE 操作都转化为clickhouse 表的插⼊操作，⽆⾮是sign 和version 在变化，最后查询的时候对同⼀个商品id 保留最新的version ⾏即可。
为什么要⽤replcaingMergeTree 呢？因为要让存储引擎⾃动淘汰掉旧版本的数据，免得存储空间⽆限上涨。
1
2
3
4
5
6
7
8CREATE TABLE db2.product ON CLUSTER mycluster (    id Int64,    name String,    sign Int8,    version UInt64) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/db2/product', '{replica}', version)ORDER BY (id);
1
2CREATE TABLE db2.dis_product ON CLUSTER mycluster AS db2.product ENGINE = Distributed(mycluster, db2, product, rand());
实践INSERT/UPDATE
我们实践模拟出整个INSERT/UPDATE过程，我们假定数据源是来⾃mysql的binlog同步产⽣，mysql每⾏记录变更都在独⽴的事务中完成，所以version总是递增（你可以利⽤canal+kafka⾃动向clickhouse⽣成这样的数据，下⾯均⼿动模拟）：
⾸先INSERT两⾏记录：
INSERT INTO db2.dis_product values(1,’尿不湿’,1,1);
INSERT INTO db2.dis_product values(2,’纸⼱’,1,2);
它们的sign=1表⽰INSERT，然后各⾃的version是1和2。
然后我们模拟UPDATE了id=1的记录：
INSERT INTO db2.dis_product values(1,’尿不湿2.0′,1,3);
这次sign=1表⽰update，版本号来到了3，再看⼀下数据：
现在出现问题了，id=1主键同时存在新旧2条记录，我们期望只看到version=3的这个新版本数据，因此如果我们希望准确获得表的实际情况，查询时应该这样做：
按主键ID分组，在组内利⽤argMax⽅法选出version最⼤的那⾏数据的各个列值。
argMax(name,version)的意思是在Group 组内version 最⼤的那⾏的name 列。
说⽩了，每个id保留最新version的那⾏数据，结果也显⽽易见：
1
2
3
4
5
6
7SELECT    id,    argMax(name,version) name,    argMax(sign,version) sign,    max(version) max_version FROM db2.dis_product GROUP BY id;
对于id=1来说，version=3的尿不湿2.0被留下了，它的sign=1表⽰version=3这次变更是⼀个INSERT/UPDATE操作，数据是有效的。
（注，replacingMergTree虽然compaction时会⾃动删除同主键旧version数据，但是compaction何时发⽣是不可知的，所以我们总是应该⽤SQL来⾃⾏去重）
模拟DELETE 操作
delete操作我们应该插⼊⼀个sign=-1的⾏，version继续跟随事务ID递增即可。
INSERT INTO db2.dis_product values(2,’纸⼱’,-1,4);
我们插⼊上述语句实现对id=2记录的删除，version是4，sign=-1表⽰删除。
当我们重新执⾏上⾯的查询语句时：
你会发现id=2记录的version=4记录被保留了下来，但实际上因为version=4是sign=-1的删除操作，我们其实不应该看得到这⾏被删掉的记录，所以我们得完善⼀下查询SQL让它能够适应这种删除记录的操作：
只需要将sign=-1的那些分组删除掉即可，⽐如id=2的分组最新版本的sign就是-1，最终被过滤掉：
1
2
3
4
5
6
7
8SELECT    id,    argMax(name,version) name,    argMax(sign,version) sign,    max(version) max_ver
sion FROM db2.dis_product GROUP BY id HAVING sign > 0;
⽤视图简化
后续我们做数据分析的话，肯定不希望写每个SQL时都考虑上述sign和version的问题，所以把上述SQL作为⼀个视图，后续数据分析SQL直接基于视图即可，不必再重复处理sign和version问题。
ON CLUSTER mycluster是在所有node上创建这个view，所以后续客户端⽆论访问任何节点都可以访问到view。
视图就是⼀个⼦查询，当我们select * from db2.dis_product_view的时候相当于
select * from (SELECT id, argMax(name,version) name, argMax(sign,version) sign, max(version) max_version FROM db2.dis_product GROUP BY id HAVING sign > 0) as tmp
这就达到了简化后续数据分析SQL复杂度的⽬的，现在我们直接select这个视图看⼀下效果：
可见，我们没有再关注过sign 和version ，但数据已经是规整的了，底层伪UPDATE/DELETE 的实现细节已经被屏蔽了。
mysql删除重复的数据保留一条
总结
本篇博客教给⼤家如何在clickhouse中模拟出update和delete操作，这也是mysql实时同步clickhouse的基本原理。
我们⽤到了关键的replacingMergeTree引擎，它可以在compaction时保留相同主键最新的数据，确保数据库不会⽆限膨胀。
同时，我们定义了sign和version控制字段实现了数据⾏的多版本设计，通过SQL为每个主键保留最新⼀份数据并过滤掉被删除的记录，通过视图屏蔽SQL负责性，为后续使⽤提供了便捷性。
你也许也看过clickhouse 的折叠表等概念，但⽬前从官⽅和⽹上的做法来看replacingMergeTree+sign+version 的⽅案是最为普遍、简单、可靠的，没有明显缺点。1
2
3
4
5
6
7
8
9
10CREATE VIEW db2.dis_product_view ON CLUSTER mycluster AS SELECT    id,    argMax(name,version) name,    argMax(sign,version) sign,    max(version) max_version FROM db2.dis_product GROUP BY id HAVING sign > 0;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。