三、案例 - MySQL数据迁移至ClickHouse

已于 2024-02-16 15:50:52 修改
阅读量1.9k 收藏 29
点赞数 11
分类专栏： # ClickHouse 文章标签： mysql clickhouse
于 2024-02-12 10:03:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37344058/article/details/136083805
版权

MySQL数据迁移至ClickHouse

一、生成测试数据表和数据

1.在MySQL创建数据表和数据

# 1.创建数据库
CREATE DATABASE test charset=utf8mb4;
USE test;
# 2.创建表
CREATE TABLE User (
    userId INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    registrationDate DATETIME NOT NULL,
    lastLogin DATETIME,
    createTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP -- 更新时间
);
# 3.插入测试数据
INSERT INTO test.`User` (username, email, registrationDate, lastLogin) VALUES
('JohnDoe01', 'john.doe01@example.com', '2023-02-01 08:00:00', '2023-02-02 09:00:00'),
('JaneDoe02', 'jane.doe02@example.com', '2023-02-02 10:00:00', '2023-02-03 11:00:00'),
('MikeSmith03', 'mike.smith03@example.com', '2023-02-03 12:00:00', '2023-02-04 13:00:00'),
('LucyBrown04', 'lucy.brown04@example.com', '2023-02-04 14:00:00', '2023-02-05 15:00:00'),
('DavidWilson05', 'david.wilson05@example.com', '2023-02-05 16:00:00', '2023-02-06 17:00:00'),
('LindaTaylor06', 'linda.taylor06@example.com', '2023-02-06 18:00:00', '2023-02-07 19:00:00'),
('RobertJones07', 'robert.jones07@example.com', '2023-02-07 20:00:00', '2023-02-08 21:00:00'),
('PatriciaWhite08', 'patricia.white08@example.com', '2023-02-08 22:00:00', '2023-02-09 23:00:00'),
('MichaelHarris09', 'michael.harris09@example.com', '2023-02-09 08:30:00', '2023-02-10 09:30:00'),
('SarahMartin10', 'sarah.martin10@example.com', '2023-02-10 10:30:00', '2023-02-11 11:30:00');

# 4.批量插入100w数据
# 4.1 创建存储过程
DELIMITER $$
CREATE PROCEDURE InsertUsers()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO User (username, email, registrationDate, lastLogin) VALUES 
    (CONCAT('User', LPAD(i, 7, '0')), CONCAT('user', LPAD(i, 7, '0'), '@example.com'), NOW(), NOW());
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
# 4.2 调用存储过程，生成100w用户数据
CALL InsertUsers();

2.在ClickHouse创建数据表

CREATE TABLE User (
    userId Int32,
    username String,
    email String,
    registrationDate DateTime,
    lastLogin Nullable(DateTime),
    createTime DateTime, -- 创建时间
    updateTime DateTime -- 更新时间
) ENGINE = MergeTree()
ORDER BY userId;

二、生成模板文件

  • 当前安装DataX的目录为：/data/datax
# 1.进入datax的工具目录
cd /data/datax/bin/
# 2.生成模板
python datax.py -r mysqlreader -w clickhousewriter > ../job/mysql_to_clickhouse.json

1.模板文件内容

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": [],
                        "connection": [
                            {
                                "jdbcUrl": [],
                                "table": []
                            }
                        ],
                        "password": "",
                        "username": "",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "clickhousewriter",
                    "parameter": {
                        "batchByteSize": 134217728,
                        "batchSize": 65536,
                        "column": [
                            "col1",
                            "col2",
                            "col3"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:clickhouse://<host>:<port>[/<database>]",
                                "table": [
                                    "table1",
                                    "table2"
                                ]
                            }
                        ],
                        "dryRun": false,
                        "password": "password",
                        "postSql": [],
                        "preSql": [],
                        "username": "username",
                        "writeMode": "insert"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}

2.模板文件参数详解

2.1 全局设置

  • job: 定义了整个数据迁移作业的配置。
    • content: 包含了一个或多个数据同步任务的列表。

