You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "bobhan1 (via GitHub)" <gi...@apache.org> on 2023/07/07 08:20:50 UTC
[GitHub] [doris] bobhan1 opened a new pull request, #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
bobhan1 opened a new pull request, #21623:
URL: https://github.com/apache/doris/pull/21623
## Proposed changes
1. expand the semantics of variable `strict_mode` to control the behavior for stream load: if `strict_mode` is true, the stream load can only update existing rows; if `strict_mode` is false, the stream load can insert new rows if the key is not present in the table
2. when inserting a new row in non-strict mode stream load, the unmentioned columns should have default value or be nullable
## Further comments
If this is a relatively large or complex change, kick off the discussion at [dev@doris.apache.org](mailto:dev@doris.apache.org) by explaining why you chose the solution you did and what alternatives you considered, etc...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1626329479
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625012529
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1626337491
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1626348828
TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 48.93 seconds
stream load tsv: 451 seconds loaded 74807831229 Bytes, about 158 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 58 seconds loaded 1101869774 Bytes, about 18 MB/s
stream load parquet: 29 seconds loaded 861443392 Bytes, about 28 MB/s
insert into select: 69.8 seconds inserted 10000000 Rows, about 143K ops/s
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230707230845_clickbench_pr_174739.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625161631
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625141261
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1627993678
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 53.6 seconds
stream load tsv: 500 seconds loaded 74807831229 Bytes, about 142 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
insert into select: 88.9 seconds inserted 10000000 Rows, about 112K ops/s
storage size: 17166847077 Bytes
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230710104755_clickbench_pr_175400.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628542835
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628584574
PR approved by anyone and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625117378
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 53.14 seconds
stream load tsv: 510 seconds loaded 74807831229 Bytes, about 139 MB/s
stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
insert into select: 86.9 seconds inserted 10000000 Rows, about 115K ops/s
storage size: 17167612250 Bytes
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230707172003_clickbench_pr_174374.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625218774
TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 47.12 seconds
stream load tsv: 455 seconds loaded 74807831229 Bytes, about 156 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 57 seconds loaded 1101869774 Bytes, about 18 MB/s
stream load parquet: 29 seconds loaded 861443392 Bytes, about 28 MB/s
insert into select: 68.7 seconds inserted 10000000 Rows, about 145K ops/s
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230707104133_clickbench_pr_174432.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1627953284
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628584515
PR approved by at least one committer and no changes requested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625027454
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628552784
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628597742
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 51.03 seconds
stream load tsv: 501 seconds loaded 74807831229 Bytes, about 142 MB/s
stream load json: 21 seconds loaded 2358488459 Bytes, about 107 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
insert into select: 89.7 seconds inserted 10000000 Rows, about 111K ops/s
storage size: 17169702682 Bytes
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230710173939_clickbench_pr_175863.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625153350
run buildall
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] github-actions[bot] commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1627957292
clang-tidy review says "All clean, LGTM! :+1:"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] zhannngchen merged pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "zhannngchen (via GitHub)" <gi...@apache.org>.
zhannngchen merged PR #21623:
URL: https://github.com/apache/doris/pull/21623
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1628590790
TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 50.1 seconds
stream load tsv: 446 seconds loaded 74807831229 Bytes, about 159 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 57 seconds loaded 1101869774 Bytes, about 18 MB/s
stream load parquet: 28 seconds loaded 861443392 Bytes, about 29 MB/s
insert into select: 69.0 seconds inserted 10000000 Rows, about 144K ops/s
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230710093450_clickbench_pr_175865.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] zhannngchen commented on a diff in pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "zhannngchen (via GitHub)" <gi...@apache.org>.
zhannngchen commented on code in PR #21623:
URL: https://github.com/apache/doris/pull/21623#discussion_r1257743582
##########
docs/zh-CN/docs/data-operate/import/import-scenes/load-strict-mode.md:
##########
@@ -131,3 +131,50 @@ under the License.
> 1. 表中的列允许导入空值
> 2. `abc` 在转换为 Decimal 后，会因类型问题变为 NULL。在严格模式开启的情况下，这类数据将会被过滤。而如果是关闭状态，则会导入 `null`。
> 3. `10` 虽然是一个超过范围的值，但是因为其类型符合 decimal 的要求，所以严格模式对其不产生影响。`10` 最后会在其他导入处理流程中被过滤。但不会被严格模式过滤。
+
+- 限定部分列更新只能更新已有的列
+
+在严格模式下，部分列更新插入的每一行数据必须满足该行数据的key在表中已经存在。而在而非严格模式下，进行部分列更新时可以更新key已经存在的行，也可以插入key不存在的新行。
+
+例如有表结构如下：
+```
+mysql> desc user_profile;
++------------------+-----------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++------------------+-----------------+------+-------+---------+-------+
+| id | INT | Yes | true | NULL | |
+| name | VARCHAR(10) | Yes | false | NULL | NONE |
+| age | INT | Yes | false | NULL | NONE |
+| city | VARCHAR(10) | Yes | false | NULL | NONE |
+| balance | DECIMALV3(9, 0) | Yes | false | NULL | NONE |
+| last_access_time | DATETIME | Yes | false | NULL | NONE |
++------------------+-----------------+------+-------+---------+-------+
+```
+
+表中有一条数据如下：
+
+```
+1,"kevin",18,"shenzhen",400,"2023-07-01 12:00:00"
+```
+
+当用户使用非严格模式的stram load部分列更新向表中插入如下数据时
+
+```
+1,500,2023-07-03 12:00:01
+3,23,2023-07-03 12:00:02
+18,9999999,2023-07-03 12:00:03
+```
+
+```
+curl --location-trusted -u root -H "partial_columns:true" -H "strict_mode:false" -H "column_separator:," -H "columns:id,balance,last_access_time" -T /tmp/test.csv http://host:port/api/db1/user_profile/_stream_load
+```
+
+表中原有的一条数据将会被更新，此外还向表中插入了两条新数据。对于插入的数据中用户没有指定的列，如果该列有默认值，则会议默认值填充；否则，如果该列可以为NULL，则将以NULL值填充；否则本次插入不成功。
Review Comment:
会议->会以
##########
regression-test/suites/unique_with_mow_p0/partial_update/test_partial_update_upsert.groovy:
##########
@@ -0,0 +1,68 @@
+
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_partial_update_upsert", "p0") {
+ def tableName = "test_partial_update_upsert"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) NULL,
+ `name` varchar(10) NULL,
+ `age` int(11) NULL DEFAULT "20",
+ `city` varchar(10) NOT NULL DEFAULT "beijing",
+ `balance` decimalv3(9, 0) NULL,
+ `last_access_time` datetime NULL
+ ) ENGINE = OLAP UNIQUE KEY(`id`)
+ COMMENT 'OLAP' DISTRIBUTED BY HASH(`id`)
+ BUCKETS AUTO PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "storage_format" = "V2",
+ "enable_unique_key_merge_on_write" = "true",
+ "light_schema_change" = "true",
+ "disable_auto_compaction" = "false",
+ "enable_single_replica_compaction" = "false"
+ );
+ """
+
+ sql """
+ insert into ${tableName} values(1,"kevin",18,"shenzhen",400,"2023-07-01 12:00:00");
+ """
+
+ qt_sql """select * from ${tableName} order by id;"""
+
+ streamLoad {
Review Comment:
check the streamload with strict_mode=ture will fail, then strict_mode=false will success
##########
docs/zh-CN/docs/data-operate/import/import-scenes/load-strict-mode.md:
##########
@@ -131,3 +131,50 @@ under the License.
> 1. 表中的列允许导入空值
> 2. `abc` 在转换为 Decimal 后，会因类型问题变为 NULL。在严格模式开启的情况下，这类数据将会被过滤。而如果是关闭状态，则会导入 `null`。
> 3. `10` 虽然是一个超过范围的值，但是因为其类型符合 decimal 的要求，所以严格模式对其不产生影响。`10` 最后会在其他导入处理流程中被过滤。但不会被严格模式过滤。
+
+- 限定部分列更新只能更新已有的列
+
+在严格模式下，部分列更新插入的每一行数据必须满足该行数据的key在表中已经存在。而在而非严格模式下，进行部分列更新时可以更新key已经存在的行，也可以插入key不存在的新行。
+
+例如有表结构如下：
+```
+mysql> desc user_profile;
++------------------+-----------------+------+-------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++------------------+-----------------+------+-------+---------+-------+
+| id | INT | Yes | true | NULL | |
+| name | VARCHAR(10) | Yes | false | NULL | NONE |
+| age | INT | Yes | false | NULL | NONE |
+| city | VARCHAR(10) | Yes | false | NULL | NONE |
+| balance | DECIMALV3(9, 0) | Yes | false | NULL | NONE |
+| last_access_time | DATETIME | Yes | false | NULL | NONE |
++------------------+-----------------+------+-------+---------+-------+
+```
+
+表中有一条数据如下：
+
+```
+1,"kevin",18,"shenzhen",400,"2023-07-01 12:00:00"
+```
+
+当用户使用非严格模式的stram load部分列更新向表中插入如下数据时
+
+```
+1,500,2023-07-03 12:00:01
+3,23,2023-07-03 12:00:02
+18,9999999,2023-07-03 12:00:03
+```
+
+```
+curl --location-trusted -u root -H "partial_columns:true" -H "strict_mode:false" -H "column_separator:," -H "columns:id,balance,last_access_time" -T /tmp/test.csv http://host:port/api/db1/user_profile/_stream_load
+```
+
+表中原有的一条数据将会被更新，此外还向表中插入了两条新数据。对于插入的数据中用户没有指定的列，如果该列有默认值，则会议默认值填充；否则，如果该列可以为NULL，则将以NULL值填充；否则本次插入不成功。
Review Comment:
会议->会以
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625012226
run build all
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625107262
TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 49.44 seconds
stream load tsv: 456 seconds loaded 74807831229 Bytes, about 156 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 57 seconds loaded 1101869774 Bytes, about 18 MB/s
stream load parquet: 29 seconds loaded 861443392 Bytes, about 28 MB/s
insert into select: 68.3 seconds inserted 10000000 Rows, about 146K ops/s
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230707091152_clickbench_pr_174378.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1625219885
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 51.15 seconds
stream load tsv: 502 seconds loaded 74807831229 Bytes, about 142 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 30 seconds loaded 861443392 Bytes, about 27 MB/s
insert into select: 87.3 seconds inserted 10000000 Rows, about 114K ops/s
storage size: 17163685711 Bytes
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230707184237_clickbench_pr_174434.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1626354013
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 52.22 seconds
stream load tsv: 500 seconds loaded 74807831229 Bytes, about 142 MB/s
stream load json: 20 seconds loaded 2358488459 Bytes, about 112 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
insert into select: 87.3 seconds inserted 10000000 Rows, about 114K ops/s
storage size: 17168976016 Bytes
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230708071929_clickbench_pr_174737.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] bobhan1 commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "bobhan1 (via GitHub)" <gi...@apache.org>.
bobhan1 commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1626355956
run feut
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [doris] hello-stephen commented on pull request #21623: [feature](partial update)support insert new rows in non-strict mode partial update with nullable unmentioned columns
Posted by "hello-stephen (via GitHub)" <gi...@apache.org>.
hello-stephen commented on PR #21623:
URL: https://github.com/apache/doris/pull/21623#issuecomment-1627988234
TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 53.4 seconds
stream load tsv: 447 seconds loaded 74807831229 Bytes, about 159 MB/s
stream load json: 21 seconds loaded 2358488459 Bytes, about 107 MB/s
stream load orc: 57 seconds loaded 1101869774 Bytes, about 18 MB/s
stream load parquet: 28 seconds loaded 861443392 Bytes, about 29 MB/s
insert into select: 66.6 seconds inserted 10000000 Rows, about 150K ops/s
https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/tmp/20230710023925_clickbench_pr_175397.html
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org