SR查询分区条件数据查询结果为空

💬 StarRocks 用户问答
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
我们有一个表设置两个分区字段$part_event和$part_date 然后通过insert into 和flink-starrocks方式写数据, 发现有的时候通过where 分区字段查询数据返回空, 但是group by 和 count整体表数据并没有少, show partitions from 也有这个分区信息.

【背景】做过哪些操作？

CREATE TABLE v_event_1515 (
$part_event varchar(65533) NOT NULL COMMENT “事件分区字段”,
$part_date varchar(65533) NOT NULL COMMENT “日期分区字段”,
#user_id bigint(20) NULL COMMENT “”,
忽略多个字段信息…
#event_name varchar(65533) NULL COMMENT “”,
#event_time datetime NULL COMMENT “”,
) ENGINE=OLAP
DUPLICATE KEY($part_event, $part_date)
COMMENT “OLAP”
PARTITION BY ($part_event,$part_date)
DISTRIBUTED BY HASH($part_event)
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“enable_persistent_index” = “false”,
“replicated_storage” = “true”,
“fast_schema_evolution” = “true”,
“compression” = “LZ4”
);

查询语句. 能看到2024-03-26 是有数据的.
mysql> SELECT count(),$part_date from v_event_1515 group by $part_date order by $part_date desc;
±---------±-----------+
| count() | $part_date |
±---------±-----------+
| 4972206 | 2024-03-29 |
| 10182231 | 2024-03-28 |
| 10716474 | 2024-03-27 |
| 10474642 | 2024-03-26 |
| 11346356 | 2024-03-25 |
| 12180267 | 2024-03-24 |
| 12511782 | 2024-03-23 |
| 10687906 | 2024-03-22 |
| 10174542 | 2024-03-21 |
| 10560766 | 2024-03-20 |
| 3833320 | 2024-03-19 |
±---------±-----------+
11 rows in set (0.44 sec)

但是where 2024-03-26 没有任何数据输出.
mysql> select count() from v_event_1515 where $part_date = ‘2024-03-26’ limit 1;
±---------+
| count() |
±---------+
| 0 |
±---------+
1 row in set (0.01 sec)

查询’2024-03-28 就有输出.
mysql> select count() from v_event_1515 where $part_date = ‘2024-03-28’ limit 1;
±---------+
| count() |
±---------+
| 10182231 |
±---------+
1 row in set (0.16 sec)

分区信息:
1531870 psuccess5fmould_20240326 599 2024-03-28 16:02:48 0 NORMAL $part_event, $part_date ((‘success_mould’, ‘2024-03-26’)) $part_event 1 1 SSD 9999-12-31 23:59:59 NULL 117KB false 677
1531843 psuccess5ffriendedit_20240326 20 2024-03-28 15:53:19 0 NORMAL $part_event, $part_date ((‘success_friendedit’, ‘2024-03-26’)) $part_event 1 1 SSD 9999-12-31 23:59:59 NULL 211.2KB false 19
1531903 psuccess5frelationship_20240326 89 2024-03-28 15:54:55 0 NORMAL $part_event, $part_date ((‘success_relationship’, ‘2024-03-26’)) $part_event 1 1 SSD 9999-12-31 23:59:59 NULL 145.6KBfalse 90
1531879 psuccess5fpicurrencyexchange_20240326 3 2024-03-28 14:29:37 0 NORMAL $part_event, $part_date((‘success_picurrencyexchange’, ‘2024-03-26’)) $part_event 1 1 SSD 9999-12-31 23:59:59 NULL 69.6KB false 2
1527790 pexpose5fanchorwish_20240326 170 2024-03-28 16:02:52 0 NORMAL $part_event, $part_date ((‘expose_anchorwish’, ‘2024-03-26’)) $part_event 1 1 SSD 9999-12-31 23:59:59 NULL 98.5KB false 177

【业务影响】
部分分区数据无法正常查询.

【是否存算分离】
【StarRocks版本】
版本: 3.2.3 存算一体模式
【集群规模】例如：2fe（2 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：8C/64G/千兆
【联系方式】
微信: zhangheng311

#2

@trueeyu 麻烦大佬给点排查思路

#4

论坛里类似一个CASE:

#6

explain costs + sql 结果发下，0326和0328的都发下

#7

query_dump.log (24.1 KB) query_profile.log (8.9 KB)

