刷新物化视图超时报错

【详述】刷新物化视图,超时报错。
【背景】创建物化视图,刷新超时失败。
【业务影响】
【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”
);

【附件】

您好,请您试下只执行select语句跑下看看能不能跑出结果?

只执行select 能跑,可以跑,不知道是不是因为工具限制了查询条数的原因。

工具限制应该不会报超时的错误,你set global query_timeout = 600;然后跑下refresh物化视图看看

这样可以。但是set query_timeout = 600;为什么无效呢?

您好,可能是设置临时session变量失效了,global是全局的。

刷新物化视图有时会出现这种错误:
Refresh materialized view failed because view_product is not active.

请问楼主最终是怎么解决问题的,set global query_timeout 吗,有其他解决方法吗

请单独开个帖子补充下您的集群信息,和详细问题报错