Migrate data from ClickHouse® to a ClickHouse® Managed Database using Trino

Last update: October 28 2026Open in Markdown

You can migrate tables from one or more ClickHouse® databases to a ClickHouse® Managed Database using Cloud for Trino. This method is suitable if you need to migrate individual tables, filter, or transform data during migration.

The server with the source database and the ClickHouse® cluster with the target database connect to Cloud for Trino as two catalogs using the ClickHouse connector. Each database is displayed in the catalog as a schema. Trino reads data from the source database and writes it to the target database in a single SQL query, without intermediate files or dumps.

When migrating data using Trino, limitations apply.

Limitations

Data migration guarantee	Cloud for Trino is in public preview: the product is provided without an SLA; service availability is not guaranteed; product malfunctions may occur. Back up the source tables before migrating critical data
Data types	Trino supports some ClickHouse® data types: integers and floating-point numbers, Decimal, Bool, String, FixedString, Date, DateTime, Enum, UUID, IPv4, and IPv6. Columns with other data types (such as Array, Map, Tuple, and DateTime64) are not supported by Trino by default, and a SELECT * query will not show them. If a table contains columns with unsupported data types, specify only the supported columns in INSERT and SELECT queries — the rest will receive default values. Learn more in the Type mapping section of the Trino documentation. If you need to migrate columns of all types, use standard ClickHouse® tools, such as the remoteSecure function. For more information about migrating data using remoteSecure, see the Migrate ClickHouse® databases to ClickHouse® Managed Databases instructions
Strings	Trino reads String columns as VARBINARY. This does not affect data migration — the same strings are written to the target table. To view strings in Trino in a human-readable format, use the from_utf8() function
Database objects	Trino migrates only table data. Create materialized views, dictionaries, users, roles, and settings profiles manually in the target cluster
Sharding	This guide is intended for a target cluster with a single shard: Trino writes data to the node whose address is specified in the catalog. For a cluster with multiple shards, write data to a table with the Distributed engine
Data consistency	Each table is read at a specific point in time. If you do not stop writing to the source database during migration, the DBMS will continue write operations to tables, causing part of the data to become inconsistent. Stop writing to the source database for the duration of the data migration or re-migrate changed data before switching the application
Tables with background row merging	In ReplacingMergeTree, CollapsingMergeTree, and similar tables, the number of rows in the source database and target cluster may differ until background merges are completed
Disk space	When writing via a temporary table, there must be enough disk space on the target cluster for the volume of data being migrated
Migrating data over the internet	When migrating data over the internet, the transfer rate depends on the bandwidth between pools. Traffic is encrypted during data transfer, but the ClickHouse® cluster public IP addresses are accessible from the internet — if necessary, restrict access with a security group and disconnect public IP addresses after the migration if they are no longer needed. If you want to migrate data from Trino to ClickHouse® via a private subnet, the target ClickHouse® cluster and the Trino cluster must be in the same pool and the same private subnet

Migrate data

1. Prepare the server with the source ClickHouse® database.
2. Prepare the target ClickHouse® cluster.
3. Create a Trino cluster with catalogs.
4. Connect to the Trino cluster.
5. Check the catalogs.
6. Migrate data using Trino.
7. Verify the result.
8. Switch services.

1. Prepare the server with the source ClickHouse® database

To protect data from modification in the source database, Trino will connect to it as a dedicated read-only user.

1. Connect to the source ClickHouse® database as a user with user management privileges.

2. Create a new user and grant them read-only permissions using the SELECT privilege:

   CREATE USER <user_name> IDENTIFIED WITH sha256_password BY '<password>';
   GRANT SELECT ON <source_database>.* TO <user_name>;

   Specify:

   • <user_name> — username;
   • <password> — user password;
   • <source_database> — name of the database from which to migrate data.

   If you need to migrate data from multiple databases, grant the SELECT privilege on each database.

3. Make sure that the server is accessible from the internet via a public IP address or DNS name.

4. Make sure that the ClickHouse® HTTP interface is enabled on the server: the https_port parameter (usually port 8443) is set in the configuration and a TLS certificate is configured. Trino connects to ClickHouse® via the HTTP interface.

