在建立物化视图的时候发生错误,请问有哪些注意点?
Freedom_战戮 发布于2021-06 浏览:2604 回复:1
0
收藏

错误如下:

1. errCode = 2, detailMessage = The mv column of agg or uniq table cannot be transformed from original column[user_id]

2. errCode = 2, detailMessage = The aggregation type of column[in_app_time] must be same as the aggregate type of base column in aggregate table

3. errCode = 2, detailMessage = Duplicate column name 'in_app_time'

原表表结构 

CREATE TABLE `dwm_user_app_online_time_mv` (
`user_id` int(11) NULL COMMENT "",
`login_time` datetime NULL COMMENT "",
`logout_time` datetime NULL COMMENT "",
`login_date` date NULL COMMENT "",
`in_app_time` int(11) NULL COMMENT ""

) ENGINE=OLAP
UNIQUE KEY(`user_id`, `login_time`, `logout_time`, login_date, in_app_time)
COMMENT "OLAP"
PARTITION BY RANGE(`login_time`)
(PARTITION p20210324 VALUES [('2021-03-24 00:00:00'), ('2021-03-25 00:00:00')),
PARTITION p20210325 VALUES [('2021-03-25 00:00:00'), ('2021-03-26 00:00:00')),
PARTITION p20210326 VALUES [('2021-03-26 00:00:00'), ('2021-03-27 00:00:00')),
PARTITION p20210327 VALUES [('2021-03-27 00:00:00'), ('2021-03-28 00:00:00')),
PARTITION p20210328 VALUES [('2021-03-28 00:00:00'), ('2021-03-29 00:00:00')),
PARTITION p20210329 VALUES [('2021-03-29 00:00:00'), ('2021-03-30 00:00:00')),
PARTITION p20210330 VALUES [('2021-03-30 00:00:00'), ('2021-03-31 00:00:00')),
PARTITION p20210331 VALUES [('2021-03-31 00:00:00'), ('2021-04-01 00:00:00')),
PARTITION p20210401 VALUES [('2021-04-01 00:00:00'), ('2021-04-02 00:00:00')),
PARTITION p20210402 VALUES [('2021-04-02 00:00:00'), ('2021-04-03 00:00:00')),
PARTITION p20210403 VALUES [('2021-04-03 00:00:00'), ('2021-04-04 00:00:00')),
PARTITION p20210404 VALUES [('2021-04-04 00:00:00'), ('2021-04-05 00:00:00')),
PARTITION p20210405 VALUES [('2021-04-05 00:00:00'), ('2021-04-06 00:00:00')),
PARTITION p20210406 VALUES [('2021-04-06 00:00:00'), ('2021-04-07 00:00:00')),
PARTITION p20210407 VALUES [('2021-04-07 00:00:00'), ('2021-04-08 00:00:00')),
PARTITION p20210408 VALUES [('2021-04-08 00:00:00'), ('2021-04-09 00:00:00')),
PARTITION p20210409 VALUES [('2021-04-09 00:00:00'), ('2021-04-10 00:00:00')),
PARTITION p20210410 VALUES [('2021-04-10 00:00:00'), ('2021-04-11 00:00:00')),
PARTITION p20210411 VALUES [('2021-04-11 00:00:00'), ('2021-04-12 00:00:00')),
PARTITION p20210412 VALUES [('2021-04-12 00:00:00'), ('2021-04-13 00:00:00')),
PARTITION p20210413 VALUES [('2021-04-13 00:00:00'), ('2021-04-14 00:00:00')),
PARTITION p20210414 VALUES [('2021-04-14 00:00:00'), ('2021-04-15 00:00:00')),
PARTITION p20210415 VALUES [('2021-04-15 00:00:00'), ('2021-04-16 00:00:00')),
PARTITION p20210416 VALUES [('2021-04-16 00:00:00'), ('2021-04-17 00:00:00')),
PARTITION p20210417 VALUES [('2021-04-17 00:00:00'), ('2021-04-18 00:00:00')),
PARTITION p20210418 VALUES [('2021-04-18 00:00:00'), ('2021-04-19 00:00:00')),
PARTITION p20210419 VALUES [('2021-04-19 00:00:00'), ('2021-04-20 00:00:00')),
PARTITION p20210420 VALUES [('2021-04-20 00:00:00'), ('2021-04-21 00:00:00')),
PARTITION p20210421 VALUES [('2021-04-21 00:00:00'), ('2021-04-22 00:00:00')),
PARTITION p20210422 VALUES [('2021-04-22 00:00:00'), ('2021-04-23 00:00:00')),
PARTITION p20210423 VALUES [('2021-04-23 00:00:00'), ('2021-04-24 00:00:00')),
PARTITION p20210424 VALUES [('2021-04-24 00:00:00'), ('2021-04-25 00:00:00')),
PARTITION p20210425 VALUES [('2021-04-25 00:00:00'), ('2021-04-26 00:00:00')),
PARTITION p20210426 VALUES [('2021-04-26 00:00:00'), ('2021-04-27 00:00:00')),
PARTITION p20210427 VALUES [('2021-04-27 00:00:00'), ('2021-04-28 00:00:00')),
PARTITION p20210428 VALUES [('2021-04-28 00:00:00'), ('2021-04-29 00:00:00')),
PARTITION p20210429 VALUES [('2021-04-29 00:00:00'), ('2021-04-30 00:00:00')),
PARTITION p20210430 VALUES [('2021-04-30 00:00:00'), ('2021-05-01 00:00:00')),
PARTITION p20210501 VALUES [('2021-05-01 00:00:00'), ('2021-05-02 00:00:00')),
PARTITION p20210502 VALUES [('2021-05-02 00:00:00'), ('2021-05-03 00:00:00')),
PARTITION p20210503 VALUES [('2021-05-03 00:00:00'), ('2021-05-04 00:00:00')),
PARTITION p20210504 VALUES [('2021-05-04 00:00:00'), ('2021-05-05 00:00:00')),
PARTITION p20210505 VALUES [('2021-05-05 00:00:00'), ('2021-05-06 00:00:00')),
PARTITION p20210506 VALUES [('2021-05-06 00:00:00'), ('2021-05-07 00:00:00')),
PARTITION p20210507 VALUES [('2021-05-07 00:00:00'), ('2021-05-08 00:00:00')),
PARTITION p20210508 VALUES [('2021-05-08 00:00:00'), ('2021-05-09 00:00:00')),
PARTITION p20210509 VALUES [('2021-05-09 00:00:00'), ('2021-05-10 00:00:00')),
PARTITION p20210510 VALUES [('2021-05-10 00:00:00'), ('2021-05-11 00:00:00')),
PARTITION p20210511 VALUES [('2021-05-11 00:00:00'), ('2021-05-12 00:00:00')),
PARTITION p20210512 VALUES [('2021-05-12 00:00:00'), ('2021-05-13 00:00:00')),
PARTITION p20210513 VALUES [('2021-05-13 00:00:00'), ('2021-05-14 00:00:00')),
PARTITION p20210514 VALUES [('2021-05-14 00:00:00'), ('2021-05-15 00:00:00')),
PARTITION p20210515 VALUES [('2021-05-15 00:00:00'), ('2021-05-16 00:00:00')),
PARTITION p20210516 VALUES [('2021-05-16 00:00:00'), ('2021-05-17 00:00:00')),
PARTITION p20210517 VALUES [('2021-05-17 00:00:00'), ('2021-05-18 00:00:00')),
PARTITION p20210518 VALUES [('2021-05-18 00:00:00'), ('2021-05-19 00:00:00')),
PARTITION p20210519 VALUES [('2021-05-19 00:00:00'), ('2021-05-20 00:00:00')),
PARTITION p20210520 VALUES [('2021-05-20 00:00:00'), ('2021-05-21 00:00:00')),
PARTITION p20210521 VALUES [('2021-05-21 00:00:00'), ('2021-05-22 00:00:00')),
PARTITION p20210522 VALUES [('2021-05-22 00:00:00'), ('2021-05-23 00:00:00')),
PARTITION p20210523 VALUES [('2021-05-23 00:00:00'), ('2021-05-24 00:00:00')),
PARTITION p20210524 VALUES [('2021-05-24 00:00:00'), ('2021-05-25 00:00:00')),
PARTITION p20210525 VALUES [('2021-05-25 00:00:00'), ('2021-05-26 00:00:00')),
PARTITION p20210526 VALUES [('2021-05-26 00:00:00'), ('2021-05-27 00:00:00')),
PARTITION p20210527 VALUES [('2021-05-27 00:00:00'), ('2021-05-28 00:00:00')),
PARTITION p20210528 VALUES [('2021-05-28 00:00:00'), ('2021-05-29 00:00:00')),
PARTITION p20210529 VALUES [('2021-05-29 00:00:00'), ('2021-05-30 00:00:00')),
PARTITION p20210530 VALUES [('2021-05-30 00:00:00'), ('2021-05-31 00:00:00')),
PARTITION p20210531 VALUES [('2021-05-31 00:00:00'), ('2021-06-01 00:00:00')),
PARTITION p20210601 VALUES [('2021-06-01 00:00:00'), ('2021-06-02 00:00:00')),
PARTITION p20210602 VALUES [('2021-06-02 00:00:00'), ('2021-06-03 00:00:00')),
PARTITION p20210603 VALUES [('2021-06-03 00:00:00'), ('2021-06-04 00:00:00')),
PARTITION p20210604 VALUES [('2021-06-04 00:00:00'), ('2021-06-05 00:00:00')),
PARTITION p20210605 VALUES [('2021-06-05 00:00:00'), ('2021-06-06 00:00:00')),
PARTITION p20210606 VALUES [('2021-06-06 00:00:00'), ('2021-06-07 00:00:00')),
PARTITION p20210607 VALUES [('2021-06-07 00:00:00'), ('2021-06-08 00:00:00')),
PARTITION p20210608 VALUES [('2021-06-08 00:00:00'), ('2021-06-09 00:00:00')),
PARTITION p20210609 VALUES [('2021-06-09 00:00:00'), ('2021-06-10 00:00:00')),
PARTITION p20210610 VALUES [('2021-06-10 00:00:00'), ('2021-06-11 00:00:00')),
PARTITION p20210611 VALUES [('2021-06-11 00:00:00'), ('2021-06-12 00:00:00')),
PARTITION p20210612 VALUES [('2021-06-12 00:00:00'), ('2021-06-13 00:00:00')),
PARTITION p20210613 VALUES [('2021-06-13 00:00:00'), ('2021-06-14 00:00:00')),
PARTITION p20210614 VALUES [('2021-06-14 00:00:00'), ('2021-06-15 00:00:00')),
PARTITION p20210615 VALUES [('2021-06-15 00:00:00'), ('2021-06-16 00:00:00')),
PARTITION p20210616 VALUES [('2021-06-16 00:00:00'), ('2021-06-17 00:00:00')),
PARTITION p20210617 VALUES [('2021-06-17 00:00:00'), ('2021-06-18 00:00:00')),
PARTITION p20210618 VALUES [('2021-06-18 00:00:00'), ('2021-06-19 00:00:00')),
PARTITION p20210619 VALUES [('2021-06-19 00:00:00'), ('2021-06-20 00:00:00')),
PARTITION p20210620 VALUES [('2021-06-20 00:00:00'), ('2021-06-21 00:00:00')),
PARTITION p20210621 VALUES [('2021-06-21 00:00:00'), ('2021-06-22 00:00:00')),
PARTITION p20210622 VALUES [('2021-06-22 00:00:00'), ('2021-06-23 00:00:00')),
PARTITION p20210623 VALUES [('2021-06-23 00:00:00'), ('2021-06-24 00:00:00')),
PARTITION p20210624 VALUES [('2021-06-24 00:00:00'), ('2021-06-25 00:00:00')),
PARTITION p20210625 VALUES [('2021-06-25 00:00:00'), ('2021-06-26 00:00:00')))
DISTRIBUTED BY HASH(`user_id`) BUCKETS 5
PROPERTIES (
"replication_num" = "1",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "7",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_num" = "1",
"dynamic_partition.buckets" = "5",
"in_memory" = "false",
"storage_format" = "V2"
);

收藏
点赞
0
个赞
共1条回复 最后由Ling缪回复于2021-06
#2Ling缪回复于2021-06

1.  The mv column of agg or uniq table cannot be transformed from original column[user_id]

对于uniq 模型的物化视图来说,只能调整顺序,不能建立上卷表。

2. The aggregation type of column[in_app_time] must be same as the aggregate type of base column in aggregate table

不可以对 uniq 模型的value 列进行聚合物化视图

3. Duplicate column name 'in_app_time' 

不要在建物化视图的时候重复声明列名

0
快速回复
TOP
切换版块