This document describes how to migrate data from a self-built IDC or other cloud platform's ClickHouse cluster to a Tencent Cloud TCHouse-C cluster. Before data migration, ensure network connectivity between the source cluster and the target cluster.
1. Database Table Schema Migration
Determine all tables in the database by executing SELECT DATABASE,TABLE FROM system.tables WHERE database != 'system'.
Query the DDL statement of the table in the source database using database.table.
clickhouse-client --host $CLICKHOUSE_HOST --port $CLICKHOUSE_PORT --user $USER --password $PASSWORD -q "SHOW CREATE database.table" > database.table.sql
Obtain the file containing the DDL statements and execute it in the new cluster. Here, CLICKHOUSE_HOST and CLICKHOUSE_PORT are the source service endpoints, while USER and PASSWORD are the username and password. The procedure is as follows:
Create the corresponding database in the new cluster:
clickhouse-client --host $TENCENT_CLICKHOUSE_HOST --port $TENCENT_CLICKHOUSE_PORT --user $USER --password $PASSWORD -q "CREATE DATABSE database"
Create a table:
clickhouse-client --host $TENCENT_CLICKHOUSE_HOST --port $TENCENT_CLICKHOUSE_PORT --user $USER --password $PASSWORD > database.table.sql
Here, TENCENT_CLICKHOUSE_HOST and TENCENT_CLICKHOUSE_PORT are the endpoints for the Tencent Cloud TCHouse-C service, while USER and PASSWORD are the username and password.
2. Migrating Data Using the Remote Function
When the data volume is small, you can directly migrate data from the source cluster to the new cluster using the Remote function. Execute the following SQL:
INSERT INTO target_database.table
SELECT *
FROM remote('source_cluster',source_database.table,$USER,$PASSWORD)
This approach offers the advantages of simplicity and high reliability, but it depends heavily on network transmission efficiency. If a timeout occurs due to network issues, you may need to re-import the data. Additionally, during the migration process, it may impose some read pressure on the source cluster. Therefore, it is recommended to perform the operation during off-peak business hours.
When the data volume is large (hundreds of GB or more), you can migrate data in batches based on more granular conditions, such as by partition, day, or hour. For example, you can migrate data on a daily basis:
INSERT INTO target_database.table
SELECT *
FROM remote('source_cluster',source_database.table,$USER,$PASSWORD)
WHERE event_date = '2025-05-25'
Or migrate data based on a date range:
INSERT INTO target_database.table
SELECT *
FROM remote('source_cluster',source_database.table,$USER,$PASSWORD)
WHERE event_date BETWEEN '2025-06-01' AND '2025-06-30'
Notes:
When the data volume is large, it is recommended to adopt a migration mode from Local Table to Local Table. Directly writing to the Distributed Table of the target cluster may cause insufficient memory or result in a "Too many parts" error.
Query the size of table partitions using the following SQL:
SELECT
database,
table,
partition,
count(),
formatReadableSize(sum(bytes_on_disk))
FROM system.parts
WHERE active
GROUP BY
database,
table,
partition
ORDER BY
database ASC,
table ASC,
partition ASC
3. Migrating Data by Exporting/Importing Data Files
Export data from the source cluster to a file, and then import that file into the new cluster. The procedure is as follows:
Data Export:
clickhouse-client --host $CLICKHOUSE_HOST --port $CLICKHOUSE_PORT --user $USER --password $PASSWORD -q "SELECT * FROM database.table FORMAT CSV" > database.table.dat
Data Import:
clickhouse-client --host $TENCENT_CLICKHOUSE_HOST --port $TENCENT_CLICKHOUSE_PORT --user $USER --password $PASSWORD -q "INSERT INTO database.table FORMAT CSV" < database.table.dat
Notes:
You can select from multiple data formats here. For the formats supported by ClickHouse, see the official documentation. 4. Migrating Data Using JDBC
If a backup data source is available, you can also use JDBC to re-write data into the Tencent Cloud TCHouse-C cluster. For details on JDBC writing, see the official documentation.