ALTER TABLE … MODIFY QUERY Statement

You can modify SELECT query that was specified when a materialized view was created with the ALTER TABLE … MODIFY QUERY statement. Use it when the materialized view was created without the TO [db.]name clause. The allow_experimental_alter_materialized_view_structure setting must be enabled.

If a materialized view uses the TO [db.]name construction, you must DETACH the view, run ALTER TABLE query for the target table, and then ATTACH the previously detached (DETACH) view.

Example

  1. CREATE TABLE src_table (`a` UInt32) ENGINE = MergeTree ORDER BY a;
  2. CREATE MATERIALIZED VIEW mv (`a` UInt32) ENGINE = MergeTree ORDER BY a AS SELECT a FROM src_table; 
  3. INSERT INTO src_table (a) VALUES (1), (2);
  4. SELECT * FROM mv;
  1. ┌─a─┐
  2. │ 1 │
  3. │ 2 │
  4. └───┘
  1. ALTER TABLE mv MODIFY QUERY SELECT a * 2 as a FROM src_table;
  2. INSERT INTO src_table (a) VALUES (3), (4);
  3. SELECT * FROM mv;
  1. ┌─a─┐
  2. │ 6 │
  3. │ 8 │
  4. └───┘
  5. ┌─a─┐
  6. │ 1 │
  7. │ 2 │
  8. └───┘

ALTER LIVE VIEW Statement

ALTER LIVE VIEW ... REFRESH statement refreshes a Live view. See Force Live View Refresh.