长沙建设外贸网站,简述微信营销的技巧,宁波seo排名外包,自己做企业网站好做吗背景
在上一篇文章中体验了 ETLCloud 的离线数据迁移功能#xff0c;就像大数据领域里有离线计算和实时计算#xff0c; ETLCloud 还提供了基于 CDC #xff08;Change Data Capture#xff09;的实时数据集成功能#xff1a;实时数据集成是指通过变化数据捕获技术#…背景
在上一篇文章中体验了 ETLCloud 的离线数据迁移功能就像大数据领域里有离线计算和实时计算 ETLCloud 还提供了基于 CDC Change Data Capture的实时数据集成功能实时数据集成是指通过变化数据捕获技术 CDC 实时监测数据库中的变化数据并捕获这些变化数据传输到 MQ 或数据库中提供给目标端消费。 今天以单表的 CDC 为例来体验下 ETLCloud 的实时数据集成功能循序渐进后续再实践下将多数据源或者多个表合并为大宽表。 依然使用诗词数据库对数据库中的诗词表数据进行近实时的监听依托 ETLCloud 的 CDC 功能借助 MySQL 的 binlog 机制即 MySQL 主从同步的原理我们熟悉的阿里开源的 Canal 同步工具同样利用的这一原理包括基于语句和基于行的复制无论是基于语句的复制还是基于行的复制都是通过在主库上记录二进制日志在从库上重放日志的方式实现异步的数据复制实现从 MySQL 到 ClickHouse 的实时数据同步。
数据集
MySQL数据库中的库表 poetry 结构如下数据量 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
)
COLLATEutf8mb4_unicode_ci
ENGINEInnoDB
AUTO_INCREMENT311829;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 8192Note这里采用 MergeTree 引擎如果使用 MySQL 引擎后续的大数据查询分析效率会很低。。
基础环境
数据库服务部署在多云环境下共涉及到3台云主机操作系统及配置如下
MySQL所在主机阿里云 操作系统Ubuntu16
rootali:~# 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/LinuxrootiZuf69c5h89bkzv0aqfm8lZ:~# cat /etc/os-release
NAMEUbuntu
VERSION16.04.2 LTS (Xenial Xerus)
IDubuntu
ID_LIKEdebian
PRETTY_NAMEUbuntu 16.04.2 LTS
VERSION_ID16.04
…基本配置2C8G 数据库版本5.7.22-0ubuntu0.16.04.1
ClickHouse所在主机华为云 操作系统CentOS 6
[rootecs-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
[rootecs-xx-0003 ~]# cat /proc/version
Linux version 2.6.32-754.15.3.el6.x86_64 (mockbuildx86-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 数据库版本19.9.5.36
[rootecs-xx-0003 clickhouse-server]# clickhouse-server --version
ClickHouse server version 19.9.5.36.ETLCloud所在主机本地虚拟机 操作系统CentOS 7 基本配置2C4G
Note上一篇用到的腾讯云主机到期释放了就在本地虚拟机使用 Docker 重新部署了一套 ETLCloud 这里选择的是社区版采用 Docker 部署的方式轻量、快速启动 docker pull ccr.ccs.tencentyun.com/restcloud/restcloud-etl:V2.2
实时数据同步实践
接下来进入我们的实时数据同步实践全程零代码、可视化、鼠标点一点即可完成从 MySQL 到 ClickHouse 的实时数据同步。
开启MySQL的CDC
我的 MySQL 服务部署在阿里云的 Ubuntu 上编辑配置文件 vi /etc/mysql/my.cnf 。
[mysqld]
log-bindb218-bin
server-id218
binlog-do_dbpoetry # 开启指定库的binlog
binlog-formatrow # 设置二进制日志格式为行级别这是支持CDC必须的数据源配置
共涉及两个数据源 MySQL 与 ClickHouse 直接选择对应的数据库配置好地址、端口、用户名密码测试连接成功即可。
新增监听器
从首页的实时数据集成——数据库监听器——新增监听器。 选择前面创建的 MySQL 数据源采用白名单方式可以自动载入数据库和数据表进行下拉选择。 采用最简单“直接传输到目标库”的方式实现 CDC 数据同步选择前面创建的 ClickHouse 数据源。 如果希望同步历史数据可以选择全量增量。 之后便可以启动监听理论上对数据表的查询、修改以及删除操作均会被监听到。
测试语句准备
在实际测试 CDC 近实时的实际同步前我先用 ChatGPT 生成了一首唐诗模仿李白的风格作一首以端午为主题的七言绝句。 粽叶飘香端午至龙舟竞渡水波涛。五月初五传古意粽香扑鼻诗情高。 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中执行以下插入语句然后查看下 ETLCloud 的可视化数据统计再到 ClickHouse 端确认下新增的数据是否同步成功。
-- 指定ID插入
INSERT INTO poetry (id, title, yunlv_rule, author_id, content, dynasty, author) VALUES (311829, 端午, 七律测试, 105, 叶飘香端午至龙舟竞渡水波涛。五月初五传古意粽香扑鼻诗情高。, T, 李白);Note由于这里用的 ClickHouse 版本较低还没有提供 Web 版的 PlayGround 就直接通过命令行客户端进行连接查询了。 -- 省略ID插入主键自增
INSERT INTO poetry (title, yunlv_rule, author_id, content, dynasty, author) VALUES (端午, 七律插入, 105, 叶飘香端午至龙舟竞渡水波涛。五月初五传古意粽香扑鼻诗情高。, T, 李白);更新测试
在 MySQL 端执行更新语句。
UPDATE poetry SET yunlv_rule 七律更新 WHERE id 311829;删除测试
在 MySQL 端执行删除语句。
DELETE FROM poetry WHERE id 311829;问题记录
修改了MySQL配置开启binlog后无法启动了当然我恢复了配置依然无法启动。。
问题描述 rootali:/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和进程pid文件所在目录/var/run/加入到mysql访问组
rootali:/var/log/mysql# chown -R mysql:mysql /run/mysqld
rootali:/var/log/mysql# chown -R mysql:mysql /var/run/mysqld
rootali:/var/log/mysql# chown -R mysql:mysql /var/log/mysql经过测试发现对于新增操作无论是指定 ID 插入还是省略 ID 插入借助主键自增策略 CDC 都可以实时同步到 ClickHouse 但是当更新、删除数据时同步出现异常对于异常数据实时数据传输时会记录下来可以到“异常数据”的 Tab 下查看具体的异常数据及出错原因。
更新操作无法同步到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 问题描述数据删除异常: 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 id311829. 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 方式实现更新、删除把 update 、 delete 操作叫做 mutation (突变)。语法为
ALTER TABLE [db.]table DELETE WHERE filter_expr
ALTER TABLE [db.]table UPDATE column1 expr1 [, ...] WHERE filter_expr区别标准 SQL 的更新、删除操作是同步的即客户端要等服务端反回执行结果通常是 int 值而 ClickHouse 的 update 、 delete 是通过异步方式实现的当执行 update 语句时服务端立即反回但是实际上此时数据还没变而是排队等着。按照官方的说明 update/delete 的使用场景是一次更新大量数据不建议一次只更新一条数据。
总结
以上就是基于 ETLCloud 实时数据集成功能实现的单表 CDC 数据从 MySQL 到 ClickHouse 的同步实践不过可能因为目标库为 ClickHouse 其更新、删除操作与传统的关系型数据库的标准 SQL 有所区别导致更新和删除数据的操作未能成功同步这个问题已反馈给官方技术人员。 ETLCloud 提供了实时数据传输统计图形展示对同步的进度及异常数据进行近实时的监控。 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!
