borkerload数据发生异常all partitions have no load data

💬 StarRocks 用户问答 数据导入与导出
#1

【详述】问题详细描述
【导入/导出方式】
【背景】将数据从hive里导出来到sr里
【业务影响】
【StarRocks版本】例如：1.19.2
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【附件】

  • fe.warn.log/be.warn.log/相应截图
#2

CREATE TABLE dwd_detail_charge (
create_time datetime NOT NULL COMMENT “创建时间”,
order_id varchar(65533) NOT NULL COMMENT “订单号ID”,
gun_id varchar(65533) NOT NULL COMMENT “充电枪ID”,
plate_number varchar(65533) NOT NULL COMMENT “车牌号”,
user_id varchar(65533) NOT NULL COMMENT “会员ID”,
elec_total decimal64(11, 4) REPLACE NULL COMMENT “充电量”,
elec_price decimal64(11, 4) REPLACE NULL COMMENT “电费单价”,
elec_amount decimal64(11, 2) REPLACE NULL COMMENT “电费总额”,
elec_start_time datetime REPLACE NULL COMMENT “电费计费开始时间”,
fixed_start_time datetime REPLACE NULL COMMENT “修复电费计费开始时间”,
fixed_end_time datetime REPLACE NULL COMMENT “修复电费计费结束时间”,
elec_end_time datetime REPLACE NULL COMMENT “电费计费结束时间”,
modify_time datetime REPLACE NULL COMMENT “修改时间”,
member_type varchar(65533) REPLACE NULL COMMENT “用户类型”,
bill_end_time datetime REPLACE NULL COMMENT “账单结束时间”
) ENGINE=OLAP
PRIMARY KEY(create_time, order_id, gun_id, plate_number, user_id)
COMMENT “OLAP”
PARTITION BY RANGE(create_time)
(PARTITION p201701 VALUES [(‘2017-01-01 00:00:00’), (‘2017-02-01 00:00:00’)),
PARTITION p201702 VALUES [(‘2017-02-01 00:00:00’), (‘2017-03-01 00:00:00’)),
PARTITION p201703 VALUES [(‘2017-03-01 00:00:00’), (‘2017-04-01 00:00:00’)),
PARTITION p201704 VALUES [(‘2017-04-01 00:00:00’), (‘2017-05-01 00:00:00’)),
PARTITION p201705 VALUES [(‘2017-05-01 00:00:00’), (‘2017-06-01 00:00:00’)),
PARTITION p201706 VALUES [(‘2017-06-01 00:00:00’), (‘2017-07-01 00:00:00’)),
PARTITION p201707 VALUES [(‘2017-07-01 00:00:00’), (‘2017-08-01 00:00:00’)),
PARTITION p201708 VALUES [(‘2017-08-01 00:00:00’), (‘2017-09-01 00:00:00’)),
PARTITION p201709 VALUES [(‘2017-09-01 00:00:00’), (‘2017-10-01 00:00:00’)),
PARTITION p201710 VALUES [(‘2017-10-01 00:00:00’), (‘2017-11-01 00:00:00’)),
PARTITION p201711 VALUES [(‘2017-11-01 00:00:00’), (‘2017-12-01 00:00:00’)),
PARTITION p201712 VALUES [(‘2017-12-01 00:00:00’), (‘2018-01-01 00:00:00’)),
PARTITION p201801 VALUES [(‘2018-01-01 00:00:00’), (‘2018-02-01 00:00:00’)),
PARTITION p201802 VALUES [(‘2018-02-01 00:00:00’), (‘2018-03-01 00:00:00’)),
PARTITION p201803 VALUES [(‘2018-03-01 00:00:00’), (‘2018-04-01 00:00:00’)),
PARTITION p201804 VALUES [(‘2018-04-01 00:00:00’), (‘2018-05-01 00:00:00’)),
PARTITION p201805 VALUES [(‘2018-05-01 00:00:00’), (‘2018-06-01 00:00:00’)),
PARTITION p201806 VALUES [(‘2018-06-01 00:00:00’), (‘2018-07-01 00:00:00’)),
PARTITION p201807 VALUES [(‘2018-07-01 00:00:00’), (‘2018-08-01 00:00:00’)),
PARTITION p201808 VALUES [(‘2018-08-01 00:00:00’), (‘2018-09-01 00:00:00’)),
PARTITION p201809 VALUES [(‘2018-09-01 00:00:00’), (‘2018-10-01 00:00:00’)),
PARTITION p201810 VALUES [(‘2018-10-01 00:00:00’), (‘2018-11-01 00:00:00’)),
PARTITION p201811 VALUES [(‘2018-11-01 00:00:00’), (‘2018-12-01 00:00:00’)),
PARTITION p201812 VALUES [(‘2018-12-01 00:00:00’), (‘2019-01-01 00:00:00’)),
PARTITION p201901 VALUES [(‘2019-01-01 00:00:00’), (‘2019-02-01 00:00:00’)),
PARTITION p201902 VALUES [(‘2019-02-01 00:00:00’), (‘2019-03-01 00:00:00’)),
PARTITION p201903 VALUES [(‘2019-03-01 00:00:00’), (‘2019-04-01 00:00:00’)),
PARTITION p201904 VALUES [(‘2019-04-01 00:00:00’), (‘2019-05-01 00:00:00’)),
PARTITION p201905 VALUES [(‘2019-05-01 00:00:00’), (‘2019-06-01 00:00:00’)),
PARTITION p201906 VALUES [(‘2019-06-01 00:00:00’), (‘2019-07-01 00:00:00’)),
PARTITION p201907 VALUES [(‘2019-07-01 00:00:00’), (‘2019-08-01 00:00:00’)),
PARTITION p201908 VALUES [(‘2019-08-01 00:00:00’), (‘2019-09-01 00:00:00’)),
PARTITION p201909 VALUES [(‘2019-09-01 00:00:00’), (‘2019-10-01 00:00:00’)),
PARTITION p201910 VALUES [(‘2019-10-01 00:00:00’), (‘2019-11-01 00:00:00’)),
PARTITION p201911 VALUES [(‘2019-11-01 00:00:00’), (‘2019-12-01 00:00:00’)),
PARTITION p201912 VALUES [(‘2019-12-01 00:00:00’), (‘2020-01-01 00:00:00’)),
PARTITION p202001 VALUES [(‘2020-01-01 00:00:00’), (‘2020-02-01 00:00:00’)),
PARTITION p202002 VALUES [(‘2020-02-01 00:00:00’), (‘2020-03-01 00:00:00’)),
PARTITION p202003 VALUES [(‘2020-03-01 00:00:00’), (‘2020-04-01 00:00:00’)),
PARTITION p202004 VALUES [(‘2020-04-01 00:00:00’), (‘2020-05-01 00:00:00’)),
PARTITION p202005 VALUES [(‘2020-05-01 00:00:00’), (‘2020-06-01 00:00:00’)),
PARTITION p202006 VALUES [(‘2020-06-01 00:00:00’), (‘2020-07-01 00:00:00’)),
PARTITION p202007 VALUES [(‘2020-07-01 00:00:00’), (‘2020-08-01 00:00:00’)),
PARTITION p202008 VALUES [(‘2020-08-01 00:00:00’), (‘2020-09-01 00:00:00’)),
PARTITION p202009 VALUES [(‘2020-09-01 00:00:00’), (‘2020-10-01 00:00:00’)),
PARTITION p202010 VALUES [(‘2020-10-01 00:00:00’), (‘2020-11-01 00:00:00’)),
PARTITION p202011 VALUES [(‘2020-11-01 00:00:00’), (‘2020-12-01 00:00:00’)),
PARTITION p202012 VALUES [(‘2020-12-01 00:00:00’), (‘2021-01-01 00:00:00’)),
PARTITION p202101 VALUES [(‘2021-01-01 00:00:00’), (‘2021-02-01 00:00:00’)),
PARTITION p202102 VALUES [(‘2021-02-01 00:00:00’), (‘2021-03-01 00:00:00’)),
PARTITION p202103 VALUES [(‘2021-03-01 00:00:00’), (‘2021-04-01 00:00:00’)),
PARTITION p202104 VALUES [(‘2021-04-01 00:00:00’), (‘2021-05-01 00:00:00’)),
PARTITION p202105 VALUES [(‘2021-05-01 00:00:00’), (‘2021-06-01 00:00:00’)),
PARTITION p202106 VALUES [(‘2021-06-01 00:00:00’), (‘2021-07-01 00:00:00’)),
PARTITION p202107 VALUES [(‘2021-07-01 00:00:00’), (‘2021-08-01 00:00:00’)),
PARTITION p202108 VALUES [(‘2021-08-01 00:00:00’), (‘2021-09-01 00:00:00’)),
PARTITION p202109 VALUES [(‘2021-09-01 00:00:00’), (‘2021-10-01 00:00:00’)),
PARTITION p202110 VALUES [(‘2021-10-01 00:00:00’), (‘2021-11-01 00:00:00’)),
PARTITION p202111 VALUES [(‘2021-11-01 00:00:00’), (‘2021-12-01 00:00:00’)),
PARTITION p202112 VALUES [(‘2021-12-01 00:00:00’), (‘2022-01-01 00:00:00’)),
PARTITION p202201 VALUES [(‘2022-01-01 00:00:00’), (‘2022-02-01 00:00:00’)),
PARTITION p202202 VALUES [(‘2022-02-01 00:00:00’), (‘2022-03-01 00:00:00’)),
PARTITION p202203 VALUES [(‘2022-03-01 00:00:00’), (‘2022-04-01 00:00:00’)),
PARTITION p202204 VALUES [(‘2022-04-01 00:00:00’), (‘2022-05-01 00:00:00’)),
PARTITION p202205 VALUES [(‘2022-05-01 00:00:00’), (‘2022-06-01 00:00:00’)),
PARTITION p202206 VALUES [(‘2022-06-01 00:00:00’), (‘2022-07-01 00:00:00’)),
PARTITION p202207 VALUES [(‘2022-07-01 00:00:00’), (‘2022-08-01 00:00:00’)),
PARTITION p202208 VALUES [(‘2022-08-01 00:00:00’), (‘2022-09-01 00:00:00’)),
PARTITION p202209 VALUES [(‘2022-09-01 00:00:00’), (‘2022-10-01 00:00:00’)),
PARTITION p202210 VALUES [(‘2022-10-01 00:00:00’), (‘2022-11-01 00:00:00’)),
PARTITION p202211 VALUES [(‘2022-11-01 00:00:00’), (‘2022-12-01 00:00:00’)),
PARTITION p202212 VALUES [(‘2022-12-01 00:00:00’), (‘2023-01-01 00:00:00’)),
PARTITION p202301 VALUES [(‘2023-01-01 00:00:00’), (‘2023-02-01 00:00:00’)),
PARTITION p202302 VALUES [(‘2023-02-01 00:00:00’), (‘2023-03-01 00:00:00’)),
PARTITION p202303 VALUES [(‘2023-03-01 00:00:00’), (‘2023-04-01 00:00:00’)),
PARTITION p202304 VALUES [(‘2023-04-01 00:00:00’), (‘2023-05-01 00:00:00’)),
PARTITION p202305 VALUES [(‘2023-05-01 00:00:00’), (‘2023-06-01 00:00:00’)),
PARTITION p202306 VALUES [(‘2023-06-01 00:00:00’), (‘2023-07-01 00:00:00’)),
PARTITION p202307 VALUES [(‘2023-07-01 00:00:00’), (‘2023-08-01 00:00:00’)),
PARTITION p202308 VALUES [(‘2023-08-01 00:00:00’), (‘2023-09-01 00:00:00’)),
PARTITION p202309 VALUES [(‘2023-09-01 00:00:00’), (‘2023-10-01 00:00:00’)),
PARTITION p202310 VALUES [(‘2023-10-01 00:00:00’), (‘2023-11-01 00:00:00’)),
PARTITION p202311 VALUES [(‘2023-11-01 00:00:00’), (‘2023-12-01 00:00:00’)),
PARTITION p202312 VALUES [(‘2023-12-01 00:00:00’), (‘2024-01-01 00:00:00’)),
PARTITION p202401 VALUES [(‘2024-01-01 00:00:00’), (‘2024-02-01 00:00:00’)),
PARTITION p202402 VALUES [(‘2024-02-01 00:00:00’), (‘2024-03-01 00:00:00’)),
PARTITION p202403 VALUES [(‘2024-03-01 00:00:00’), (‘2024-04-01 00:00:00’)),
PARTITION p202404 VALUES [(‘2024-04-01 00:00:00’), (‘2024-05-01 00:00:00’)),
PARTITION p202405 VALUES [(‘2024-05-01 00:00:00’), (‘2024-06-01 00:00:00’)),
PARTITION p202406 VALUES [(‘2024-06-01 00:00:00’), (‘2024-07-01 00:00:00’)),
PARTITION p202407 VALUES [(‘2024-07-01 00:00:00’), (‘2024-08-01 00:00:00’)),
PARTITION p202408 VALUES [(‘2024-08-01 00:00:00’), (‘2024-09-01 00:00:00’)),
PARTITION p202409 VALUES [(‘2024-09-01 00:00:00’), (‘2024-10-01 00:00:00’)),
PARTITION p202410 VALUES [(‘2024-10-01 00:00:00’), (‘2024-11-01 00:00:00’)),
PARTITION p202411 VALUES [(‘2024-11-01 00:00:00’), (‘2024-12-01 00:00:00’)),
PARTITION p202412 VALUES [(‘2024-12-01 00:00:00’), (‘2025-01-01 00:00:00’)),
PARTITION p202501 VALUES [(‘2025-01-01 00:00:00’), (‘2025-02-01 00:00:00’)),
PARTITION p202502 VALUES [(‘2025-02-01 00:00:00’), (‘2025-03-01 00:00:00’)),
PARTITION p202503 VALUES [(‘2025-03-01 00:00:00’), (‘2025-04-01 00:00:00’)),
PARTITION p202504 VALUES [(‘2025-04-01 00:00:00’), (‘2025-05-01 00:00:00’)),
PARTITION p202505 VALUES [(‘2025-05-01 00:00:00’), (‘2025-06-01 00:00:00’)),
PARTITION p202506 VALUES [(‘2025-06-01 00:00:00’), (‘2025-07-01 00:00:00’)),
PARTITION p202507 VALUES [(‘2025-07-01 00:00:00’), (‘2025-08-01 00:00:00’)),
PARTITION p202508 VALUES [(‘2025-08-01 00:00:00’), (‘2025-09-01 00:00:00’)),
PARTITION p202509 VALUES [(‘2025-09-01 00:00:00’), (‘2025-10-01 00:00:00’)),
PARTITION p202510 VALUES [(‘2025-10-01 00:00:00’), (‘2025-11-01 00:00:00’)),
PARTITION p202511 VALUES [(‘2025-11-01 00:00:00’), (‘2025-12-01 00:00:00’)),
PARTITION p202512 VALUES [(‘2025-12-01 00:00:00’), (‘2026-01-01 00:00:00’)),
PARTITION p202601 VALUES [(‘2026-01-01 00:00:00’), (‘2026-02-01 00:00:00’)),
PARTITION p202602 VALUES [(‘2026-02-01 00:00:00’), (‘2026-03-01 00:00:00’)),
PARTITION p202603 VALUES [(‘2026-03-01 00:00:00’), (‘2026-04-01 00:00:00’)),
PARTITION p202604 VALUES [(‘2026-04-01 00:00:00’), (‘2026-05-01 00:00:00’)),
PARTITION p202605 VALUES [(‘2026-05-01 00:00:00’), (‘2026-06-01 00:00:00’)),
PARTITION p202606 VALUES [(‘2026-06-01 00:00:00’), (‘2026-07-01 00:00:00’)),
PARTITION p202607 VALUES [(‘2026-07-01 00:00:00’), (‘2026-08-01 00:00:00’)),
PARTITION p202608 VALUES [(‘2026-08-01 00:00:00’), (‘2026-09-01 00:00:00’)),
PARTITION p202609 VALUES [(‘2026-09-01 00:00:00’), (‘2026-10-01 00:00:00’)),
PARTITION p202610 VALUES [(‘2026-10-01 00:00:00’), (‘2026-11-01 00:00:00’)),
PARTITION p202611 VALUES [(‘2026-11-01 00:00:00’), (‘2026-12-01 00:00:00’)),
PARTITION p202612 VALUES [(‘2026-12-01 00:00:00’), (‘2027-01-01 00:00:00’)))
DISTRIBUTED BY HASH(order_id) BUCKETS 10
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
); |

