使用ETLCloud实现CDC实时数据集成：从MySQL到ClickHouse的实时数据同步

背景

在上一篇文章中体验了 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
)
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

3-TableDesc.jpg

Note：这里采用 MergeTree 引擎，如果使用 MySQL 引擎，后续的大数据查询分析效率会很低。。

基础环境

数据库服务部署在多云环境下，共涉及到3台云主机，操作系统及配置如下：

  1. MySQL所在主机（阿里云）
    操作系统：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/Linux

root@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
数据库版本：5.7.22-0ubuntu0.16.04.1

  1. ClickHouse所在主机（华为云）
    操作系统：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
数据库版本：19.9.5.36

[root@ecs-xx-0003 clickhouse-server]# clickhouse-server --version
ClickHouse server version 19.9.5.36.
  1. 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-bin=db218-bin
server-id=218
binlog-do_db=poetry # 开启指定库的binlog
binlog-format=row # 设置二进制日志格式为行级别，这是支持CDC必须的

1-OpenBinlog.jpg

2-ConfirmBinlog.jpg

数据源配置

共涉及两个数据源 MySQL 与 ClickHouse ，直接选择对应的数据库，配置好地址、端口、用户名密码，测试连接成功即可。

新增监听器

从首页的实时数据集成——>数据库监听器——>新增监听器。
选择前面创建的 MySQL 数据源，采用白名单方式，可以自动载入数据库和数据表进行下拉选择。

4-Config1.jpg

采用最简单“直接传输到目标库”的方式实现 CDC 数据同步，选择前面创建的 ClickHouse 数据源。

4-Config2.jpg

如果希望同步历史数据，可以选择全量+增量。

4-Config3.jpg

之后，便可以启动监听，理论上对数据表的查询、修改以及删除操作均会被监听到。

测试语句准备

在实际测试 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 ，就直接通过命令行客户端进行连接查询了。

5-Insert1.jpg5-Insert3.jpg

-- 省略ID插入，主键自增
INSERT INTO `poetry` (`title`, `yunlv_rule`, `author_id`, `content`, `dynasty`, `author`) VALUES ('端午', '七律插入', 105, '叶飘香端午至，龙舟竞渡水波涛。五月初五传古意，粽香扑鼻诗情高。', 'T', '李白');

9-Insert.jpg

更新测试

在  MySQL 端执行更新语句。

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

删除测试

在  MySQL  端执行删除语句。

DELETE FROM poetry WHERE id = 311829;

6-UpdateDelete.jpg

问题记录

  • 修改了MySQL配置，开启binlog后，无法启动了（当然，我恢复了配置依然无法启动）。。

问题描述：

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和进程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

经过测试发现，对于新增操作（无论是指定 ID 插入，还是省略 ID 插入，借助主键自增策略）， CDC 都可以实时同步到 ClickHouse ，但是当更新、删除数据时，同步出现异常；对于异常数据，实时数据传输时会记录下来，可以到“异常数据”的 Tab 下查看具体的异常数据及出错原因。

  • 更新操作无法同步到ClickHouse

7-Update.jpg

问题描述：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

8-Delete.jpg

问题描述：数据删除异常: 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 方式实现更新、删除，把 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 提供了实时数据传输统计图形展示，对同步的进度及异常数据进行近实时的监控。

5-Insert2.jpg

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!

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/189102.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

数组的申明

数组的申明

数组是一组相同类型的数据 数组中的数据需要通过数字索引来访问。 数组的声明需要使用new关键字 在声明数组时，可以使用{}来初始化数组中的数组 如果在数组声明之初没有使用大括号来初始化数组的元素，则需要指定数组的大小 在声明初始化元素的数组时…
阅读更多...
调试器gdb

调试器gdb

目录 一、调试 1、前言 2、 debug和release 二、基本操作 1、退出 quit 2、开始调试 r 3、打断点 b 4、查看断点 info b 5、查看代码 l 6、删除断点 d 7、逐过程 n 8、打印变量内容 p 9、逐语句（进入函数） s 10、查看函数调用堆栈 bt 11、…
阅读更多...
Nginx反向代理实现负载均衡webshell

Nginx反向代理实现负载均衡webshell

