Interconnecting ClickHouse with RDS for MySQL

Updated on 2024-10-08 GMT+08:00

View PDF

ClickHouse provides efficient data analysis in OLAP scenarios. It can map a table on the remote database server to the ClickHouse cluster through a database engine such as MySQL, so data can be analyzed in the ClickHouse cluster. The following describes how to interconnect the ClickHouse cluster with the MySQL database instance of RDS.

Prerequisites

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

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 MySQL and PostgreSQL instances of RDS, 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.

Syntax for using the MySQL engine:

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

Parameters of the MySQL engine:

host:port: IP address and port number of the RDS MySQL database instance.
database: Name of the RDS MySQL database.
user: Username of the RDS MySQL database.
password: Password of the RDS MySQL database user. There can be security risks if a command contains the authentication password. You are advised to disable the command recording function (history) before running the command.

Example of using the MySQL engine:

Connect to the MySQL database of RDS. For details, see Connecting to a DB Instance.
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);
Log in to the node where the ClickHouse client is installed. Run the following command to go to the client installation directory:

cd /opt/client
Run the following command to configure environment variables:

source bigdata_env
If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The user must have the permission to create ClickHouse tables. Therefore, you need to bind the corresponding role to the user. For details, see ClickHouse User and Permission Management. If Kerberos authentication is disabled for the current cluster, skip this step.
1. Run the following command if it is an MRS 3.1.0 cluster:
  export CLICKHOUSE_SECURITY_ENABLED=true
2. kinit Component service user
  Example: kinit clickhouseuser
Run the client command to connect to ClickHouse.

clickhouse client --host IP address of the ClickHouse instance --user Username --password --port Port number

Enter the user password.
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', 'MySQL database username', 'Password of the MySQL database user');
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;
```
┌─int_id─┬─float─┐
│      1   │     2   │
└─────┴──── ┘
```
Data can be properly queried after being inserted.

INSERT INTO mysql_table VALUES (3,4);
SELECT * FROM mysql_table;
```
┌─int_id─┬─float─┐
│      1   │       2 │
│      3   │       4 │
└─────┴──── ┘
```

Parent topic: ClickHouse Data Import

Previous topic: ClickHouse Data Import

Next topic: Interconnecting ClickHouse with OBS

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot