SUBMIT TASK 报错（Total byte size of binary column exceed the limit: 4294967296）

💬 StarRocks 用户问答
#1

背景：
我们使用submit 的方式，280G的数据10分钟写入主键模型，但是800G的写不进去，贴源数据是按天分区的，现在是把800G的数据按照半年的时间使用 SUBMIT
报错信息如下：
Total byte size of binary column exceed the limit: 4294967296

image
image2316×100 34.6 KB

SUBMIT TASK loin_insert_202307 AS
insert into bidw.XW_DYMID_REPAY_PLAN
(cur_date
,fx_account_id
,loan_id
,term_no
,start_date
,billing_date
,end_date
,clear_date
,term_status
,prin_total
,prin_repay
,prin_bal
,int_total
,int_bal
,int_repay
,cycle_int_total
,cycle_int_repay
,cycle_int_bal
,pnlt_int_total
,pnlt_int_repay
,pnlt_int_bal
,pre_pmt_fee_repay
,days_ovd
,capital_set_no
,DATE_TIME
,PART_DATE
)
select
CUR_DATE,
FX_ACCOUNT_ID,
LOAN_ID,
TERM_NO,
START_DATE,
BILLING_DATE,
END_DATE,
CLEAR_DATE,
TERM_STATUS,
PRIN_TOTAL,
PRIN_REPAY,
PRIN_BAL,
INT_TOTAL,
INT_BAL,
INT_REPAY,
CYCLE_INT_TOTAL,
CYCLE_INT_REPAY,
CYCLE_INT_BAL,
PNLT_INT_TOTAL,
PNLT_INT_REPAY,
PNLT_INT_BAL,
PRE_PMT_FEE_REPAY,
DAYS_OVD,
CAPITAL_SET_NO,
now(),
PART_DATE
from (
select
CUR_DATE,
FX_ACCOUNT_ID,
LOAN_ID,
TERM_NO,
START_DATE,
BILLING_DATE,
END_DATE,
CLEAR_DATE,
TERM_STATUS,
PRIN_TOTAL,
PRIN_REPAY,
PRIN_BAL,
INT_TOTAL,
INT_BAL,
INT_REPAY,
CYCLE_INT_TOTAL,
CYCLE_INT_REPAY,
CYCLE_INT_BAL,
PNLT_INT_TOTAL,
PNLT_INT_REPAY,
PNLT_INT_BAL,
PRE_PMT_FEE_REPAY,
DAYS_OVD,
CAPITAL_SET_NO,
dw_tx_dt as part_date ,
row_number() over(partition by LOAN_ID, TERM_NO order by cur_date desc) as rn
from bistg.DRXW_DYMID_REPAY_PLAN_TEMP1
where dw_tx_dt >= str_to_date(‘20230601’,’%Y%m%d’) and dw_tx_dt <= str_to_date(‘20230701’,’%Y%m%d’)
)a where rn = 1

#2

image
image1824×370 50.3 KB

#3

CREATE TABLE bidw.XW_DYMID_REPAY_PLAN (
LOAN_ID varchar(200)not NULL COMMENT “”,
TERM_NO varchar(200)not NULL COMMENT “”,
CUR_DATE varchar(8) NULL COMMENT “”,
FX_ACCOUNT_ID varchar(200) NULL COMMENT “”,
START_DATE varchar(8) NULL COMMENT “”,
BILLING_DATE varchar(8) NULL COMMENT “”,
END_DATE varchar(8) NULL COMMENT “”,
CLEAR_DATE varchar(8) NULL COMMENT “”,
TERM_STATUS varchar(2) NULL COMMENT “”,
PRIN_TOTAL decimal64(16, 2) NULL COMMENT “”,
PRIN_REPAY decimal64(16, 2) NULL COMMENT “”,
PRIN_BAL decimal64(16, 2) NULL COMMENT “）”,
INT_TOTAL decimal64(16, 2) NULL COMMENT “”,
INT_BAL decimal64(16, 2) NULL COMMENT “”,
INT_REPAY decimal64(16, 2) NULL COMMENT “”,
CYCLE_INT_TOTAL decimal64(16, 2) NULL COMMENT “”,
CYCLE_INT_REPAY decimal64(16, 2) NULL COMMENT “”,
CYCLE_INT_BAL decimal64(16, 2) NULL COMMENT “”,
PNLT_INT_TOTAL decimal64(16, 2) NULL COMMENT “”,
PNLT_INT_REPAY decimal64(16, 2) NULL COMMENT “（单位：分）”,
PNLT_INT_BAL decimal64(16, 2) NULL COMMENT “（单位：分）”,
PRE_PMT_FEE_REPAY decimal64(16, 2) NULL COMMENT “(分)”,
DAYS_OVD decimal64(6, 0) NULL COMMENT “”,
CAPITAL_SET_NO varchar(64) NULL COMMENT “”,
DATE_TIME datetime NULL COMMENT “数据入库时间”,
PART_DATE date NULL COMMENT “分区日期,使用业务日期”
)
PRIMARY KEY(LOAN_ID,TERM_NO)
COMMENT “”
DISTRIBUTED BY HASH(LOAN_ID,TERM_NO) BUCKETS 60
order by (CUR_DATE,LOAN_ID)
PROPERTIES (
“replication_num” = “2”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);

