建表语句:
CREATE TABLE test
(
a
TINYINT NULL DEFAULT "0",
b
INT NULL DEFAULT "0",
c
INT NULL DEFAULT "0",
t
DATETIME NOT NULL COMMENT '',
d
BIGINT SUM NULL DEFAULT "0",
e
BIGINT SUM NULL DEFAULT "0"
)ENGINE = OLAP AGGREGATE KEY
(a,b,c,t)
PARTITION BY RANGE(t
) ()
DISTRIBUTED BY random buckets 1
PROPERTIES
(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "HOUR",
"dynamic_partition.start" = "-3",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "1",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "3",
"light_schema_change" = "true"
);
随便插入一条记录:
insert into test VALUES (1,1,1,'2024-07-27 19:30:00', 11, 11);
创建异步物化视图:
CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 1 MINUTE
PARTITION BY (t)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"partition_sync_limit" = "1",
"grace_period"="300",
"partition_date_format" = "%Y%m%d%H",
"replication_num" = "2"
)
AS
SELECT a,b,t,SUM(d),SUM(e) FROM test GROUP BY 1,2,3;
查看命中情况: explain select a,t, SUM(d), SUM(e) FROM test WHERE t <= '2024-07-27 19:30:00' GROUP BY 1,2;
提示 MaterializedViewRewriteSuccessButNotChose
执行 explain select a,t, SUM(IF(a=0,d,e)) FROM test WHERE t <= '2024-07-27 19:30:00' GROUP BY 1,2; (使用IF函数):
提示 MaterializedViewRewriteFail: FailSummary: View struct info is invalid, View dimensions doesn't not cover the query dimensions, Query function roll up fail, Query struct info is invalid
有几个疑问:
- MaterializedViewRewriteSuccessAndChose 和 MaterializedViewRewriteSuccessButNotChose 分别在什么情况下出现?
- 异步物化视图透明改写是否不支持 IF 函数, 异步物化视图都已经覆盖查询需要的所有字段, 为什么还会报这个错。