Interconnecting ClickHouse with RDS for MySQL

Scenarios

ClickHouse delivers efficient data analysis for Online Analytical Processing (OLAP) workloads. With support for compatible database engines like MySQL, it enables you to map tables from remote database servers directly into a ClickHouse cluster. This allows you to run fast, efficient analytical tasks within the ClickHouse environment.

This section uses the interconnection between a ClickHouse cluster and an RDS for MySQL instance as an example.

Prerequisites

• You have prepared the RDS database instance to be interconnected with and the username and password of the database. For details, see Connecting to an RDS for MySQL DB Instance.
• A ClickHouse cluster has been created and is running properly.

Notes and Constraints

• The RDS database instance and ClickHouse cluster are in the same VPC and subnet.
• Before synchronizing data, you need to evaluate the impact on the performance of the source and destination databases. You are advised to synchronize data during off-peak hours.
• Currently, ClickHouse can interconnect with RDS for MySQL and RDS for PostgreSQL instances, but cannot interconnect with SQL Server instances.

Interconnecting ClickHouse with RDS Using the MySQL Engine

The MySQL engine is used to map tables on the remote MySQL server to ClickHouse and allows you to run INSERT and SELECT statements on tables to facilitate data exchange between ClickHouse and MySQL.

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

Example of using the MySQL engine:

1. Connect to the RDS for MySQL database. For details, see Instance Connection Overview.
2. Create a table in the MySQL database and insert data into the table.

   Create table mysql_table.

   CREATE TABLE `mysql_table` (
   `int_id` INT NOT NULL AUTO_INCREMENT,
   `float` FLOAT NOT NULL,
   PRIMARY KEY (`int_id`));

   Insert data into the table.

   insert into mysql_table (`int_id`, `float`) VALUES (1,2);

3. Log in to the ClickHouse client node in the MRS cluster.

   Use the cluster client to connect to the ClickHouse server by referring to ClickHouse Client Practices.

4. Create a MySQL database in ClickHouse. After the database is created, it automatically exchanges data with a MySQL server.

   CREATE DATABASE mysql_db ENGINE = MySQL('IP address of the RDS MySQL database instance:Port number of the MySQL database instance', 'MySQL database name', 'Username of the MySQL database user', 'Password of the MySQL database user');

5. Switch to the created database mysql_db and query data in the table.

   USE mysql_db;

   Query the table data in the MySQL database in ClickHouse.

   SELECT * FROM mysql_table;

   The result is as follows:

   ┌─int_id─┬─float─┐
   │      1   │     2   │
   └─────┴──── ┘

   Data can be properly queried after being inserted.

   INSERT INTO mysql_table VALUES (3,4);
   SELECT * FROM mysql_table;

   The result is as follows:

   ┌─int_id─┬─float─┐
   │      1   │       2 │
   │      3   │       4 │
   └─────┴──── ┘