在使用阿里云数据传输服务DTS进行MySQL->ClickHouse数据迁移时，从准备工作，到创建任务，再到后期运维处理，新手可能会感到茫然和不知所措。为了帮助新手顺利过渡，本文将介绍使用阿里云DTS在进行MySQL到ClickHouse迁移时的最佳实践以及常见踩坑问题，我们希望通过这篇文章，让您能无忧使用阿里云DTS进行数据迁移，享受ClickHouse带来的高效数据分析体验。点击文末【阅读原文】即可体验DTS MySQL->ClickHouse迁移链路。
01. 导读
在企业数字化转型的大趋势下，大规模业务数据的在线统计分析需求越来越强烈，传统的数据库往往难以满足高性能和实时分析的要求，随着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的数据迁移。
02. 使用阿里云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等等。
03. 使用阿里云DTS进行MySQL->ClickHouse迁移的必知必会
在使用阿里云DTS进行MySQL->ClickHouse迁移时，从准备工作，到创建任务，再到后期运维处理，可能会遇到一系列问题，下文将呈现最佳实践手册以及踩坑实录，希望这些建议能够帮助利用阿里云DTS进行无忧的数据迁移，享受ClickHouse带来的高效数据分析体验。
3.1. 最佳实践
3.1.1 为数据库账号增加权限
在处理ClickHouse链路工单时，经常会有一些数据库账号权限不足导致DTS任务失败的问题。DTS会在预检查中对配置任务时使用的数据库账号进行权限校验，如果校验不通过，则无法启动任务。在配置任务之前，需要给源端和目标端的数据库账号增加如下权限：
3.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
3.1.3 尽量使用DTS结构迁移
ClickHouse的表结构对于数据写入的性能及稳定性有重要影响。在使用DTS进行MySQL到ClickHouse迁移时，尽可能使用DTS提供的结构迁移功能，将能有效避免一些DTS不支持的表结构导致的报错。
如果用户因为业务原因需要自建表结构， 需要满足DTS对表结构的如下要求：
▶︎ 如果是ClickHouse社区版，需要建一个本地表和一个分布式表。分布式表表名与dblist中目标端表名一致，本地表表名为分布式表表名+_local。如果是ClickHouse企业版，只需建一个与dblist中目标端表名相同的表。
▶︎ 需要增加_sign和_version两个附加列。其详细说明如下：
▶︎ DTS只支持RepladingMergeTree系列引擎，具体要求如下：
▶︎ 列的数据类型映射，参考文档《结构初始化涉及的数据类型映射关系》[1]
3.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结构迁移做了如下优化：1. 如果用户没有指定分区键，那么结构迁移建表时不会添加partition by xxx，也即不进行分区。
2. 如果用户指定了分区键，则使用用户指定的列作为分区键。a）如果用户指定的分区键源端类型为BIGINT，则建表时添加partition by intDiv(XXX，18014398509481984)；b）如果用户指定的分区键源端类型为TinyInt，SmallInt，MediumInt，则建表时添加partition by intDiv(XXX, 4194304)；c）否则如果是时间类型(date,datetime和timestamp)，则建表时添加partition by toYYYMM(update_time)；d）如果是其他类型，则建表时不添加partition by语句， 也即不进行分区。用户如果不使用DTS结构迁移自己建表时，需要选择一个合理的分区键，以避免DTS数据写入时出现"Too many inactive parts" 问题。
3.1.5 选择合适的分片键
在数据写入的过程中，分布式表会依据分片键的规则，将数据分布到各个host节点的本地表中。分片键要求返回一个整型类型的取值，可以是一个具体的整形列字段。当分片键不为整型取值时，结构迁移在创建表时会报错。例如当使用String类型作为分片键时， 会报Sharding expression has type String, but should be one of integer type.
为了避免出现上述问题，DTS结构迁移做了如下优化：
▶︎ 如果用户没有指定分片键， 使用rand()作为分片键。
▶︎ 如果用户中指定了分片键。
a）如果分片键有多列，使用sipHash64(A，B，C)作为分片键。
b）如果分片键为单列，且为int，则就使用该列作为分片键。
c）如果分片键为单列，且不为int，则使用sipHash64(A)作为分片键。
3.2 踩坑实录
3.2.1 数据有重复怎么办？
ClickHouse本身没有主键约束，也就是说即便多行数据相同的主键相同，ClickHouse还是允许他们正常写入。而DTS在如下两个场景可能会引入重复的数据：全量迁移DTS会将一个表中的数据分成若干切片并发地拉取和写入。当DTS在迁移一个切片的数据时，如果用户暂停任务，然后重启任务。DTS会将当前切片的数据重新迁移到目标端。这样就会造成这个切片的一部分数据有重复。
增量迁移由于Mutations查询是一种比较重的操作，DTS采用了和MaterializeMySQL引擎一样的方案，即基于ReplacingMergeTree引擎的版本控制的更新/删除方式。更新数据时，会insert一条相同的数据,并将_sign置为1。删除数据时，会insert一条相同的数据，并将_sign置为-1。ReplacingMergeTree在合并分区时会删除重复的数据，保留同一组重复数据中版本号_version取值最大的行。
基于以上原理，我们可以使用如下方法来去除重复数据：
● 执行optimize table table_name final来强制ClickHouse进行分区合并，以去除重复数据。
● 查询数据时加上final，如果希望过滤已经删除的数据，还需要加上_sign>0。例如: select * from table_name final where _sign>0。
3.2.2 Datetime类型数据为何和源端不一致？
ClickHouse的时间类型(Date, Date32, DateTime, DateTime64)是基于Unix时间戳的。云数据库ClickHouse的DateTime，DateTime64，Date32，Date数据类型的时间范围如下，若RDS MySQL中的时间不在该范围内，会被转换到该范围内。因此ClickHouse中的时间可能会与MySQL的时间不一致。
了解更多
1. 首月购买MySQL->ClickHouse迁移链路仅需99元，最多可省2421元，快来选购吧！
🔗 https://sourl.cn/jpt4sg
2. 数据传输服务(Data Transmission Service，简称DTS)支持关系型数据库、NoSQL、大数据（OLAP）等数据源，集数据迁移、订阅、实时同步、校验功能于一体，能够解决公共云、混合云场景下，远距离、秒级异步数据传输难题。其底层基础设施采用阿里双11异地多活架构，为数千下游应用提供实时数据流，已在线上稳定运行7年之久，是一款沉淀了丰富实践经验的可靠产品。了解DTS更多内容。[2]
欢迎钉钉扫码入群交流
钉钉群号：68325004196
3. 感兴趣的小伙伴还可参加「ClickHouse数据库训练营」，多重好礼等你拿～🎁
🔗 https://edu.aliyun.com/trainingcamp/354200?spm=a2c6h.12873639.article-detail.12.13f838c9B2oGHQ
[1] 结构初始化涉及的数据类型映射关系
https://help.aliyun.com/zh/dts/user-guide/data-type-mappings-for-schema-synchronization?spm=a2c6h.12873639.article-detail.9.13f838c9Tbyycs[2] 了解DTS更多内容
https://help.aliyun.com/zh/dts/product-overview/what-is-dts?spm=a2c6h.12873639.article-detail.11.6c9038c9fpAcd8
推荐阅读
点击即可体验 DTS MySQL->ClickHouse迁移链路
