Integrating MySQL with ClickHouse

This page covers using the MySQL table engine, for reading from a MySQL table

Connecting ClickHouse to MySQL using the MySQL Table Engine

The MySQL table engine allows you to connect ClickHouse to MySQL. SELECT and INSERT statements can be made in either ClickHouse or in the MySQL table. This article illustrates the basic methods of how to use the MySQL table engine.

1. Configure MySQL

1. Create a database in MySQL:

2. Create a table:

3. Insert sample rows:

4. Create a user to connect from ClickHouse:

5. Grant privileges as needed. (For demonstration purposes, the mysql_clickhouse user is granted admin privileges.)

Note

If you are using this feature in ClickHouse Cloud, you may need the to allow the ClickHouse Cloud IP addresses to access your MySQL instance. Check the ClickHouse Cloud Endpoints API for egress traffic details.

2. Define a Table in ClickHouse

1. Now let's create a ClickHouse table that uses the MySQL table engine:

The minimum parameters are:

parameter	Description	example
host	hostname or IP	mysql-host.domain.com
database	mysql database name	db1
table	mysql table name	table1
user	username to connect to mysql	mysql_clickhouse
password	password to connect to mysql	Password123!

Note

View the MySQL table engine doc page for a complete list of parameters.

3. Test the Integration

1. In MySQL, insert a sample row:

2. Notice the existing rows from the MySQL table are in the ClickHouse table, along with the new row you just added:

You should see 4 rows:

3. Let's add a row to the ClickHouse table:

4. Notice the new row appears in MySQL:

You should see the new row:

Summary

The MySQL table engine allows you to connect ClickHouse to MySQL to exchange data back and forth. For more details, be sure to check out the documentation page for the MySQL table engine.