如何利用FlinkCDC实现数据增量备份到Clickhouse

挖了很久的CDC坑&＃xff0c;今天打算填一填了。本文我们首先来介绍什么是CDC&＃xff0c;以及CDC工具选型&＃xff0c;接下来我们来介绍如何通过Flink CDC抓取mysql中的数据&＃xff0c;并把他汇入Clickhouse里&＃xff0c;最后我们还将介绍Flink SQL CDC的方式。

首先什么是CDC &＃xff1f;它是Change Data Capture的缩写,即变更数据捕捉的简称&＃xff0c;使用CDC我们可以从数据库中获取已提交的更改并将这些更改发送到下游&＃xff0c;供下游使用。这些变更可以包括INSERT,DELETE,UPDATE等操作。

其主要的应用场景&＃xff1a;

• 异构数据库之间的数据同步或备份 / 建立数据分析计算平台

• 微服务之间共享数据状态

• 更新缓存 / CQRS 的 Query 视图更新

CDC 它是一个比较广义的概念&＃xff0c;只要能捕获变更的数据&＃xff0c;我们都可以称为 CDC 。业界主要有基于查询的 CDC 和基于日志的 CDC &＃xff0c;可以从下面表格对比他们功能和差异点。

Debezium是一个开源项目&＃xff0c;为捕获数据更改(change data capture,CDC)提供了一个低延迟的流式处理平台。你可以安装并且配置Debezium去监控你的数据库&＃xff0c;然后你的应用就可以消费对数据库的每一个行级别(row-level)的更改。只有已提交的更改才是可见的&＃xff0c;所以你的应用不用担心事务(transaction)或者更改被回滚(roll back)。Debezium为所有的数据库更改事件提供了一个统一的模型&＃xff0c;所以你的应用不用担心每一种数据库管理系统的错综复杂性。另外&＃xff0c;由于Debezium用持久化的、有副本备份的日志来记录数据库数据变化的历史&＃xff0c;因此&＃xff0c;你的应用可以随时停止再重启&＃xff0c;而不会错过它停止运行时发生的事件&＃xff0c;保证了所有的事件都能被正确地、完全地处理掉。

Debezium is an open source distributed platform for change data capture. Start it up, point it at your databases, and your apps can start responding to all of the inserts, updates, and deletes that other apps commit to your databases. Debezium is durable and fast, so your apps can respond quickly and never miss an event, even when things go wrong

实时数据分析数据库&＃xff0c;俄罗斯的谷歌开发的&＃xff0c;推荐OLAP场景使用

Clickhouse的优点.

1. 真正的面向列的 DBMS

   ClickHouse 是一个 DBMS&＃xff0c;而不是一个单一的数据库。它允许在运行时创建表和数据库、加载数据和运行

   查询&＃xff0c;而无需重新配置和重新启动服务器。

2. 数据压缩

   一些面向列的 DBMS&＃xff08;InfiniDB CE 和 MonetDB&＃xff09;不使用数据压缩。但是&＃xff0c;数据压缩确实提高了性能。

3. 磁盘存储的数据

4. 在多个服务器上分布式处理

5. SQL支持

6. 数据不仅按列存储&＃xff0c;而且由矢量 - 列的部分进行处理&＃xff0c;这使开发者能够实现高 CPU 性能

Clickhouse的缺点

1. 没有完整的事务支持&＃xff0c;

2. 缺少完整的Update/Delete操作&＃xff0c;缺少高频率、低延迟的修改或删除已存在数据的能力&＃xff0c;仅能用于批量删

   除或修改数据

3. 聚合结果必须小于一台机器的内存大小&＃xff1a;

4. 不适合key-value存储&＃xff0c;

什么时候不可以用Clickhouse?

1. 事物性工作&＃xff08;OLTP&＃xff09;

2. 高并发的键值访问

3. Blob或者文档存储

4. 超标准化的数据

Flink cdc connector 消费 Debezium 里的数据&＃xff0c;经过处理再sink出来&＃xff0c;这个流程还是相对比较简单的

首先创建 Source 和 Sink&＃xff08;对应的依赖引用&＃xff0c;在文末&＃xff09;

