clickhouse 20.8將新增 MaterializeMySQL引擎 ,可通過binlog日志實時物化mysql數(shù)據(jù)淌哟，極大提升了數(shù)倉的查詢性能和數(shù)據(jù)同步的時效性呈昔；原有mysql中承擔的數(shù)據(jù)分析工作 可交由clickhouse去做，這么做可顯著降低線上mysql的負載腻窒，從此OLTP與OLAP業(yè)務實現(xiàn)完美融合
介紹
目前 MaterializeMySQL database engine 還不支持表級別的同步操作语御，需要將整個mysql database映射到clickhouse袁勺，映射過來的庫表會自動創(chuàng)建為ReplacingMergeTree表engine。
MaterializeMySQL 支持全量和增量同步缓屠，首次創(chuàng)建數(shù)據(jù)庫引擎時進行一次全量復制奇昙，之后通過監(jiān)控binlog變化進行增量數(shù)據(jù)同步；該引擎支持mysql 5.6/5.7/8.0版本數(shù)據(jù)庫敌完，兼容insert储耐，update，delete蠢挡，alter弧岳，create凳忙，drop，truncate等大部分DDL操作禽炬。
演示
- 修改my.cnf開啟mysql binlog模式
log-bin=/data/logs/mysql/mysql-bin.log # 指定binlog日志存儲位置
binlog_format=ROW # 這里一定是row格式
server-id=1
如果clickhouse使用的是20.8 prestable之后發(fā)布的版本涧卵，那么還需要配置開啟GTID模式
gtid-mode=on
enforce-gtid-consistency=1 # 設置為主從強一致性
log-slave-updates=1 # 記錄日志
- 首先在mysql中先創(chuàng)建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
# 因為該功能目前還處于實驗階段，在使用之前需要開啟
- 創(chuàng)建一個復制管道
CREATE DATABASE scene_mms
ENGINE = MaterializeMySQL('localhost:3306', 'db', 'root', 'xxx')
創(chuàng)建成果后可查看到clickhouse對應的表
VM_10_14_centos :) show tables
SHOW TABLES
┌─name───────────────────────┐
│ scene │
└────────────────────────────┘
25 rows in set. Elapsed: 0.002 sec.
表結(jié)構(gòu)如下:
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根據(jù)id热幔，按照長度為4294967進行分段分區(qū)
- 向表中插入數(shù)據(jù)
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版本使用的監(jiān)聽事件方式為UpdateRowsEventV2 乐设，而20.8 prestable只后的版本使用的gtid的binlog監(jiān)聽方式，這種方式在mysql主從模式下可以確保數(shù)據(jù)同步的一致性绎巨，但使用過程中可能會有一些意向不到問題近尚，建議大家先使用testing版本進行測試，等20.8穩(wěn)定版出來后再測試gtid的同步模式场勤。
查詢clickhouse對應的表戈锻，已可以實時看到數(shù)據(jù)變化
SELECT * FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 2 │ 1001 │ gyc │ 2020-09-03 10:00:02 │
└────┴──────┴───────┴─────────────────────┘
- 更新mysql中id為2的數(shù)據(jù)
update scene set title="校園招聘" where id = 2
檢查clickhouse中id為2的數(shù)據(jù)
select * from scene where id = 2
- 嘗試刪除mysql中id為2的數(shù)據(jù)
DELETE FROM scene where id = 2
再次查詢clickhouse中的數(shù)據(jù)已無數(shù)據(jù)
- 在mysql執(zhí)行刪除表
drop table scene
此時在clickhouse處會同步刪除對應表，如果查詢會報錯
DB::Exception: Table scene_mms.scene doesn't exist..
- 同理 和媳，如果在mysql客戶端新增一張表格遭，在clickhouse處也可實時生成對應的數(shù)據(jù)表
- 修改表名
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處沒有執(zhí)行成功，所以會報找不到對應表的錯誤她倘；
同理璧微， 在mysql處刪除test表，clickhouse中的scene表依然存在硬梁，可見兩邊執(zhí)行語句是根據(jù)表名進行對應的
修改列名稱也是不支持的往毡，如果出現(xiàn)這種情況，刪除通道重建就好了
支持添加列與刪除列靶溜，在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中前后表結(jié)構(gòu)變化
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 │ │ │ │
└────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
總結(jié)
- 通過上面的測試我們發(fā)現(xiàn)clickhouse的刪除動作也是實時同步的，原因在于我們創(chuàng)建的MaterializeMySQL engine會默認為每一張表生成ReplacingMergeTree engine罩息，當clickhouse遇到刪除的binlog操作時嗤详，會將這條數(shù)據(jù)的_sign字段設為-1；
目前ReplacingMergeTree還只是標記性刪除瓷炮，并非物理上的實際刪除葱色，索引隨著刪除日志的增多，查詢過濾會有一定的負擔娘香。
- MaterializeMySQL DataBase中的ReplacingMergeTree Engine表查詢不再需要額外添加final修飾符了：
select * from scene
##等同于
select * from scene final
- 需要注意的是20.8版本目前還不是穩(wěn)定版苍狰，如果mysql中沒有設置主鍵字段時办龄，會在創(chuàng)建MaterializeMySQL數(shù)據(jù)庫時報錯:
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
不過該ISSUES目前已被重視，20.7版本在create table 時解決了這個問題淋昭，可以通過設置allow_nullable_key=1來解決俐填，但因為MaterializeMySQL是自動創(chuàng)建的數(shù)據(jù)表，所以該問題還是存在的翔忽，相信不久的版本在創(chuàng)建MaterializeMySQL DataBase時 也會解決這個問題
CREATE TABLE nullable_key
(k Nullable(int), v int) ENGINE MergeTree ORDER BY k SETTINGS allow_nullable_key = 1;
- clickhouse單線程寫入能力可以達到每秒幾十萬英融，在一般業(yè)務體系下增量更新的模式是完全沒有問題的。
