Skip to content

Instantly share code, notes, and snippets.

@den-crane
Last active September 7, 2021 07:40
Show Gist options
  • Save den-crane/431010ca08b9e51b960e55344b1dbbe3 to your computer and use it in GitHub Desktop.
Save den-crane/431010ca08b9e51b960e55344b1dbbe3 to your computer and use it in GitHub Desktop.
MV alter
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment