Migrating between self-managed ClickHouse and ClickHouse Cloud using remoteSecure

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/07_OSS_to_Cloud/01_clickhouse-to-cloud_with_remotesecure.md)

This guide shows you how to migrate from a self-managed ClickHouse server to ClickHouse Cloud, and also how to migrate between ClickHouse Cloud services.
The [`remoteSecure`](/docs/sql-reference/table-functions/remote) function is used in `SELECT` and `INSERT` queries to allow access to remote ClickHouse servers, which makes migrating tables as simple as writing an `INSERT INTO` query with an embedded `SELECT`.

## Migrating from self-managed ClickHouse to ClickHouse Cloud[​](#migrating-from-self-managed-clickhouse-to-clickhouse-cloud "Direct link to Migrating from self-managed ClickHouse to ClickHouse Cloud")

Regardless of if your source table is sharded and/or replicated, on ClickHouse Cloud you only need to create a destination table (you can leave out the Engine parameter for this table, `SharedMergeTree` will be selected automatically as the table engine),
and ClickHouse Cloud will automatically take care of vertical and horizontal scaling.
There is no need from your side to think about how to replicate and shard the table.

In this example, the self-managed ClickHouse server is the *source* and the ClickHouse Cloud service is the *destination*.

### Overview[​](#overview "Direct link to Overview")

The process is:

1. Add a read-only user to the source service
2. Duplicate the source table structure on the destination service
3. Pull the data from source to destination, or push the data from the source, depending on the network availability of the source
4. Remove the source server from the IP Access List on the destination (if applicable)
5. Remove the read-only user from the source service

### Migration of tables from one system to another:[​](#migration-of-tables-from-one-system-to-another "Direct link to Migration of tables from one system to another:")

This example migrates one table from a self-managed ClickHouse server to ClickHouse Cloud.

Compatibility

If you're noticing differences in behavior between your self-hosted ClickHouse deployment and your ClickHouse Cloud service,
it may be related to the [compatibility setting](/docs/operations/settings/settings#compatibility).
In Cloud, compatibility is set when a service is created and doesn't change at the service level to ensure that clients get consistent behavior even as the service upgrades.
If you wish to change compatibility, you may request to do so via [support](https://clickhouse.com/support/program).

### On the source ClickHouse system (the system that currently hosts the data)[​](#on-the-source-clickhouse-system-the-system-that-currently-hosts-the-data "Direct link to On the source ClickHouse system (the system that currently hosts the data)")

- Add a read only user that can read the source table (`db.table` in this example)

```
CREATE USER exporter
IDENTIFIED WITH SHA256_PASSWORD BY 'password-here'
SETTINGS readonly = 1;
```

```
GRANT SELECT ON db.table TO exporter;
```

- Copy the table definition

```
SELECT create_table_query
FROM system.tables
WHERE database = 'db' AND table = 'table'
```

### On the destination ClickHouse Cloud system:[​](#on-the-destination-clickhouse-cloud-system "Direct link to On the destination ClickHouse Cloud system:")

- Create the destination database:

```
CREATE DATABASE db
```

- Using the CREATE TABLE statement from the source, create the destination.

Tip

Change the ENGINE to to ReplicatedMergeTree without any parameters when you run the CREATE statement. ClickHouse Cloud always replicates tables and provides the correct parameters. Keep the `ORDER BY`, `PRIMARY KEY`, `PARTITION BY`, `SAMPLE BY`, `TTL`, and `SETTINGS` clauses though.

```
CREATE TABLE db.table ...
```

- Use the `remoteSecure` function to pull the data from the self-managed source

```
INSERT INTO db.table SELECT * FROM
remoteSecure('source-hostname', db, table, 'exporter', 'password-here')
```

Note

If the source system isn't available from outside networks then you can push the data rather than pulling it, as the `remoteSecure` function works for both selects and inserts. See the next option.

- Use the `remoteSecure` function to push the data to the ClickHouse Cloud service

Add the remote system to your ClickHouse Cloud service IP Access List

In order for the `remoteSecure` function to connect to your ClickHouse Cloud service the IP Address of the remote system will need to be allowed by the IP Access List. Expand **Manage your IP Access List** below this tip for more information.

Manage your IP Access List

From your ClickHouse Cloud services list choose the service that you will work with and switch to **Settings**. If the IP Access List doesn't contain the IP Address or range of the remote system that needs to connect to your ClickHouse Cloud service, then you can resolve the problem with **Add IPs**:

Add the individual IP Address, or the range of addresses that need to connect to your ClickHouse Cloud service. Modify the form as you see fit and then **Save**.

```
INSERT INTO FUNCTION
remoteSecure('HOSTNAME.clickhouse.cloud:9440', 'db.table',
'default', 'PASS') SELECT * FROM db.table
```

## Migrating between ClickHouse Cloud services[​](#migrating-between-clickhouse-cloud-services "Direct link to Migrating between ClickHouse Cloud services")

Some example uses for migrating data between ClickHouse Cloud services:

- Migrating data from a restored backup
- Copying data from a development service to a staging service (or staging to production)

In this example there are two ClickHouse Cloud services, and they will be referred to as *source* and *destination*. The data will be pulled from the source to the destination. Although you could push if you like, pulling is shown as it uses a read-only user.

There are a few steps in the migration:

1. Identify one ClickHouse Cloud service to be the *source*, and the other as the *destination*
2. Add a read-only user to the source service
3. Duplicate the source table structure on the destination service
4. Temporarily allow IP access to the source service
5. Copy the data from source to destination
6. Re-establish the IP Access List on the destination
7. Remove the read-only user from the source service

#### Add a read-only user to the source service[​](#add-a-read-only-user-to-the-source-service "Direct link to Add a read-only user to the source service")

- Add a read only user that can read the source table (`db.table` in this example)

  ```
  CREATE USER exporter
  IDENTIFIED WITH SHA256_PASSWORD BY 'password-here'
  SETTINGS readonly = 1;
  ```

  ```
  GRANT SELECT ON db.table TO exporter;
  ```
- Copy the table definition

  ```
  select create_table_query
  from system.tables
  where database = 'db' and table = 'table'
  ```

#### Duplicate the table structure on the destination service[​](#duplicate-the-table-structure-on-the-destination-service "Direct link to Duplicate the table structure on the destination service")

On the destination create the database if it isn't there already:

- Create the destination database:

  ```
  CREATE DATABASE db
  ```
- Using the CREATE TABLE statement from the source, create the destination.

  On the destination create the table using the output of the `select create_table_query...` from the source:

  ```
  CREATE TABLE db.table ...
  ```

#### Allow remote access to the source service[​](#allow-remote-access-to-the-source-service "Direct link to Allow remote access to the source service")

In order to pull data from the source to the destination the source service must allow connections. Temporarily disable the "IP Access List" functionality on the source service.

Tip

If you will continue to use the source ClickHouse Cloud service then export the existing IP Access list to a JSON file before switching to allow access from anywhere; this will allow you to import the access list after the data is migrated.

Modify the allow list and allow access from **Anywhere** temporarily. See the [IP Access List](/docs/cloud/security/setting-ip-filters) docs for details.