mysql> explain select count() from bi.v_event_1515 where $part_date = ‘2024-03-26’ limit 1;
±--------------------------------+
| Explain String |
±--------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:310: count |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 1:AGGREGATE (update finalize) |
| | output: count() |
| | group by: |
| | limit: 1 |
| | |
| 0:EMPTYSET |
±--------------------------------+
12 rows in set (0.01 sec)

#8

mysql> explain select count() from bi.v_event_1515 where $part_date = ‘2024-03-28’;
±----------------------------------------------------------------------------------------------------+
| Explain String |
±----------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:310: count |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:AGGREGATE (merge finalize) |
| | output: count(310: 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 312> : 1 |
| | |
| 0:OlapScanNode |
| TABLE: v_event_1515 |
| PREAGGREGATION: ON |
| PREDICATES: DictExpr(313: $part_date,[ = ‘2024-03-28’]) |
| partitions=245/2667 |
| rollup: v_event_1515 |
| tabletRatio=245/245 |
| tabletList=1544727,1545238,1543189,1544724,1544721,1540640,1544736,1543198,1540637,1544733 … |
| cardinality=539246 |
| avgRowSize=11.0 |
±----------------------------------------------------------------------------------------------------+
37 rows in set (0.02 sec)

#9

mysql> explain logical select count(*) from bi.v_event_1515 where $part_date = ‘2024-03-26’;
±------------------------------------------------------------------------------+
| Explain String |
±------------------------------------------------------------------------------+
| - Output => [310:count] |
| - AGGREGATE(GLOBAL) [] |
| Estimates: {row: 1, cpu: ?, memory: ?, network: ?, cost: 16.5} |
| 310:count := count() |
| - EMPTY |
| Estimates: {row: 1, cpu: ?, memory: ?, network: ?, cost: 0.0} |
±------------------------------------------------------------------------------+
6 rows in set (0.03 sec)

mysql> explain logical select count(*) from bi.v_event_1515 where $part_date = ‘2024-03-28’;
±-----------------------------------------------------------------------------------------------------------+
| Explain String |
±-----------------------------------------------------------------------------------------------------------+
| - Output => [310:count] |
| - AGGREGATE(GLOBAL) [] |
| Estimates: {row: 1, cpu: 8.00, memory: 8.00, network: 0.00, cost: 5931760.00} |
| 310:count := count(310:count) |
| - EXCHANGE(GATHER) |
| Estimates: {row: 1, cpu: 8.00, memory: 0.00, network: 8.00, cost: 5931740.00} |
| - AGGREGATE(LOCAL) [] |
| Estimates: {row: 1, cpu: 5931708.00, memory: 8.00, network: 0.00, cost: 5931724.00} |
| 310:count := count() |
| - SCAN [v_event_1515] => [312:auto_fill_col] |
| Estimates: {row: 539246, cpu: 5931708.00, memory: 0.00, network: 0.00, cost: 0.00} |
| partitionRatio: 245/2667, tabletRatio: 245/245 |
| 312:auto_fill_col := 1 |
| predicate: DictMapping(313: $part_date{2: $part_date = 2024-03-28}) |
±-----------------------------------------------------------------------------------------------------------+
14 rows in set (0.02 sec)

#10

dump log和相关explain 都回复到下面了哈

#11

你 analyze 对应的 table 再试试？看起来上面的 plan 有问题

#12

用了自动分区的功能吗? 分去键是$part_event, $part_date两列?

#13

PARTITION BY ( $part_event , $part_date ) 分区就这两列, 这么创建表应该是用了自动分区吧

#14

遇到了相同的问题

#15

你们这个现象也是偶发的嘛

#16

目前导了批数据做临时表，能稳定复现。但重复导数据做临时表，不是必现。

#17

anlyze下这个table再试试

image
image1041×537 60.9 KB

#18

哪个版本

#19

不算是偶发,研发那边用flink 从kafka写数据进来有一定概率会遇到. insert into 进来也会有， 运维这边seatunnel在导入也会出现. 但是curl stream_load csv 好像还没遇到过, 比较奇怪

#21

你们在导入数据的时候中间有truncate table 过么? 就是清空过表然后又重新写

#22

新版3.2.4好像是修复了. https://github.com/StarRocks/starrocks/pull/40578

京ICP备2022026421号-1