需求:主键模型,实现对plate_no等列的部分列更新
1.表结构:
plate_no列是not null类型
CREATE TABLE `test` (
`archive_id` VARCHAR(20) NOT NULL,
`plate_no` VARCHAR(18) NOT NULL,
`plate_color` VARCHAR(2) NOT NULL,
`first_device_id` VARCHAR(48) NULL,
`last_device_id` VARCHAR(48) NULL,
`vehicle_brand` VARCHAR(3) NULL,
`vehicle_model` VARCHAR(32) NULL,
`vehicle_color` VARCHAR(2) NULL,
`storage_url_1` VARCHAR(1024) NULL,
`storage_url_3` VARCHAR(1024) NULL,
`first_pass_time` DATETIME NULL,
`last_pass_time` DATETIME NULL,
`tags` ARRAY<TEXT> NULL,
`dm_motorvehicle_tags` ARRAY<TEXT> NULL,
`insert_time` DATETIME NULL,
`update_time` DATETIME NULL,
`dm_motorvehicle_tags_pattern` ARRAY<TEXT> NULL
) ENGINE=OLAP
UNIQUE KEY(`archive_id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`archive_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);
2.代码使用stream load csv格式实现部分列更新:
outStr = csvSb.append(motorVehicleProfileDoris.getArchive_id()).append(",")
.append(motorVehicleProfileDoris.getPlate_no()).append(",") //csv数据中已经包含plate_no的数据
.append(motorVehicleProfileDoris.getPlate_color()).append(",")
.append(motorVehicleProfileDoris.getLast_device_id()).append(",")
.append(motorVehicleProfileDoris.getStorage_url_1()).append(",")
.append(motorVehicleProfileDoris.getStorage_url_3()).append(",") .append(motorVehicleProfileDoris.getLast_pass_time()).append(",")
.append(motorVehicleProfileDoris.getUpdate_time())
.toString();
3.报错内容:
INFO (leaderCheckpointer|102) [DatabaseTransactionMgr.replayUpsertTransactionState():2156] replay a ABORTED transaction TransactionState. transaction id: 2838905, label: unique-label-stream-load-update_0_test_b1b3e0e8-ad03-406f-94b6-e1313abfb832, db id: 10838, table id list: 2906761, c allback id: -1, coordinator: BE: xxx, transaction status: ABORTED, error replicas num: 0, replica ids: , prepare time: 1727418709789, commit time: -1, finish time: 1727418709888, reason: [CANCELLED][INTERNAL_ERROR]tablet error: [E-207]the unmentioned column plate_no
should have default value or be nullable for newly inserted rows in non-strict mode partial update, host: xxx
造的数据都是更新的数据,key在表中存在的数据,那为什么还会出现newly inserted rows呢?