Skip to main content

Migrate ClickHouse® databases to Managed ClickHouse® databases

You can migrate databases from your ClickHouse® to a Managed ClickHouse® database using the remoteSecure or remote function. These functions are used in SELECT and INSERT queries to access remote ClickHouse® servers, allowing you to transfer tables as easily as executing an INSERT INTO query with an embedded SELECT.

We recommend using the remoteSecure function, as it supports a secure connection unlike remote.

Learn more about the functions in the remote, remoteSecure guide of the ClickHouse® documentation.

Prerequisites

Migration steps

  1. Prepare the source ClickHouse® cluster.

  2. Prepare the target ClickHouse® cluster.

  3. Transfer data.

1. Prepare the source ClickHouse® cluster

  1. Create a user that will read the source table:

    CREATE USER <user_name>
    IDENTIFIED WITH SHA256_PASSWORD BY '<password>'
    SETTINGS readonly = 1;

    Specify:

    • <user_name> — username;
    • <password> — user password.

  2. Grant the user permissions to read the source table:

    GRANT SELECT ON <database_name>.<table_name> TO <user_name>;

    Specify:

    • <database_name> — database name;
    • <table_name> — table name;
    • <user_name> — username.

  3. Get the structure of the source table:

    SELECT create_table_query
    FROM system.tables
    WHERE database = '<database_name>' AND table = '<table_name>'

    Specify:

    • <database_name> — database name;
    • <table_name> — table name.

    Copy the query result.

2. Prepare the target ClickHouse® cluster

  1. Create a ClickHouse® cluster.

  2. To allow cluster access to and from the internet, connect a public IP address.

  3. Create the target database:

    CREATE DATABASE <database_name>;

    Specify <database_name> — the database name.

  4. Create a table in the target cluster:

    <create_table_query>

    Specify <create_table_query> — the definition with the structure of the source table that you obtained and copied when preparing the source ClickHouse® cluster in step 3. When executing the CREATE statement, change ENGINE to ReplicatedMergeTree without specifying parameters. Leave the ORDER BY, PRIMARY KEY, PARTITION BY, SAMPLE BY, TTL and SETTINGS clauses unchanged.

3. Migrate data

For your information

To migrate data, we recommend using the remoteSecure function as it supports a secure connection, unlike remote. If SSL is not configured on the source cluster, use the remote function.

On the target cluster, migrate data from the source cluster using the remoteSecure function:

INSERT INTO <database_name>.<table_name> SELECT * FROM remoteSecure('<host>', <database_name>, <table_name>, '<user_name>', '<password>')

Specify:

  • <database_name> — database name;
  • <table_name> — table name;
  • <host> — IP address or DNS address of the source cluster;
  • <user_name> — username;
  • <password> — user password.

ClickHouse® is a registered trademark of ClickHouse, Inc. https://clickhouse.com.