【已解决】主键模型表指定部分列更新失败,未指定的默认列仍然被更新

Viewed 146

具体问题

目前有一张表,其中有一个入库时间字段默认值为当前时间,业务数据需要实时插入和更新,针对已有的数据,更新时不需要更新入库时间。
现场使用api接口stream_load工具导入数据时指定了除入库时间以外的字段,且开启了mow ,但数据更新时任然更新了入库时间字段,
具体数据及表结构内容无法拍照

1.表结构关键结构如下

2.kettle方案及关键节点配置


1 Answers

【问题状态】处理中
【问题处理】通过stream load文件可以实现部分列更新,并且如果不指定入库时间字段是不会更新的,确认以下几个问题:

  1. 表模型是不是unique表,并且开启了mow;
  2. "partial_columns:true"参数是否传到doris。

验证步骤如下:
一、 insert into方式

CREATE TABLE `test_update` (
  `id` int(11) NULL,
  `date_time` datetime(6) NULL COMMENT 'insert时间',
  `cruise_id` int(11) NULL,
  `dt` datetime(6) not NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=OLAP
unique KEY(`id`, `date_time`)
COMMENT '车辆子任务'
PARTITION BY RANGE(`date_time`)
(PARTITION p20240703 VALUES [('2024-07-03 00:00:00'), ('2024-07-04 00:00:00')),
PARTITION p20240704 VALUES [('2024-07-04 00:00:00'), ('2024-07-05 00:00:00')),
PARTITION p20240705 VALUES [('2024-07-05 00:00:00'), ('2024-07-06 00:00:00')),
PARTITION p20240706 VALUES [('2024-07-06 00:00:00'), ('2024-07-07 00:00:00')),
PARTITION p20240707 VALUES [('2024-07-07 00:00:00'), ('2024-07-08 00:00:00')))
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-6",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "2",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_unique_key_merge_on_write" = "true",
"enable_single_replica_compaction" = "false"
);

SELECT  * FROM test_update

set enable_unique_key_partial_update=true


INSERT INTO test_update (id, date_time, cruise_id) VALUES(2189990, '2024-07-06 11:17:33', 222);

INSERT INTO test_update (id, date_time, cruise_id) VALUES(2189990, '2024-07-06 11:17:33', 333);

image.png

二、stream Load 方式

数据文件:
2189990,2024-07-06 11:17:33, 333

curl --location-trusted -u root -T test_part.csv -H "format:csv" -H "columns:id,date_time,cruise_id" -H "column_separator:," -H "label:112123" -H "partial_columns:true" http://10.16.10.6:6010/api/demo/test_update/_stream_load