【详述】刷新物化视图，超时报错。
【背景】创建物化视图，刷新超时失败。
【业务影响】
【StarRocks版本】2.5.2
相关表结构,以及操作脚本：
SHOW VARIABLES;
– 尝试修改查询超时时间，设置之后依然报错，无效果
SET query_timeout = 600;
REFRESH MATERIALIZED VIEW view_comment_clause_all;
– 相关物化视图脚本
CREATE MATERIALIZED VIEW view_comment_clause_all
DISTRIBUTED BY HASH(goods_id,comment_id)
– REFRESH MANUAL
REFRESH ASYNC
PARTITION BY date_trunc(“MONTH”, comment_time)
– PROPERTIES ()
AS
SELECT a.goods_id
,a.comment_id
,a.comment_time
,a.comment_phrase
,a.first_prop_dim
,a.second_prop_dim
,a.third_prop_dim
,a.terminal_prop_dim
,a.all_dimension
,a.property_term
,a.opinion_term
,a.emotion_term
,a.comment_brand
,a.sku_name
,a.is_recommend
,a.is_repurchase
,b.category_name
,b.efficacy_names
,b.ingredient_names
,b.concept_names
,b.brand_type
,b.brand_name
FROM product_comment_clause a
LEFT JOIN tb_product b
ON a.goods_id = b.goods_id
– WHERE ((array_contains(b.brand_type, ‘BIG_OUT’)) = TRUE) OR ((array_contains(b.brand_type, ‘BIG_IN’)) = TRUE)
;
– 基本表，数据量 2.5亿
DROP TABLE IF EXISTS product_comment_clause;
CREATE TABLE IF NOT EXISTS product_comment_clause (
goods_id BIGINT NOT NULL COMMENT “商品ID”,
comment_id VARCHAR(32) NULL COMMENT “所评论的 sku”,
comment_time DATETIME NULL COMMENT “评论时间所在的月份”,
sku_name VARCHAR(512) NULL COMMENT “所评论的 sku”,
comment_phrase VARCHAR(2048) NULL COMMENT “评论短句内容”,
first_prop_dim VARCHAR(32) NULL COMMENT “一级属性维度”,
second_prop_dim VARCHAR(32) NULL COMMENT “二级属性维度”,
third_prop_dim VARCHAR(32) NULL COMMENT “三级属性维度”,
terminal_prop_dim VARCHAR(32) NULL COMMENT “最细属性维度”,
all_dimension ARRAY<VARCHAR(32)> NULL COMMENT “全部维度”,
property_term VARCHAR(1024) NULL COMMENT “属性词”,
opinion_term VARCHAR(1024) NULL COMMENT “观点词”,
comment_brand VARCHAR(1024) NULL COMMENT “观点词”,
keywords ARRAY<VARCHAR(4048)> NULL COMMENT “关键词（属性词、观点词）”,
emotion_term VARCHAR(32) NULL COMMENT “情感分类”,
is_recommend INT(1) NULL COMMENT “推荐意愿:1-正推荐;-1-负推荐”,
is_repurchase INT(1) NULL COMMENT “回购意愿:1-正回购;-1-负回购”
)
DUPLICATE KEY(goods_id)
PARTITION BY RANGE (comment_time) (
START (“2019-01-01”) END (“2030-12-31”) EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(goods_id,comment_id,sku_name)
PROPERTIES (
“replication_num” = “3”
);
– 基表数据量 160万+
DROP TABLE IF EXISTS tb_product;
CREATE TABLE IF NOT EXISTS tb_product (
goods_id BIGINT NOT NULL COMMENT “商品ID”,
goods_name VARCHAR(256) NULL COMMENT “产品名称”,
product_name VARCHAR(256) NULL COMMENT “产品名称”,
brand_name varchar(64) NULL COMMENT “品牌”,
brand_type ARRAY<varchar(16)> NULL COMMENT “品牌类型”,
shop_name varchar(128) NULL COMMENT “店铺名称”,
goods_url varchar(128) NULL COMMENT “商品链接”,
first_category_name varchar(64) NULL COMMENT “一级品类”,
second_category_name varchar(64) NULL COMMENT “二品类类”,
third_category_name varchar(64) NULL COMMENT “三级品类”,
fourth_category_name varchar(64) NULL COMMENT “四级品类”,
fifth_category_name varchar(64) NULL COMMENT “五级品类”,
category_name varchar(64) NULL COMMENT “标准品类”,
image_urls varchar(1024) NULL COMMENT “图片链接”,
apply_sn varchar(256) NULL COMMENT “备案号”,
efficacy_names ARRAY<varchar(128)> NULL COMMENT “功效亮点”,
ingredient_names ARRAY<varchar(128)> NULL COMMENT “成分亮点”,
concept_names ARRAY<varchar(128)> NULL COMMENT “概念亮点”,
crowd_names ARRAY<varchar(16)> NULL COMMENT “适用人群”,
function_names ARRAY<varchar(32)> NULL COMMENT “功能亮点”,
action_mode_names ARRAY<varchar(32)> NULL COMMENT “作用方式亮点”,
sales_volume_30d BIGINT NULL COMMENT “最近30天销量”
)
PRIMARY KEY(goods_id)
DISTRIBUTED BY HASH(goods_id)
PROPERTIES (
“replication_num” = “3”
);
【附件】
- 报错截图
