RDS+ClickHouse构建一站式HTAP-阿里云帮助中心

手动部署

100

https://www.aliyun.com/solution/tech-solution/rdsclickhouse_htap

方案概览

在面对大规模业务数据的在线统计分析需求时，传统的数据库往往难以满足高性能和实时分析的要求，随着ClickHouse社区的不断发展壮大，越来越多的开发者寄希望于通过将MySQL的数据同步到ClickHouse进行加速分析，虽然ClickHouse官方推出了MaterializedMySQL的方式，然而如何将MySQL的数据简单方便、快速灵活地同步到ClickHouse却是一个比较繁琐的问题，需要依赖大量的黑屏操作来配置同步。

本解决方案通过产品化融合MySQL和ClickHouse，实现用户可视化和白屏化操作数据一键同步，灵活地配置MySQL与ClickHouse数据表的实时同步，构建更易用、更好用的一站式HTAP（混合事务/分析处理）解决方案。利用ClickHouse的在线实时分析能力，解决大规模业务数据的在线统计分析，如业务报表统计、交互式运营分析、对账以及实时数仓等业务场景，实现事务在线处理和在线分析的一体化。

方案架构

方案提供的默认设置（如地域、VPC、安全组、vSwitch、实例名称等）完成部署后在阿里云上搭建的RDS MySQL实时数据同步到云数据库 ClickHouse 进行加速分析的架构图如下图所示。实际部署时您可以根据资源规划修改部分设置，但最终形成的运行环境与下图相似。

本方案的技术架构包括以下基础设施和云服务：

地域和可用区：RDS MySQL 实例和云数据库 ClickHouse 实例必须在同一个地域中，可以选择部署在不同的可用区。
1个专有网络 VPC：RDS MySQL 实例和云数据库 ClickHouse 实例必须在同一个 VPC 网络环境中。
1个 RDS MySQL 实例：RDS MySQL 实例，用于日常在线业务（OLTP）系统的数据库存取操作等。
1个云数据库 ClickHouse 实例：云数据库 ClickHouse 实例，用于将 RDS MySQL 实例的数据同步到云数据库 ClickHouse 中，实现针对 MySQL 数据的加速访问，用于实时报表、运营分析等 OLAP 业务。

部署准备

开始部署前，请按以下指引完成账号申请、服务开通、网络规划、资源规划等准备工作。

准备账号

如果您还没有阿里云账号，请访问阿里云账号注册页面，根据页面提示完成注册。阿里云账号是您使用云资源的付费实体，因此是部署方案的必要前提。

为阿里云账号充值。

为节省成本，本方案默认选择使用按量付费资源，使用按量付费资源需要确保账户余额不小于100元。

完成本方案的部署及体验，预计产生费用不超过20元（假设您选择下表中的相关规格资源，且运行时间不超过2小时，如果调整了资源规格，请以控制台显示的实际报价以及最终账单为准）。

云服务	规格配置	地域	预估费用参考
云数据库 RDS MySQL 版	规格：mysql.x4.medium.2c（2 核 8GB）存储空间：100 GB	华东2（上海）	6.10 元/小时
云数据库 ClickHouse 版	规格：单副本标准版 8 核 32GB 存储空间：100 GB	华东2（上海）	2.981 元/小时
按量费用：9.081 元/小时

阿里云账号拥有操作资源的最高权限，从云资源安全角度考虑，建议您创建RAM用户。RAM用户需要获得相关云服务的访问权限才能完成方案部署，详情如下：

云服务	需要的权限	描述
专有网络 VPC	AliyunVPCFullAccess	管理专有网络VPC的权限
云数据库 RDS MySQL 版	AliyunRDSFullAccess	管理云数据库RDS的权限
云数据库 ClickHouse 版	AliyunClickHouseFullAccess AliyunHBaseFullAccess	管理云数据库ClickHouse的权限

创建RAM用户的操作指引请参见创建RAM用户。

为RAM用户授权的操作指引请参见为RAM用户授权。

规划网络和资源

网络规划

请参考表格中的说明和方案默认示例值为每个规划项做详细规划并在实际部署时将默认示例值修改为您的实际规划。

