mysql同步数据到clickhouse

目前官方已经提供了MaterializeMySQL数据库引擎来支持将mysql中的数据通过binlog的形式同步到clickhouse,但是这个特性到目前为止还处于实验性阶段。目前网上大部分的其他方案也暂时只能同步存留数据和新增数据。对于已有的数据进行update、delete同步是不支持的。这种方案对于一些非事实数据来说,根本无法使用。因此我们要设计一个同时支持insert、update、delete实时同步工具才能符合绝大部分人的需求。

1、同步数据流

数据流:mysql -> dts -> kafka -> sync2any -> clickhouse

依然是使用binlog当作数据源,不过其中我们使用的是腾讯云提供的数据同步功能(dts),他会帮我们讲binlog的数据投递到kafka中。

而消费kafka中数据的中间件则是sync2any,我们自研的一个中间件,能够对kafka中的数据进行进一步清洗、转换再保存到clickhouse中。

2、表引擎的选择

众所周知,clickhouse并不能支持直接的update、delete语句。如果要确切的删改某条数据,必须动用alter table的语句才能做到。alter table是一个重量级操作,是不能被频繁执行的。因此直接把mysql中的增删语句转换成clickhouse中的alter table是行不通的。

于是我们必须从表引擎的角度来考虑到底,哪种表引擎可以变相的支持update和delete操作。以下引擎映入了我的眼帘:

CollapsingMergeTreeVersionedCollapsingMergeTree其实算同一个系列的表引擎,他们的核心特性是能根据主键sign字段动态的合并数据,从而达到updaedelete的效果(具体后面会讲到)。

ReplacingMergeTree只能根据一个条件(主键)进行合并,因此只能做到update,不能做到delete的效果。于该引擎惨遭淘汰!

3、同步语句转换

在看这届时,请务必理解CollapsingMergeTree表引擎的合并数据原理。然后我们再明确一点:从binlog投递到kafka的数据中带有前后镜像数据。

  • 前镜像:修改之前的数据(update、delete语句时存在)
  • 后镜像:修改之后的数据(update、insert语句是存在)

3.1 insert语句的转换

clickhouse的天然支持insert语句的(不然数据怎么来),因此不需要过多的转换,只需要取后镜像的数据生成insert语句就好了。不过有一点需要注意,得给insert语句加一字段的数据(在clickhouse建表时新增的)。列名为”_sign”,值为”1”。_sign字段只在ck端存在,mysql端不存在该字段。

1
INSERT INTO `test` (`id`,`order_no`,`_sign`) VALUES ('1','20210528141123209344',1);

3.2 update语句的转换

update一条数据需要生成两个insert语句。第一条insert把之前的数据抵消删除掉,第二条insert保存修改后的数据。因此_sign字段值分别是-11

1
2
INSERT INTO `test` (`id`,`order_no`,`_sign`) VALUES ('1','20210528141123209344',-1);
INSERT INTO `test` (`id`,`order_no`,`_sign`) VALUES ('1','66666',1);

3.3 delete语句的转换

当发现delete数据,证明这个数据之前已经存在过了。只需要新增相同数据,同时把_sign字段置为-1即可。

1
INSERT INTO `test` (`id`,`order_no`,`_sign`) VALUES ('1','66666',-1);

3.4 VersionedCollapsingMergeTree

上面是拿CollapsingMergeTree举例,当然你也可以使用VersionedCollapsingMergeTree。该引擎的容错性更高,不要求数据的插入的顺序。不过需要新增一个ver的字段标记每一行记录的版本。这个字段在MyqSql那边通过增加一个更新时间戳来解决。

4、同步的频率

订阅binlog后,你会一瞬间收到海量的数据。我们不能立即针对每一行数据生产相应的sql持久化到clickhouse中。从ck的官方文档里明确的阐述了不能频繁的insert,推荐的最佳实践是累计到一定的数据然后进行批量新增。sync2any对这种情况做了处理,我们采取的方式为每隔5秒钟持久化一次。当然这样依赖实时性就收到了一丢丢的影响,但是我认为还是在可控范围内的。