2.2 数据读取（Reader）

  • reader: 定义了数据来源的相关配置。
    • name: 使用的读取插件名称，这里是mysqlreader，表示从MySQL数据库读取数据。
    • parameter: 读取数据时的参数配置。
      • column: 需要读取的列名列表。这里指定了从MySQL表中读取userId, username, email, registrationDate, lastLogin这几个字段。
      • connection: 数据库连接信息。
        • jdbcUrl: 数据库的JDBC连接URL。需要替换<your_mysql_host>, <your_mysql_port>, <your_mysql_database>为实际的MySQL服务器地址、端口和数据库名。
        • table: 指定要读取数据的表名列表，在这个例子中是User表。
      • password: 用于连接MySQL数据库的密码。
      • username: 用于连接MySQL数据库的用户名。
      • where: 可以指定一个WHERE条件来过滤读取的数据，这里留空表示不过滤，读取所有数据。

2.3 数据写入（Writer）

  • writer: 定义了数据目的地的相关配置。
    • name: 使用的写入插件名称，这里是clickhousewriter，表示数据将被写入到ClickHouse数据库。
    • parameter: 写入数据时的参数配置。
      • batchByteSize: 指定每个批次写入的最大字节数。这里设置为134217728，约等于128MB。
      • batchSize: 指定每个批次写入的记录数。这里设置为65536。
      • column: 指定写入到目标表的列名。应与读取的列对应。
      • connection: 目标数据库的连接信息。
        • jdbcUrl: ClickHouse的JDBC连接URL。需要替换, , [/]为实际的ClickHouse服务器地址、端口和数据库名。
        • table: 指定要写入数据的表名，在这个例子中是User表。
      • dryRun: 是否进行干运行（不实际写入数据）。这里设置为false，表示将实际执行数据写入。
      • password: 用于连接ClickHouse数据库的密码。
      • postSql: 在数据写入完成后执行的SQL语句列表，这里留空。
      • preSql: 在数据写入前执行的SQL语句列表，这里留空。
      • username: 用于连接ClickHouse数据库的用户名。
      • writeMode: 写入模式，这里设置为insert，表示通过INSERT语句进行数据写入。

2.4 性能设置

  • setting: 定义了作业的全局设置。
    • speed: 控制数据同步的速度。
      • channel: 指定并发通道的数量，这里设置为4，意味着数据迁移任务将并行执行，使用4个并发通道。

三、案例

1.全量数据迁移

1.1 配置迁移模板

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": [
                            "userId",
                            "username",
                            "email",
                            "registrationDate",
                            "lastLogin",
                            "createTime",
                            "updateTime"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://192.168.86.128:3306/test?useUnicode=true&characterEncoding=utf-8"],
                                "table": [
                                    "User"
                                ]
                            }
                        ],
                        "password": "xxx",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "clickhousewriter",
                    "parameter": {
                        "batchByteSize": 134217728,
                        "batchSize": 65536,
                        "column": [
                            "userId",
                            "username",
                            "email",
                            "registrationDate",
                            "lastLogin",
                            "createTime",
                            "updateTime"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:clickhouse://192.168.86.128:8123/default",
                                "table": [
                                    "User"
                                ]
                            }
                        ],
                        "dryRun": false,
                        "password": "qwe123",
                        "postSql": [],
                        "preSql": [],
                        "username": "root",
                        "writeMode": "insert"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": 4
            }
        }
    }
}

1.2.运行迁移命令

python /data/datax/bin/datax.py /data/datax/job/mysql_to_clickhouse.json

在这里插入图片描述

2.增量数据迁移

  • 主要差别在于，需要有一个createTime字段，代表源数据的创建时间，那么更新的时候，只迁移过滤这个时间段的数据，达到增量数据迁移

2.1 配置迁移模板

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": [
                            "userId",
                            "username",
                            "email",
                            "registrationDate",
                            "lastLogin",
                            "createTime",
                            "updateTime"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://192.168.86.128:3306/test?useUnicode=true&characterEncoding=utf-8"],
                                "table": [
                                    "User"
                                ]
                            }
                        ],
                        "password": "qwe123",
                        "username": "root",
                        "where": "createTime>='${startDatetime} 00:00:00' and createTime<='${endDatetime} 23:59:59'"
                    }
                },
                "writer": {
                    "name": "clickhousewriter",
                    "parameter": {
                        "batchByteSize": 134217728,
                        "batchSize": 65536,
                        "column": [
                            "userId",
                            "username",
                            "email",
                            "registrationDate",
                            "lastLogin",
                            "createTime",
                            "updateTime"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:clickhouse://192.168.86.128:8123/default",
                                "table": [
                                    "User"
                                ]
                            }
                        ],
                        "dryRun": false,
                        "password": "qwe123",
                        "postSql": [],
                        "preSql": [],
                        "username": "root",
                        "writeMode": "insert"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": 4
            }
        }
    }
}

2.2 运行迁移命令

  • 注意：指定参数的话，参数名称面前需要加：-D
python /data/datax/bin/datax.py /data/datax/job/mysql_to_clickhouse.json -p "-DstartDatetime=2024-02-09 -DendDatetime=2024-02-10"
青啊青斯
关注
专栏目录
【SDC】史上最全-mysql迁移到clickhouse的5种办法
菜鸟叔叔的博客
06-14 878
转载原文地址：https://anjia0532.github.io/2019/07/17/mysql-to-clickhouse/1create table engin mysql 官方文档: 注意，实际数据存储在远端mysql数据库中，可以理解成外表。可以通过在mysql增删数据进行验证。2insert into select from 可以自定义列类型，列数，使用clickhouse函数对数据进行处理，比如 3create table as select from 网友文章: 不支持自定义列，参考资料
【微服务】springboot3 集成 Flink CDC 1.17 实现mysql数据同步
最新发布
congge_study的博客
10-13 7778
springboot3 集成 Flink CDC 1.17 实现mysql数据同步
【沃趣科技】初探MySQL迁移到ClickHouse
woqutechteam的博客
10-28 950
MySQL to Clickhouse，目前有ClickHouse原生提供的同步工具、命令行同步工具和界面指引型同步工具。原生的工具暂时不能用在生产环境，命令行工具使用起来比较繁琐，界面型相对简单易用。
探索数据的新高度：Metabase ClickHouse 驱动器
gitblog_00899的博客
09-13 1061
探索数据的新高度：Metabase ClickHouse 驱动器 metabase-clickhouse-driver ClickHouse database driver for the Metabase business intelligence front-end ...
MySQL迁移到ClickHouse
云原生智能数据管理平台
08-29 1474
对于 ClickHouse 的特性，相信也无谓过多介绍，各位看官都懂，对于有在线数据分析需求的企业来说，MySQL 在 OLAP 上的处理能力明显是不足的（人家本来也不是干这个的），因此搭配 ClickHouse 使用可以完美弥补 MySQL 这个短板，企业完全能够同时享受高效的事务处理和快速的数据分析，鱼和熊掌亦可兼得也。同时，当出现数据不一致时，能够提供一键修复能力。当任务的延迟为 0，或接近 0 的时候，代表两端的数据是一致的，此时就可以通过 ClickHouse 进行数据分析了。
【ClickHouse】从Mysql迁移到ClickHouse大全
Zsigner的博客
01-06 5060
从关系型的数据库(Mysql)升级到列式管理的联机分析型数据库(ClickHouse)，这不亚于是小米加步枪升级为加特林机关枪的性能提升了，查询能力等确实是大大的提升了，这出现了一个问题我们之前存储在Mysql里的历史数据怎么往ClickHouse里面迁移呢，总不能不要吧那对业务来说他们可是不关心你底层数据怎么存储的，他们只希望数据准确，齐全，那下面我们就来说说都有哪些方式可以实现补全缺失的历史数据 环境：ClickHouse：21.8.12.1,Python:3.6.8
mysql to clickhouse
weixin_41544886的博客
07-03 82
从MySQL迁移数据到ClickHouse 背景介绍 MySQL是一种常用的关系型数据库，而ClickHouse是一种用于分析的列式数据库。当我们需要将MySQL中的数据迁移至ClickHouse进行进一步的数据分析时，就需要进行数据迁移操作。本文将介绍如何将MySQL中的数据迁移到ClickHouse中。 数据迁移流程 ...
迁移到ClickHouse
06-20
迁移到ClickHouse ClickHouse是一种高性能的列式数据库，专门为大规模数据仓库和OLAP（Online Analytical Processing）应用而设计。ClickHouse的出现，解决了传统数据库在大规模数据处理和分析方面的一些瓶颈问题。...
2-8+云数据库ClickHouse分析业务最佳实践.pdf
03-18
对于MySQL等其他数据源与ClickHouse的集成，可以创建相应的数据同步机制，如使用binlog或者ETL工具，将数据实时或定期导入ClickHouse，以实现实时分析。同时，确保数据格式的一致性，以便于在不同系统间无缝迁移。 ...
十分钟掌握 Flink CDC，实现Mysql数据增量备份到Clickhouse [纯干货，建议收藏]
2401_83945851的博客
04-03 687
总而言之，面试官问来问去，问的那些Redis知识点也就这么多吧，复习的不够到位，知识点掌握不够熟练，所以面试才会卡壳。将这些Redis面试知识解析以及我整理的一些学习笔记分享出来给大家参考学习还有更多学习笔记面试资料也分享如下：，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！**因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
浅析mysql迁移到clickhouse的5种方法
01-21
数据迁移需要从mysql导入clickhouse, 总结方案如下，包括clickhouse自身支持的三种方式，第三方工具两种。 create table engin mysql CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], ... INDEX inde
ClickHouse技术应用实践深度剖析与案例分析
报告可能探讨如何将MySQL或MongoDB等其他数据库的数据同步到ClickHouse中，以及如何在这些系统之间进行数据迁移和集成。 8. ClickHouse在苏宁用户画像场景的实践（ClickHouse在苏宁用户画像系统中的应用实践） 文档...
MySQL 到 ClickHouse 实时数据同步实操分享，Python开发者值得深入思考的几个问题
2401_84103675的博客
04-04 1876
4.在打开的连接信息配置页面依次输入需要的配置信息，配置完成后测试连接保存即可。第三步：选择同步模式-全量/增量/全+增进入Tapdata Cloud 操作后台任务管理页面，点击添加任务按钮进入任务设置流程根据刚才建好的连接，选定源端与目标端。根据数据需求，选择需要同步的库、表，如果你对表名有修改需要，可以通过页面中的表名批量修改功能对目标端的表名进行批量设置.在以上选项设置完毕后，下一步选择同步类型，平台提供全量同步、增量同步、全量+增量同步，设定写入模式和读取数量。
数据迁移实践 | MySQL到ClickHouse，HTAP黄金搭档
04-23 767
DBMotion是在线数据迁移SaaS服务，也可以docker run一键式本地化运行。对于MySQL迁移同步到ClickHouse，提供了结构迁移/全量初始化/增量同步/数据校验功能支持，并且为实时数据同步提供了精准一致性查询视图。里面一些功能点的设计，可以分享给大家，供参考。
数据库迁徙，将数据从mysql迁徙到clickhouse内
qq_45699223的博客
07-22 840
将MySQL数据迁徙到clickhouse内操作步骤： #clickhouse-client :create database dw; :use dw; –导入数据： CREATE TABLE Orders ENGINE = MergeTree ORDER BY OrderID AS SELECT * FROM mysql(‘10.42.134.136:4000’, ‘dw’, ‘Orders’, ‘root’, ‘xyz’); CREATE TABLE Orders ENGINE = MergeTree
巧妙利用表函数将MySQL数据导入到ClickHouse
白石的专栏
09-28 1449
巧妙利用表函数将MySQL数据导入到ClickHouse
MySQL迁移到ClickHouse方案
YunWisdom
03-11 2137
MySQL迁移到ClickHouse方案 这篇文章主要介绍了mysql迁移到clickhouse的几种方法，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下。 数据迁移需要从mysql导入clickhouse, 总结方案如下，包括clickhouse自身支持的方式及第三方工具迁移方案。 ClickHouse同步建表 1 2 3 4 5 6 7 8 9 create ta..
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值