ApsaraDB for ClickHouse:Import data from ApsaraDB RDS for MySQL

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB for ClickHouse cluster. If you haven't created one, see Create a cluster.

• A privileged database account for the ClickHouse cluster. See Create an account.

• The ApsaraDB RDS for MySQL instance and the ApsaraDB for ClickHouse cluster in the same region and virtual private cloud (VPC).

• The ClickHouse cluster IP address added to the RDS MySQL whitelist, and the RDS MySQL IP address added to the ClickHouse whitelist.

Step 1: Set up the source table in ApsaraDB RDS for MySQL

1. Connect to the ApsaraDB RDS for MySQL instance using Data Management (DMS). See Use DMS to log on to an ApsaraDB RDS for MySQL instance.

2. Create a database:

   CREATE DATABASE testdb;

3. Create the source table:

   CREATE TABLE testdb.mysql_test_table (
     v1 Int NOT NULL,
     v2 Int DEFAULT NULL,
     v3 Float DEFAULT NULL
   ) ENGINE=InnoDB;

4. Insert sample data:

   INSERT INTO testdb.mysql_test_table VALUES (4,4,4.0),(1,1,2.0),(1,1,0.0),(4,1,2.0),(7,1,3.0);

Step 2: Create a table in ApsaraDB for ClickHouse

1. Connect to the ApsaraDB for ClickHouse cluster using DMS. See Connect to a ClickHouse cluster using DMS.

2. Create a local table:

   Note

   Map each MySQL column type to the corresponding ClickHouse type. Columns defined without NOT NULL in MySQL become Nullable in ClickHouse. For the full mapping, see Data type mappings.

   CREATE TABLE default.clickhouse_test_table ON CLUSTER default (
     v1 Int32,
     v2 Nullable(Int32),
     v3 Nullable(Float32)
   ) ENGINE = MergeTree ORDER BY v1;

3. (Optional) Create a distributed table for multi-node clusters:

   CREATE TABLE clickhouse_test_table_distributed ON CLUSTER default
     AS clickhouse_test_table
   ENGINE = Distributed(default, default, clickhouse_test_table, rand());

   Distributed tables spread data across all nodes in the cluster, enabling parallel reads and writes for higher throughput and availability. We recommend creating a distributed table for multi-node clusters.

Step 3: Import data from ApsaraDB RDS for MySQL

Connect to the ApsaraDB for ClickHouse cluster using DMS, then run an INSERT INTO ... SELECT statement using the mysql() table function:

INSERT INTO <clickhouse_table>
SELECT * FROM mysql('<rds_endpoint>:<port>', '<database>', '<table>', '<user>', '<password>');

Parameters:

To get the RDS MySQL endpoint and port, see View and manage instance endpoints and ports.

Full import example:

INSERT INTO clickhouse_test_table_distributed
SELECT * FROM mysql('rm-bp16t9h3999xb****.mysql.rds.aliyuncs.com:3306', 'testdb', 'mysql_test_table', 'test', '123456Aa');

Step 4: Verify the imported data

Query the destination table to confirm the data was imported:

SELECT * FROM clickhouse_test_table_distributed;

Click Execute(F8) in DMS to run the query. The result should match the data inserted in Step 1.

Data type mappings

When creating the ClickHouse table, use the following type mappings. If the source and destination types don't match exactly, ClickHouse attempts automatic type conversion.

Columns that allow NULL in MySQL are mapped to Nullable in ClickHouse. Columns defined without NOT NULL in MySQL become Nullable in the ClickHouse table.