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的查询是只能全量不能增量刷新吗?有没有其他的方式能支持的?