Accessing RDS MySQL Using ClickHouse
ClickHouse provides efficient data analysis in OLAP scenarios. It can map a table on the remote database server to a ClickHouse cluster through a database engine such as MySQL, so data can be analyzed in the ClickHouse cluster. This section describes how to interconnect a ClickHouse cluster with a MySQL database instance of RDS.
Prerequisites
- You have prepared the RDS database instance and the username and password of the database. For details, see Buying an RDS for MySQL Instance and Connecting to It Using a MySQL Client.
- 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')
Table 1 Parameters of the MySQL database Parameter
Description
hostport
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.
Example of using the MySQL engine:
- Connect to the MySQL database of RDS. For details, see Buying an RDS for MySQL Instance and Connecting to It Using a MySQL Client.
- Create a table in the MySQL database and insert data into the table.
- Run a client command to connect to ClickHouse.
In a non-security cluster:
./clickhouse client --host Internal IP address of the cluster --port 9000 --user admin --password Password
For details about how to connect to a security cluster, see Using a Client to Connect to a ClickHouse Security Cluster.
./clickhouse client --host Internal IP address of the cluster --port 9440 --user admin --password Password --secure --config-file /root/config.xml
Internal IP address of the cluster: cluster access address on the cluster details page. Replace it with the access address of the cluster you purchased.
- Create a MySQL database in ClickHouse. After the database is created, it automatically exchanges data with the 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.
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 │ └─────┴──── ┘
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot