在建立物化视图的时候发生错误，请问有哪些注意点？

首页版块访问AI主站注册发帖

Freedom_战戮发布于2021-06 浏览:2547 回复:1

在建立物化视图的时候发生错误，请问有哪些注意点？

快速回复

错误如下：

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"
);

技术问答

个赞

共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'

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

快速回复

TOP

操作指南

常见问答

平台公告

经验交流

技术专区

文字识别

人脸识别

语音技术

PaddlePaddle

EasyDL

BML

EasyData

AI Studio

UNIT

人体分析

图像搜索

图像识别

内容审核

自然语言处理

机器人视觉

视频技术

增强现实

知识图谱

智能创作

智能呼叫中心

文心

EdgeBoard

DuerOS

EasyEdge

度目硬件

百度AI市场

Doris

AI赛事

百度之星大赛

AI Studio人工智能竞赛

语言与智能技术竞赛

千言数据集

集思广益

共享工具

头脑风暴

成果展示

智能客服