基于ClickHouse的版本化数据迁移方法

Versioned data migration method based on ClickHouse

针对ClickHouse进行数据迁移过程中存在的业务开发周期较长、数据空窗和集群性能下降的问题,提出了一种版本化的数据迁移方法。首先采用参数化配置模式提升开发人员在不同业务场景下的开发效率;其次,利用ClickHouse中的原生ATTACH方法在源数据表和目标表之间构建一张版本表,保证数据迁移过程对用户无感知;接着,通过数据预处理以及对集群状态的实时监控,选择负载最小的副本方法来减少集群负担;此外,还加入验数逻辑和分片级的数据回滚功能来保证数据准确性。在广泛使用的业务生产场景中进行亿级数据的迁移测试对比,结果表明,该方法优于市面上最先进的技术,在硬件设备相同的情况下数据迁移时间缩短90%以上。

In order to solve the problems of long business development cycle,data empty window and cluster performance degradation in ClickHouse’s data migration process,a versioned data migration method was proposed.Firstly,the parameterized configuration mode was adopted to improve the development efficiency of developers under different business scenarios.Secondly,the native ATTACH method in ClickHouse was used to build a version table between the source table and the target table to ensure that downstream users were unaware of the data migration process.Thirdly,the cluster burden was reduced through preprocessing the data and selecting the least data load copy with monitoring system.In addition,verification logic and shard-level data rollback function were added to ensure data accuracy.In widely used business production scenarios,the migration test of billion-level data was compared.The results show that the proposed method was superior to the most advanced technology on the market,and the data migration time was reduced by more than 90%under the same hardware equipment.