How does ALTER on MATERIALIZED VIEWs work?

[mvav...@cloudflare.com]

Hi,

Say I have a  MATERIALIZED VIEW with AggregatingMergeTree engine, I'm able to ALTER the underlying table (as it's MergeTree), however I'm not able to alter MV itself

(change pkey or group by). Is there a way how to go around this without losing data? From documentation I understand that ALTER isn't implemented on MVs yet, which is fine. So I'm thinking something like:

1. detach the MV from the table

2. create new MV with new schema

3. insert from old MV into new one

4. delete the old MV

Would something like this be feasible?

Cheers,

Marek

[tatiana....@revjet.com]

Your plan will work.

Or you can do something like this:

1. Create a table (let's call it tempMV) with the same schema as the MV

2. Detach all partitions from the MV and attach them to the tempMV table

3. Alter the tempMV table

4. Drop the old MV and create a new MV with the new schema

5. Detach all partitions from the tempMV table and attach them to the new MV

[mvav...@cloudflare.com]

That's a neat idea with turning MV into regular table and then back.

Is (2) doable without copying/moving the parts from detached directory manually?

As far as I know, it's not possible to attach parts from a different directory or table.

Something like ALTER TABLE tempMV ATTACH PARTITION '/data/.../default/oldmv/detached/*'

Marek

[tatiana....@revjet.com]

> Is (2) doable without copying/moving the parts from detached directory manually?

I don't think so

[tatiana....@revjet.com]

The best way to alter a materialized view is this:

1. Detach the old MV

2. Alter the .inner. table

3. Attach the new MV with the new schema