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)
-- !!! 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 TABLE ... MODIFY REFRESH 语句
ALTER TABLE ... MODIFY REFRESH 用于修改可刷新materialized view的刷新参数，包括调度计划、依赖关系、随机化以及刷新设置。
ALTER TABLE [db.]name MODIFY REFRESH EVERY|AFTER ... [RANDOMIZE FOR ...] [DEPENDS ON ...] [SETTINGS ...]
调度 (EVERY 或 AFTER) 是必填项：该语句会一次性替换所有刷新参数。任何未指定的子句——RANDOMIZE FOR、DEPENDS ON 或 SETTINGS——都会被移除或重置为基本值。若只想修改刷新设置，请重复写出当前调度。
-- Change the schedule.
ALTER TABLE rmv MODIFY REFRESH EVERY 30 MINUTE;
-- Change retry settings (schedule must be repeated).
ALTER TABLE rmv MODIFY REFRESH EVERY 1 HOUR
SETTINGS refresh_retries = 5,
refresh_retry_initial_backoff_ms = 500,
refresh_retry_max_backoff_ms = 60000;
-- Add or keep a dependency.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR DEPENDS ON other_rmv;
-- Drop the dependency by omitting `DEPENDS ON`.
ALTER TABLE rmv MODIFY REFRESH EVERY 6 HOUR;
限制：
- 不支持对 materialized view 使用
ALTER TABLE ... MODIFY SETTING；刷新设置只能通过MODIFY REFRESH修改。 - 不支持添加或移除
APPEND。 - 视图创建后，无法再修改
all_replicas刷新设置。
完整的刷新设置列表见 Refresh Settings。刷新状态 (包括当前生效的设置) 可在 system.view_refreshes 中查看。
