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.

Getting startedGetting started

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

• Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Yandex Virtual Private Cloud pricing).

Transferring dataTransferring data

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>;
   

   For 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 information 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

Getting startedGetting started

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

Required paid resourcesRequired paid resources

• Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Object Storage bucket: use of storage, data operations (see Object Storage pricing).

Transferring dataTransferring data

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 a 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, the admin user has this privilege. To assign the privilege 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 under a user privileged with CREATE DATABASE, CREATE TABLE, and INSERT for the desired database. By default, the admin user has these privileges. To assign the privileges 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 an 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.