Migrate a standalone ClickHouse® installation to DoubleCloud using clickhouse-client

This tutorial shows how to migrate your self-managed ClickHouse® cluster to the DoubleCloud Managed Service for ClickHouse®.

Before you start

To migrate your cluster, first install the clickhouse-client :

Prepare your self-managed cluster

If your self-managed ClickHouse® cluster has tables created with the CollapsingMergeTree engine, you need to disable automatic merges. This will protect you from possible data corruption on the target:

1. Disable automatic merges by executing the following query:

   SYSTEM STOP MERGES ON CLUSTER 'default' <database-name>.<table-name>
   

2. Perform the manual merge:

   OPTIMIZE TABLE <database-name>.<table-name> FINAL DEDUPLICATE
   

   The FINAL statement merges all the table parts into one, and the DEDUPLICATE statement removes repeating rows during the merge to avoid possible data corruption.

Arrive at the cluster configuration in DoubleCloud

When migrating a self-managed ClickHouse® cluster to DoubleCloud which is already sharded, we strongly recommend using a single-shard configuration whenever possible.

With hybrid storage enabled, sharding isn't necessary when the only reason is the amount of data. Hybrid storage can handle tens of terabytes per node with EAS.

Therefore, sharding on DoubleCloud is only needed if a single node can't cover your writes anymore.

In case of read performance instead, increase the number of replicas and upgrade your cluster to a better hardware configuration.

If the primary reason for sharding is to improve the write performance, it's important that you choose a sharding key that fits your use case. To select the best key option for your use case, see the official ClickHouse® documentation .

To handle large amounts of data, consider splitting it into manageable chunks when setting up a multi-shard cluster. Consult the official ClickHouse® documentation on data partitioning

Prepare the DoubleCloud cluster

Recreate the DDL structure of the cluster you want to migrate on the DoubleCloud side:

1. Connect to your cluster and recreate create a database:

   1. Go to the Clusters page in the console.

   2. Select the name of your cluster to open its information page. By default, you will see the Overview tab.

   3. Under Connection strings, find the Native interface string and click Copy.

   4. Open your terminal and run a command to connect to your cluster:

      <Native interface connection string>
      

   5. After you have connected to the cluster, run the CREATE query for each database you want to recreate on the target cluster:

      CREATE DATABASE IF NOT EXISTS <database_name> ON CLUSTER default
      

2. Manually recreate at the target database each table you want to migrate. To get the CREATE query for all the tables within a database, run the following on your source ClickHouse® cluster:

   SELECT create_table_query FROM system.tables WHERE database NOT IN ('system', 'information_schema’) AND (engine NOT IN ('MaterializedView', 'View', 'Dictionary'))
   

   You need to add the ON CLUSTER default to every CREATE statement at the DoubleCloud target cluster.

   Migrating materialized views

   You can't directly migrate your materialized views .

   Migrate the data from source and target tables manually, and after the migration, recreate the materialized views on the target system.

3. Add the IP address of your source cluster to the ClickHouse® cluster allowlist.

Migrate your data

To migrate your table, run the following query on the source cluster:

INSERT INTO FUNCTION remoteSecure (
   '<target-clickhouse-host>',
   database_name, 
   table_name,
   '<your-cluster-username>', 
   '<your-cluster-password>'s
)
SELECT *
FROM database_name.table_name;

To get the target-clickhouse-host:

1. Go to your cluster's information page and open the Hosts tab.

2. Find the host to which you want to migrate your data and click .

Post-migration

After migrating the data, you need to recreate all materialized views manually (you need to add the ON CLUSTER default at the source):

SELECT create_table_query
FROM system.tables
WHERE (lower(database) 
NOT IN ('system', 'information_schema')) 
AND (engine IN ('MaterializedView', 'View', 'Dictionary'))

See also

• Migrate a standalone ClickHouse® installation to DoubleCloud using Transfer