背景

在上一篇文章中體驗(yàn)了 ETLCloud 的離線數(shù)據(jù)遷移功能，就像大數(shù)據(jù)領(lǐng)域里有離線計(jì)算和實(shí)時(shí)計(jì)算， ETLCloud 還提供了基于 CDC （Change Data Capture）的實(shí)時(shí)數(shù)據(jù)集成功能：實(shí)時(shí)數(shù)據(jù)集成是指通過(guò)變化數(shù)據(jù)捕獲技術(shù)（ CDC ）實(shí)時(shí)監(jiān)測(cè)數(shù)據(jù)庫(kù)中的變化數(shù)據(jù)，并捕獲這些變化數(shù)據(jù)傳輸?shù)?MQ 或數(shù)據(jù)庫(kù)中提供給目標(biāo)端消費(fèi)。
今天以單表的 CDC 為例來(lái)體驗(yàn)下 ETLCloud 的實(shí)時(shí)數(shù)據(jù)集成功能，循序漸進(jìn)，后續(xù)再實(shí)踐下將多數(shù)據(jù)源或者多個(gè)表合并為大寬表。
依然使用詩(shī)詞數(shù)據(jù)庫(kù)，對(duì)數(shù)據(jù)庫(kù)中的詩(shī)詞表數(shù)據(jù)進(jìn)行近實(shí)時(shí)的監(jiān)聽(tīng)；依托? ETLCloud ?的 CDC 功能，借助 MySQL 的 binlog 機(jī)制（即 MySQL 主從同步的原理，我們熟悉的阿里開(kāi)源的 Canal 同步工具，同樣利用的這一原理，包括：基于語(yǔ)句和基于行的復(fù)制；無(wú)論是基于語(yǔ)句的復(fù)制，還是基于行的復(fù)制，都是通過(guò)在主庫(kù)上記錄二進(jìn)制日志，在從庫(kù)上重放日志的方式實(shí)現(xiàn)異步的數(shù)據(jù)復(fù)制）實(shí)現(xiàn)從 MySQL 到 ClickHouse 的實(shí)時(shí)數(shù)據(jù)同步。

數(shù)據(jù)集

MySQL數(shù)據(jù)庫(kù)中的庫(kù)表 poetry 結(jié)構(gòu)如下，數(shù)據(jù)量： 311828 。

CREATE TABLE `poetry` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,`title` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_unicode_ci',`yunlv_rule` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',`author_id` INT(10) UNSIGNED NOT NULL,`content` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',`dynasty` VARCHAR(10) NOT NULL COMMENT '詩(shī)所屬朝代（S-宋代, T-唐代）' COLLATE 'utf8mb4_unicode_ci',`author` VARCHAR(150) NOT NULL COLLATE 'utf8mb4_unicode_ci',PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=311829;

ClickHouse 中的建表語(yǔ)句：

CREATE TABLE poetry.poetry (`id` Int32, `title` String, `yunlv_rule` String, `author_id` Int32, `content` String, `dynasty` String, `author` String) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id SETTINGS index_granularity = 8192

Note：這里采用 MergeTree 引擎，如果使用 MySQL 引擎，后續(xù)的大數(shù)據(jù)查詢分析效率會(huì)很低。。

基礎(chǔ)環(huán)境

數(shù)據(jù)庫(kù)服務(wù)部署在多云環(huán)境下，共涉及到3臺(tái)云主機(jī)，操作系統(tǒng)及配置如下：

1. MySQL所在主機(jī)（阿里云）
   操作系統(tǒng)：Ubuntu16

root@ali:~# uname -a
Linux ali 4.4.0-62-generic #83-Ubuntu SMP Wed Jan 18 14:10:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linuxroot@iZuf69c5h89bkzv0aqfm8lZ:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="16.04.2 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.2 LTS"
VERSION_ID="16.04"
…

基本配置：2C8G
數(shù)據(jù)庫(kù)版本：5.7.22-0ubuntu0.16.04.1

2. ClickHouse所在主機(jī)（華為云）
   操作系統(tǒng)：CentOS 6

[root@ecs-xx-0003 ~]# uname -a
Linux ecs-xx-0003 2.6.32-754.15.3.el6.x86_64 #1 SMP Tue Jun 18 16:25:32 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@ecs-xx-0003 ~]# cat /proc/version 
Linux version 2.6.32-754.15.3.el6.x86_64 (mockbuild@x86-01.bsys.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-23) (GCC) ) #1 SMP Tue Jun 18 16:25:32 UTC 2019

基本配置：4C8G
數(shù)據(jù)庫(kù)版本：19.9.5.36

[root@ecs-xx-0003 clickhouse-server]# clickhouse-server --version
ClickHouse server version 19.9.5.36.

3. ETLCloud所在主機(jī)（本地虛擬機(jī)）
   操作系統(tǒng)：CentOS 7
   基本配置：2C4G

