clickhouse和mysql的 clickhouse mysql引擎
1.概述
clickhouse高级功能之MaterializeMySQL详解
2. 介绍
目前 MaterializeMySQL database engine 还不支持表级别的同步操作，需要将整个mysql database映射到clickhouse，映射过来的库表会自动创建为ReplacingMergeTree表engine。
MaterializeMySQL 支持全量和增量同步，首次创建数据库引擎时进行一次全量复制，之后通过监控binlog变化进行增量数据同步；该引擎支持mysql 5.6/5.7/8.0版本数据库，兼容insert，update，delete，alter，create，drop，truncate等大部分DDL操作。
3.演示
修改my.cnf开启mysql binlog模式
log-bin=/data/logs/mysql/mysql-bin.log # 指定binlog日志存储位置 binlog_format=ROW # 这里一定是row格式 server-id=1
如果clickhouse使用的是20.8 prestable之后发布的版本，那么还需要配置开启GTID模式
gtid-mode=on enforce-gtid-consistency=1 # 设置为主从强一致性 log-slave-updates=1 # 记录日志
首先在mysql中先创建scene表
CREATE TABLE `scene` ( `id` int NOT NULL AUTO_INCREMENT, `code` int NOT NULL, `title` text DEFAULT NULL, `updatetime` datetime DEFAULT NULL, PRIMARY KEY (`id`), ##主键要设置为not null，否则会报错 KEY `idx_code` (`code`) ##索引键也要设置为not null，否则会报错 ) ENGINE=InnoDB default charset=Latin1;
登陆clickhouse客户端，开启mysql物化引擎
SET allow_experimental_database_materialize_mysql = 1 # 因为该功能目前还处于实验阶段，在使用之前需要开启
创建一个复制管道
CREATE DATABASE scene_mms
ENGINE = MaterializeMySQL('localhost:3306', 'db', 'root', 'xxx')创建成果后可查看到clickhouse对应的表
VM_10_14_centos :) show tables SHOW TABLES ┌─name───────────────────────┐ │ scene │ └────────────────────────────┘ 25 rows in set. Elapsed: 0.002 sec.
ATTACH TABLE scene ( `id` Int32, `code` Int32, `title` Nullable(String), `updatetime` Nullable(DateTime), `_sign` Int8 MATERIALIZED 1, `_version` UInt64 MATERIALIZED 1 ) ENGINE = ReplacingMergeTree(_version) PARTITION BY intDiv(id, 4294967) ORDER BY tuple(id,code) SETTINGS index_granularity = 8192
其中partition根据id，按照长度为4294967进行分段分区
向表中插入数据
INSERT INTO scene(code, title, updatetime) VALUES(1000,'邀请函',NOW()); INSERT INTO scene(code, title, updatetime) VALUES(1001,'gyc',NOW()); INSERT INTO scene(code, title, updatetime) VALUES(1002,'易企秀',NOW());
目前20.8 testing版本使用的监听事件方式为UpdateRowsEventV2 ，而20.8 prestable只后的版本使用的gtid的binlog监听方式，这种方式在mysql主从模式下可以确保数据同步的一致性，但使用过程中可能会有一些意向不到问题，建议大家先使用testing版本进行测试，等20.8稳定版出来后再测试gtid的同步模式。
查询clickhouse对应的表，已可以实时看到数据变化
SELECT * FROM scene ┌─id─┬─code─┬─title─┬──────────updatetime─┐ │ 2 │ 1001 │ gyc │ 2020-09-03 10:00:02 │ └────┴──────┴───────┴─────────────────────┘
更新mysql中id为2的数据
update scene set title="校园招聘" where id = 2
检查clickhouse中id为2的数据
select * from scene where id = 2
尝试删除mysql中id为2的数据
DELETE FROM scene where id = 2
再次查询clickhouse中的数据已无数据
在mysql执行删除表
drop table scene
此时在clickhouse处会同步删除对应表，如果查询会报错
DB::Exception: Table scene_mms.scene doesn't exist..
同理 ，如果在mysql客户端新增一张表，在clickhouse处也可实时生成对应的数据表
修改表名
mysql> alter table scene rename test Query OK, 0 rows affected (0.02 sec) mysql> show tables +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)
修改表名称不会同步至clickhouse，且查询会报错
VM_10_14_centos :) show tables SHOW TABLES ┌─name──┐ │ scene │ └───────┘ VM_10_14_centos :) select * from scene Received exception from server (version 20.8.1): Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table test_mms.test doesn't exist..
通过报错信息可以看出 虽然查询的是scene表 ，但底层已重写为改名之后的test表，因为test表在clickhouse处没有执行成功，所以会报找不到对应表的错误；
同理， 在mysql处删除test表，clickhouse中的scene表依然存在，可见两边执行语句是根据表名进行对应的
修改列名称也是不支持的，如果出现这种情况，删除通道重建就好了
支持添加列与删除列，在mysql添加一列，随后再删除
mysql> alter table scene add column title text; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table scene drop column title; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 观察clickhouse中前后表结构变化 DESCRIBE TABLE scene ┌─name───────┬─type───────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ id │ Int32 │ │ │ │ │ │ │ code │ Int32 │ │ │ │ │ │ │ name │ Nullable(String) │ │ │ │ │ │ │ updatetime │ Nullable(DateTime) │ │ │ │ │ │ │ title │ Nullable(String) │ │ │ │ │ │ │ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │ │ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │ └────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 7 rows in set. Elapsed: 0.001 sec. VM_10_14_centos :) desc scene DESCRIBE TABLE scene ┌─name───────┬─type───────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ id │ Int32 │ │ │ │ │ │ │ code │ Int32 │ │ │ │ │ │ │ name │ Nullable(String) │ │ │ │ │ │ │ updatetime │ Nullable(DateTime) │ │ │ │ │ │ │ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │ │ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │ └────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
4.总结
通过上面的测试我们发现clickhouse的删除动作也是实时同步的，原因在于我们创建的MaterializeMySQL engine会默认为每一张表生成ReplacingMergeTree engine，当clickhouse遇到删除的binlog操作时，会将这条数据的_sign字段设为-1；
目前ReplacingMergeTree还只是标记性删除，并非物理上的实际删除，索引随着删除日志的增多，查询过滤会有一定的负担
MaterializeMySQL DataBase中的ReplacingMergeTree Engine表查询不再需要额外添加final修饰符了：
select * from scene ##等同于 select * from scene final
需要注意的是20.8版本目前还不是稳定版，如果mysql中没有设置主键字段时，会在创建MaterializeMySQL数据库时报错:
DB::Exception: The db.scene cannot be materialized, because there is no primary keys.
主键字段和索引字段不允许为NULL
Rewritten MySQL DDL Query ... wasn't finished successfully: Code: 44, e.displayText() = DB::Exception: Sorting key cannot contain nullable columns
这里貌似会导致很严重的问题呢？ 参考：没有主键导致CK不可用
不过该ISSUES目前已被重视，20.7版本在create table 时解决了这个问题，可以通过设置allow_nullable_key=1来解决，但因为MaterializeMySQL是自动创建的数据表，所以该问题还是存在的，相信不久的版本在创建MaterializeMySQL DataBase时 也会解决这个问题
CREATE TABLE nullable_key (k Nullable(int), v int) ENGINE MergeTree ORDER BY k SETTINGS allow_nullable_key = 1;
clickhouse单线程写入能力可以达到每秒几十万，在一般业务体系下增量更新的模式是完全没有问题的。
