This document describes how to import data in MySQL into a ClickHouse cluster in the following two methods:
clickhouse-mysql-data-reader
tool provided by Altinity.In the examples below, data is imported from the MySQL data table test.clickhouse_test
into the ClickHouse cluster. The table schema is as shown below:
ClickHouse's MySQL table engine allows you to perform SELECT
queries on data stored on remote MySQL servers. Based on this capability, you can use the CREATE ... SELECT * FROM
or INSERT INTO ... SELECT * FROM
statement to import data.
Directions:
You can combine steps 2 and 3 into one step, i.e., using CREATE TABLE AS SELECT * FROM
to achieve the same result.
ClickHouse supports MySQL external table engines. Is it necessary to import data into ClickHouse?
Yes. A MySQL external table engine does not store data itself; instead, data is stored in MySQL. In copy queries, especially when there are JOIN
statements, access to an external table is very slow and even impossible. This scheme has obvious flaws and does not support importing incremental data.
Altinity provides the clickhouse-mysql-data-reader tool for data import. This tool can export both existing and incremental data from MySQL.
As described on the official website, use of the pypy tool can significantly improve the data import performance of clickhouse-mysql-data-reader
.
Tool preparations
pypy
directoryclickhouse-mysql
. If you are performing the operations in a Tencent Cloud ClickHouse cluster, after completing the installation operations below, the tool will be integrated and can be used out of the box with no configuration requiredpip
: run pypy/bin/pypy3 -m ensurepip
.mysql-replication
and clickhouse-driver
: run pypy/bin/pip3 install mysql-replication
and pypy/bin/pip3 install clickhouse-driver
.clickhouse-mysql
: run pypy/bin/pip3 install clickhouse-mysql
and pypy/bin/clickhouse-mysql --install
.clickhouse-client
: run yum install -y clickhouse-client
.mysql-community-devel
: run yum install -y mysql-community-devel
.SUPER
and REPLICATION CLIENT
CREATE USER 'root'@'%' IDENTIFIED BY 'cloud';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SUPER ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
After completing the preparations, you can use the tool to import data from MySQL to the ClickHouse cluster in the following steps:
clickhouse-mysql-data-reader
to generate the SQL statement for table creation.TinyLog
is used in this example). Run the table creation statement clickhouse-client -m < create.sql
.mempool flush
.
Was this page helpful?