【已解决】使用routine load向包含varaint列的动态分区表写入数据导致be崩溃

Viewed 81

使用routine load向包含varaint列的动态分区表写入数据,刚开始正常,某一天突然异常,写入时be频繁崩溃,刚开始写入的json数据和表结构完成匹配,后来加了一列tenant_id,不知道是不是这个原因导致异常。相关语句如下:

-- 建表
create table asset_data(
  `dt` date not null,
  `asset_code` varchar(20),
  `data_time` datetime not null,
  `data_type` tinyint not null,
  `device_code` varchar(20) not null,
  `raw_time` datetime not null,
  `system_time` datetime not null default current_timestamp,
  `content` variant
) unique key(`dt`, `asset_code`, `data_time`)
partition by range (`dt`)(
  partition p_default values less than ('2024-01-01'),
  partition p202401 values less than  ('2024-02-01'),
  partition p202402 values less than  ('2024-03-01'),
  partition p202403 values less than  ('2024-04-01'),
  partition p202404 values less than  ('2024-05-01')
)
distributed by hash(`asset_code`) buckets 6
properties(
  "replication_allocation" = "tag.location.default: 2",
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "MONTH",
  "dynamic_partition.end" = "1",
  "dynamic_partition.prefix" = "p"
);

-- 创建routine load
create routine load rl_workdata on asset_data
properties(
  "desired_concurrent_number" = "4",
  "max_error_number" = "99999999999999999",
  "max_batch_interval" = "5",
  "max_batch_rows" = "200000",
  "max_batch_size" = "1073741824",
  "strict_mode" = "false",
  "format" = "json"
)
from kafka(
  "kafka_broker_list" = "bigdata15:6667,bigdata16:6667,bigdata18:6667",
  "kafka_topic" = "iot_workdata_doris",
  "property.kafka_default_offsets" = "offset_beginning",
  "property.group.id" = "doris_hanyunos_xgkj",
  "property.client.id" = "doris_hanyunos_xgkj"
);

-- insert插入数据正常
insert into asset_data(dt, asset_code, device_code, data_time, raw_time, data_type, content) values
('2024-05-19', 'test', 'test', '2024-05-19 18:09:37', '2024-05-20 00:03:27', 1, '{"vincode":"test","OpenCapStatus":0,"AccStatus":1,"posuijileixing":2,"TowedStatus":0,"EncryptLng":35.3,"deviceId":"A1615217400141","functionSetCode":"1791000311071879168","GSMRegistStatus":1,"int2":564,"int1":1600,"CanCommunicationFault":0,"int4":85,"int3":100,"int6":13,"int5":25,"int9":92,"lng":35.3,"FunctionCode":"1791000311071879168","GsmSignal":0,"CmdId":3,"VinCode":"test","LocationStatus":1,"yeyayouwendu":30,"GpsAntennaShort":0,"GpsModuleFault":0,"ChargingStatus":0,"ipFlag":0,"PowerOff":0,"gpsPlace":0,"zhujidongeyouwendu":100,"CI":0,"VehicleId":"test","youzhouchengwendu":52,"VehicleWorkStatus":0,"LAC":0,"GpsSatellite":7,"OverSpeedStatus":0,"fadongjilengqueyewendu":85,"GpsHealthStatus":1,"fadongjifuzaibaifenbi":25,"AccTimeSum":11688170,"LowVoltage":0,"fadongjijiyouyali":564,"PPPStatus":0,"gpsfankuizhi":0,"int25":3141,"int24":0,"suojingangyali":142,"int23":0,"fadongjilengqueyewei":100,"int22":100,"int21":142,"blindFlag":1,"int20":0,"GpsAntennaBreak":0,"lat":28.67,"SystemTime":"2024-05-20 00:03:27","LinkStatus":0,"SerialNo":6,"int14":52,"fadongjishishiyouhao":13,"int13":53,"gpsTime":"2024-05-19 18:09:37","int12":37,"fadongjizonggongzuoshijian":3141,"int19":856,"GpsWorkStatus":0,"int18":219,"int17":100,"TotalEngineFuel":79,"int16":100,"hoistFlag":0,"InV":4,"decimal5":79,"EncryptLat":28.67,"int11":30,"byte1":2,"int10":86,"TotalEngineHours":2729,"WorkDataTime":"2024-05-19 18:09:37","SwitchStatus":0,"gongzuomoshi":0,"zhujidongezuowendu":100,"altitude":1821,"DeviceId":"A1615217400141","shebeiguzhanghao":37,"VoltLowerThanThreshold":0,"OutV":28,"speed":0,"zuozhouchengwendu":53,"zhujisudu":219,"zhujidianliu":100,"vehicleId":"1755393806301908994","zhushusongjisudu":0,"yeyayouwei":86,"direction":0,"GPRSRegistStatus":0,"kjFlag":0,"LockStatus":0,"fadongjizhuaisu":1600,"ranyouyewei":92,"geiliaosudu":856,"VehicleHealthStatus":0}');

