【详述】同步物化视图聚合去重异常
【是否存算分离】否
【StarRocks版本】3.3.19
建表语句
create table dwd.dwd_ais_static_info_log (
mmsi VARCHAR(32) NOT NULL COMMENT ‘船舶mmsi号’,
receiveTime DATETIME COMMENT ‘接收时间’,
vesselName VARCHAR(255) COMMENT ‘船舶名称’,
shipClass VARCHAR(255) COMMENT ‘class类型，A、B’,
shipType VARCHAR(255) COMMENT ‘船舶类型’,
shipTypeCode SMALLINT COMMENT ‘船舶类型编码’,
callSign VARCHAR(255) COMMENT ‘船舶呼号’,
vendorId VARCHAR(255) COMMENT ‘造船厂id’,
imo VARCHAR(255) COMMENT ‘船舶imo号’,
mothershipMmsi VARCHAR(255) COMMENT ‘具有主从关系的船舶之间，主船的mmsi号’,
length SMALLINT COMMENT ‘船长’,
realTimeLength SMALLINT COMMENT ‘实时船长’,
wide SMALLINT COMMENT ‘船宽’,
destination VARCHAR(255) COMMENT ‘目的地’,
etaTime VARCHAR(255) COMMENT ‘预计到港时间’,
draught FLOAT COMMENT ‘吃水深度’,
aidName VARCHAR(255) COMMENT ‘助航物名称’,
aidNameExtension VARCHAR(255) COMMENT ‘助航物名称扩展信息’,
aidType VARCHAR(255) COMMENT ‘助航物类型’,
offPosition VARCHAR(255) COMMENT ‘标识目标是否偏移，主要应用静态目标上’,
nationality VARCHAR(255) COMMENT ‘国籍’,
positioningDeviceType VARCHAR(255) COMMENT ‘’,
sourceId VARCHAR(255) COMMENT ‘’,
bow SMALLINT COMMENT ‘船头’,
stern SMALLINT COMMENT ‘船尾’,
port SMALLINT COMMENT ‘左舷’,
starboard SMALLINT COMMENT ‘右舷’,
dataSource VARCHAR(255) COMMENT ‘主数据源，海兰寰宇 龙船’,
receiveTimeStamp BIGINT(13) COMMENT ‘接收时间’
) DUPLICATE KEY (mmsi,receiveTime)
PARTITION BY RANGE(receiveTime) (
START (“2023-12-25”) END (“2024-01-01”) EVERY (INTERVAL 1 day)
) DISTRIBUTED BY HASH(mmsi)
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.start” = “-365”, – 设置保留多久数据
“dynamic_partition.end” = “7”,
“dynamic_partition.prefix” = “p”
);
物化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS dwd.mv_mmsi_vessel_names
AS
SELECT
mmsi,
vesselName,
MIN(receiveTimeStamp) as first_seen_timestamp,
MAX(receiveTimeStamp) as last_seen_timestamp
FROM dwd.dwd_ais_static_info_log
WHERE mmsi IS NOT NULL
AND mmsi != ‘’
AND vesselName IS NOT NULL
AND vesselName != ‘’
GROUP BY mmsi, vesselName;
查询语句
select count(1) from (SELECT
mmsi,
vesselName
FROM dwd.dwd_ais_static_info_log
WHERE
– mmsi IS NOT NULL
– AND mmsi != ‘’
– AND
vesselName IS NOT NULL
AND vesselName != ‘’
GROUP BY mmsi, vesselName) t WHERE mmsi = ‘413123456’;
结果 144
explain
PLAN FRAGMENT 0
OUTPUT EXPRS:32: count
PARTITION: UNPARTITIONED
RESULT SINK
7:AGGREGATE (merge finalize)
| output: count(32: count)
| group by:
|
6:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 1: mmsi, 3: vesselName
STREAM DATA SINK
EXCHANGE ID: 06
UNPARTITIONED
5:AGGREGATE (update serialize)
| output: count(1)
| group by:
|
4:Project
| <slot 39> : 1
|
3:AGGREGATE (merge finalize)
| group by: 1: mmsi, 3: vesselName
|
2:EXCHANGE
PLAN FRAGMENT 2
OUTPUT EXPRS:
colocate exec groups: ExecGroup{groupId=2, nodeIds=[0, 1]}
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 02
HASH_PARTITIONED: 1: mmsi, 3: vesselName
1:AGGREGATE (update serialize)
| STREAMING
| group by: 1: mmsi, 3: vesselName
|
0:OlapScanNode
TABLE: dwd_ais_static_info_log
PREAGGREGATION: ON
PREDICATES: 1: mmsi = ‘413123456’, 3: vesselName IS NOT NULL, 3: vesselName != ‘’
partitions=364/373
rollup: dwd_ais_static_info_log
tabletRatio=364/2184
tabletList=11049697,11054733,11063081,11067119,11070950,11074708,11079675,11083867,11088129,11092124 …
cardinality=3207
avgRowSize=20.190434
查询语句
select count(1) from (SELECT
mmsi,
vesselName
FROM dwd.dwd_ais_static_info_log
WHERE
mmsi IS NOT NULL
AND mmsi != ‘’
AND
vesselName IS NOT NULL
AND vesselName != ‘’
GROUP BY mmsi, vesselName) t WHERE mmsi = ‘413123456’;
结果 2140
explain
PLAN FRAGMENT 0
OUTPUT EXPRS:32: count
PARTITION: UNPARTITIONED
RESULT SINK
4:AGGREGATE (merge finalize)
| output: count(32: 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(1)
| group by:
|
1:Project
| <slot 38> : 1
|
0:OlapScanNode
TABLE: mv_mmsi_vessel_names
PREAGGREGATION: OFF. Reason: The parameter of aggregate function isn’t value column or CAST/CASE-WHEN expression
PREDICATES: 33: mmsi = ‘413123456’
partitions=364/373
rollup: mv_mmsi_vessel_names
tabletRatio=364/2184
tabletList=85692842,85692866,85692890,85692914,85692938,85692962,85692986,85693010,85693034,85693058 …
cardinality=9449017280
avgRowSize=2.0
MaterializedView: true