Note：上一篇用到的騰訊云主機(jī)到期釋放了，就在本地虛擬機(jī)使用 Docker 重新部署了一套 ETLCloud ，這里選擇的是社區(qū)版，采用 Docker 部署的方式輕量、快速啟動(dòng)： docker pull ccr.ccs.tencentyun.com/restcloud/restcloud-etl:V2.2

實(shí)時(shí)數(shù)據(jù)同步實(shí)踐

接下來(lái)，進(jìn)入我們的實(shí)時(shí)數(shù)據(jù)同步實(shí)踐：全程零代碼、可視化、鼠標(biāo)點(diǎn)一點(diǎn)即可完成從 MySQL 到 ClickHouse 的實(shí)時(shí)數(shù)據(jù)同步。

開(kāi)啟MySQL的CDC

我的 MySQL 服務(wù)部署在阿里云的 Ubuntu 上，編輯配置文件： vi /etc/mysql/my.cnf 。

[mysqld]
log-bin=db218-bin
server-id=218
binlog-do_db=poetry # 開(kāi)啟指定庫(kù)的binlog
binlog-format=row # 設(shè)置二進(jìn)制日志格式為行級(jí)別，這是支持CDC必須的

數(shù)據(jù)源配置

共涉及兩個(gè)數(shù)據(jù)源 MySQL 與 ClickHouse ，直接選擇對(duì)應(yīng)的數(shù)據(jù)庫(kù)，配置好地址、端口、用戶名密碼，測(cè)試連接成功即可。

新增監(jiān)聽(tīng)器

從首頁(yè)的實(shí)時(shí)數(shù)據(jù)集成——>數(shù)據(jù)庫(kù)監(jiān)聽(tīng)器——>新增監(jiān)聽(tīng)器。
選擇前面創(chuàng)建的 MySQL 數(shù)據(jù)源，采用白名單方式，可以自動(dòng)載入數(shù)據(jù)庫(kù)和數(shù)據(jù)表進(jìn)行下拉選擇。

采用最簡(jiǎn)單“直接傳輸?shù)侥繕?biāo)庫(kù)”的方式實(shí)現(xiàn) CDC 數(shù)據(jù)同步，選擇前面創(chuàng)建的 ClickHouse 數(shù)據(jù)源。

如果希望同步歷史數(shù)據(jù)，可以選擇全量+增量。

之后，便可以啟動(dòng)監(jiān)聽(tīng)，理論上對(duì)數(shù)據(jù)表的查詢、修改以及刪除操作均會(huì)被監(jiān)聽(tīng)到。

測(cè)試語(yǔ)句準(zhǔn)備

在實(shí)際測(cè)試 CDC 近實(shí)時(shí)的實(shí)際同步前，我先用 ChatGPT 生成了一首唐詩(shī)：模仿李白的風(fēng)格，作一首以端午為主題的七言絕句。

粽葉飄香端午至，龍舟競(jìng)渡水波濤。五月初五傳古意，粽香撲鼻詩(shī)情高。

