阿里云DTS作为数据世界高速传输通道的建造者，每周为您分享一个避坑技巧，助力数据之旅更加快捷、便利、安全。

点击【立即体验MySQL->ClickHouse同步链路】

导读

在企业数字化转型的大趋势下，大规模业务数据的在线统计分析需求越来越强烈，传统的数据库往往难以满足高性能和实时分析的要求，随着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会在预检查中对配置任务时使用的数据库账号进行权限校验，如果校验不通过，则无法启动任务。在配置任务之前，需要给源端和目标端的数据库账号增加如下权限：

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 两个附加列。其详细说明如下：
  

• DTS只支持RepladingMergeTree系列引擎，具体要求如下：
  

• 列的数据类型映射，参考
  

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的时间不一致。

快来关注

1. 首月购买MySQL->ClickHouse同步链路仅需99元，最多可省2421元 ，点击【前往】 立即选购！
2. 数据传输服务（Data Transmission Service，简称DTS）支持关系型数据库、NoSQL、大数据（OLAP）等数据源，集数据迁移、订阅、实时同步、校验功能于一体，能够解决公共云、混合云场景下，远距离、秒级异步数据传输难题。其底层基础设施采用阿里双11异地多活架构，为数千下游应用提供实时数据流，已在线上稳定运行7年之久，是一款沉淀了丰富实践经验的可靠产品。点击了解更多DTS相关信息
   
3. 欢迎加入钉群讨论交流：

4. 点击【体验ClickHouse训练营】，多重好礼等你拿～

欢迎大家转发 “还在自行构建面向Clickhouse的数据集成链路？ 阿里云数据传输服务产品DTS告诉你里面坑不少！ #DTS避坑指南”。