5. If traffic filtering is used on the server, allow incoming connections to the ClickHouse® HTTPS port in the firewall or cloud provider security groups.

2. Prepare the target ClickHouse® cluster

For your information

Cloud for Trino is available only in the ru-7 pool. If you want to migrate data from Trino to ClickHouse® Managed Database via a private subnet, create the ClickHouse® cluster in the ru-7 pool and use the same subnet for both ClickHouse® and Trino clusters.

If the ClickHouse® and Trino clusters are located in different pools, Trino will only be able to connect to ClickHouse® over the internet via a public IP address.

1. Create a ClickHouse® cluster. When creating a cluster:

   • if you want to migrate data via a private subnet, select the location — ru-7 pool;

   • in the Node group configuration block, select a configuration with a disk size sufficient for the amount of data being migrated.

2. If you want to migrate data over the internet, assign a public IP address to the cluster shard. Public IP addresses are connected to all nodes of the shard simultaneously — a dedicated address for each node.

3. Create a Trino cluster with catalogs

Follow the Create Trino cluster instructions. When creating a cluster:

• in the Network block:

  • if the target ClickHouse® cluster is created in the ru-7 pool, select the target cluster subnet;

  • to connect to Trino from the internet, select the External GUI access checkbox;

• in the Catalogs block, add two catalogs with the ClickHouse connector type for the source and target databases. When adding catalogs, use the connection parameters:

Parameter	Catalog of the server with the source database	Catalog of the target cluster
Host	Public IP address or DNS name of the server	if the ClickHouse® cluster is in the ru-7 pool — private IP address of the cluster node, which can be viewed in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → IP address column; if the ClickHouse® cluster is not in the ru-7 pool — public IP address of the cluster node, which can be copied in the Control panel: in the top menu, click Products → Managed Databases → Active tab → cluster page → Connection tab → Connection addresses block → node row → in the IP address column, click next to the public IP address
Port	Port for connecting via the HTTP interface, usually 8443	8443
Database	Name of the source database	default
Username	Name of the user you created when preparing the server with the source ClickHouse® database in step 2	admin
Password	Password of the user you created when preparing the server with the source ClickHouse® database in step 2	Administrator user password — set when creating the cluster. After cluster creation, the password cannot be viewed in the Control panel, but it can be changed

4. Connect to the Trino cluster

You can connect to the cluster via the Trino GUI, the Trino CLI console client, and DBeaver.

Trino GUI

1. In the Control panel, in the top menu, click Products and select Trino.

2. In the cluster card, in the GUI column, click .

3. Enter the login — admin.

4. Enter the administrator password — set when creating the cluster. After cluster creation, the password cannot be viewed in the Control panel, but it can be changed.

5. Click Log In.

Trino CLI

1. Open the CLI.

2. Connect to the cluster:

   ./trino --server https://<cluster_id>.c.trino.data-platform.selcloud.ru --user admin --password

   Specify <cluster_id> — cluster UUID. You can copy it in the Control panel: in the top menu, click Products → Trino → in the cluster menu, select Copy UUID.

3. Enter the administrator password — set when creating the cluster. After cluster creation, the password cannot be viewed in the Control panel, but it can be changed.

DBeaver

1. Open DBeaver.

2. In the top menu, click Database → New Connection.

3. Select Trino.

4. Click Next.

5. In the Host field, enter <cluster_id>.c.trino.data-platform.selcloud.ru.

   Specify <cluster_id> — cluster UUID. You can copy it in the Control panel: in the top menu, click Products → Trino → in the cluster menu, select Copy UUID.

6. In the Port field, enter 443.

7. Enter the login — admin.

8. Enter the administrator password — set when creating the cluster. After cluster creation, the password cannot be viewed in the Control panel, but it can be changed.

9. Open the Driver properties tab and set the SSL property value to true.

10. Click Test Connection.

11. Click Finish.

5. Check the catalogs

List catalogs, schemas, and tables of the source database and schemas of the target database:

SHOW CATALOGS;
SHOW SCHEMAS FROM <source_catalog>;
SHOW TABLES FROM <source_catalog>.<source_database>;
SHOW SCHEMAS FROM <target_catalog>;

Specify:

