This document describes how to import data in MySQL into a ClickHouse cluster in the following two methods:
clickhouse-mysql-data-readertool 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.
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. 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 required
pypy/bin/pypy3 -m ensurepip.
pypy/bin/pip3 install mysql-replicationand
pypy/bin/pip3 install clickhouse-driver.
pypy/bin/pip3 install clickhouse-mysqland
yum install -y clickhouse-client.
yum install -y mysql-community-devel.
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-readerto generate the SQL statement for table creation.
TinyLogis used in this example). Run the table creation statement
clickhouse-client -m < create.sql.