背景

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

數(shù)據(jù)集

MySQL數(shù)據(jù)庫中的庫表 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 '詩所屬朝代（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 中的建表語句：

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ù)查詢分析效率會很低。。

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

數(shù)據(jù)庫服務(wù)部署在多云環(huán)境下，共涉及到3臺云主機(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ù)庫版本：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ù)庫版本：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 部署的方式輕量、快速啟動： docker pull ccr.ccs.tencentyun.com/restcloud/restcloud-etl:V2.2

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

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

開啟MySQL的CDC

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

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

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

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

新增監(jiān)聽器

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

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

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

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

測試語句準(zhǔn)備

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

粽葉飄香端午至，龍舟競渡水波濤。五月初五傳古意，粽香撲鼻詩情高。

INSERT INTO `poetry` (`id`, `title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES (311829, '端午', '七律測試', 105, '葉飄香端午至，龍舟競渡水波濤。五月初五傳古意，粽香撲鼻詩情高。', '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, '葉飄香端午至，龍舟競渡水波濤。五月初五傳古意，粽香撲鼻詩情高。', 'T', '李白');

插入測試

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

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

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

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

更新測試

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

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

刪除測試

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

DELETE FROM poetry WHERE id = 311829;

問題記錄

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

問題描述：

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訪問組

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)過測試發(fā)現(xiàn)，對于新增操作（無論是指定 ID 插入，還是省略 ID 插入，借助主鍵自增策略）， CDC 都可以實時同步到 ClickHouse ，但是當(dāng)更新、刪除數(shù)據(jù)時，同步出現(xiàn)異常；對于異常數(shù)據(jù)，實時數(shù)據(jù)傳輸時會記錄下來，可以到“異常數(shù)據(jù)”的 Tab 下查看具體的異常數(shù)據(jù)及出錯原因。

• 更新操作無法同步到ClickHouse

問題描述：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=?
問題分析： ClickHouse 中沒有 dual 虛擬表，它的虛擬表是 system.one

• 刪除操作無法同步到ClickHouse

問題描述：數(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)

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

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 是通過異步方式實現(xiàn)的，當(dāng)執(zhí)行 update 語句時，服務(wù)端立即反回，但是實際上此時數(shù)據(jù)還沒變，而是排隊等著。按照官方的說明， update/delete 的使用場景是一次更新大量數(shù)據(jù)，不建議一次只更新一條數(shù)據(jù)。

總結(jié)

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