How to Copy Tables Between Databases in ClickHouse

Galaxy Glossary

How do you copy a table between two ClickHouse databases without data loss?

COPY TABLE copies both schema and data from a source database to a target database in ClickHouse.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why copy a table between ClickHouse databases?

Moving a table lets you reorganize data, split workloads, or migrate to a new cluster without downtime. ClickHouse supports fast, online copies that preserve table engines, settings, and data.

What are the main methods?

Three approaches dominate: 1) CREATE TABLE ... AS, 2) INSERT INTO ... SELECT, and 3) BACKUP / RESTORE.Choose based on size, engine, and need for exact metadata.

How to copy schema and data in one step?

Use CREATE TABLE db2.Customers AS db1.Customers ENGINE = MergeTree().It clones structure and then streams data.

How to copy only data when schema exists?

Run INSERT INTO db2.Customers SELECT * FROM db1.Customers; This pushes data through the cluster and respects partitions.

How to copy large tables loss-free?

BACKUP TABLE db1.Customers TO '/var/backup/' and RESTORE TABLE db1.Customers FROM '/var/backup/' AS db2.Customers; handles terabytes atomically and is resumable.

When should I use clickhouse-copier?

Use the clickhouse-copier tool for cross-cluster transfers.It distributes tasks, retries automatically, and maintains replication state.

Best practices for ecommerce data

Copy reference tables (Products) first, then fact tables (Orders, OrderItems) to maintain foreign-key logic at the application layer. Validate row counts and sample checksums.

How to verify the copy finished?

Compare counts: SELECT count() FROM db1.Orders; and SELECT count() FROM db2.Orders; Differences signal missing partitions or failed shards.

Can I keep both tables in sync?

Create a MATERIALIZED VIEW on db1.Orders that INSERTs into db2.Orders for continuous replication until cutover.

Why How to Copy Tables Between Databases in ClickHouse is important

How to Copy Tables Between Databases in ClickHouse Example Usage


--Copy the Orders table from analytics to reporting
CREATE TABLE reporting.Orders AS analytics.Orders ENGINE = MergeTree();

--Then copy OrderItems data only
INSERT INTO reporting.OrderItems SELECT * FROM analytics.OrderItems;

How to Copy Tables Between Databases in ClickHouse Syntax


--1. Clone structure + data in one statement
CREATE TABLE target_db.Customers AS source_db.Customers ENGINE = MergeTree();

--2. Copy data only (schema already created)
INSERT INTO target_db.Customers SELECT * FROM source_db.Customers;

--3. Atomic backup & restore (20.3+)
BACKUP TABLE source_db.Customers TO '/var/backup/';
RESTORE TABLE source_db.Customers FROM '/var/backup/' AS target_db.Customers;

--4. Copy across clusters with clickhouse-copier config file

Common Mistakes

Using INSERT SELECT for terabyte-scale tables on a single node. It can saturate CPU and I/O. Fix by using BACKUP/RESTORE or clickhouse-copier for distributed transfer.
Forgetting to specify the engine in CREATE TABLE ... AS. ClickHouse defaults to the source engine only in v21.3+. Explicitly set ENGINE to avoid surprises on older versions.