- TechDailyFeed - Your Daily Dose of Tech News, AI, Programming, and More

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.

Apr 17, 2025 - 01:36

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.