🛠️ Copying and Moving Data Between Tables in ClickHouse (with Different Engines)

#clickhouse #dataengineering #sql #database

Sometimes in ClickHouse, you need to move or copy data between tables — especially when you're changing the table engine, for example from MergeTree to ReplicatedMergeTree.

At first glance, it may seem like MOVE PARTITION TO TABLE should do the trick — but in many cases, it doesn't work due to strict requirements. Let’s go over why, and a better approach we used.

❌ Why `MOVE PARTITION TO TABLE` Didn’t Work for Us

The ALTER TABLE source MOVE PARTITION TO TABLE dest command physically moves partitions and deletes them from the source table. But it comes with strict requirements:

✅ Same structure
✅ Same partition key
✅ Same primary key
✅ Same order by
✅ Same storage policy
✅ Same engine family (e.g., both must be MergeTree or both ReplicatedMergeTree)
✅ Same or superset of indices and projections

So when you’re trying to move data from a MergeTree table to a ReplicatedMergeTree table, this query will fail because the engines are different.

✅ Alternative: `ATTACH PARTITION FROM`

Instead, we used this command:

ALTER TABLE new_table ATTACH PARTITION partition_expr FROM old_table;

This copies the data (without deleting it from the source) and supports different engine types, which makes it ideal for our use case.

✅ Requirements:

Same structure
Same partition key, order by, and primary key
Same storage policy
Indices/projections must match (or be a superset if enforce_index_structure_match_on_partition_manipulation is disabled)

It’s a safe and clean way to transfer data between tables — even when they use different engines.

🧹 Final Step: Drop the Old Partition

Once the data is successfully attached and validated, we clean up the original table:

ALTER TABLE old_table DROP PARTITION partition_expr;

This removes the partition from the old table (and on all replicas, if any).

Data is physically deleted after ~10 minutes.

🧾 What About `ATTACH TABLE AS REPLICATED`?

ClickHouse also supports converting a table like this:

DETACH TABLE my_table;
ATTACH TABLE my_table AS REPLICATED;
SYSTEM RESTORE REPLICA my_table;

But we chose not to use this option.

Why?

Our goal wasn’t to convert the existing table but to control the replication and migration process manually.

So while ATTACH TABLE AS REPLICATED is valid and useful in some scenarios, it wasn’t the right fit for us.

🧪 Summary: Best Way to Copy Between Tables with Different Engines

🔧 Create a new table with the same schema, keys, and partitioning.
📥 Use ATTACH PARTITION FROM to copy data.
✅ Validate.
🗑️ Drop the original partition if needed.

This approach is clean, safe, and works even when the engines are different.

📚 Resources

🧡 Thanks for reading!
If you’ve used other approaches or faced gotchas while doing engine migrations in ClickHouse, let’s discuss in the comments!