SourceFunction sourceFunction &＃61; MySQLSource.builder().hostname("localhost").port(3306).databaseList("test").username("flinkcdc").password("dafei1288").deserializer(new JsonDebeziumDeserializationSchema()).build();// 添加 sourceenv.addSource(sourceFunction)// 添加 sink.addSink(new ClickhouseSink());

这里用到的JsonDebeziumDeserializationSchema&＃xff0c;是我们自定义的一个序列化类&＃xff0c;用于将Debezium输出的数据&＃xff0c;序列化

// 将cdc数据反序列化public static class JsonDebeziumDeserializationSchema implements DebeziumDeserializationSchema {&＃64;Overridepublic void deserialize(SourceRecord sourceRecord, Collector collector) throws Exception {Gson jsstr &＃61; new Gson();HashMap hs &＃61; new HashMap<>();String topic &＃61; sourceRecord.topic();String[] split &＃61; topic.split("[.]");String database &＃61; split[1];String table &＃61; split[2];hs.put("database",database);hs.put("table",table);//获取操作类型Envelope.Operation operation &＃61; Envelope.operationFor(sourceRecord);//获取数据本身Struct struct &＃61; (Struct)sourceRecord.value();Struct after &＃61; struct.getStruct("after");if (after !&＃61; null) {Schema schema &＃61; after.schema();HashMap afhs &＃61; new HashMap<>();for (Field field : schema.fields()) {afhs.put(field.name(), after.get(field.name()));}hs.put("data",afhs);}String type &＃61; operation.toString().toLowerCase();if ("create".equals(type)) {type &＃61; "insert";}hs.put("type",type);collector.collect(jsstr.toJson(hs));}&＃64;Overridepublic TypeInformation getProducedType() {return BasicTypeInfo.STRING_TYPE_INFO;}}

这里是将数据序列化成如下Json格式

{"database":"test","data":{"name":"jacky","description":"fffff","id":8},"type":"insert","table":"test_cdc"}

接下来就是要创建Sink&＃xff0c;将数据变化存入Clickhouse中&＃xff0c;这里我们仅以insert为例

