How to ALTER the structure of an implicit ClickHouse Materialized View

Introduction

Say you need to ALTER a column in a Materialized View - change a type, widen a column, anything structural. If your MV was created without the TO clause (an implicit MV, where ClickHouse owns a hidden .inner table), you will quickly find out: you cannot do it directly.

This post shows the way around it: convert the implicit MV into an explicit one that writes into a target table you control, run your ALTER on that table, and rebuild the view - all without POPULATE, without re-aggregating the whole history, and without losing data.

Why you cannot ALTER an implicit MV directly

To change the schema of an implicit MV you would have to touch its hidden .inner table. But ALTER TABLE ... MODIFY COLUMN on that table changes the storage only - it does not update the MV's own column definition. The two drift apart, and reading through the MV name then breaks, because the view definition still believes in the old schema (this is a known issue).

So the structural change has to go through a rebuild of the MV. The trick is to rebuild it as an explicit MV, which also fixes the problem permanently: with TO, the column types live in the target table, so any future ALTER is a one-liner on that table with no MV rebuild at all.

Preparation

The setup this guide is based on:

• A Materialized View PayoutAggregate created without TO, engine AggregatingMergeTree.
• A source table PayoutStream feeding it.
• ClickHouse 21.12.
• An ingestion pipeline you can pause and replay, so nothing is lost during the window.

Before touching anything, do two things.

Rehearse on a copy. Run the whole cycle on a small throwaway MV of the same ClickHouse version first. Confirm that ATTACH succeeds after the rename and that the renamed table still has its data after DROP VIEW - behaviour around detached views differs between versions.

Stop the ingestion. Pause whatever feeds the source table. The upstream keeps accumulating, so nothing is lost - the MV just will not aggregate during the window, and you replay afterwards.

Find the inner table uuid. An implicit MV stores its data in a hidden table named .inner_id.<uuid> (Atomic database) or .inner.<name> (older Ordinary databases). You need its uuid for the rename:

SELECT uuid FROM system.tables
WHERE database = 'cpa' AND name = 'PayoutAggregate';

Step 1: Turn the inner table into a standalone one

The hidden .inner_id.<uuid> table can simply be renamed into a regular table. That detaches the data from the MV without moving a single byte - this is what saves you from POPULATE.

-- stop the trigger
DETACH TABLE cpa.PayoutAggregate;

-- rename the inner table into a normal one
RENAME TABLE `cpa`.`.inner_id.<uuid>` TO `cpa`.`PayoutAggregateData`;

-- verify the rows are there under the new name
SELECT count() FROM cpa.PayoutAggregateData;

Step 2: Run your ALTER on the standalone table

This is the whole point. PayoutAggregateData is now an ordinary AggregatingMergeTree table, so you can ALTER it however you need:

ALTER TABLE cpa.PayoutAggregateData
    MODIFY COLUMN your_column <NewType>;

Then check whether it ran as metadata-only or kicked off a real mutation (parts rewrite). The latter takes time and disk space on large tables:

SELECT command, is_done, parts_to_do
FROM system.mutations
WHERE database = 'cpa' AND table = 'PayoutAggregateData' AND is_done = 0;

An empty result means the change was metadata-only - instant. A non-empty one means parts are being rewritten; wait for it to finish before moving on, and check system.disks for free space.

Step 3: Rebuild the MV as explicit (TO)

After the rename the MV is a broken shell - its inner table is gone. Drop it and recreate it pointing at the standalone table. Remember to update the SELECT so its output types match the column you just altered.

-- bring the detached MV back so it can be dropped
ATTACH TABLE cpa.PayoutAggregate;

-- drop it (data is already safe in PayoutAggregateData)
DROP VIEW IF EXISTS cpa.PayoutAggregate SYNC;

-- recreate as an explicit MV over the renamed table
CREATE MATERIALIZED VIEW cpa.PayoutAggregate TO cpa.PayoutAggregateData
AS SELECT
    ...
FROM cpa.PayoutStream
...
GROUP BY ...;

Step 4: Verify nothing broke

-- the MV reads correctly through its name
SELECT * FROM cpa.PayoutAggregate LIMIT 5;

-- logical row count (FINAL, since count() without it is unstable in AggregatingMergeTree)
SELECT count() FROM cpa.PayoutAggregate FINAL;

-- the standalone table still holds the data
SELECT count() FROM cpa.PayoutAggregateData;

Step 5: Resume the ingestion

Unpause the pipeline. It catches up from upstream, and the MV resumes writing into PayoutAggregateData.

Conclusion

You cannot ALTER the structure of an implicit Materialized View directly - the inner table and the view definition drift apart. The fix is to rename the inner table into a standalone one, run your ALTER there, and rebuild the MV with an explicit TO target. The data survives without POPULATE, and from now on any structural change is just a plain ALTER on the target table.