当前位置：首页 > backend >正文

Doris主键模型使用异步物化视图

backend 2025/7/26 15:54:26

基于ods_money做测试,数据量是3662649295；约36.6亿条数据；

CREATE TABLE `ods_money` (`event_time`           datetime    NOT NULL COMMENT '事件发生的时间',`platform`             varchar(30) NOT NULL COMMENT '平台标识符',`actual_bet_amount`    decimal(10,2) NULL    COMMENT '金额'
)
ENGINE=OLAP
UNIQUE KEY (`event_time`, `platform`)
COMMENT '记录'
AUTO PARTITION BY RANGE (date_trunc(`event_time`, 'day'));

未创建物化视图，执行查询；约36.6亿条数据；做聚合查询约6198ms

SELECTdate_trunc(event_time, 'day') as event_time_day , platform,sum(actual_bet_amount)                                              
FROM flydata_game_dwh_v1.rt_ods_log_cc_placebet_v1
GROUP BY event_time_day,platform;

已创建物化视图下进行测试；做聚合查询约134ms

CREATE MATERIALIZED VIEW placebet_rollup_partition_mv1
BUILD IMMEDIATE
REFRESH AUTO
ON MANUAL   
PARTITION BY (event_time_day)   
DISTRIBUTED BY RANDOM BUCKETS 2   
AS   
SELECTdate_trunc(event_time, 'day') as event_time_day , platform,sum(actual_bet_amount)                                              
FROM flydata_game_dwh_v1.rt_ods_log_cc_placebet_v1
GROUP BY event_time_day,platform; SELECTdate_trunc(event_time, 'day') as event_time_day , platform,sum(actual_bet_amount)                                              
FROM flydata_game_dwh_v1.rt_ods_log_cc_placebet_v1
GROUP BY event_time_day,platform;