将 mysql 数据迁移到 clickhouse (最新版)

一、前驱知识

已经在mysql中插入了海量的数据了，这个时候mysql 承载不了这么大的数据，并且数据只需要查询，修改和删除非常少，并且不需要支持事务，这个时候需要换一个底层存储，这里选用的是 clickhouse 来进行存储。

演示方便这里使用的数据量很小，其实很大的话也很快。

本文迁移示例表

• mysql安装在docker中,迁移的表结构

CREATE TABLE `change_handler1` (
  `client_id` int NOT NULL COMMENT '网关id',
  `equipment` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备编码',
  `point_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点id',
  `value_last` decimal(18,6) DEFAULT NULL COMMENT '上值',
  `value_latest` decimal(18,6) DEFAULT NULL COMMENT '下值',
  `value_change` decimal(18,6) DEFAULT NULL COMMENT '电量',
  `window_end_time_last` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最新封窗时间',
  `window_end_time_latest` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上个封窗时间',
  `window_end_time_msg_time` datetime DEFAULT NULL COMMENT '窗口网关发送时间',
  `window_size` bigint DEFAULT NULL COMMENT '窗口时间长度秒',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `electricity_value` decimal(18,6) DEFAULT NULL COMMENT '电费',
  `price_type` bigint DEFAULT NULL COMMENT '峰谷平尖类型',
  PRIMARY KEY (`client_id`,`equipment`,`point_id`,`window_end_time_last`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='电量清洗';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• clickhouse 安装在docker中，映射后的表结构

CREATE TABLE history_db.change_handler(
   `client_id` int  COMMENT '网关id',
  `equipment` String  COMMENT '设备编码',
  `point_id`  String COMMENT '测点id',
  `value_last` Decimal(18,6)  COMMENT '上值',
  `value_latest` Decimal(18,6)  COMMENT '下值',
  `value_change` Decimal(18,6)  COMMENT '电量',
  `window_end_time_last` DateTime  COMMENT '最新封窗时间',
  `window_end_time_latest` DateTime  COMMENT '上个封窗时间',
  `window_end_time_msg_time` DateTime  COMMENT '窗口网关发送时间',
  `window_size` Int32  COMMENT '窗口时间长度秒',
  `create_time` DateTime  COMMENT '创建时间',
  `update_time` DateTime  COMMENT '更新时间',
  `electricity_value` Decimal(18,6)  COMMENT '电费',
  `price_type` Int32  COMMENT '峰谷平尖类型'
   ) ENGINE = MergeTree()
   order by (client_id,equipment,point_id,window_end_time_last)
   PRIMARY KEY (client_id,equipment,point_id,window_end_time_last);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

二、数据迁移

我看了网上的几种方式,几种都是在clickhouse 这边来做的，我觉得没必要，因为表非常大的话，网络开销是非常大的，所以我这里采用的是从mysql 那里将数据迁移出来，然后导入clickhouse 用 clickhouse-client 来导入

2.1 mysql 数据导出

首先进到mysql的机器里面执行命令

select change_handler1.* into outfile '/var/lib/mysql-files/change_handler1.txt' from change_handler1;
# 如果分表 有多个表的数据结构都是相同的话  那可以多个表导成一个文件
SELECT *
INTO OUTFILE '/var/lib/mysql-files/change_handler.txt'
FROM (
    SELECT * FROM change_handler1
    UNION ALL
    SELECT * FROM change_handler2
    -- 添加更多的表
) AS combined_tables;
1
2
3
4
5
6
7
8
9
10

ps: 注意这里的导出路径，如果不是这个(/var/lib/mysql-files)路径下会报错，最好写这个路径,反正只是一个临时存储路径

然后我们将导出的文件拷贝到clickhouse的机器上，我这里是容器，直接用 docker cp 拷贝的，如果不是容器，用FTP之类的拷贝过去

拷贝到 clickhouse 容器中

2.2 clickhouse 导入

先进入clickhouse容器，这里使用的是 clickhouse-client 来导入的，如果没有的话可以直接安装

sudo apt-get install clickhouse-client
1

执行导入命令，如果设置了密码的话用 --password 来指定

cat change_handler1.txt | clickhouse-client -u 用户 --password 密码 --query="INSERT INTO history_db.change_handler FORMAT TabSeparated"
1

FORMAT 后面跟的是格式，如果导入的是CSV格式的话 写CSV

2.3 结果

执行完成之后就可以连接clickhouse 看数据了，数据已经过去了