• <source_catalog> — catalog name of the server with the source database;
• <source_database> — name of the source database;
• <target_catalog> — catalog name of the target cluster.

Both created catalogs should appear in the catalog list, and the tables of the source database should appear in the <source_catalog>.<source_database> schema.

6. Migrate data using Trino

Trino creates ClickHouse® tables only with the Log and MergeTree engines and without some source table settings. Therefore, you first need to create tables in the target cluster with the same structure as the source, and then migrate data into them. We recommend executing standard SQL queries to the target cluster via the system.execute procedure — in this case, separate access to the cluster is not required.

1. Create a database in the target cluster:

   CALL <target_catalog>.system.execute(query => 'CREATE DATABASE IF NOT EXISTS <target_database>');

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database.

2. Get the definition with the structure of the source table:

   SHOW CREATE TABLE <source_database>.<source_table>;

   Specify:

   • <source_database> — name of the source database;
   • <source_table> — name of the source table.

   Copy the query result.

3. Create a table in the target cluster:

   CALL <target_catalog>.system.execute(query => '
   <create_table_query>
   ');

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <create_table_query> — source table structure definition you obtained and copied in step 2.

   If the table structure contains single quotes (for example, in DEFAULT values or in the Enum type), change them to paired single quotes: ''. Remove settings from the structure that are not available in the target cluster, such as storage_policy. If a shard in the target cluster contains multiple nodes, use the replicated engine ReplicatedMergeTree.

4. Optional: to speed up data migration, disable writing via a temporary table:

   SET SESSION <target_catalog>.non_transactional_insert = true;

   Specify <target_catalog> — catalog name of the target cluster.

5. Migrate the data:

   INSERT INTO <target_catalog>.<target_database>.<target_table>
   SELECT * FROM <source_catalog>.<source_database>.<source_table>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database;
   • <target_table> — name of the target table;
   • <source_catalog> — catalog name of the server with the source database;
   • <source_database> — name of the source database;
   • <source_table> — name of the source table.

   The output will show the number of written rows.

6. If you disabled writing via a temporary table in step 4 and an error occurred while writing data to the target table, it will only contain part of the rows. To re-migrate all data:

   6.1. Clear the target table:

   TRUNCATE TABLE <target_catalog>.<target_database>.<target_table>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database;
   • <target_table> — name of the target table.

   6.2. Repeat step 5.

7. Repeat steps 2–6 for each table.

7. Verify the result

1. Compare the number of rows in the source and target tables:

   SELECT
     (SELECT count(*) FROM <source_catalog>.<source_database>.<source_table>) AS source_rows,
     (SELECT count(*) FROM <target_catalog>.<target_database>.<target_table>) AS target_rows;

   Specify:

   • <source_catalog> — catalog name of the server with the source database;
   • <source_database> — name of the source database;
   • <source_table> — name of the source table;
   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database;
   • <target_table> — name of the target table.

2. Find dates where the number of rows does not match:

   SELECT <column>, count(*) AS rows FROM <source_catalog>.<source_database>.<source_table> GROUP BY <column>
   EXCEPT
   SELECT <column>, count(*) AS rows FROM <target_catalog>.<target_database>.<target_table> GROUP BY <column>;

   Specify:

   • <column> — date column against which the number of rows is verified;
   • <source_catalog> — catalog name of the server with the source database;
   • <source_database> — name of the source database;
   • <source_table> — name of the source table;
   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database;
   • <target_table> — name of the target table.

   An empty output means that the number of rows matches for all days.

3. List the columns of the target table:

   SHOW COLUMNS FROM <target_catalog>.<target_database>.<target_table>;

   Specify:

   • <target_catalog> — catalog name of the target cluster;
   • <target_database> — name of the target database;
   • <target_table> — name of the target table.

4. Compare the target table columns obtained in step 3 with the source table structure obtained during data migration in step 2.

8. Switch services

1. Switch services to the target cluster — learn more in the Connect to ClickHouse® cluster instructions.

2. If you assigned a public IP address in the ClickHouse® cluster and no longer need it, disconnect it.

3. If you no longer need the Trino cluster, delete it.

4. If you no longer need the server with the source ClickHouse® database, delete it.

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