阿里云DTS踩坑经验分享系列|如何使用DTS进行MySQL->ClickHouse同步

本文涉及的产品
数据管理 DMS,安全协同 3个实例 3个月
推荐场景:
学生管理系统数据库
数据传输服务 DTS,数据迁移 small 3个月
推荐场景:
MySQL数据库上云
简介: 在使用阿里云DTS 进行MySQL->ClickHouse同步时,从准备工作,到创建任务,再到后期运维处理,新手可能会感到茫然和不知所措。为了帮助新手顺利过渡,本文将介绍使用阿里云DTS在进行MySQL到ClickHouse迁移时的最佳实践以及常见踩坑问题, 我们希望通过这篇文章,让您能无忧使用阿里云DTS进行数据迁移,享受ClickHouse带来的高效数据分析体验。

阿里云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会在预检查中对配置任务时使用的数据库账号进行权限校验,如果校验不通过,则无法启动任务。在配置任务之前,需要给源端和目标端的数据库账号增加如下权限:

数据库

库表结构同步

全量同步

增量同步

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操作的类型:

  • Insert: 值为1
  • Delete: 值为-1
  • Update: 值为1

_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


快来关注

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

image.png

  1. 点击【体验ClickHouse训练营,多重好礼等你拿~


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

相关文章
|
1月前
|
分布式计算 DataWorks 调度
DataWorks报错问题之dataworks同步clickhouse数据报错如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台,支持数据集成、数据开发、数据治理等功能;在本汇总中,我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答,以助用户在数据处理和分析工作中提高效率,降低难度。
|
1月前
|
存储 SQL NoSQL
数据传输DTS同步问题之同步失败如何解决
数据传输服务(DTS)是一项专注于数据迁移和同步的云服务,在使用过程中可能遇到多种问题,本合集精选常见的DTS数据传输问题及其答疑解惑,以助用户顺利实现数据流转。
|
2月前
|
存储 容灾 安全
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
23 3
|
2月前
|
监控 NoSQL 关系型数据库
在进行RDS(例如阿里云的RDS)数据迁移后,评估数据一致性
在进行RDS(例如阿里云的RDS)数据迁移后,评估数据一致性
126 3
|
29天前
|
SQL 关系型数据库 MySQL
阿里云MySQL数据库价格、购买、创建账号密码和连接数据库教程
阿里云数据库使用指南:购买MySQL、SQL Server等RDS实例,选择配置和地区,完成支付。创建数据库和账号,设置权限。通过DMS登录数据库,使用账号密码访问。同地域VPC内的ECS需将IP加入白名单以实现内网连接。参考链接提供详细步骤。
367 3
|
1月前
|
弹性计算 关系型数据库 MySQL
阿里云ECS使用docker搭建mysql服务
阿里云ECS使用docker搭建mysql服务
152 1
|
23天前
|
存储 关系型数据库 数据库
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
迎峰而上:汇联易依托阿里云RDS通用云盘,加速业务智能化升级
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
|
29天前
|
弹性计算 关系型数据库 MySQL
阿里云MySQL云数据库优惠价格、购买和使用教程分享!
阿里云数据库使用流程包括购买和管理。首先,选购支持MySQL、SQL Server、PostgreSQL等的RDS实例,如选择2核2GB的MySQL,设定地域和可用区。购买后,等待实例创建。接着,创建数据库和账号,设置DB名称、字符集及账号权限。最后,通过DMS登录数据库,填写账号和密码。若ECS在同一地域和VPC内,可内网连接,记得将ECS IP加入白名单。
431 2
|
29天前
|
SQL 关系型数据库 MySQL
阿里云mysql数据库价格购买和使用教程
阿里云数据库使用指南:购买MySQL、SQL Server等RDS实例,通过选择配置、地域和可用区完成购买。创建数据库和账号,分配权限。使用DMS登录数据库,进行管理操作。确保ECS与RDS在同一地域的VPC内,配置白名单实现内网连接。详细步骤见官方文档。
630 1
|
29天前
|
SQL 数据可视化 Apache
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移
阿里云数据库 SelectDB 内核 Doris 的 SQL 方言转换工具, Doris SQL Convertor 致力于提供高效、稳定的 SQL 迁移解决方案,满足用户多样化的业务需求。兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移。
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移