5分钟搞定 MySQL 到 ClickHouse 宽表构建和同步-CloudCanal实战

ClouGence 2021-12-17

446

简述

上一篇文章介绍了使用 CloudCanal 进行 MySQL 到 ElasticSearch 的宽表构建, 有蛮多朋友关注和尝试使用，我们也在不断迭代升级这个能力。

作为产品的通用功能，今天我们介绍下 MySQL -> ClickHouse 的宽表构建案例。

技术点

ClickHouse 表关联之觞

ClickHouse 作为标准的列存数据库，其特点相当鲜明，对于多维度数据聚合、筛选特别高效,对于列存面向计算的特点，用得相当不错，包括但不限于以下特点

io 效率高

列压缩
少数列数据存取io放大效应较小

极致计算优化

向量化
利用 SSE 等 SIMD 指令集加速
未来可选 AVX 512 等指令集优化
未来对于计算卸载到 FPGA、GPU 较便利

但是 ClickHouse 对于数据关联（join）, 相比于其 多维聚合、筛选能力要弱一些。对于这个问题，我们觉得有必要通过 CloudCanal 的宽表能力，让其适用性得到进一步提升。大宽表 + 突出的数据 多维聚合、筛选能力，几乎等于交互式分析的杀手锏。

操作示例

前置条件:

CloudCanal 社区版部署,参见社区版安装文档(https://doc-cloudcanal.clougence.com/operation/install_linux)
准备好 MySQL 数据库（本例使用 5.7 版本）和 ClickHouse 数据库（本例使用 21.8.X 版本）

MySQL 上创建 1 张事实表(my_order)和 2 张维表 (user 、product)

 CREATE TABLE `my_order` (
   `id` bigint(19) NOT NULL AUTO_INCREMENT,
   `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1460 DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(20,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2719 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `name` varchar(255) NOT NULL,
  `level` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2224 DEFAULT CHARSET=utf8
复制

ClickHouse 上创建 1 张宽表 my_order , 并额外包含两张维表相关数据

CREATE TABLE trade.my_order
(
   `id` Int64,
   `gmt_create` DateTime,
   `gmt_modified` DateTime,
   `product_id` Int64,
   `user_id` Int64,
   `user_name` Nullable(String),
   `product_name` Nullable(String),
   `product_price` Nullable(Decimal(20, 2))
 )
ENGINE = ReplacingMergeTree
ORDER BY id
SETTINGS index_granularity = 8192
复制

user_id (关联user.id), user_name(对应user.name)
product_id(关联product.id) ,product_name(对应product.name),product_price (对应product.price)

开发宽表代码

打开代码工程cloudcanal-data-process
(https://gitee.com/clougence/cloudcanal-data-process) ，并找到代码类 MySqlToChOnlyFact_one_fact_two_dim.java
修改必要信息

打包

进入工程目录，使用命令进行打包

% pwd
/Users/zylicfc/source/product/cloudcanal/cloudcanal-data-process
% mvn -Dtest -DfailIfNoTests=false -Dmaven.javadoc.skip=true -Dmaven.compile.fork=true clean package
复制