【StarRocks版本】3.1.2-4f3a2ee
【集群规模】3个FE，3个BE，混合部署，存算一体
【服务器配置】16C，64G内存，500G硬盘，万兆网络，共3台
【联系方式】本站
【问题】异步物化视图不能自动改写，查询时依然查的是基表。如何操作才能自动改为查询异步物化视图？
【starrocks建表语句】
CREATE TABLE test_db.message_table (
event_time DATETIME NOT NULL,
act SMALLINT NOT NULL,
fid TINYINT NOT NULL ,
uid INT NOT NULL DEFAULT ‘0’,
cid INT NOT NULL,
view_id INT NOT NULL,
view_type SMALLINT NOT NULL ,
keyword VARCHAR(1500) NOT NULL ,
INDEX index_act (act) USING BITMAP,
INDEX index_fid (fid) USING BITMAP,
INDEX index_view_type (view_type) USING BITMAP
)
DUPLICATE KEY(event_time,jid)
PARTITION BY date_trunc(“day”,event_time)
DISTRIBUTED BY HASH(jid)
PROPERTIES(
“replication_num” = “2”,
“bloom_filter_columns” = “jid,uid,cid,view_id”
);
【异步物化视图创建语句】
CREATE MATERIALIZED VIEW message_everyday_num
PARTITION BY event_day
DISTRIBUTED BY HASH(jid)
ORDER BY (event_day,jid)
REFRESH IMMEDIATE ASYNC START(‘2023-12-06 17:18:00’) EVERY (interval 10 MINUTE)
AS
SELECT date_trunc(“day”,event_time) as event_day,jid,count(*) message_num
FROM test_db.message_table
GROUP BY event_day,jid;
【routine load作业创建语句】
CREATE ROUTINE LOAD test_db.kafka2sr_ods_message_table_2023120616 ON message_table
COLUMNS(tmp_event_time,jid,act,fid,uid,cid,view_id,view_type,keyword,event_time=from_unixtime(tmp_event_time,‘yyyy-MM-dd HH:mm:ss’))
PROPERTIES
(
“desired_concurrent_number” = “3”,
“max_batch_interval” = “20”,
“format” = “json”,
“strip_outer_array” = “true”,
“json_root” = “$.records”,
“jsonpaths” = “[”$.event_time","$.jid","$.act","$.fid","$.uid","$.cid","$.view_id","$.view_type","$.kw"]",
“max_error_number”=“2000000”,
“strict_mode” = “true”,
“log_rejected_record_num”= “-1”
)
FROM KAFKA
(
“kafka_broker_list” = “node-103:9092,node-104:9092,node-105:9092”,
“kafka_topic” = “ods_message_table”,
“property.kafka_default_offsets” = “OFFSET_BEGINNING”
);
【查看异步物化视图】
mysql> show materialized views\G
*************************** 1. row ***************************
id: 40653
database_name: test_db
name: message_everyday_num
refresh_type: ASYNC
is_active: true
inactive_reason: NULL
partition_type: RANGE
task_id: 40655
task_name: mv-40653
last_refresh_start_time: 2023-12-07 16:11:59
last_refresh_finished_time: 2023-12-07 16:12:00
last_refresh_duration: 1.533
last_refresh_state: SUCCESS
last_refresh_force_refresh: false
last_refresh_start_partition:
last_refresh_end_partition:
last_refresh_base_refresh_partitions: {message_table=[p20231207]}
last_refresh_mv_refresh_partitions: p20231207_20231208
last_refresh_error_code: 0
last_refresh_error_message:
rows: 41743088
【explain】
mysql> explain select count(*) message_num from test_db.message_table where event_time >=‘2023-01-01 00:00:00’ and event_time<‘2023-12-01 00:00:00’ and jid = 3038035;
±--------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:10: count |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:AGGREGATE (merge finalize) |
| | output: count(10: count) |
| | group by: |
| | |
| 3:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| UNPARTITIONED |
| |
| 2:AGGREGATE (update serialize) |
| | output: count(*) |
| | group by: |
| | |
| 1:Project |
| | <slot 15> : 1 |
| | |
| 0:OlapScanNode |
| TABLE: message_table |
| PREAGGREGATION: ON |
| PREDICATES: 2: jid = 3038035 |
| partitions=328/337 |
| rollup: message_table |
| tabletRatio=328/353 |
| tabletList=12123,12129,12135,12141,12147,12153,12159,12165,12171,12177 … |
| cardinality=1069 |
| avgRowSize=13.0 |
| numNodes=0 |
±--------------------------------------------------------------------------------+
【explain costs】
mysql> explain costs select count(*) message_num from test_db.message_table where event_time >=‘2023-01-01 00:00:00’ and event_time<‘2023-12-01 00:00:00’ and jid = 3038035;
±-------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±-------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0(F01) |
| Output Exprs:10: count |
| Input Partition: UNPARTITIONED |
| RESULT SINK |
| |
| 4:AGGREGATE (merge finalize) |
| | aggregate: count[([10: count, BIGINT, false]); args: ; result: BIGINT; args nullable: true; result nullable: false] |
| | cardinality: 1 |
| | column statistics: |
| | * count–>[0.0, 1069.356319033882, 0.0, 8.0, 1.0] ESTIMATE |
| | |
| 3:EXCHANGE |
| distribution type: GATHER |
| cardinality: 1 |
| |
| PLAN FRAGMENT 1(F00) |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 03 |
| |
| 2:AGGREGATE (update serialize) |
| | aggregate: count[(*); args: ; result: BIGINT; args nullable: false; result nullable: false] |
| | cardinality: 1 |
| | column statistics: |
| | * count–>[0.0, 1069.356319033882, 0.0, 8.0, 1.0] ESTIMATE |
| | |
| 1:Project |
| | output columns: |
| | 15 <-> 1 |
| | cardinality: 1069 |
| | column statistics: |
| | * auto_fill_col–>[1.0, 1.0, 0.0, 1.0, 1.0] ESTIMATE |
| | |
| 0:OlapScanNode |
| table: message_table, rollup: message_table |
| preAggregation: on |
| Predicates: [2: jid, INT, false] = 3038035 |
| partitionsRatio=328/337, tabletsRatio=328/353 |
| tabletList=12123,12129,12135,12141,12147,12153,12159,12165,12171,12177 … |
| actualRows=1183206103, avgRowSize=13.0 |
| cardinality: 1069 |
| column statistics: |
| * event_time–>[1.6725024E9, 1.70136E9, 0.0, 8.0, 1069.356319033882] ESTIMATE |
| * jid–>[3038035.0, 3038035.0, 0.0, 4.0, 1069.356319033882] ESTIMATE |
| * auto_fill_col–>[1.0, 1.0, 0.0, 1.0, 1.0] ESTIMATE |
±-------------------------------------------------------------------------------------------------------------------------+
【trace rewrite】
mysql> trace rewrite select count(*) message_num from test_db.message_table where event_time >=‘2023-01-01 00:00:00’ and event_time<‘2023-12-01 00:00:00’ and jid = 3038035;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| – [TRACE: message_everyday_num] |
| [SYNC=false] Prepare MV message_everyday_num success |
| [TF_MV_AGGREGATE_SCAN_RULE] Construct 1 relation id mappings from query to mv |
| [TF_MV_AGGREGATE_SCAN_RULE] Rewrite query failed: get range compensation predicates failed,srcPr:2: jid = 3038035, targetPr:1: event_time >= 2022-01-01 00:00:00 AND 1: event_time < 2022-01-02 00:00:00 OR 1: event_time >= 2023-01-01 00:00:00 AND 1: event_time < 2023-10-09 00:00:00 OR 1: event_time >= 2023-10-12 00:00:00 AND 1: event_time < 2023-10-24 00:00:00 OR 1: event_time >= 2023-10-27 00:00:00 AND 1: event_time < 2023-12-07 00:00:00 |
| [TF_MV_AGGREGATE_SCAN_RULE] Rewrite query failed: cannot get compensation predicates from MV, Try to use union rewrite. |
| [TF_MV_AGGREGATE_SCAN_RULE] Rewrite query failed: get range compensation predicates failed,srcPr:1: event_time >= 2022-01-01 00:00:00 AND 1: event_time < 2022-01-02 00:00:00 OR 1: event_time >= 2023-01-01 00:00:00 AND 1: event_time < 2023-10-09 00:00:00 OR 1: event_time >= 2023-10-12 00:00:00 AND 1: event_time < 2023-10-24 00:00:00 OR 1: event_time >= 2023-10-27 00:00:00 AND 1: event_time < 2023-12-07 00:00:00, targetPr:2: jid = 3038035 |
| [TF_MV_AGGREGATE_SCAN_RULE] Rewrite union failed: cannot get compensation from view to query |
| [TF_MV_ONLY_SCAN_RULE] MV is not applicable: mv expression is not valid |
| [TF_MV_ONLY_SCAN_RULE] mv message_everyday_num applicable check failed |
| – [TRACE: PREPARE GLOBAL] |
| [SYNC=false] RelatedMVs: [message_everyday_num], CandidateMVs: [message_everyday_num] |
| [SYNC=true] There are no related mvs for the query plan |
| – [TRACE: Summary] |
| Query cannot be rewritten, please check the trace logs or set enable_mv_optimizer_trace_log=on to find more infos. |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@dongquan @jingdan @yuchen1019 技术老师们，这个问题帮忙看一下，在线等，谢谢