#5

补充一下。集群信息：
使用的是存算一体架构 Starrock版本使用的是 3.0.3
带宽是万兆带宽

image
image730×482 28.4 KB

#6
  • 当前表中是否有某些大字段，字段长度较大
  • 建议先拆成多个任务跑
#7

最大的字段使用的是varchar 200
image

#8

另外是否考虑打开这个限制呢？

#9

应该是查询触发窗口函数那里的限制了。去BE日志里搜索下这个报错，发下上下文。

#10

别外把这个SQL的执行计划发下，按说会走到一个特殊优化的。

#11

PLAN FRAGMENT 0
OUTPUT EXPRS:1: loan_id | 2: term_no | 3: cur_date | 4: fx_account_id | 5: start_date | 6: billing_date | 7: end_date | 8: clear_date | 9: term_status | 10: prin_total | 11: prin_repay | 12: prin_bal | 13: int_total | 14: int_bal | 15: int_repay | 16: cycle_int_total | 17: cycle_int_repay | 18: cycle_int_bal | 19: pnlt_int_total | 20: pnlt_int_repay | 21: pnlt_int_bal | 22: pre_pmt_fee_repay | 29: DAYS_OVD | 24: capital_set_no | 28: now | 30: PART_DATE
PARTITION: HASH_PARTITIONED: 1: loan_id, 2: term_no

OLAP TABLE SINK
TABLE: XW_DYMID_REPAY_PLAN
TUPLE ID: 6
RANDOM

6:Project
| <slot 1> : 1: loan_id
| <slot 2> : 2: term_no
| <slot 3> : 3: cur_date
| <slot 4> : 4: fx_account_id
| <slot 5> : 5: start_date
| <slot 6> : 6: billing_date
| <slot 7> : 7: end_date
| <slot 8> : 8: clear_date
| <slot 9> : 9: term_status
| <slot 10> : 10: prin_total
| <slot 11> : 11: prin_repay
| <slot 12> : 12: prin_bal
| <slot 13> : 13: int_total
| <slot 14> : 14: int_bal
| <slot 15> : 15: int_repay
| <slot 16> : 16: cycle_int_total
| <slot 17> : 17: cycle_int_repay
| <slot 18> : 18: cycle_int_bal
| <slot 19> : 19: pnlt_int_total
| <slot 20> : 20: pnlt_int_repay
| <slot 21> : 21: pnlt_int_bal
| <slot 22> : 22: pre_pmt_fee_repay
| <slot 24> : 24: capital_set_no
| <slot 28> : ‘2024-01-31 14:22:35’
| <slot 29> : CAST(23: days_ovd AS DECIMAL64(6,0))
| <slot 30> : CAST(26: dw_tx_dt AS DATE)
|
5:SELECT
| predicates: 27: row_number() = 1
|
4:ANALYTIC
| functions: [, row_number(), ]
| partition by: 1: loan_id, 2: term_no
| order by: 3: cur_date DESC
| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
3:SORT
| order by: <slot 1> 1: loan_id ASC, <slot 2> 2: term_no ASC, <slot 3> 3: cur_date DESC
| offset: 0
|
2:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
HASH_PARTITIONED: 1: loan_id, 2: term_no

1:PARTITION-TOP-N
| partition by: 1: loan_id , 2: term_no
| partition limit: 1
| order by: <slot 1> 1: loan_id ASC, <slot 2> 2: term_no ASC, <slot 3> 3: cur_date DESC
| offset: 0
|
0:OlapScanNode
TABLE: DRXW_DYMID_REPAY_PLAN_TEMP1
PREAGGREGATION: ON
PREDICATES: 26: dw_tx_dt <= ‘2023-07-01 00:00:00’
partitions=31/952
rollup: DRXW_DYMID_REPAY_PLAN_TEMP1
tabletRatio=186/186
tabletList=5948664,5948666,5948668,5948670,5948672,5948674,5956064,5956066,5956068,5956070 …
cardinality=30900557
avgRowSize=243.24316
numNodes=0

京ICP备2022026421号-1