创建异步物化视图提示分区异常

Viewed 39
mtmv 2.1

doris版本2.1.5。
在使用时有点疑惑，希望能帮忙解答一下。
创建了 test_a、test_b、test_c 三张表并写入了数据

CREATE TABLE `test_a` (	
  `order_key` VARCHAR(32) NOT NULL,
  `key_1` VARCHAR(255) NULL,
  `key_2` VARCHAR(255) NULL
) DUPLICATE KEY(`order_key`)
AUTO PARTITION BY LIST ( `order_key` ) ( ) 
DISTRIBUTED BY HASH ( `order_key` ) BUCKETS 1
PROPERTIES ( "replication_num" = "1" );

CREATE TABLE `test_b` (	
  `order_key` VARCHAR(32) NOT NULL,
  `key_3` VARCHAR(255) NULL,
  `key_4` VARCHAR(255) NULL
) DUPLICATE KEY(`order_key`)
AUTO PARTITION BY LIST ( `order_key` ) ( ) 
DISTRIBUTED BY HASH ( `order_key` ) BUCKETS 1
PROPERTIES ( "replication_num" = "1" );

CREATE TABLE `test_c` (	
  `order_key` VARCHAR(32) NOT NULL,
  `key_5` VARCHAR(255) NULL,
  `key_6` VARCHAR(255) NULL
) UNIQUE KEY(`order_key`)
AUTO PARTITION BY LIST ( `order_key` ) ( ) 
DISTRIBUTED BY HASH ( `order_key` ) BUCKETS 1
PROPERTIES ( "replication_num" = "1" );

INSERT INTO test_c (order_key, key_5, key_6)
VALUES ("order_1", "Emily", "25"), ("order_2", "Benjamin", "35");

INSERT INTO test_b (order_key, key_3, key_4)
VALUES ("order_1", "feelunique", "10"), ("order_1", "alibaba", "10");

INSERT INTO test_a (order_key, key_1, key_2)
VALUES ("order_2", "jd", "20"), ("order_2", "taobao", "20");

测试1
创建 test_a+test_c 数据的物化视图，并设置手动刷新，刷新方式为增量更新，按照test_a表order_key进行分区

CREATE MATERIALIZED VIEW test_a_mv BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (`order_key`) DISTRIBUTED BY RANDOM BUCKETS 5
PROPERTIES ("replication_num" = "1") 
AS SELECT a.order_key, a.key_1, a.key_2, c.key_5, c.key_6 FROM test_a a
LEFT JOIN test_c c ON a.order_key = c.order_key;

sql执行出现异常：

errCode = 2, detailMessage = Unable to find a suitable base table for partitioning, the fail reason is can't not find valid partition track column, because

如果减少查询中a表中的一列，比如 a.key_2，则能创建成功，不知道是什么原因。

已解决

CREATE MATERIALIZED VIEW test_a_mv BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (`order_key`) DISTRIBUTED BY RANDOM BUCKETS 5
PROPERTIES ("replication_num" = "1") 
AS SELECT a.order_key AS order_key, a.key_1, a.key_2, c.key_5, c.key_6 FROM test_a a
LEFT JOIN test_c c ON a.order_key = c.order_key;

测试2
创建 test_a+test_b+test_c 的物化视图，并设置手动刷新，刷新方式为增量更新，按照order_key进行分区

CREATE MATERIALIZED VIEW test_summary_mv BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (`order_key`) DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_num" = "1") 
AS
SELECT a.order_key, a.key_1, a.key_2, NULL AS key_3, NULL AS key_4, c.key_5, c.key_6
FROM test_a a LEFT JOIN test_c c ON a.order_key = c.order_key
UNION ALL
SELECT b.order_key, NULL AS key_1, NULL AS key_2, b.key_3, b.key_4, c.key_5, c.key_6
FROM test_b b LEFT JOIN test_c c ON b.order_key = c.order_key;

这种方式创建异步物化视图会报错

errCode = 2, detailMessage = Unable to find a suitable base table for partitioning, the fail reason is can't not find valid partition track column, because Unsupported plan operate in track partition, the invalid plan node is LogicalUnion

对于UNION ALL的查询是只能全量不能增量刷新吗？有没有其他的方式能支持的？

wwhjiaparts7
1 Answers

测试1，应该是个低版本的问题，高版本的已经修复了。
测试2，目前如果物化视图的定义sql 包含UNION ALL，暂时不支持构建分区的物化视图。可以使用UNION ALL的各个输入构建分区物化视图
比如

SELECT a.order_key, a.key_1, a.key_2, NULL AS key_3, NULL AS key_4, c.key_5, c.key_6
FROM test_a a LEFT JOIN test_c c ON a.order_key = c.order_key
UNION ALL
SELECT b.order_key, NULL AS key_1, NULL AS key_2, b.key_3, b.key_4, c.key_5, c.key_6
FROM test_b b LEFT JOIN test_c c ON b.order_key = c.order_key;

可以分别基于

CREATE MATERIALIZED VIEW test_mv1 BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (`order_key`) DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_num" = "1") 
AS
SELECT a.order_key, a.key_1, a.key_2, NULL AS key_3, NULL AS key_4, c.key_5, c.key_6
FROM test_a a LEFT JOIN test_c c ON a.order_key = c.order_key
b.order_key = c.order_key;

和

CREATE MATERIALIZED VIEW test_mv2 BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (`order_key`) DISTRIBUTED BY RANDOM BUCKETS 1
PROPERTIES ("replication_num" = "1") 
AS
SELECT b.order_key, NULL AS key_1, NULL AS key_2, b.key_3, b.key_4, c.key_5, c.key_6
FROM test_b b LEFT JOIN test_c c ON b.order_key = c.order_key;

构建物化视图，再基于以上物化视图构建普通视图（union all的性能还是可以的）

CREATE VIEW test_summary_mv 
AS
SELECT a.order_key, a.key_1, a.key_2, NULL AS key_3, NULL AS key_4, c.key_5, c.key_6
from
test_mv1
UNION ALL
SELECT b.order_key, NULL AS key_1, NULL AS key_2, b.key_3, b.key_4, c.key_5, c.key_6
from
test_mv2;
seawinde101