Skip to main content

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:

    CREATE DATABASE db1;
  2. Create a table:

    CREATE TABLE db1.table1 (
    id INT,
    column1 VARCHAR(255)
    );
  3. Insert sample rows:

    INSERT INTO db1.table1 
    (id, column1)
    VALUES
    (1, 'abc'),
    (2, 'def'),
    (3, 'ghi');
  4. Create a user to connect from ClickHouse:

    CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';
  5. Grant privileges as needed. (For demonstration purposes, the mysql_clickhouse user is granted admin prvileges.)

    GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';

2. Define a Table in ClickHouse​

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

    CREATE TABLE mysql_table1 (
    id UInt64,
    column1 String
    )
    ENGINE = MySQL('mysql-host.domain.com','db1','table1','mysql_clickhouse','Password123!')

    The minimum parameters are:

    parameterDescriptionexample
    hosthostname or IPmysql-host.domain.com
    databasemysql database namedb1
    tablemysql table nametable1
    userusername to connect to mysqlmysql_clickhouse
    passwordpassword to connect to mysqlPassword123!
    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:

    INSERT INTO db1.table1 
    (id, column1)
    VALUES
    (4, 'jkl');
  2. Notice the existing rows from the MySQL table are in the ClickHouse table, along with the new row you just added:

    SELECT
    id,
    column1
    FROM mysql_table1

    You should see 4 rows:

    Query id: 6d590083-841e-4e95-8715-ef37d3e95197

    ┌─id─┬─column1─┐
    │ 1 │ abc │
    │ 2 │ def │
    │ 3 │ ghi │
    │ 4 │ jkl │
    └────┴─────────┘

    4 rows in set. Elapsed: 0.044 sec.
  3. Let's add a row to the ClickHouse table:

    INSERT INTO mysql_table1
    (id, column1)
    VALUES
    (5,'mno')
  4. Notice the new row appears in MySQL:

    mysql> select id,column1 from db1.table1;

    You should see the new row:

    +------+---------+
    | id | column1 |
    +------+---------+
    | 1 | abc |
    | 2 | def |
    | 3 | ghi |
    | 4 | jkl |
    | 5 | mno |
    +------+---------+
    5 rows in set (0.01 sec)

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.