public static class ClickhouseSink extends RichSinkFunction{Connection connection;PreparedStatement pstmt;private Connection getConnection() {Connection conn &＃61; null;try {Class.forName("ru.yandex.clickhouse.ClickHouseDriver");String url &＃61; "jdbc:clickhouse://localhost:8123/default";conn &＃61; DriverManager.getConnection(url,"default","dafei1288");} catch (Exception e) {e.printStackTrace();}return conn;}&＃64;Overridepublic void open(Configuration parameters) throws Exception {super.open(parameters);connection &＃61; getConnection();String sql &＃61; "insert into sink_ch_test(id,name,description) values (?,?,?)";pstmt &＃61; connection.prepareStatement(sql);}// 每条记录插入时调用一次public void invoke(String value, Context context) throws Exception {//{"database":"test","data":{"name":"jacky","description":"fffff","id":8},"type":"insert","table":"test_cdc"}Gson t &＃61; new Gson();HashMap hs &＃61; t.fromJson(value,HashMap.class);String database &＃61; (String)hs.get("database");String table &＃61; (String)hs.get("table");String type &＃61; (String)hs.get("type");if("test".equals(database) && "test_cdc".equals(table)){if("insert".equals(type)){System.out.println("insert &＃61;> "&＃43;value);LinkedTreeMap data &＃61; (LinkedTreeMap)hs.get("data");String name &＃61; (String)data.get("name");String description &＃61; (String)data.get("description");Double id &＃61; (Double)data.get("id");// 未前面的占位符赋值pstmt.setInt(1, id.intValue());pstmt.setString(2, name);pstmt.setString(3, description);pstmt.executeUpdate();}}}&＃64;Overridepublic void close() throws Exception {super.close();if(pstmt !&＃61; null) {pstmt.close();}if(connection !&＃61; null) {connection.close();}}}

完整代码案例&＃xff1a;

package name.lijiaqi.cdc;import com.alibaba.ververica.cdc.debezium.DebeziumDeserializationSchema;
import com.google.gson.Gson;
import com.google.gson.internal.LinkedTreeMap;
import io.debezium.data.Envelope;
import org.apache.flink.api.common.typeinfo.BasicTypeInfo;
import org.apache.flink.api.common.typeinfo.TypeInformation;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction;
import org.apache.flink.streaming.api.functions.source.SourceFunction;
import com.alibaba.ververica.cdc.connectors.mysql.MySQLSource;
import org.apache.flink.util.Collector;
import org.apache.kafka.connect.source.SourceRecord;import org.apache.kafka.connect.data.Field;
import org.apache.kafka.connect.data.Schema;
import org.apache.kafka.connect.data.Struct;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.HashMap;public class MySqlBinlogSourceExample {public static void main(String[] args) throws Exception {SourceFunction sourceFunction &＃61; MySQLSource.builder().hostname("localhost").port(3306).databaseList("test").username("flinkcdc").password("dafei1288").deserializer(new JsonDebeziumDeserializationSchema()).build();StreamExecutionEnvironment env &＃61; StreamExecutionEnvironment.getExecutionEnvironment();// 添加 sourceenv.addSource(sourceFunction)// 添加 sink.addSink(new ClickhouseSink());env.execute("mysql2clickhouse");}// 将cdc数据反序列化public static class JsonDebeziumDeserializationSchema implements DebeziumDeserializationSchema {&＃64;Overridepublic void deserialize(SourceRecord sourceRecord, Collector collector) throws Exception {Gson jsstr &＃61; new Gson();HashMap hs &＃61; new HashMap<>();String topic &＃61; sourceRecord.topic();String[] split &＃61; topic.split("[.]");String database &＃61; split[1];String table &＃61; split[2];hs.put("database",database);hs.put("table",table);//获取操作类型Envelope.Operation operation &＃61; Envelope.operationFor(sourceRecord);//获取数据本身Struct struct &＃61; (Struct)sourceRecord.value();Struct after &＃61; struct.getStruct("after");if (after !&＃61; null) {Schema schema &＃61; after.schema();HashMap afhs &＃61; new HashMap<>();for (Field field : schema.fields()) {afhs.put(field.name(), after.get(field.name()));}hs.put("data",afhs);}String type &＃61; operation.toString().toLowerCase();if ("create".equals(type)) {type &＃61; "insert";}hs.put("type",type);collector.collect(jsstr.toJson(hs));}&＃64;Overridepublic TypeInformation getProducedType() {return BasicTypeInfo.STRING_TYPE_INFO;}}public static class ClickhouseSink extends RichSinkFunction{Connection connection;PreparedStatement pstmt;private Connection getConnection() {Connection conn &＃61; null;try {Class.forName("ru.yandex.clickhouse.ClickHouseDriver");String url &＃61; "jdbc:clickhouse://localhost:8123/default";conn &＃61; DriverManager.getConnection(url,"default","dafei1288");} catch (Exception e) {e.printStackTrace();}return conn;}&＃64;Overridepublic void open(Configuration parameters) throws Exception {super.open(parameters);connection &＃61; getConnection();String sql &＃61; "insert into sink_ch_test(id,name,description) values (?,?,?)";pstmt &＃61; connection.prepareStatement(sql);}// 每条记录插入时调用一次public void invoke(String value, Context context) throws Exception {//{"database":"test","data":{"name":"jacky","description":"fffff","id":8},"type":"insert","table":"test_cdc"}Gson t &＃61; new Gson();HashMap hs &＃61; t.fromJson(value,HashMap.class);String database &＃61; (String)hs.get("database");String table &＃61; (String)hs.get("table");String type &＃61; (String)hs.get("type");if("test".equals(database) && "test_cdc".equals(table)){if("insert".equals(type)){System.out.println("insert &＃61;> "&＃43;value);LinkedTreeMap data &＃61; (LinkedTreeMap)hs.get("data");String name &＃61; (String)data.get("name");String description &＃61; (String)data.get("description");Double id &＃61; (Double)data.get("id");// 未前面的占位符赋值pstmt.setInt(1, id.intValue());pstmt.setString(2, name);pstmt.setString(3, description);pstmt.executeUpdate();}}}&＃64;Overridepublic void close() throws Exception {super.close();if(pstmt !&＃61; null) {pstmt.close();}if(connection !&＃61; null) {connection.close();}}}
}

执行查看结果

数据成功汇入

接下来&＃xff0c;我们看一下如何通过Flink SQL实现CDC &＃xff0c;只需3条SQL语句即可。

创建数据源表

// 数据源表String sourceDDL &＃61;"CREATE TABLE mysql_binlog (\n" &＃43;" id INT NOT NULL,\n" &＃43;" name STRING,\n" &＃43;" description STRING\n" &＃43;") WITH (\n" &＃43;" &＃39;connector&＃39; &＃61; &＃39;mysql-cdc&＃39;,\n" &＃43;" &＃39;hostname&＃39; &＃61; &＃39;localhost&＃39;,\n" &＃43;" &＃39;port&＃39; &＃61; &＃39;3306&＃39;,\n" &＃43;" &＃39;username&＃39; &＃61; &＃39;flinkcdc&＃39;,\n" &＃43;" &＃39;password&＃39; &＃61; &＃39;dafei1288&＃39;,\n" &＃43;" &＃39;database-name&＃39; &＃61; &＃39;test&＃39;,\n" &＃43;" &＃39;table-name&＃39; &＃61; &＃39;test_cdc&＃39;\n" &＃43;")";

创建输出表

// 输出目标表String sinkDDL &＃61;"CREATE TABLE test_cdc_sink (\n" &＃43;" id INT NOT NULL,\n" &＃43;" name STRING,\n" &＃43;" description STRING,\n" &＃43;" PRIMARY KEY (id) NOT ENFORCED \n " &＃43;") WITH (\n" &＃43;" &＃39;connector&＃39; &＃61; &＃39;jdbc&＃39;,\n" &＃43;" &＃39;driver&＃39; &＃61; &＃39;com.mysql.jdbc.Driver&＃39;,\n" &＃43;" &＃39;url&＃39; &＃61; &＃39;" &＃43; url &＃43; "&＃39;,\n" &＃43;" &＃39;username&＃39; &＃61; &＃39;" &＃43; userName &＃43; "&＃39;,\n" &＃43;" &＃39;password&＃39; &＃61; &＃39;" &＃43; password &＃43; "&＃39;,\n" &＃43;" &＃39;table-name&＃39; &＃61; &＃39;" &＃43; mysqlSinkTable &＃43; "&＃39;\n" &＃43;")";

这里我们直接将数据汇入

// 简单的聚合处理String transformSQL &＃61;"insert into test_cdc_sink select * from mysql_binlog";

完整参考代码

package name.lijiaqi.cdc;import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.SqlDialect;
import org.apache.flink.table.api.TableResult;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;public class MysqlToMysqlMain {public static void main(String[] args) throws Exception {EnvironmentSettings fsSettings &＃61; EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build();StreamExecutionEnvironment env &＃61; StreamExecutionEnvironment.getExecutionEnvironment();env.setParallelism(1);StreamTableEnvironment tableEnv &＃61; StreamTableEnvironment.create(env, fsSettings);tableEnv.getConfig().setSqlDialect(SqlDialect.DEFAULT);// 数据源表String sourceDDL &＃61;"CREATE TABLE mysql_binlog (\n" &＃43;" id INT NOT NULL,\n" &＃43;" name STRING,\n" &＃43;" description STRING\n" &＃43;") WITH (\n" &＃43;" &＃39;connector&＃39; &＃61; &＃39;mysql-cdc&＃39;,\n" &＃43;" &＃39;hostname&＃39; &＃61; &＃39;localhost&＃39;,\n" &＃43;" &＃39;port&＃39; &＃61; &＃39;3306&＃39;,\n" &＃43;" &＃39;username&＃39; &＃61; &＃39;flinkcdc&＃39;,\n" &＃43;" &＃39;password&＃39; &＃61; &＃39;dafei1288&＃39;,\n" &＃43;" &＃39;database-name&＃39; &＃61; &＃39;test&＃39;,\n" &＃43;" &＃39;table-name&＃39; &＃61; &＃39;test_cdc&＃39;\n" &＃43;")";String url &＃61; "jdbc:mysql://127.0.0.1:3306/test";String userName &＃61; "root";String password &＃61; "dafei1288";String mysqlSinkTable &＃61; "test_cdc_sink";// 输出目标表String sinkDDL &＃61;"CREATE TABLE test_cdc_sink (\n" &＃43;" id INT NOT NULL,\n" &＃43;" name STRING,\n" &＃43;" description STRING,\n" &＃43;" PRIMARY KEY (id) NOT ENFORCED \n " &＃43;") WITH (\n" &＃43;" &＃39;connector&＃39; &＃61; &＃39;jdbc&＃39;,\n" &＃43;" &＃39;driver&＃39; &＃61; &＃39;com.mysql.jdbc.Driver&＃39;,\n" &＃43;" &＃39;url&＃39; &＃61; &＃39;" &＃43; url &＃43; "&＃39;,\n" &＃43;" &＃39;username&＃39; &＃61; &＃39;" &＃43; userName &＃43; "&＃39;,\n" &＃43;" &＃39;password&＃39; &＃61; &＃39;" &＃43; password &＃43; "&＃39;,\n" &＃43;" &＃39;table-name&＃39; &＃61; &＃39;" &＃43; mysqlSinkTable &＃43; "&＃39;\n" &＃43;")";// 简单的聚合处理String transformSQL &＃61;"insert into test_cdc_sink select * from mysql_binlog";tableEnv.executeSql(sourceDDL);tableEnv.executeSql(sinkDDL);TableResult result &＃61; tableEnv.executeSql(transformSQL);// 等待flink-cdc完成快照result.print();env.execute("sync-flink-cdc");}}

查看执行结果

添加依赖

org.apache.flinkflink-core1.13.0org.apache.flinkflink-streaming-java_2.121.13.0



org.apache.flinkflink-connector-jdbc_2.121.13.0org.apache.flinkflink-java1.13.0org.apache.flinkflink-clients_2.121.13.0org.apache.flinkflink-table-api-java-bridge_2.121.13.0org.apache.flinkflink-table-common1.13.0org.apache.flinkflink-table-planner_2.121.13.0org.apache.flinkflink-table-planner-blink_2.121.13.0org.apache.flinkflink-table-planner-blink_2.121.13.0test-jarcom.alibaba.ververicaflink-connector-mysql-cdc1.4.0com.aliyunflink-connector-clickhouse1.12.0ru.yandex.clickhouseclickhouse-jdbc0.2.6com.google.code.gsongson2.8.6

参考链接&＃xff1a;

https://blog.csdn.net/zhangjun5965/article/details/107605396

https://cloud.tencent.com/developer/article/1745233?from&＃61;article.detail.1747773

https://segmentfault.com/a/1190000039662261

https://www.cnblogs.com/weijiqian/p/13994870.html

《大数据成神之路》正在全面PDF化。

你只需要关注并在后台回复「PDF」就可以看到阿里云盘下载链接了&＃xff01;

另外我把发表过的文章按照体系全部整理好了。现在你可以在后台方便的进行查找&＃xff1a;

电子版把他们分类做成了下面这个样子&＃xff0c;并且放在了阿里云盘提供下载。

我们点开一个文件夹后:

如果这个文章对你有帮助&＃xff0c;不要忘记 「在看」 「点赞」 「收藏」 三连啊喂&＃xff01;

Hi&＃xff0c;我是王知无&＃xff0c;一个大数据领域的原创作者。 

放心关注我&＃xff0c;获取更多行业的一手消息。

八千里路云和月 | 从零到大数据专家学习路径指南

互联网最坏的时代可能真的来了

我在B站读大学&＃xff0c;大数据专业

我们在学习Flink的时候&＃xff0c;到底在学习什么&＃xff1f;

193篇文章暴揍Flink&＃xff0c;这个合集你需要关注一下

Flink生产环境TOP难题与优化&＃xff0c;阿里巴巴藏经阁YYDS

Flink CDC我吃定了耶稣也留不住他&＃xff01;| Flink CDC线上问题小盘点

我们在学习Spark的时候&＃xff0c;到底在学习什么&＃xff1f;

在所有Spark模块中&＃xff0c;我愿称SparkSQL为最强&＃xff01;

硬刚Hive | 4万字基础调优面试小总结

数据治理方法论和实践小百科全书

标签体系下的用户画像建设小指南

4万字长文 | ClickHouse基础&实践&调优全视角解析

【面试&个人成长】2021年过半&＃xff0c;社招和校招的经验之谈

大数据方向另一个十年开启 |《硬刚系列》第一版完结

我写过的关于成长/面试/职场进阶的文章

当我们在学习Hive的时候在学习什么&＃xff1f;「硬刚Hive续集」