Migrate data between ApsaraDB for ClickHouse Enterprise Edition clusters - ApsaraDB for ClickHouse

This topic describes how to migrate data between ApsaraDB for ClickHouse Enterprise Edition clusters. This method uses the remote function to execute an INSERT INTO statement with a nested SELECT. This process enables efficient and direct data replication for the migration.

Scenarios

Migrate data from a restored backup.
Copy data from a development service to a pre-production service, or from a pre-production service to a production service.

Procedure

Step 1: Configure source cluster permissions and get the table schema

Create a read-only user to query data from the source cluster during data migration.

CREATE USER exporter
IDENTIFIED WITH SHA256_PASSWORD BY 'password-here'
SETTINGS readonly = 1;

Grant the query permission on a specific database table.
```
GRANT SELECT ON test_db.test_tb TO exporter;
```

Obtain the source table schema.

SELECT create_table_query
FROM system.tables
WHERE database = 'test_db' AND TABLE = 'test_tb'

Step 2: Create the table schema on the destination cluster

Create a database.
```
CREATE DATABASE test_db
```
Use the source table schema (the CREATE TABLE statement) obtained in Step 1 to create a table in the destination database.
```
CREATE TABLE test_db.test_tb ...
```

Step 3: Configure whitelists

The source and destination clusters must be able to connect to each other during data migration. To enable this connection, configure the following settings:

Add the IP address of the source cluster to the whitelist of the destination cluster.
Add the IP address of the destination cluster to the whitelist of the source cluster.

Note

You can run SELECT * FROM system.clusters; to view the IP address of an ApsaraDB for ClickHouse Enterprise Edition cluster.

Step 4: Perform the data migration

You can run the following SQL statement to read data from the source cluster and insert it into the destination table using the remote function.

INSERT INTO test_db.test_tb 
SELECT * 
FROM remote(
  'source-hostname:9000', -- Endpoint and port of the source cluster
  'test_db',              -- Source database name
  'test_tb',              -- Source table name
  'exporter',             -- Username
  'password-here'         -- User password
);

Step 5: (Optional) Perform secure configuration and environment cleanup

Create an IP address access list for the destination cluster.
After the data migration is complete, if the destination cluster will serve as the new production environment, configure a whitelist.
Delete the read-only user from the source cluster.
```
DROP USER exporter;
```
Remove the IP address of the destination cluster from the whitelist of the source cluster.