ClickHouse 导入数据实战：MySQL篇

2020-08-28 15:32:48 浏览数 (2)

1. 概述

在生产环境中，经常遇到将数据库中的数据写入ClickHouse集群中。本文介绍2种将MySQL数据库中的数据导入到ClickHouse集群的方案。

其一，利用ClickHouse支持MySQL外表的特性来实现；其二，使用Altinity提供的clickhouse-mysql-data-reader 工具来实现数据导入。

本文示例中，将MySQL数据表test.clickhouse_test中的数据导入到ClickHouse集群中，该表的Schema如下：

image.pngimage.png

2. 简易方案：基于MySQL表引擎来实现数据导入

ClickHouse 的MySQL表引擎可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询。基于这样能力，利用

"CREATE ... SELECT FROM"或者" INSERT INTO ... SELECT FROM"语句即可完成数据导入。

具体步骤：

  • 步骤1：在ClickHouse中创建MySQL表引擎
image.pngimage.png
  • 步骤2：建立ClickHouse 表
image.pngimage.png
  • 步骤3：将步骤1中的外表中数据，导入到ClickHouse表中
image.pngimage.png

还可以将步骤2/3合并成一个步骤，即采用CREATE TABLE AS SELECT * FROM 方式来达到同样效果。

讨论

    1. 有人就要问了，既然ClickHouse支持MySQL外表引擎，还有必要将数据导入到ClickHouse中吗？ 实际上还是非常有必要的。MySQL外表引擎，本身不存储数据，数据存储在MySQL中。在复制查询中，特别是有JOIN的情况下，访问外表是相当慢的，甚至不可能完成。
    1. 该方案有明显缺陷，无法增量导入数据。

3. 推荐方案：基于Altinity的工具实现数据导入

Altinity提供了一个工具clickhouse-mysql-data-reader来实现数据导入。该工具可以实现MySQL的存量数据导出，和增量数据的导出。

按照官网推荐，使用pypy工具能够显著提升clickhouse-mysql-data-reader导入数据的性能。

工具准备

  • 步骤1：下载pypy, pypy3.6-7.2.0 , 解压为pypy目录下。
  • 步骤2：安装clickhouse-mysql
    • 安装pip: 执行pypy/bin/pypy3 -m ensurepip
    • 安装mysql-replication,clickhouse-driver, 执行pypy/bin/pip3 install mysql-replication和pypy/bin/pip3 install clickhouse-driver
    • 安装clickhouse-mysql并初始化，执行pypy/bin/pip3 install clickhouse-mysql, 执行pypy/bin/clickhouse-mysql --install
  • 安装clickhouse-client，执行yum install -y clickhouse-client
  • 安装mysql-community-devel, 执行yum install -y mysql-community-devel

如果是在腾讯云ClickHouse集群，上述工具已经集成，开箱即用，无需配置。

需要注意的是，如果是自行安装，请主要pymsql版本，需要安装0.9.3

准备工作完成后，即可使用该工具完成数据从MySQL导入到ClickHouse集群中。

本文以导入MySQL中clickhouse_mysql.message表至ClickHouse为例。

MySQL中表clickhouse_mysql.message Schema如下：

ClickHouse中表clickhouse_msyql.message Schema如下：

具体步骤如下：

  • 步骤1: 在ClickHouse中创建表

```

create database clickhouse_mysql;

create table message (id Int64, content String) engine=MergeTree() order by tuple();

```

  • 步骤2: 导入存量数据

```

clickhouse-mysql --src-host=172.30.0.44

--src-user=root

--src-password=cloud

--migrate-table

--src-tables=clickhouse_mysql.message --dst-host=172.30.0.39

--dst-port=9000

--dst-user=default --dst-table=message

```

步骤3： 导入增量数据

步骤3.1 创建导入数据账号以及配置权限。为了完成数据导入，所创建的用户至少需要```SELECT, REPLICATION SLAVE```权限。

假设我们创建用户 ```reader```：

```

CREATE USER 'reader'@'%' IDENTIFIED BY 'cloud';

GRANT SELECT, REPLICATION SLAVE, ON *.* TO 'reader'@'172.30.0.39';

```

步骤3.2，制作binlog位置标记文件。在mysql 中执行``` show master status```

例如输出如下：

执行 ```echo "mysql-bin.000003:326892" > /root/bin.pos```

步骤3.3，使用clickhouse-mysql工具完成增量数据导入，执行

```

clickhouse-mysql

--src-server-id=1

--src-resume

--src-host=172.30.0.44

--src-user=reader

--src-password=cloud

--binlog-position-file=/root/bin.pos

--src-wait

--nice-pause=1

--log-level=info

--src-tables=clickhouse_mysql.message

--dst-host=172.30.0.39

--dst-port=9000

--dst-user=default

--dst-table=message

--pump-data

```

其中, 参数含义如下：

src-host: MySQL数据库IP

src-user: MySQL数据库用户名

src-password:MySQL数据库密码

create-table-sql-template: 生产ClickHouse的建表脚本

with-create-database: 建表脚本中增加创建数据库语句

src-tables: 源表（MySQL表）

mempool-max-flush-interval mempool flush 的时间周期

src-server-id: 源MySQL 是否为master节点

src-resume: 断点续传

src-wait: 等待数据

nice-pause: 如果没有数据，睡眠的时间间隔

结束

本文介绍了MySQL数据库中数据导入ClickHouse集群的步骤。

**注意**

需要注意的是，本次实验环境使用的ClickHouse版本为19.16.10.44，其中select * from mysql() 无法正确工作，是已知issue.

官方建议使用MySQL Engine来替代，也就是本文中的简易方案中提到的。

更多的ClickHouse技术交流问题，请留言，拉您进群。

云数据仓库ClickHouse

0 人点赞