一般同步mysql的数据到clickhouse中有两种常见的方式

方式1:
每天定时拉取mysql的全量数据同步到clickhouse中,对于相同的主键,只保留update_time最新的一条记录,这种方式要求mysql的数据记录必须包含update_time字段,并且不能物理删除数据,删除时只能软删除比如设置is_deleted=1这种方式.我目前所在的公司就是采用的这种方式同步mysql的数据.

方式2:
mysql数据变更的binlog通过canal + kafka的形式以kafka主题的对外提供,kafka的每一条记录包括以下信息:
a. 记录变更前内容
b. 事务id(递增的形式)
c. 变更类型(insert/update, delete)
d. 记录变更后的内容
我们可以通过把delete当成insert的方式,结合ReplaceMergeTree的方式把mysql数据记录同步到clickhouse中,具体方式如下:
步骤1:
create table default.clickhouse_mysql_table(id int32, name string,sign int32, version int32) engine=ReplaceMergetree(version) order by id, 这里insert/update的记录的sign的值为1,如果是delete操作,sign的值为-1,version字段就是就是事务id,严格递增
步骤2:
查询的sql select id, name from (select id, argMax(name,version) as name, argMax(sign,version) as sign from clickhouse_mysql_table group by id) as a where a.sign > 0
这样就可以获取最新的记录,并且把删除的记录排除在外。
为了屏蔽这个复杂的sql语句,我们可以创建一个视图:

create view default.clickhouse_mysql_view AS select id, name from (select id, argMax(name,version) as name, argMax(sign,version) as sign from  clickhouse_mysql_table group by id) as a  where a.sign > 0`

这样对外的时候我们直接查询视图就可以了,在这里ReplaceMergeTree的作用是合并同样id的数据,由于ReplaceMergeTree只是合并相同分区内id相同的数据,并且何时合并是不定的,这就是步骤2的sql查询语句是必须存在的原因
这个视图提供的所有字段才是真正的有效的字段,屏蔽了记录新增修改的细节,通过这个视图我们可以进行其他的操作,比如按照name分组,查找相同名字的用户数量等等,对这个对外视图的操作我们不再需要基于id进行分组,任何的sql操作都是被允许的.

总结:

方式一
每天全量同步mysql数据的方式需要mysql数据表按照一定的规范创建,比如必须存在updatetime字段,并且不能物理删除数据,此外这种方式的数据实时性只能是天维度
方式二
优点:通过消费kafka的binlog日志的方式可以做到数据是准实时生效的,并且对mysql的表规范没有约束,业务可以物理删除mysql的表数据,没有影响
缺点:ReplaceMergeTree只能是分区内的主键字段的数据合并操作,并且为了简化对外的数据查询操作,需要提供对外视图的形式,视图屏蔽了内部的处理细节。

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