LOAD LABEL towatt_charge.label1

(

DATA INFILE("hdfs://node1.ambari.com:8020/warehouse/tablespace/managed/hive/towatt_data.db/dwd_detail_charge/month=202101/HIVE_UNION_SUBDIR_1/000000_0/")

INTO TABLE dwd_detail_charge

COLUMNS TERMINATED BY "\t"

(create_time, order_id,gun_id,plate_number,user_id,elec_total,elec_price,elec_amount,elec_start_time,fixed_start_time,fixed_end_time,elec_end_time,modify_time,member_type,bill_end_time)    

SET

(

   create_time=if(create_time is null,'1900-01-01 00:00:00',create_time),

   order_id=if(order_id is null,'null',order_id),

   gun_id=if(gun_id is null,'null',gun_id),

   plate_number=if(plate_number is null,'null',plate_number),

   user_id=if(user_id is null,'null',user_id),

   elec_total=elec_total,

   elec_price=elec_price,

   elec_amount=elec_amount,

   elec_start_time=elec_start_time,

   fixed_start_time=fixed_start_time,

   fixed_end_time=fixed_end_time,

   elec_end_time=elec_end_time,

   modify_time=modify_time,

   member_type=member_type,

   bill_end_time=bill_end_time

)

where order_id is not null and create_time is not null

)

WITH BROKER ‘broker1’

(

"username" = "hive",

"password" = "hive"

)

PROPERTIES

(

"timeout" = "259200"

);

#3

所有分区的匹配 tabname// 报错： No source file in this table(dwd_detail_charge).

hdfs://node1.ambari.com:8020/warehouse/tablespace/managed/hive/towatt_data.db/dwd_detail_charge/month=202101/HIVE_UNION_SUBDIR_1/000000_0

的确按照号匹匹配 ，我按照路径匹配改写成 /// 就报错 all partitions have no load data

#4

image

#5

是文件，这里我设置目录，后缀我加不加 “/” 都报这个错误

#6

我把源文件内容：附件000000_0 (4.1 MB)

#7

where order_id is not null and create_time is not null 这个指定应该也没错吧

#8

COLUMNS TERMINATED BY “\t” 应该也没错啊

#9

您这第一列字段不是create_time时间格式image

#10

顺序不对，好的我调整一下

#11

但是现在sr 和hive数据量匹配不上，
hive select count(1) from dwd_detail_charge where order_id is not null and create_time is not null; 599604
load sr时 where create_time is not null

image
image1047×296 19 KB

京ICP备2022026421号-1