物化视图无法正常使用

为了更快的定位您的问题,请提供以下信息,谢谢
【详述】创建物化视图后没有数据
【业务影响】无法正常使用
【StarRocks版本】3.0.3 ,3.1.0
【集群规模】:3fe+3be(fe与be混部)
【机器信息】8/32G
【联系方式】站内回复
【附件】
CREATE TABLE olap.click_conversion
(
aff_id VARCHAR(10),
offer_id VARCHAR(10),
app_id VARCHAR(100),
country VARCHAR(10),
platform VARCHAR(1),
media VARCHAR(5),
conversion VARCHAR(10),
day VARCHAR(8) not null,
year VARCHAR(4)not null,
month VARCHAR(2)not null,
media_source VARCHAR(100),
model VARCHAR(100),
make VARCHAR(100),
bundle VARCHAR(100),
cat VARCHAR(1000),
num int,
day_time DATE,
INDEX click_conversion_year (year) USING BITMAP ,
INDEX click_conversion_month (month) USING BITMAP ,
INDEX click_conversion_day (day) USING BITMAP ,
INDEX click_conversion_aff_id (aff_id) USING BITMAP ,
INDEX click_conversion_offer_id (offer_id) USING BITMAP ,
INDEX click_conversion_app_id (app_id) USING BITMAP ,
INDEX click_conversion_country (country) USING BITMAP,
INDEX click_conversion_platform (platform) USING BITMAP,
INDEX click_conversion_media (media) USING BITMAP,
INDEX click_data_media_source (media_source) USING BITMAP,
INDEX click_data_model (model) USING BITMAP,
INDEX click_data_make (make) USING BITMAP,
INDEX click_conversion_bundle (bundle) USING BITMAP,
INDEX click_conversion_cat (cat) USING BITMAP,
INDEX click_conversion_conversion (conversion) USING BITMAP,
INDEX click_conversion_day_time (day_time) USING BITMAP
)
DUPLICATE KEY(aff_id,offer_id,app_id,country,platform,media,conversion,day)
PARTITION BY (day)
DISTRIBUTED BY HASH(aff_id,offer_id,app_id,country,platform,media,conversion,day,year,month)BUCKETS 48
PROPERTIES (
“replication_num” = “2”,
“enable_persistent_index” = “true”
);
INSERT INTO click_conversion (aff_id,offer_id,app_id,country,platform,media,conversion,day,year,month,media_source,model,make,bundle,cat,num,day_time) VALUES
(‘221’,‘10026732’,‘com.finaccel.android’,‘id’,‘1’,‘113’,NULL,‘20230821’,‘2023’,‘08’,NULL,‘22021211RG’,’’,‘com.kayac.DrawFight’,NULL,1,‘2023-08-21’),
(‘221’,‘10026732’,‘com.finaccel.android’,‘id’,‘1’,‘113’,NULL,‘20230821’,‘2023’,‘08’,NULL,‘2201117TY’,’’,‘com.billiards.shooting.ball.pool’,NULL,1,‘2023-08-21’);

CREATE MATERIALIZED VIEW test
DISTRIBUTED BY HASH(aff_id) BUCKETS 12
REFRESH ASYNC START(‘2023-08-22 16:03:00’) EVERY (interval 1 hour)
AS
SELECT aff_id, offer_id, app_id, country, case platform WHEN 1 THEN ‘android’ ELSE 'ios’end AS platform, day,media,model,make,bundle,cat, media_source,
SUM(CASE WHEN conversion IS NULL THEN 1 ELSE 0 END) AS click_num,
SUM(CASE WHEN conversion = ‘true’ THEN 1 ELSE 0 END) AS install_num,
SUM(CASE WHEN conversion = ‘false’ THEN 1 ELSE 0 END) AS reject_num,
day_time,year,month
FROM click_conversion
GROUP BY aff_id, offer_id, app_id, country, platform, media,model,make,bundle,cat, media_source,day,year,month,day_time;

select * from information_schema.tasks order by CREATE_TIME desc limit 1
select * from information_schema.task_runs where task_name=‘mv-83963’ order by CREATE_TIME

错误信息:
aa8b0148-40c7-11ee-a385-00163e045198|mv-83963 |2023-08-22 16:41:19|2023-08-22 16:41:21|FAILED|olap |insert overwrite test SELECT olap.click_conversion.aff_id, olap.click_conversion.offer_id, olap.click_conversion.app_id, olap.click_conversion.country, CASE olap.click_conversion.platform WHEN 1 THEN ‘android’ ELSE ‘ios’ END |2023-08-23 16:41:19| -1|java.lang.ClassCastException: com.starrocks.catalog.ListPartitionInfo cannot be cast to com.starrocks.catalog.RangePartitionInfo|0% |{“forceRefresh”:false,“mvPartitionsToRefresh”:[],“basePartitionsToRefreshMap”:{}}|

我这边确认下给您反馈

您好,3.1不支持list分区,3.2才支持,可以base的的partition by day改成partition by range(day)绕过

是的,我了解到date_trunc(‘day’, day_time) 也可以解决,只是表的partition by (字段)类型或者列无法修改了