[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Tutorials](index.md) > Migrating data to Managed Service for ClickHouse® using ClickHouse®

# Migrating data to Managed Service for ClickHouse® using ClickHouse®

# 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](#transfer-remote). This method is suitable for migrating individual tables.
* [Built-in `BACKUP`/`RESTORE` commands and a Yandex Object Storage bucket](#backup-objstorage). 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](../../tutorials/dataplatform/ch-to-mch-migration.md).

## Data migration using the remote function {#transfer-remote}

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. 

{% note tip %}

Before migrating data, we recommend pausing merge operations on the source cluster using the `STOP MERGES` and `STOP TTL MERGES` commands. You should also disable all consumers.

{% endnote %}

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](../operations/cluster-create.md#create-cluster).
1. Connect to the source cluster.
1. Get the create table statement text:

   ```sql
   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:

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

1. [Connect to the target Managed Service for ClickHouse® cluster](../operations/connect/clients.md#clickhouse-client) 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.

1. Run the following query on the source cluster:

   ```sql
   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](../operations/connect/fqdn.md).

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

   ```sql
   SHOW TABLES FROM <DB_name>;
   ```

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

   ```sql
   SELECT * FROM <DB_name>.<table_name>;
   ```

For more information on using the `remote` function, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/sql-reference/table-functions/remote).

## Migrating data using the BACKUP/RESTORE commands and an Object Storage bucket {#backup-objstorage}

{% note warning %}

You need ClickHouse® version 22.10 or later to work with the `BACKUP`/`RESTORE` commands in a third-party cluster.

{% endnote %}

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.

1. [Create a Managed Service for ClickHouse® target cluster](../operations/cluster-create.md#create-cluster) with [User management via SQL](../operations/cluster-users.md#sql-user-management) enabled.
1. [Create a service account](../../iam/operations/sa/create.md#create-sa) with the `storage.editor` role.
1. [Create a static key](../../iam/operations/authentication/manage-access-keys.md#create-access-key) for the service account.

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

1. [Create a Object Storage](../../storage/operations/buckets/create.md) bucket.


1. If [encryption](../../storage/concepts/encryption.md) is enabled for the bucket, [assign](../../kms/operations/key-access.md#add-access-binding) to the service account the [kms.keys.encrypterDecrypter](../../iam/roles-reference.md#kms-keys-encrypterDecrypter) role for the encryption key linked to the bucket.


1. 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:

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

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

    ```sql
    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:

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

1. [Connect to the Managed Service for ClickHouse® target cluster](../operations/connect/clients.md#clickhouse-client) 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:

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

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

    ```sql
    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: 

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

1. Check that the restore operation was successful:

    * If you restored a table, run this command:

        ```sql
        SELECT * FROM <DB_name>.<table_name>;
        ```

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

        ```sql
        SHOW DATABASES;
        ```

For more details on using the `BACKUP`/`RESTORE` commands with an S3 storage, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/operations/backup/overview#backuprestore-using-an-s3-disk).