MV alter

MV_alter.txt

There are two options - implicit storages in MV (MV without TO) and explicit (table created in advance) (MV with TO).

And I have not been using MV without TO for a long time. Because it’s much more obvious and convenient to stupidly drop MV and create a new one.


# alter MV with implicit storage .inner (without TO) 
1. stop ingestion 
2. detach table MVX
3. alter table `.inner.MVX` add column ..... 
4. attach MATERIALIZED VIEW MVX as select new_SELECT_.....
5. start ingestion 

# Table in which MV stores data created explicitly (MV with TO)
1. stop ingestion
2. alter table to_таблица add column .....
3. drop MVX; create MVX to ... as 
4. start ingestion

# how to convert MV implicit storage .inner (without TO) to explicit storage (with TO)
1. stop ingestion 
2. detach table MVX
3. rename table `.inner.MVX` to MVX_store;
4. create table `.inner.MVX` as MVX_store engine=TinyLog;
5. attach table MVX
6. drop table MVX
7. rename table MVX_store to MVX
8. create materialized view MVX_mv to MVX as select ....
9. start ingestion