目录 本实验所用的环境： 问题一：由于nginx采用的反向代理是轮询的方式，所以上传文件必须在两台后端服务器的相同位置上传相同的文件 问题二：我们在执行命令时，无法知道下次的请求交给哪台机器去执行我们在执行hostn…
阅读更多...
堆和栈的区别 重点来说一下堆和栈；堆与栈之间的联系

堆和栈的区别 重点来说一下堆和栈；堆与栈之间的联系

文章目录 堆和栈的区别重点来说一下堆和栈：那么堆和栈是怎么联系起来的呢? 堆与栈的区别 很明显： 今天来聊一聊java中的堆和栈，工作当中这两个也是经常遇到的，知识我们没有去注意理论上的这些内容，今天就来分享一下。…
阅读更多...
Ps：拾色器 - 选取专色

Ps：拾色器 - 选取专色

在 Adobe 拾色器中，可点击“颜色库” Color Libraries按钮来选取专色。 首先在色库 Book列表中选择对应的色库，然后在中间的色相条中选择需要的样本组，再从左侧颜色列表中选取颜色。 可以直接键入颜色名称来选择。比如，键入 13&am…
阅读更多...
【ArcGIS Pro微课1000例】0038：基于ArcGIS Pro的人口密度分析与制图

【ArcGIS Pro微课1000例】0038：基于ArcGIS Pro的人口密度分析与制图

文章目录 一、人口密度二、人口密度分析1. 点密度分析2. 核密度分析三、结果比对一、人口密度 人口密度是指单位土地面积上居住的人口数，通常以每平方千米或每公顷内的常住人口为单位计算。人口密度同资源、经济密切结合，因此，科学准确地分析人口密度的分布情况，对合理制定…
阅读更多...
【数据结构】树与二叉树（廿三）：树和森林的遍历——层次遍历（LevelOrder）

【数据结构】树与二叉树（廿三）：树和森林的遍历——层次遍历（LevelOrder）

文章目录 5.3.1 树的存储结构5. 左儿子右兄弟链接结构 5.3.2 获取结点的算法5.3.3 树和森林的遍历1. 先根遍历（递归、非递归）2. 后根遍历（递归、非递归）3. 森林的遍历4. 层次遍历a. 算法LevelOrderb. 算法解读c. 时间复杂度d.代码…
阅读更多...
MATLAB在信号系统中的应用

MATLAB在信号系统中的应用

1.产生一个幅度为1, 基频为2Hz，占空比为50%的周期方波.要求画出图形。 在MATLAB中，函数square(w0*t, DUTY)产生基本频率为w0 (周期T2*pi/w0)、占空比DUTY (τ/T)*100的周期矩形波（方波），默认情况下占空比DUTY50。占空…
阅读更多...
Nivision 图像处理方法-Brightness(BCGTransform)实现验证测试

Nivision 图像处理方法-Brightness(BCGTransform)实现验证测试

