多表建立异步物化视图创建异常

💬 StarRocks 用户问答 功能使用相关
#1

【详述】问题详细描述
两个明细表join建立异步刷新物化视图报错
明细表建表sql:
CREATE TABLE biz_diagnose_result_detail_tmp (
book_day date COMMENT ‘检查时间day yyyy-MM-dd’,
vid varchar(12),
id bigint ,
result_id bigint(18) DEFAULT NULL,
diagnose_result varchar(600) DEFAULT NULL,
diagnose_result_comm varchar(600) DEFAULT NULL,
question_type int(1) DEFAULT NULL,
check_time datetime DEFAULT NULL COMMENT ‘登记时间’,
book_time datetime DEFAULT NULL COMMENT ‘预约时间’
)
DUPLICATE KEY(book_day)
PARTITION BY RANGE (book_day) (
START (“2016-01-01”) END (“2023-12-31”) EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(vid) BUCKETS 8
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

CREATE TABLE biz_reg_customer_tmp (
book_day date COMMENT ‘检查时间day yyyy-MM-dd’,
shop_no char(2) DEFAULT NULL COMMENT ‘门店编号’,
vid varchar(12) DEFAULT NULL COMMENT ‘体检流水号’,
id int(11) NOT NULL COMMENT ‘编号’,
cid varchar(12) DEFAULT NULL COMMENT ‘体检案例编号’,
name varchar(50) DEFAULT NULL COMMENT ‘姓名’,
sex varchar(2) DEFAULT NULL COMMENT '1:男 0：女 ',
birth_date datetime DEFAULT NULL COMMENT ‘出生日期’,
job varchar(20) DEFAULT NULL COMMENT ‘客户职业’,
job_industry varchar(20) DEFAULT NULL COMMENT ‘工作行业’,
book_time datetime DEFAULT NULL COMMENT ‘预约时间’,
book_person varchar(6) DEFAULT NULL COMMENT ‘预约登记人’,
book_reg_time datetime DEFAULT NULL COMMENT ‘预约登记时间’,
status char(1) DEFAULT NULL COMMENT ‘状态’,
other_reg_person varchar(6) DEFAULT NULL COMMENT ‘其他登记人’,
other_reg_time datetime DEFAULT NULL COMMENT ‘其他登记时间’,
body_check_time datetime DEFAULT NULL COMMENT ‘体检时间’,
body_check_org_time datetime DEFAULT NULL COMMENT ‘体检组织时间’,
member_type varchar(2) DEFAULT NULL COMMENT ‘会员类型’,
print_time datetime DEFAULT NULL COMMENT ‘打印时间’,
age int(3) DEFAULT NULL COMMENT ‘年龄’,
age_discrete int(1) DEFAULT NULL COMMENT ‘年龄离散化’,
clean_status char(1) DEFAULT NULL COMMENT ‘清洗状态：0：确认后数据、1：未能识别数据、2：机器识别数据’,
company_code varchar(20) DEFAULT NULL COMMENT ‘单位编码’,
department varchar(200) DEFAULT NULL COMMENT ‘部门’,
id_card varchar(50) DEFAULT NULL COMMENT ‘身份证号’,
clean_date datetime DEFAULT NULL COMMENT ‘清洗时间’,
INDEX sex_index (sex) USING BITMAP COMMENT ‘性别bitmap索引’

)
DUPLICATE KEY(book_day,shop_no)
PARTITION BY RANGE (book_day) (
START (“2016-01-01”) END (“2023-12-31”) EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(vid) BUCKETS 8
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

物化视图创建sql:
CREATE MATERIALIZED VIEW customer_anyc1
DISTRIBUTED BY HASH(rc.shop_no) BUCKETS 5
REFRESH ASYNC START(‘2023-01-04 10:00:00’) EVERY (interval 1 hour)
AS
select
rc.book_day as bookDay,
rc.shop_no as shopNo,
dr.diagnose_result as diagnoseResult,
count( case when rc.age<18 then 1 end) as childCount,
count( case when rc.age between 18 and 50 then 1 end ) as youngCount,
count( case when rc.age > 50 then 1 end ) as oldCount,
bitmap_union(distinct rc.id_card) as diagnoseCount,
count(case when rc.sex =1 then 1 end ) as diagnoseManCount,
count(case when rc.sex =0 then 1 end ) as diagnoseWomenCount
from biz_diagnose_result_detail_tmp dr
left join biz_reg_customer_tmp rc
on rc.vid = dr.vid group by rc.book_day,rc.shop_no,dr.diagnose_result order by rc.book_day;

【背景】

image
image788×933 51.3 KB

【业务影响】
【StarRocks版本】 2.4.0
【集群规模】单机 1fe+1be
【附件】
image
image1471×520 29.7 KB

#2

您参考下以下命令创建物化视图,bitmap_union 仅支持bitmap类型。其中注释部分为您原sql内容

CREATE MATERIALIZED VIEW customer_anyc1

--DISTRIBUTED BY HASH(rc.shop_no) BUCKETS 5

DISTRIBUTED BY HASH(shopNo) BUCKETS 5

--REFRESH ASYNC START('2023-01-04 10:00:00') EVERY (interval 1 hour)

REFRESH ASYNC START('2023-01-05 10:00:00') EVERY (interval 1 hour)

AS

select

rc.book_day as bookDay,

rc.shop_no as shopNo,

dr.diagnose_result as diagnoseResult,

count( case when rc.age<18 then 1 end) as childCount,

count( case when rc.age between 18 and 50 then 1 end ) as youngCount,

count( case when rc.age > 50 then 1 end ) as oldCount,

-- bitmap_union(distinct rc.id_card) as diagnoseCount,

count(distinct rc.id_card) as diagnoseCount,

count(case when rc.sex =1 then 1 end ) as diagnoseManCount,

count(case when rc.sex =0 then 1 end ) as diagnoseWomenCount

from biz_diagnose_result_detail_tmp dr

left join biz_reg_customer_tmp rc

on rc.vid = dr.vid group by rc.book_day,rc.shop_no,dr.diagnose_result order by rc.book_day;```
#3

谢谢 count函数试了还是不行，想咨询一下，分桶键是不是要取分组字段其中的一个啊

#4

问了技术群的大佬说是别名的问题 这边试试看

#5

我使用发您的sql是可以执行成功，您执行的报错是什么？分桶键必须是您select 语句中查询的字段，如上面的shopNo

#6

执行成功了，谢谢

#7

@dongquan 还想咨询一下，建完这个异步视图之后，执行分析
explain select

rc.book_day as bookDay,

rc.shop_no as shopNo,

dr.diagnose_result as diagnoseResult,

count( case when rc.age<18 then 1 end) as childCount,

count( case when rc.age between 18 and 50 then 1 end ) as youngCount,

count( case when rc.age > 50 then 1 end ) as oldCount,

– bitmap_union(distinct rc.id_card) as diagnoseCount,

count(distinct rc.id_card) as diagnoseCount,

count(case when rc.sex =1 then 1 end ) as diagnoseManCount,

count(case when rc.sex =0 then 1 end ) as diagnoseWomenCount

from biz_diagnose_result_detail_tmp dr

left join biz_reg_customer_tmp rc

on rc.vid = dr.vid group by rc.book_day,rc.shop_no,dr.diagnose_result order by rc.book_day;

分析没命中物化视图
image
image
是只有单表才能命中视图吗

#8

还想请教一下，建完这个异步视图之后，执行分析
explain select

rc.book_day as bookDay,

rc.shop_no as shopNo,

dr.diagnose_result as diagnoseResult,

count( case when rc.age<18 then 1 end) as childCount,

count( case when rc.age between 18 and 50 then 1 end ) as youngCount,

count( case when rc.age > 50 then 1 end ) as oldCount,

– bitmap_union(distinct rc.id_card) as diagnoseCount,

count(distinct rc.id_card) as diagnoseCount,

count(case when rc.sex =1 then 1 end ) as diagnoseManCount,

count(case when rc.sex =0 then 1 end ) as diagnoseWomenCount

from biz_diagnose_result_detail_tmp dr

left join biz_reg_customer_tmp rc

on rc.vid = dr.vid group by rc.book_day,rc.shop_no,dr.diagnose_result order by rc.book_day;

分析没命中物化视图

image
image860×460 61.6 KB

image
image748×390 50 KB

是只有单表才能命中视图吗

#9

只有单表可以命中物化视图，单表物化视图无法通过视图名称直接查询，通过查询sql判断是否命中。多表的物化视图可以通过物化视图名直接查询。

#10

还想请教一下，建完这个异步视图之后，执行分析
explain select

rc.book_day as bookDay,

rc.shop_no as shopNo,

dr.diagnose_result as diagnoseResult,

count( case when rc.age<18 then 1 end) as childCount,

count( case when rc.age between 18 and 50 then 1 end ) as youngCount,

count( case when rc.age > 50 then 1 end ) as oldCount,

– bitmap_union(distinct rc.id_card) as diagnoseCount,

count(distinct rc.id_card) as diagnoseCount,

count(case when rc.sex =1 then 1 end ) as diagnoseManCount,

count(case when rc.sex =0 then 1 end ) as diagnoseWomenCount

from biz_diagnose_result_detail_tmp dr

left join biz_reg_customer_tmp rc

on rc.vid = dr.vid group by rc.book_day,rc.shop_no,dr.diagnose_result order by rc.book_day;

分析没命中物化视图
收到 谢谢

京ICP备2022026421号-1