在写入分区表数据时, 出现报错
execute sql error: Insert has filtered data in strict mode, txn_id = 185276 tracking
sql = select tracking_log from information_schema.load_tracking_logs where job_id=163287
Error: The row create partition failed since Runtime error: failed to analyse partition value:20001… Row: [20001, 2010101, 2023-06-11, ‘M’, 0, ‘L1’, 25000.00, ‘Mac Book Pro 13寸 16G 银色’, 170, 20101, 201, 2, ‘苹果手机’, ‘苹果电脑’, ‘电脑’, 210, 2, 1, 50000.00, ‘2023-06-12 10:57:14’, ‘insert’]
Error: The row create partition failed since Runtime error: failed to analyse partition value:10001… Row: [10001, 1010101, 2023-06-11, ‘M’, 0, ‘L1’, 50000.00, ‘iPhone16-plus粉红色’, 169, 10101, 101, 1, ‘苹果手机’, ‘智能手机’, ‘手机’, 110, 3, 1, 300000.00, ‘2023-06-12 10:57:14’, ‘insert’]
集群：单机部署, 单数据副本
建表：
CREATE TABLE dim_sku_info (
id bigint(20) NOT NULL COMMENT “商品 id”,
spu_id bigint(20) NOT NULL COMMENT “spuid”,
create_time date NOT NULL COMMENT “创建时间”,
price decimal64(10, 0) NULL COMMENT “商品价格”,
sku_name varchar(200) NULL COMMENT “商品名称”,
sku_desc varchar(2000) NULL COMMENT “商品描述”,
weight decimal64(10, 2) NULL COMMENT “重量”,
tm_id bigint(20) NULL COMMENT “品牌 id”,
tm_name varchar(20) NULL COMMENT “品牌名称”,
category3_id bigint(20) NULL COMMENT “三级分类 id”,
category2_id bigint(20) NULL COMMENT “二级分类 id”,
category1_id bigint(20) NULL COMMENT “一级分类 id”,
category3_name varchar(20) NULL COMMENT “三级分类名称”,
category2_name varchar(20) NULL COMMENT “二级分类名称”,
category1_name varchar(20) NULL COMMENT “一级分类名称”,
spu_name varchar(200) NULL COMMENT “spu 名称”,
starrocks_last_modify_date varchar(655) NULL COMMENT “操作时间”,
starrocks_last_modify_type varchar(655) NULL COMMENT “操作类型, insert, delete, update”
) ENGINE=OLAP
PRIMARY KEY(id, spu_id, create_time)
COMMENT “商品维度表”
PARTITION BY date_trunc(‘day’, create_time)
DISTRIBUTED BY HASH(id, spu_id) BUCKETS 8
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
数仓SQL脚本
insert into dwd.dim_sku_info
select
sku.id id,
sku.spu_id spu_id,
sku.create_time create_time,
sku.price price,
sku.sku_name sku_name,
sku.sku_desc sku_desc,
sku.weight weight,
sku.tm_id tm_id,
ob.tm_name tm_name,
sku.category3_id category3_id,
c2.id category2_id,
c1.id category1_id,
c3.name category3_name,
c2.name category2_name,
c1.name category1_name,
spu.spu_name spu_name,
CURRENT_TIMESTAMP starrocks_last_modify_date,
sku.starrocks_last_modify_type starrocks_last_modify_type
from (select * from ods.sku_info WHERE date_trunc(‘day’, starrocks_last_modify_date) = CURRENT_DATE()) sku
join
(select * from ods.base_trademark) ob
on
sku.tm_id = ob.tm_id
join
(select * from ods.spu_info) spu
on
spu.id = sku.spu_id
join
(select * from ods.base_category3) c3
on
sku.category3_id = c3.id
join
(select * from ods.base_category2) c2
on
c3.category2_id = c2.id
join
(select * from ods.base_category1) c1
on
c2.category1_id = c1.id