INSERT INTO `poetry` (`id`, `title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES (311829, '端午', '七律測(cè)試', 105, '葉飄香端午至，龍舟競(jìng)渡水波濤。五月初五傳古意，粽香撲鼻詩(shī)情高。', 'T', '李白');UPDATE poetry SET yunlv_rule = "七律更新" WHERE id = 311829;DELETE FROM poetry WHERE id = 311829;INSERT INTO `poetry` (`title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES ('端午', '七律插入', 105, '葉飄香端午至，龍舟競(jìng)渡水波濤。五月初五傳古意，粽香撲鼻詩(shī)情高。', 'T', '李白');

插入測(cè)試

在MySQL中執(zhí)行以下插入語(yǔ)句，然后查看下 ETLCloud 的可視化數(shù)據(jù)統(tǒng)計(jì)，再到 ClickHouse 端確認(rèn)下新增的數(shù)據(jù)是否同步成功。

-- 指定ID插入
INSERT INTO `poetry` (`id`, `title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES (311829, '端午', '七律測(cè)試', 105, '葉飄香端午至，龍舟競(jìng)渡水波濤。五月初五傳古意，粽香撲鼻詩(shī)情高。', 'T', '李白');

Note：由于這里用的 ClickHouse 版本較低，還沒(méi)有提供 Web 版的 PlayGround ，就直接通過(guò)命令行客戶端進(jìn)行連接查詢了。

-- 省略ID插入，主鍵自增
INSERT INTO `poetry` (`title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES ('端午', '七律插入', 105, '葉飄香端午至，龍舟競(jìng)渡水波濤。五月初五傳古意，粽香撲鼻詩(shī)情高。', 'T', '李白');

更新測(cè)試

在? MySQL 端執(zhí)行更新語(yǔ)句。

UPDATE poetry SET yunlv_rule = "七律更新" WHERE id = 311829;

刪除測(cè)試

在? MySQL ?端執(zhí)行刪除語(yǔ)句。

DELETE FROM poetry WHERE id = 311829;

問(wèn)題記錄

• 修改了MySQL配置，開(kāi)啟binlog后，無(wú)法啟動(dòng)了（當(dāng)然，我恢復(fù)了配置依然無(wú)法啟動(dòng)）。。

問(wèn)題描述：

root@ali:/var/lib/mysql# systemctl start mysql.service

Job for mysql.service failed because the control process exited with error code. See “systemctl status mysql.service” and “journalctl -xe” for details.

解決方法：將日志文件所在目錄/var/log和進(jìn)程pid文件所在目錄/var/run/加入到mysql訪問(wèn)組

root@ali:/var/log/mysql# chown -R mysql:mysql /run/mysqld
root@ali:/var/log/mysql# chown -R mysql:mysql /var/run/mysqld
root@ali:/var/log/mysql# chown -R mysql:mysql /var/log/mysql

經(jīng)過(guò)測(cè)試發(fā)現(xiàn)，對(duì)于新增操作（無(wú)論是指定 ID 插入，還是省略 ID 插入，借助主鍵自增策略）， CDC 都可以實(shí)時(shí)同步到 ClickHouse ，但是當(dāng)更新、刪除數(shù)據(jù)時(shí)，同步出現(xiàn)異常；對(duì)于異常數(shù)據(jù)，實(shí)時(shí)數(shù)據(jù)傳輸時(shí)會(huì)記錄下來(lái)，可以到“異常數(shù)據(jù)”的 Tab 下查看具體的異常數(shù)據(jù)及出錯(cuò)原因。

• 更新操作無(wú)法同步到ClickHouse

問(wèn)題描述：Query must be like ‘INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)’. Got: alter table poetry update id=?, title=?, yunlv_rule=?, author_id=?, content=?, dynasty=?, author=? where id=?
問(wèn)題分析： ClickHouse 中沒(méi)有 dual 虛擬表，它的虛擬表是 system.one

• 刪除操作無(wú)法同步到ClickHouse

問(wèn)題描述：數(shù)據(jù)刪除異常: ClickHouse exception, code: 62, host: 139.9.172.55, port: 8123; Code: 62, e.displayText() = DB:: Exception: Syntax error: failed at position 1: delete from poetry where id=311829. Expected one of: ATTACH, DETACH, DROP, SHOW, USE, SELECT, WITH, KILL, TRUNCATE, DESC, DESCRIBE, SYSTEM query, SELECT subquery, list of elements, ALTER query, ALTER TABLE, EXISTS, CREATE TABLE or ATTACH TABLE query, Query with output, SHOW PROCESSLIST query, SHOW PROCESSLIST, RENAME query, RENAME TABLE, SELECT query, possibly with UNION, SET query, SHOW [TEMPORARY] TABLES|DATABASES [[NOT] LIKE ‘str’], EXISTS or SHOW CREATE query, SELECT query, subquery, possibly with UNION, USE query, CHECK TABLE, DESCRIBE query, DROP query, INSERT query, INSERT INTO, KILL QUERY query, OPTIMIZE query, OPTIMIZE TABLE, SELECT query, CREATE, SET, Query (version 19.9.5.36)

問(wèn)題分析： ClickHouse 中的刪除操作與MySQL中不一樣， ClickHouse 通過(guò) alter 方式實(shí)現(xiàn)更新、刪除，把 update 、 delete 操作叫做 mutation (突變)。語(yǔ)法為：

ALTER TABLE [db.]table DELETE WHERE filter_expr
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

區(qū)別：標(biāo)準(zhǔn) SQL 的更新、刪除操作是同步的，即客戶端要等服務(wù)端反回執(zhí)行結(jié)果（通常是 int 值）；而 ClickHouse 的 update 、 delete 是通過(guò)異步方式實(shí)現(xiàn)的，當(dāng)執(zhí)行 update 語(yǔ)句時(shí)，服務(wù)端立即反回，但是實(shí)際上此時(shí)數(shù)據(jù)還沒(méi)變，而是排隊(duì)等著。按照官方的說(shuō)明， update/delete 的使用場(chǎng)景是一次更新大量數(shù)據(jù)，不建議一次只更新一條數(shù)據(jù)。

總結(jié)

以上就是基于 ETLCloud 實(shí)時(shí)數(shù)據(jù)集成功能實(shí)現(xiàn)的單表 CDC 數(shù)據(jù)從 MySQL 到 ClickHouse 的同步實(shí)踐，不過(guò)可能因?yàn)槟繕?biāo)庫(kù)為 ClickHouse ，其更新、刪除操作與傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn) SQL 有所區(qū)別，導(dǎo)致更新和刪除數(shù)據(jù)的操作未能成功同步，這個(gè)問(wèn)題已反饋給官方技術(shù)人員。
ETLCloud 提供了實(shí)時(shí)數(shù)據(jù)傳輸統(tǒng)計(jì)圖形展示，對(duì)同步的進(jìn)度及異常數(shù)據(jù)進(jìn)行近實(shí)時(shí)的監(jiān)控。

Reference

• ETLCloud官方文檔
• ClickHouse官方文檔
• https://blog.csdn.net/wyq/article/details/124203649

---

If you have any questions or any bugs are found, please feel free to contact me.
Your comments and suggestions are welcome!