规划项	说明	方案默认示例
地域
地域	您的云服务部署的地域。选择地域的基本原则请参见地域和可用区。	华东2（上海）
专有网络 VPC
VPC名称	建议您再部署过程中新建一个VPC作为本方案的专有网络。部署过程中填写VPC名称即可创建对应名称的VPC。长度为2~128个字符，以英文大小字母或中文开头，可包含数字、下划线（_）和连字符（-）。	vpc_rdsmysql2ck
网段	在创建VPC时，您必须按照无类域间路由块（CIDR block）的格式为您的专有网络划分私网网段。阿里云VPC支持的网段信息请参见专有网络组成部分。在网络规划时可以按照管理网段-开发网段-测试网段-生产网段等规则做好规划。网段一旦投入使用，调整过程复杂，因此规划十分重要。	192.168.0.0/16
虚拟交换机
台数	本方案需要至少1台交换机，用来连接不同的云资源实例。	1
vswitch 名称	建议您在部署过程中在新建的VPC内创建虚拟交换机。部署过程中填写vswitch名称即可创建对应名称的虚拟交换机。长度为2~128个字符，以英文大小字母或中文开头，可包含数字、下划线（_）和连字符（-）。	vswitch_rdsmysql2ck
可用区	在规划的地域内选择2个可用区，2台虚拟交换机分别部署在2个可用区。建议选择排序靠后的，一般此类可用区较新。新可用区资源更充沛，新规格也会在新的可用区优先上线。	可用区 G
IPv4网段	每台虚拟交换机需要一个IPv4网段	192.168.0.0/24

规划云资源

请参考表格中的说明和方案默认示例值为每个规划项做详细规划并在实际部署时将默认示例值修改为您的实际规划。

规划项	数量	说明
云服务器 ECS （可选）	1	本方案需要1台ECS实例，用于部署应用程序向RDS MySQL实例写入实时数据等。
云数据库 RDS MySQL 版	1	本方案需要1个RDS MySQL实例，用于承接线上业务系统数据，在线事务相关的数据等。
云数据库 ClickHouse 版	1	本方案需要1个ClickHouse实例，用于实现将RDS MySQL的数据实时、近实时同步到ClickHouse进行加速分析等。

部署资源

规划好云数据库 RDS MySQL 以及云数据库 ClickHouse 的资源后，请按照以下步骤部署方案中的所有资源。

1. 创建专有网络VPC和交换机

登录专有网络管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击专有网络。
在专有网络页面，单击创建专有网络。

在创建专有网络页面，按照网络规划配置1个专有网络和1台交换机，然后单击确定。配置交换机时，请确保交换机所属的可用区的ECS、RDS、ClickHouse等资源是可用状态。

项目	说明	示例值
专有网络
名称	建议您在部署过程中新建一个VPC作为本方案的专有网络。部署过程中填写VPC名称即可创建对应名称的VPC。长度为2~128个字符，以英文大小字母或中文开头，可包含数字、下划线（_）和连字符（-）。	vpc_rdsmysql2ck
IPv4网段	在创建VPC时，您必须按照无类域间路由块（CIDR block）的格式为您的专有网络划分私网网段。阿里云VPC支持的网段信息请参见专有网络组成部分。在网络规划时可以按照管理网段-开发网段-测试网段-生产网段等规则做好规划。网段一旦投入使用，调整过程复杂，因此规划十分重要。	192.168.0.0/16
交换机
名称	建议您在部署过程中在新建的VPC内创建虚拟交换机。部署过程中填写交换机名称即可创建对应名称的虚拟交换机。长度为2~128个字符，以英文大小字母或中文开头，可包含数字、下划线（_）和连字符（-）。	vsw_rdsmysql2ck
可用区	在规划的地域内选择2个可用区，2台虚拟交换机分别部署在2个可用区。建议选择排序靠后的，一般此类可用区较新。新可用区资源更充沛，新规格也会在新的可用区优先上线。	可用区 G

2. 创建云数据库RDS MySQL

登录云数据库 RDS 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击实例列表。

在实例列表页面中，单击创建实例，按照如下规划配置 RDS MySQL 相关的配置项。

项目	说明	示例值
计费方案	本方案以按量付费为例。	按量付费
地域	本方案以华东2（上海）地域为例。	华东2（上海）
类型	本方案以MySQL 8.0为例。	MySQL 8.0
产品系列	为保证实例的高可用性，本方案选择高可用版本。选择高可用版本或者集群版本等，请参见如何选择系列。	高可用版
架构	云数据库RDS MySQL版实例的CPU架构支持ARM和X86架构，选择架构请参见如何选择架构。	X86
存储类型	云数据库RDS MySQL版实例支持多种不同的存储类型，选择存储类型请参见如何选择存储类型	ESSD PL1云盘
主节点可用区	选择RDS MySQL实例部署主节点所在的可用区。	上海可用区G
部署方案	选择RDS MySQL实例部署方案，可以选择多可用区部署或者单可用区部署。	单可用区部署
实例规格	选择RDS MySQL实例的规格，RDS MySQL支持各种不同的规格，请参见如何选择实例规格。	分类：独享规格规格代码：mysql.x4.medium.2c
存储空间	选择RDS MySQL实例的存储空间，请参见存储空间与自动扩展。	100GB
VPC	选择之前规划的专有网络VPC及交换机。
主节点交换机	选择之前规划的专有网络VPC及交换机。
加入白名单	将VPC网段加入到RDS实例白名单中，使得同一VPC下的ECS实例可以访问RDS实例。	是

单击确认订单，根据提示完成购买。

3. 创建云数据库ClickHouse

登录云数据库 ClickHouse 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击集群列表。

在默认实例列表页面中（注意：本方案不支持云原生版本实例列表），单击新建集群，按照如下规划配置云数据库 ClickHouse 相关的配置项。

项目	说明	示例值
商品类型	云数据库 ClickHouse 实例的计费方式。云数据库 ClickHouse 计费的详细信息请参见产品计费。	按量付费
地域和可用区	选择实例部署的地域和可用区。	华东2（上海）
专有网络（VPC）	选择之前规划的专有网络VPC。
主可用区	选择实例部署可用区。	上海可用区G
主专有网络交换机	选择之前规划的交换机。
内核版本	选择Redis实例的内核版本。	22.8
规格类型	标准规格实例基于云盘存储，数据存储默认具有多副本，支持实例的升降配和扩容，数据安全性和资源弹性灵活度更高。	标准规格
规格	云数据库 ClickHouse 实例规格信息。关于云数据库 ClickHouse 规格列表和定价请参见云ClickHouse规格说明。	标准型 8核 32GB
存储类型	ESSD盘或者高效云盘，推荐ESSD盘。	ESSD PL1云盘（推荐）
加密类型	选择是否进行云盘加密。	不加密
存储空间	存储容量。	100 GB
节点数量	云数据库 ClickHouse 实例节点数量	1

单击确认订单，根据提示完成购买。

创建数据同步账号

在云数据库 RDS MySQL 实例以及云数据库 ClickHouse 实例资源创建完成之后，分别针对 RDS MySQL 实例和云数据库 ClickHouse 实例创建用于数据同步的账号，并且配置密码等。

1. 创建RDS MySQL用户账号

登录云数据库 RDS 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击实例列表。
在实例列表页面中，找到上述步骤中创建出来的 RDS MySQL 实例，单击实例ID进入实例详情控制台。
在控制台实例详情页，在左侧导航栏中，单击账号管理。
在用户账号标签页中，单击创建账号按钮，填写相应的账号信息（数据库账号、账号类型、密码信息）。

重要

在后续的配置步骤中，需要使用该账号进行 RDS MySQL 数据同步到 ClickHouse 中，故该账号的权限至少需要满足：RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, SELECT，建议创建高权限账号。

2. 创建云ClickHouse用户账号

登录云数据库 ClickHouse 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击集群列表。
在集群信息页面中，找到上述步骤中创建出来的云数据库 ClickHouse 实例，单击集群ID进入实例详情控制台。
在控制台实例详情页，在左侧导航栏中，单击账号管理。
在用户账号标签页中，单击创建账号按钮，填写相应的账号信息（数据库账号、账号类型、密码信息），建议创建高权限账号。

RDS MySQL写入数据（可选）

在云数据库 RDS MySQL 实例以及云数据库 ClickHouse 实例资源创建完成以及创建好用于数据同步的用户账号后，接下来开始通过控制台白屏化的方式进行数据同步操作，在进行窗口白屏化配置数据同步之前，需要先确保 RDS MySQL数据库中有相应的待同步库、表对象和数据信息，本方案为了更好的说明数据同步的配置步骤，在此给出可选操作，用于模拟向 RDS MySQL 数据库写入库表信息、持续产生数据负载。在真实的业务场景中，可以采用真正的业务系统涉及到的库表信息等进行操作。