测试发现NIvision中实际使用的公式是: int brightness199; float contrast61.38; float gamma0.52; uchar lut[256]; float k tan(contrast*CV_PI / 180); for (int i 0; i < 256; i) { lut[i] saturate_cast<uchar>(k*(pow(i, gamma)/ pow(255, gamma) * 2…
阅读更多...
操作系统——操作系统概论s

操作系统——操作系统概论s

一、操作系统基本概念 1 操作系统定义 操作系统是裸机上的第一层软件&#xff0c;它是对硬件系统功能的首次扩充&#xff0c; 用以填补人与机器之间的鸿沟。 OS定义&#xff1a;操作系统是控制和管理计算机系统内各种硬件和软件资源&#xff0c;有效地组织多道程序运行的系统软…
阅读更多...
小程序中的大道理之四--单元测试

小程序中的大道理之四--单元测试

在讨论领域模型之前, 先继续说下关于测试方面的内容, 前面为了集中讨论相应主题而对此作了推迟, 下面先补上关于测试方面的. 测试覆盖(Coverage) 先回到之前的一些步骤上, 假设我们现在写好了 getPattern 方法, 而 getLineContent 还处于 TODO 状态, 如下: public String ge…
阅读更多...
Python 前后端分离项目Vue部署应用

Python 前后端分离项目Vue部署应用

一、视图创建 from django.http import JsonResponse from django.shortcuts import render# Create your views here. from django.views import Viewclass IndexView(View):def get(self,request):# 前后端分离 &#xff08;前端JS代码渲染数据&#xff09;return JsonRespo…
阅读更多...
html实现360度产品预览（附源码）

html实现360度产品预览（附源码）

文章目录 1.设计来源1.1 拖动汽车产品旋转1.2 汽车产品自动控制 2.效果和源码2.1 动态效果2.2 源代码 源码下载 作者&#xff1a;xcLeigh 文章地址&#xff1a;https://blog.csdn.net/weixin_43151418/article/details/134613931 html实现360度产品预览&#xff08;附源码&…
阅读更多...
【代码】平抑风电波动的电-氢混合储能容量优化配置（完美复现）matlab-yalmip-cplex/gurobi

【代码】平抑风电波动的电-氢混合储能容量优化配置（完美复现）matlab-yalmip-cplex/gurobi

程序名称&#xff1a;平抑风电波动的电-氢混合储能容量优化配置 实现平台&#xff1a;matlab-yalmip-cplex/gurobi 代码简介&#xff1a;针对电-氢混合系统协同平抑接入新型电力系统的 新能源波动问题&#xff0c;提出考虑碱性电解槽运行特性的电-氢 混合储能容量优化配置方案…
阅读更多...
MSI Center，XBox从任务栏取消固定

MSI Center，XBox从任务栏取消固定

1&#xff0c;设置查看方式中隐藏项目可见 2&#xff0c;进入文件夹&#xff1a;C:\Users\Default\AppData\Local\Microsoft\Windows\Shell 找到下面这两个文件夹&#xff1a; 3&#xff0c;修改文件名或者删除这两个文件即可
阅读更多...
MySQL 批量插入记录报 Error 1390 (HY000)

MySQL 批量插入记录报 Error 1390 (HY000)

文章目录 1.背景2.问题3.分批插入4.一次最多能插入多少条记录&#xff1f;5.什么是 Prepared Statement&#xff1f;参考文献 1.背景 Golang 后台服务使用 GORM 实现与 MySQL 的交互&#xff0c;在实现一个通过 Excel 导入数据的接口时&#xff0c;使用 Save 方法一次性插入大…
阅读更多...
Mybatis-Plus 租户使用

Mybatis-Plus 租户使用

Mybatis-Plus 租户使用 文章目录 Mybatis-Plus 租户使用一. 前言1.1 租户存在的意义1.2 租户框架 二. Mybatis-plus 租户2.1 租户处理器2.2 前置准备1. 依赖2. 表及数据准备3. 代码生成器 2.3 使用 三. 深入使用3.1 前言3.2 租户主体设值&#xff0c;取值3.3 部分表全量db操作3…
阅读更多...
《斯坦福数据挖掘教程·第三版》读书笔记（英文版）Chapter 3 Finding Similar Items

《斯坦福数据挖掘教程·第三版》读书笔记（英文版）Chapter 3 Finding Similar Items

来源&#xff1a;《斯坦福数据挖掘教程第三版》对应的公开英文书和PPT It is therefore a pleasant surprise to learn of a family of techniques called locality-sensitive hashing, or LSH, that allows us to focus on pairs that are likely to be similar, without hav…
阅读更多...
第二十章 解读PASCAL VOC2012与MS COCO数据集(工具)

第二十章 解读PASCAL VOC2012与MS COCO数据集(工具)

PASCAL VOC2012数据集 Pascal VOC2012官网地址&#xff1a;http://host.robots.ox.ac.uk/pascal/VOC/voc2012/ 官方发表关于介绍数据集的文章 《The PASCALVisual Object Classes Challenge: A Retrospective》&#xff1a;http://host.robots.ox.ac.uk/pascal/VOC/pubs/everi…
阅读更多...
github上不去

github上不去

想要网上找代码发现github上不去了 发现之前的fastgit也用不了了 搜了很多地方终于找到了 记录保存一下 fastgithub最新下载 选择第二个下载解压就行 使用成功&#xff01;
阅读更多...
最新文章

Copyright @ 2022~2023