ALTER TABLE ... MODIFY QUERY 语句
您可以修改在创建物化视图时使用ALTER TABLE ... MODIFY QUERY语句指定的SELECT查询，而不会中断数据摄取过程。
此命令用于更改使用TO [db.]name子句创建的物化视图。它不会更改底层存储表的结构，也不会更改物化视图的列定义，因此，对于没有使用TO [db.]name子句创建的物化视图，此命令的应用非常有限。
带有 TO 表的示例
CREATE TABLE events (ts DateTime, event_type String)
ENGINE = MergeTree ORDER BY (event_type, ts);
CREATE TABLE events_by_day (ts DateTime, event_type String, events_cnt UInt64)
ENGINE = SummingMergeTree ORDER BY (event_type, ts);
CREATE MATERIALIZED VIEW mv TO events_by_day AS
SELECT toStartOfDay(ts) ts, event_type, count() events_cnt
FROM events
GROUP BY ts, event_type;
INSERT INTO events
SELECT Date '2020-01-01' + interval number * 900 second,
['imp', 'click'][number%2+1]
FROM numbers(100);
SELECT ts, event_type, sum(events_cnt)
FROM events_by_day
GROUP BY ts, event_type
ORDER BY ts, event_type;
┌──────────────────ts─┬─event_type─┬─sum(events_cnt)─┐
│ 2020-01-01 00:00:00 │ click │ 48 │
│ 2020-01-01 00:00:00 │ imp │ 48 │
│ 2020-01-02 00:00:00 │ click │ 2 │
│ 2020-01-02 00:00:00 │ imp │ 2 │
└─────────────────────┴────────────┴─────────────────┘
-- Let's add the new measurement `cost`
-- and the new dimension `browser`.
ALTER TABLE events
ADD COLUMN browser String,
ADD COLUMN cost Float64;
-- Column do not have to match in a materialized view and TO
-- (destination table), so the next alter does not break insertion.
ALTER TABLE events_by_day
ADD COLUMN cost Float64,
ADD COLUMN browser String after event_type,
MODIFY ORDER BY (event_type, ts, browser);
INSERT INTO events
SELECT Date '2020-01-02' + interval number * 900 second,
['imp', 'click'][number%2+1],
['firefox', 'safary', 'chrome'][number%3+1],
10/(number+1)%33
FROM numbers(100);
-- New columns `browser` and `cost` are empty because we did not change Materialized View yet.
SELECT ts, event_type, browser, sum(events_cnt) events_cnt, round(sum(cost),2) cost
FROM events_by_day
GROUP BY ts, event_type, browser
ORDER BY ts, event_type;
┌──────────────────ts─┬─event_type─┬─browser─┬─events_cnt─┬─cost─┐
│ 2020-01-01 00:00:00 │ click │ │ 48 │ 0 │
│ 2020-01-01 00:00:00 │ imp │ │ 48 │ 0 │
│ 2020-01-02 00:00:00 │ click │ │ 50 │ 0 │
│ 2020-01-02 00:00:00 │ imp │ │ 50 │ 0 │
│ 2020-01-03 00:00:00 │ click │ │ 2 │ 0 │
│ 2020-01-03 00:00:00 │ imp │ │ 2 │ 0 │
└─────────────────────┴────────────┴─────────┴────────────┴──────┘
ALTER TABLE mv MODIFY QUERY
SELECT toStartOfDay(ts) ts, event_type, browser,
count() events_cnt,
sum(cost) cost
FROM events
GROUP BY ts, event_type, browser;
INSERT INTO events
SELECT Date '2020-01-03' + interval number * 900 second,
['imp', 'click'][number%2+1],
['firefox', 'safary', 'chrome'][number%3+1],
10/(number+1)%33
FROM numbers(100);
SELECT ts, event_type, browser, sum(events_cnt) events_cnt, round(sum(cost),2) cost
FROM events_by_day
GROUP BY ts, event_type, browser
ORDER BY ts, event_type;
┌──────────────────ts─┬─event_type─┬─browser─┬─events_cnt─┬──cost─┐
│ 2020-01-01 00:00:00 │ click │ │ 48 │ 0 │
│ 2020-01-01 00:00:00 │ imp │ │ 48 │ 0 │
│ 2020-01-02 00:00:00 │ click │ │ 50 │ 0 │
│ 2020-01-02 00:00:00 │ imp │ │ 50 │ 0 │
│ 2020-01-03 00:00:00 │ click │ firefox │ 16 │ 6.84 │
│ 2020-01-03 00:00:00 │ click │ │ 2 │ 0 │
│ 2020-01-03 00:00:00 │ click │ safary │ 16 │ 9.82 │
│ 2020-01-03 00:00:00 │ click │ chrome │ 16 │ 5.63 │
│ 2020-01-03 00:00:00 │ imp │ │ 2 │ 0 │
│ 2020-01-03 00:00:00 │ imp │ firefox │ 16 │ 15.14 │
│ 2020-01-03 00:00:00 │ imp │ safary │ 16 │ 6.14 │
│ 2020-01-03 00:00:00 │ imp │ chrome │ 16 │ 7.89 │
│ 2020-01-04 00:00:00 │ click │ safary │ 1 │ 0.1 │
│ 2020-01-04 00:00:00 │ click │ firefox │ 1 │ 0.1 │
│ 2020-01-04 00:00:00 │ imp │ firefox │ 1 │ 0.1 │
│ 2020-01-04 00:00:00 │ imp │ chrome │ 1 │ 0.1 │
└─────────────────────┴────────────┴─────────┴────────────┴───────┘
-- !!! During `MODIFY ORDER BY` PRIMARY KEY was implicitly introduced.
SHOW CREATE TABLE events_by_day FORMAT TSVRaw
CREATE TABLE test.events_by_day
(
`ts` DateTime,
`event_type` String,
`browser` String,
`events_cnt` UInt64,
`cost` Float64
)
ENGINE = SummingMergeTree
PRIMARY KEY (event_type, ts)
ORDER BY (event_type, ts, browser)
SETTINGS index_granularity = 8192
-- !!! The columns' definition is unchanged but it does not matter, we are not querying
-- MATERIALIZED VIEW, we are querying TO (storage) table.
-- SELECT section is updated.
SHOW CREATE TABLE mv FORMAT TSVRaw;
CREATE MATERIALIZED VIEW test.mv TO test.events_by_day
(
`ts` DateTime,
`event_type` String,
`events_cnt` UInt64
) AS
SELECT
toStartOfDay(ts) AS ts,
event_type,
browser,
count() AS events_cnt,
sum(cost) AS cost
FROM test.events
GROUP BY
ts,
event_type,
browser
没有 TO 表的示例
应用范围非常有限，因为您只能更改SELECT部分，而不能添加新列。
CREATE TABLE src_table (`a` UInt32) ENGINE = MergeTree ORDER BY a;
CREATE MATERIALIZED VIEW mv (`a` UInt32) ENGINE = MergeTree ORDER BY a AS SELECT a FROM src_table;
INSERT INTO src_table (a) VALUES (1), (2);
SELECT * FROM mv;
┌─a─┐
│ 1 │
│ 2 │
└───┘
ALTER TABLE mv MODIFY QUERY SELECT a * 2 as a FROM src_table;
INSERT INTO src_table (a) VALUES (3), (4);
SELECT * FROM mv;
┌─a─┐
│ 6 │
│ 8 │
└───┘
┌─a─┐
│ 1 │
│ 2 │
└───┘
ALTER LIVE VIEW 语句
ALTER LIVE VIEW ... REFRESH语句刷新实时视图。请参阅强制刷新实时视图。