1. 准备数据程序

准备数据写入程序，用于模拟向RDS MySQL数据库实例的库表对象写入数据，也可以采用日常业务应用系统连接到数据库进行增删改查等操作。举例：下载并且编译tpcc-mysql压测程序（找到一个客户端环境，比如 ECS服务器等）

说明

上述TPCC-MySQL压测程序来自于 tpcc-mysql 源码，已经编译安装好，在 src 目录下生成两个可执行程序，分别是：tpcc_load 和 tpcc_start 。也可以自行进行下载编译安装，命令为：
```
git clone https://github.com/Percona-Lab/tpcc-mysql.git
cd tpcc-mysql/src; make
```
为了更好的对比RDS MySQL数据库实例与ClickHouse实例之间对查询加速的效果对比，优先建议使用您自身的业务系统的数据和SQL语句等，也可以使用TPCC/TPCH等标准工具。亦可参考ClickHouse官网ClickBench性能对比相关数据。

2. 创建库表对象

创建数据库：连接 RDS MySQL 数据库，并创建数据库对象，比如 tpcc 数据库，参考命令如下：
```
# 连接数据库
mysql -h <connection_string> -P 3306 -u <user> -p<password> -e "CREATE DATABASE IF NOT EXISTS tpcc;"
```
创建表结构：连接 RDS MySQL 数据库，并创建表对象（表结构、索引、外检约束等），比如通过上述下载的 tpcc-mysql 工具创建相关的库表对象，参考命令如下：
```
mysql -h <connection_string> -P 3306 -u <user> -p<password> -D tpcc < tpcc-mysql/create_table.sql
mysql -h <connection_string> -P 3306 -u <user> -p<password> -D tpcc < tpcc-mysql/add_fkey_idx.sql
```

3. 启动TPCC程序

向数据库加载测试数据，比如通过上述下载的 tpcc-mysql 工具中的 tpcc_load 工具，参考命令行如下：
```
tpcc-mysql/tpcc_load -h <connection_string> -P <port> -d <database> -u <user> -p <password> -w <warehouse>
```
示例：
```
tpcc-mysql/tpcc_load -h <rds2ck_demo>.mysql.rds.aliyuncs.com -P 3306 -d tpcc -u <user> -p <password> -w 10
```
参数说明：
- <database> 表示数据库名称，上面的步骤中我们定义为 tpcc 。
- <warehouse> 表示 warehouse 的数量，比如我们设置为 10。
启动 TPCC 性能负载压测，比如通过上述下载的 tpcc-mysql 工具中的 tpcc_start 工具，参考命令行如下：
```
tpcc-mysql/tpcc_start -h <connection_string> -P 3306 -d tpcc -u <user> -p <password> -w <warehouse> -c <connections> -r <warmup_time> -l <running_time> -i <report_interval>
```
示例：
```
tpcc-mysql/tpcc_start -h <rds2ck_demo>.mysql.rds.aliyuncs.com -P 3306 -d tpcc -u <rds2ck_demo> -p <password> -w10 -c 100 -r 180 -l 600 -i 1 
```
参数说明：
- 可以使用 tpcc-mysql/tpcc_start --help 来查看每一个命令行参数的说明。
- <warehouse> 表示 warehouse 的数量，比如设置为 10。
- <connections> 表示客户端的数量，比如设置为100。
- <warmup_time> 表示 TPCC 程序压测时的预热时间，单位是秒。
- <running_time> 表示 TPCC 程序压测的持续时间，单位是秒。
- <report_intervel> 表示 TPCC 程序每隔多久（秒）打印一次信息。

配置数据同步

在云数据库 RDS MySQL 实例以及云数据库 ClickHouse 实例资源创建完成以及创建好用于数据同步的用户账号后，接下来开始通过控制台白屏化的方式进行数据同步操作。

