Transferring data from MySQL to ClickHouse

ClickHouse supports direct connection to MySQL servers, allowing you to quickly transfer data from MySQL to ClickHouse without any unnecessary intermediate steps.

Access settings

First, you need to allow the ClickHouse user to connect to MySQL servers. The corresponding privilege is configured at the user level and applies to all databases within the MySQL server. To configure the privilege, set the flag in the Users section of the ClickHouse control panel. "MySQL" in the user settings. If the privilege is not configured, the server will return an error. "Not enough privileges. To execute this query, it's necessary to have the grant READ ON MYSQL."

Retrieving data from MySQL

To read data from a MySQL table, use the table function. mysql. Request example:

SELECT *
FROM mysql('host:port', 'database', 'table_name', 'login', 'password')

The query returns data from the specified MySQL table and allows you to work with it in the same way as with a regular ClickHouse table.

Loading data into ClickHouse

The obtained data can be immediately saved to a pre-created ClickHouse table using the operator INSERT … SELECT:

INSERT INTO clickhouse_table (date, ip, log)
SELECT date, ip, log
FROM mysql('host:port', 'database', 'table_name', 'login', 'password')
WHERE date > CURRENT_DATE() - INTERVAL 1 MONTH

In this example, only data for the last month is loaded into ClickHouse, which is convenient for incremental transfer or initial migration of large amounts of data.