写入数据样例:
{"dt":"2024-05-19","asset_code":"test","device_code":"test","data_time":"2024-05-19 18:09:37","raw_time":"2024-05-20 00:03:27","data_type":1,"content":{"vincode":"test","OpenCapStatus":0,"AccStatus":1,"posuijileixing":2,"TowedStatus":0,"EncryptLng":35.3,"deviceId":"A1615217400141","functionSetCode":"1791000311071879168","GSMRegistStatus":1,"int2":564,"int1":1600,"CanCommunicationFault":0,"int4":85,"int3":100,"int6":13,"int5":25,"int9":92,"lng":35.3,"FunctionCode":"1791000311071879168","GsmSignal":0,"CmdId":3,"VinCode":"test","LocationStatus":1,"yeyayouwendu":30,"GpsAntennaShort":0,"GpsModuleFault":0,"ChargingStatus":0,"ipFlag":0,"PowerOff":0,"gpsPlace":0,"zhujidongeyouwendu":100,"CI":0,"VehicleId":"test","youzhouchengwendu":52,"VehicleWorkStatus":0,"LAC":0,"GpsSatellite":7,"OverSpeedStatus":0,"fadongjilengqueyewendu":85,"GpsHealthStatus":1,"fadongjifuzaibaifenbi":25,"AccTimeSum":11688170,"LowVoltage":0,"fadongjijiyouyali":564,"PPPStatus":0,"gpsfankuizhi":0,"int25":3141,"int24":0,"suojingangyali":142,"int23":0,"fadongjilengqueyewei":100,"int22":100,"int21":142,"blindFlag":1,"int20":0,"GpsAntennaBreak":0,"lat":28.67,"SystemTime":"2024-05-20 00:03:27","LinkStatus":0,"SerialNo":6,"int14":52,"fadongjishishiyouhao":13,"int13":53,"gpsTime":"2024-05-19 18:09:37","int12":37,"fadongjizonggongzuoshijian":3141,"int19":856,"GpsWorkStatus":0,"int18":219,"int17":100,"TotalEngineFuel":79,"int16":100,"hoistFlag":0,"InV":4,"decimal5":79,"EncryptLat":28.67,"int11":30,"byte1":2,"int10":86,"TotalEngineHours":2729,"WorkDataTime":"2024-05-19 18:09:37","SwitchStatus":0,"gongzuomoshi":0,"zhujidongezuowendu":100,"altitude":1821,"DeviceId":"A1615217400141","shebeiguzhanghao":37,"VoltLowerThanThreshold":0,"OutV":28,"speed":0,"zuozhouchengwendu":53,"zhujisudu":219,"zhujidianliu":100,"vehicleId":"1755393806301908994","zhushusongjisudu":0,"yeyayouwei":86,"direction":0,"GPRSRegistStatus":0,"kjFlag":0,"LockStatus":0,"fadongjizhuaisu":1600,"ranyouyewei":92,"geiliaosudu":856,"VehicleHealthStatus":0},"tenant_id":"1790664137855545345"}

异常信息:
1716773006327.jpg

2 Answers

问题已定位,是由于空key导致的, 数据如 {"" : 1234, "column1" : 456}会导致crash

这上面有一个queryid去审计日志里面看下具体的sql