登录云数据库 RDS 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击实例列表。
在实例列表页面中，找到上述步骤中创建出来的 RDS MySQL 实例，单击实例ID进入实例详情控制台。
在控制台实例详情页，在左侧导航栏中，单击基本信息。
导航到实例分布区块，在分析实例模块上点击云ClickHouse分析实例，如图所示。
在 MySQL 到 ClickHouse 分析实例数据同步配置 配置窗口，分别填写 RDS MySQL 实例用户账号信息、下拉选择云数据库 ClickHouse 集群ID、填写云数据库 ClickHouse 用户账号信息，填写完成后，点击测试连接以进入下一步进行连通性测试，如图所示。
进入同步库表配置窗口，勾选需要同步的库表信息，核对源库对象和目标实例配置，最后点击下一步：保存任务并启动同步，如图所示。
完成上述步骤后，将会自动导航到云ClickHouse控制台，在左侧导航栏MySQL实时同步，会展示出当前正在进行的同步任务，可以查看同步状态、同步库表对象，同时也可以修改同步任务等，如图所示。

方案验证

一、通过执行复杂查询命令，验证云数据库ClickHouse高性能查询

登录云数据库 ClickHouse 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击集群列表。
在集群信息页面中，找到上述步骤中创建出来的云数据库 ClickHouse 实例，单击右上角按钮登录数据库可以进入DMS界面。
说明
除了DMS，也可以通过 clickhouse-client 命令行的方式登录云数据库 ClickHouse 数据库进行查询分析。（本方案以DMS登录为例）
由于 MySQL 和 ClickHouse 之间的同步原理是基于 MaterializedMySQL 数据库引擎的机制实现同步的，在ClickHouse中的表采用的是 ReplicatedReplacingMergeTree 引擎。故表结构会新增两个额外的字段：_sign 和 _version。其中：
1. _sign 字段：对于源端 insert 操作，此字段的值为1；对于源端 delete 操作，此字段的值为-1。
2. _version 字段：此字段的值用来表示操作的顺序，每次 insert、update 和 delete 操作，此字段的值都会增加；
基于上面的 MySQL 和 ClickHouse 之间的数据同步原理，由于可能存在 MySQL 和 ClickHouse 之间数据同步延迟的情况，故在 ClickHouse 端进行数据查询和分析时，需要特别注意数据一致性的问题，要想查询最新的数据，有如下两种方法：
1. 使用 ClickHouse 的 FINAL 关键字：在 ClickHouse 中 FINAL 关键字可以用于获取已经合并的数据（但是使用 FINAL 关键字会增加消耗查询的计算资源），比如可以使用如下的语句进行查询：
```
SELECT w_id, w_name, w_street_1, w_street_2, w_city, w_state, w_zip, w_tax, w_ytd
FROM tpcc.warehouse FINAL
WHERE _sign = 1;
```
2. 通过 order by + group by 配合_version&_sign 的方式来获取最新数据：比如可以使用如下的语句进行查询：
```
SELECT
    w_id,
    w_name,
    w_street_1,
    w_street_2,
    w_city,
    w_state,
    w_zip,
    w_tax,
    w_ytd
FROM tpcc.warehouse
WHERE (_sign = 1) AND ((w_id, _version) GLOBAL IN (
    SELECT
        w_id,
        max(_version)
    FROM tpcc.warehouse
    GROUP BY w_id
))
```

二、通过登录云数据库ClickHouse实例后台，验证云数据库MySQL实时同步状态

登录云数据库 ClickHouse 管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击MySQL实时同步。
查看同步任务状态如下：
说明
检查当前正在进行的同步任务，可以查看同步状态、同步库表对象，同时也可以修改同步任务等

清理资源

在本方案中，您创建了1个专有网络VPC、1台交换机、1个云数据库 RDS MySQL 实例、1个云数据库 ClickHouse 实例。测试完方案后，您可以参考以下规则处理基础设施和云服务的实例，避免继续产生费用：

释放1个云数据库 RDS MySQL 实例：
登录RDS管理控制台在实例列表页面，找到目标实例，然后在操作列选择>释放设置，根据界面提示释放实例。
释放1个云数据库 ClickHouse 实例：
登录ClickHouse管理控制台，在默认实例列表页面，找到目标实例，然后在操作列选择>删除，根据界面提示释放实例。
释放1台交换机：
登录专有网络控制台，在交换机页面，找到目标交换机，然后在操作列单击删除，按照界面提示释放交换机。
释放1个专有网络VPC：
登录专有网络控制台，在专有网络页面，找到目标VPC，然后在操作列单击删除，按照界面提示释放专有网络。

一键部署