阿里云DTS作为数据世界高速传输通道的建造者，每周为您分享一个避坑技巧，助力数据之旅更加快捷、便利、安全。
导读
在企业数字化转型的大趋势下，大规模业务数据的在线统计分析需求越来越强烈，传统的数据库往往难以满足高性能和实时分析的要求，随着ClickHouse社区的不断发展壮大，越来越多的开发者寄希望于通过将MySQL的数据同步到ClickHouse进行加速分析。小编我也跃跃欲试，但是尝试了ClickHouse官方推出的MaterializedMySQL方式，发现有如下限制：
- 阿里云ClickHouse有社区兼容版和企业版，ClickHouse企业版不支持MaterializeMySQL引擎， 社区兼容版集群仅20.8及以上版本支持MaterializeMySQL引擎。
- 同步过程无法可视化，同步任务无状态显示，无法直接看到同步性能与延迟。
- MaterializeMySQL引擎不支持无主键表迁移。
- 数据源RDS MySQL集群和目标ClickHouse集群必须属于同一个VPC网络。
- 不支持ETL， 无法做库表列映射，也不支持附加列。
- 不支持数据过滤， 无法选择同步哪些dml或者ddl， 无法通过filter来选择只同步一个表中的部分数据。
- 同步任务无法暂停，只能删除重配，删除任务后目标库同步的库表也会被删除。无法进行断点续传。
- 不支持某些DDL同步，例如create table， 修改表的comment, 修改列的comment。
小编所在的DTS团队针对上述限制快速开发了MySQL到ClickHouse的同步链路，下面小编将带领大家详细了解如何使用DTS平滑、可靠地进行MySQL到ClickHouse的数据同步。
使用阿里云DTS进行MySQL->ClickHouse同步的优势
针对使用MaterializedMySQL的方式存在的问题，阿里云DTS开发了MySQL数据库到ClickHouse的同步链路，相比于MaterializedMySQL的方式，DTS MySQL->ClickHouse同步具有如下优势：
- 兼容性高：目标端支持阿里云ClickHouse社区兼容版和企业版。
- 同步过程可视化：可在阿里云DTS控制台上看到同步过程，比如现在同步任务正处于结构迁移，还是全量迁移，还是进入了增量迁移。可看到同步的RPS，BPS。可看到同步任务是否失败或者有延迟。
- 支持无主键表迁移。
- 灵活选用VPC网络：由于阿里云DTS是第三方迁移工具，数据源RDS MySQL集群和目标ClickHouse集群可以属于不同的VPC网络。
- 支持ETL，库表列映射，附加列等功能。
- 支持数据过滤，可以只同步某个表指定条件的数据。 也可以选择只同步某些DML或者DDL。
- 可以随时暂停、重启同步任务。支持断点续传。删除任务后不会删除目标端库表。
- 支持常见DDL同步，例如create table, alter table, truncate table,drop table等等。
在使用阿里云DTS 进行MySQL->ClickHouse同步时，从准备工作，到创建任务，再到后期运维处理，新手可能会感到茫然和不知所措。为了帮助新手顺利过渡，本文将介绍使用阿里云DTS在进行MySQL到ClickHouse迁移时的最佳实践以及常见踩坑问题， 我们希望通过这篇文章，让您能无忧使用阿里云DTS进行数据迁移，享受ClickHouse带来的高效数据分析体验。
使用阿里云DTS进行MySQL->ClickHouse同步的必知必会
在使用阿里云DTS进行MySQL->ClickHouse同步时，从准备工作，到创建任务，再到后期运维处理，可能会遇到一系列问题，小编整理了最佳实践手册以及踩坑实录，希望这些建议能够帮助利用阿里云DTS进行无忧的数据迁移，享受ClickHouse带来的高效数据分析体验。
1. 最佳实践
1.1 为数据库账号增加权限
在处理ClickHouse链路工单时，经常会有一些数据库账号权限不足导致DTS任务失败的问题。DTS会在预检查中对配置任务时使用的数据库账号进行权限校验，如果校验不通过，则无法启动任务。在配置任务之前，需要给源端和目标端的数据库账号增加如下权限：
数据库 |
库表结构同步 |
全量同步 |
增量同步 |
MySQL |
SELECT权限 |
SELECT权限 |
待同步对象的SELECT权限 REPLICATION CLIENT、REPLICATION SLAVE、SHOW VIEW 建库建表的权限，以允许DTS创建库dts，用于记录同步期间的心跳数据 |
ClickHouse |
读写权限 |
||
1.2 尽量使用阿里云DTS控制台创建任务
DTS MySQL->ClickHouse链路 对dblist 有一定的要求，使用DTS控制台配置任务会得到标准化的dblist。如果用户因为业务原因需要使用DTS openapi配置任务，则在指定dblist 时需要遵循如下格式：
dbList={ "source_db_name": { "name": "target_db_name", "all": false, "state": "open", "Table": { "source_table_name": { "name": "target_table_name", "all": true, "primary_key": "id", "message_key": "id", "partition_key": "sipHash64(id)", "part_key": "id", "type": "partition" } }, "num": 1 } }
其中：
- primary_key是主健
- message_key 是排序健
- part_key 是分区键
- partition_key 是分布键, 也就是shardkey
1.3 尽量使用DTS结构迁移
ClickHouse的表结构对于数据写入的性能及稳定性有重要影响。在使用DTS进行MySQL到ClickHouse迁移时，尽可能使用DTS提供的结构迁移功能，将能有效避免一些DTS不支持的表结构导致的报错。
如果用户因为业务原因需要自建表结构， 需要满足DTS对表结构的如下要求：
- 如果是ClickHouse社区版，需要建一个本地表和一个分布式表。分布式表表名与dblist中目标端表名一致， 本地表表名为分布式表表名+_local。如果是ClickHouse企业版，只需建一个与dblist中目标端表名相同的表。
- 需要增加_sign和_version 两个附加列。其详细说明如下：
列名 |
数据类型 |
默认值 |
说明 |
_sign |
Int8 |
1 |
记录DML操作的类型：
|
_version |
UInt64 |
1 |
数据写入ClickHouse的时间戳 |
- DTS只支持RepladingMergeTree系列引擎，具体要求如下：
CK类型 |
集群类型 |
源端是否有主键 |
目标端CK引擎 |
社区版 |
单副本 |
有主键 |
MergeTree |
无主键 |
ReplacingMergeTree |
||
多副本 |
有主键 |
ReplicatedMergeTree |
|
无主键 |
ReplicatedReplacingMergeTree |
||
企业版 |
有主键 |
MergeTree（CK会自动加上Sharding+XXX) |
|
无主键 |
ReplacingMergeTree（CK会自动加上Sharding+XXX) |
||
- 列的数据类型映射，参考
https://help.aliyun.com/zh/dts/user-guide/data-type-mappings-for-schema-synchronization
1.4 选择合适的分区键
在配置MySQL->ClickHouse迁移任务时，用户可以指定一个列作为分区键，按该键来分隔数据。ClickHouse会为每一个键值创建一个新的文件目录。这通常是一个数据管理技巧，允许用户在表中逻辑分隔数据，例如按天。DROP PARTITION这样的操作允许快速删除数据子集。但是如果选择了不合适的分区键，可能会导致ClickHouse 创建的parts超过参数配置的限制（parts_to_throw_insert和max_parts_in_total），从而引发"Too many inactive parts (N). Parts cleaning are processing significantly slower than inserts"错误。
为了避免出现上述问题，DTS结构迁移做了如下优化：
- 如果用户没有指定分区键， 那么结构迁移建表时不会添加partition by xxx， 也即不进行分区。
- 如果用户指定了分区键，则使用用户指定的列作为分区键
- 如果用户指定的分区键源端类型为BIGINT, 则建表时添加partition by intDiv(XXX, 18014398509481984)
- 如果用户指定的分区键源端类型为TinyInt, SmallInt,MediumInt, 则建表时添加partition by intDiv(XXX, 4194304)
- 否则如果是时间类型(date,datetime和timestamp)，则建表时添加partition by toYYYMM(update_time)
- 如果是其他类型，则建表时不添加partition by 语句， 也即不进行分区。
用户如果不使用DTS结构迁移自己建表时，需要选择一个合理的分区键，以避免DTS数据写入时出现"Too many inactive parts" 问题。
1.5 选择合适的分片键
在数据写入的过程中，分布式表会依据分片键的规则，将数据分布到各个host节点的本地表中。分片键要求返回一个整型类型的取值，可以是一个具体的整形列字段。当分片键不为整型取值时，结构迁移在创建表时会报错。例如当使用String类型作为分片键时， 会报Sharding expression has type String, but should be one of integer type.
为了避免出现上述问题，dts结构迁移做了如下优化：
- 如果用户没有指定分片键， 使用rand()作为分片键。
- 如果用户 中指定了分片键，
- 如果分片键有多列，使用sipHash64(A，B, C)作为分片键。
- 如果分片键为单列，且为int, 则就使用该列作为分片键。
- 如果分片键为单列，且不为int, 则使用sipHash64(A)作为分片键。
2. 踩坑实录
2.1 数据有重复怎么办？
ClickHouse本身没有主键约束，也就是说即便多行数据相同的主键相同，ClickHouse还是允许他们正常写入。而DTS在如下两个场景可能会引入重复的数据：
- 在全量迁移时，DTS会将一个表中的数据分成若干切片并发地拉取和写入。当DTS在迁移一个切片的数据时，如果用户暂停任务，然后重启任务。DTS会将当前切片的数据重新迁移到目标端。这样就会造成这个切片的一部分数据有重复。
- 在增量迁移时，由于Mutations查询是一种比较重的操作，DTS采用了和MaterializeMySQL引擎一样的方案，即基于ReplacingMergeTree引擎的版本控制的更新/删除方式。更新数据时，会insert一条相同的数据,并将_sign置为1。删除数据时，会insert 一条相同的数据，并将_sign置为-1。ReplacingMergeTree在合并分区时会删除重复的数据，保留同一组重复数据中版本号_version取值最大的行。
基于以上原理，我们可以使用如下方法来去除重复数据：
- 执行optimize table xxx来强制ClickHouse进行分区合并，以去除重复数据。
- 查询数据时加上final， 如果希望过滤已经删除的数据，还需要加上_sign>0。例如: select * from table_name final where _sign>0;
2.2 datetime类型数据为何和源端不一致？
ClickHouse 的时间类型(Date, Date32, DateTime, DateTime64) 是基于Unix 时间戳的。云数据库ClickHouse的DateTime，DateTime64，Date32, Date数据类型的时间范围如下， 若RDS MySQL中的时间不在该范围内，会被转换到该范围内。因此ClickHouse中的时间可能会与MySQL的时间不一致。
ClickHouse数据类型 |
最小值 |
最大值 |
DateTime64 |
1925-01-01 08:00:00 |
2283-11-12 07:59:59 |
DateTime |
1970-01-01 08:00:00 |
2106-02-07 14:28:15 |
Date32 |
1925-01-01 |
2283-11-11 |
Date |
1970-01-01 |
2149-06-06 |
快来关注
- 首月购买MySQL->ClickHouse同步链路仅需99元，最多可省2421元 ，点击【前往】 立即选购！
- 数据传输服务（Data Transmission Service，简称DTS）支持关系型数据库、NoSQL、大数据（OLAP）等数据源，集数据迁移、订阅、实时同步、校验功能于一体，能够解决公共云、混合云场景下，远距离、秒级异步数据传输难题。其底层基础设施采用阿里双11异地多活架构，为数千下游应用提供实时数据流，已在线上稳定运行7年之久，是一款沉淀了丰富实践经验的可靠产品。点击了解更多DTS相关信息
- 欢迎加入钉群讨论交流：
- 点击【体验ClickHouse训练营】，多重好礼等你拿～
欢迎大家转发 “还在自行构建面向Clickhouse的数据集成链路？ 阿里云数据传输服务产品DTS告诉你里面坑不少！ #DTS避坑指南”。
