Data Import Between ClickHouse Clusters

When two UClickHouse clusters need to synchronize data and are in the same availability zone.

Download the clickhouse-client client.

Rebuild Table Structure

View the database list of the source ClickHouse cluster.


clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW databases"  > database.list

View the table list of the source ClickHouse cluster.


clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW tables from <database_name>"  > table.list

Export the table creation DDL from the source ClickHouse cluster.


clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW CREATE TABLE <database_name>.<table_name>"  > table.sql

Import the table creation DDL into the target ClickHouse cluster.


clickhouse-client --host="<new host>" --port="<new port>" --user="<new user name>" --password="<new password>"  < table.sql

Data Migration

Remote Data Migration


insert into <new_database>.<new_table> select * from remote('old_endpoint', <old_database>.<old_table>, '<username>', '<password>');

Parameter descriptions:

Parameter	Description
new_database	Target ClickHouse cluster database name
new_table	Target ClickHouse cluster table name
old_endpoint	Source ClickHouse endpoint
old_database	Source ClickHouse cluster database name
old_table	Source ClickHouse cluster table name
username	Source ClickHouse cluster username
password	Source ClickHouse cluster password

File Export Migration

Export the source data to a CSV format file.


clickhouse-client --host="<old host>" --port="<oldport>" --user="<old user name>" --password="<old password>"  --query="select * from <database_name>.<table_name> FORMAT CSV"  > table.csv

Import the CSV file into the target cluster.


clickhouse-client --host="<new host>" --port="<new port>" --user="<new user name>" --password="<new password>"  --query="insert into <database_name>.<table_name> FORMAT CSV"  < table.csv