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 either individual tables or an entire database from a third-party ClickHouse® cluster.

1. Create a Managed Service for ClickHouse® target cluster with User management via SQL enabled.

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. If encryption is enabled for the bucket, assign to the service account the kms.keys.encrypterDecrypter role for the encryption key linked to the bucket.

6. Connect to the source cluster as a user with the BACKUP privilege for the database. By default, it is the admin user's privilege. To assign it to another user, run this query:

   GRANT BACKUP ON <DB_name>.* TO <username>;
   

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

   BACKUP TABLE <DB_name>.<table_name> TO S3(
     'https://storage.yandexcloud.net/<Object_Storage_bucket_name>',
     '<service_account_static_key_ID>',
     '<service_account_static_key>'
   );
   

   To migrate the entire database, run the command below:

   BACKUP DATABASE <DB_name> TO S3(
     'https://storage.yandexcloud.net/<Object_Storage_bucket_name>',
     '<service_account_static_key_ID>',
     '<service_account_static_key>'
   );
   

8. Connect to the Managed Service for ClickHouse® target cluster as a user with the CREATE DATABASE, CREATE TABLE, and INSERT privileges for the database. By default, these are the admin user's privileges. To assign them to another user, run this query:

   GRANT CREATE DATABASE, CREATE TABLE, INSERT ON <DB_name>.* TO <username>;
   

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

   RESTORE TABLE <DB_name>.<table_name> FROM S3(
     'https://storage.yandexcloud.net/<Object_Storage_bucket_name>',
     '<service_account_static_key_ID>',
     '<service_account_static_key>'
   );
   

   To restore the entire database, use this command:

   RESTORE DATABASE <DB_name> FROM S3(
     'https://storage.yandexcloud.net/<Object_Storage_bucket_name>',
     '<service_account_static_key_ID>',
     '<service_account_static_key>'
   );
   

10. 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.