Migrating data to Managed Service for ClickHouse® using ClickHouse®

You can migrate data from your ClickHouse® cluster to a Managed Service for ClickHouse® cluster by using:

• Built-in remote function. This method is suitable for migrating individual tables.
• Built-in backup/restore commands and a Yandex Object Storage bucket. Use this method to migrate both individual tables and an entire database.

You can also migrate a database from a ClickHouse® cluster to a Managed Service for ClickHouse® cluster using Data Transfer. For more information about this method, see this tutorial.

Data migration using the remote functionData migration using the remote function

You can use remote to migrate individual tables from a third-party ClickHouse® cluster. This method does not require installing ZooKeeper, additional tools, or upgrading the source cluster's ClickHouse® version.

To migrate a table from a third-party ClickHouse® cluster to a Managed Service for ClickHouse® cluster:

1. Create a Managed Service for ClickHouse® target cluster.

2. Connect to the source cluster.

3. Get the create table statement text:

   SELECT create_table_query FROM system.tables WHERE database = '<name_of_DB_to_migrate>';
   

   For example, your database named db1 stores a table named tasks with the list of tasks. The response to your query will look as follows:

   CREATE TABLE db1.tasks (`task_id` Int32,
                           `title` String, 
                           `start_date` Date, 
                           `due_date` Date, 
                           `priority` Int8 DEFAULT 3, 
                           `description` String) 
                  ENGINE = MergeTree 
                  PRIMARY KEY tuple(task_id) 
                  ORDER BY tuple(task_id) 
                  SETTINGS index_granularity = 8192;
   

4. Connect to the target Managed Service for ClickHouse® cluster and create a new table using the statement text you got earlier.

   If the source cluster did not use replicas, while the target cluster does, switch to a Replicated table engine.

   To create an object on all hosts of the target cluster, use the ON CLUSTER clause in the CREATE command.

5. Run the following query on the source cluster:

   INSERT INTO FUNCTION
   remoteSecure('<Managed_Service_for_ClickHouse®_cluster_host_FQDN>:9440',
                '<target_cluster_DB_name>.<target_table_name>',
                '<username_in_target_cluster>',
                '<user_password_in_target_cluster>')
   SELECT * from <DB_name>.<table_name>;
   

   To learn how to get a host FQDN, see this guide.

6. On the target cluster, check that the table from the source cluster has appeared in the database:

   SHOW TABLES FROM <DB_name>;
   

7. Check that the target table contains the same data as the source table:

   SELECT * FROM <DB_name>.<table_name>;
   

For more details on using the remote function, see this ClickHouse® guide.

Migrating data using the backup/restore commands and an Object Storage bucketMigrating data using the backup/restore commands and an Object Storage bucket

You can use the backup/restore commands and an Object Storage bucket to migrate both individual tables and an entire database from a third-party ClickHouse® cluster. To do this:

1. Create a Managed Service for ClickHouse® target cluster.

2. Create a service account with the storage.editor role.

3. Create a static key for the service account.

   Save both the key and its ID, as you will need them in the next steps.

4. Create an Object Storage bucket.

5. Connect to the source cluster.

6. Run the following command to save the backup of your table to the Object Storage bucket:

   BACKUP TABLE <DB_name>.<table_name> TO S3('<Object_Storage_bucket_endpoint>', '<service_account_static_key_ID>', '<service_account_static_key>');
   

   To migrate the entire database, run the command below:

   BACKUP DATABASE <DB_name> TO S3('<Object_Storage_bucket_endpoint>', '<service_account_static_key_ID>', '<service_account_static_key>');
   

7. Connect to the target Managed Service for ClickHouse® cluster.

8. Run the following command to restore your table from a backup:

   RESTORE TABLE <DB_name>.<table_name> FROM S3('<Object_Storage_bucket_endpoint>', '<service_account_static_key_ID>', 'service_account_static_key>');
   

   To restore an entire database, use this command:

   RESTORE DATABASE <DB_name> FROM S3('<Object_Storage_bucket_endpoint>', '<service_account_static_key_ID>', 'service_account_static_key>');
   

9. Check that the restore operation was successful:

   • If you restored a table, run this command:

     SELECT * FROM <DB_name>.<table_name>;
     

   • If you restored an entire database, run this command:

     SHOW DATABASES;
     

For more details on using the backup/restore commands with an S3 storage, see this ClickHouse® guide.