You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by lu...@apache.org on 2024/03/28 09:38:07 UTC
(doris-website) branch master updated: [docs](docs) Update EN docs (#479)
This is an automated email from the ASF dual-hosted git repository.
luzhijing pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 60146c95c7bc [docs](docs) Update EN docs (#479)
60146c95c7bc is described below
commit 60146c95c7bc0935a53e4100cb7018bc53cf1958
Author: KassieZ <13...@users.noreply.github.com>
AuthorDate: Thu Mar 28 17:38:02 2024 +0800
[docs](docs) Update EN docs (#479)
---
.../data-operate/update/unique-update.md | 8 +-
...load-update.md => update-of-aggregate-model.md} | 6 +-
...ue-load-update.md => update-of-unique-model.md} | 2 +-
.../data-operate/update/update-overview.md | 6 +-
.../table-design/cold-hot-separation.md | 12 +-
.../version-2.0/table-design/schema-change.md | 6 +-
.../data-operate/import/load-atomicity.md | 208 +++++++-
.../data-operate/import/load-data-convert.md | 529 ++++++++++++---------
.../data-operate/update/unique-load-update.md | 233 ---------
.../update/unique-update-transaction.md | 190 +++++---
.../data-operate/update/unique-update.md | 38 +-
...load-update.md => update-of-aggregate-model.md} | 0
.../data-operate/update/update-of-unique-model.md | 162 +++++++
.../data-operate/update/update-overview.md | 129 ++---
.../table-design/cold-hot-separation.md | 205 ++++----
.../version-2.0/table-design/schema-change.md | 492 +++++++++++++------
versioned_sidebars/version-2.0-sidebars.json | 4 +-
17 files changed, 1317 insertions(+), 913 deletions(-)
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-update.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-update.md
index ae46130976c2..b94ffd1763f8 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-update.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-update.md
@@ -56,7 +56,7 @@ Update 语句的性能和待更新的行数以及 condition 的检索效率密
| ------ | -------- | -------- |
| 1 | 100 | 待付款 |
-```SQL
+```sql
+----------+--------------+--------------+
| order_id | order_amount | order_status |
+----------+--------------+--------------+
@@ -67,7 +67,7 @@ Update 语句的性能和待更新的行数以及 condition 的检索效率密
这时候，用户点击付款后，Doris 系统需要将订单 id 为 '1' 的订单状态变更为 '待发货'，就需要用到 Update 功能。
-```SQL
+```sql
mysql> UPDATE test_order SET order_status = '待发货' WHERE order_id = 1;
Query OK, 1 row affected (0.11 sec)
{'label':'update_20ae22daf0354fe0-b5aceeaaddc666c5', 'status':'VISIBLE', 'txnId':'33', 'queryId':'20ae22daf0354fe0-b5aceeaaddc666c5'}
@@ -75,7 +75,7 @@ Query OK, 1 row affected (0.11 sec)
更新后结果如下
-```SQL
+```sql
+----------+--------------+--------------+
| order_id | order_amount | order_status |
+----------+--------------+--------------+
@@ -86,4 +86,4 @@ Query OK, 1 row affected (0.11 sec)
## 更多帮助
-关于数据更新使用的更多详细语法，请参阅 [update](../../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) 命令手册，也可以在 MySQL 客户端命令行下输入 `HELP UPDATE` 获取更多帮助信息。
\ No newline at end of file
+关于数据更新使用的更多详细语法，请参阅 [update](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) 命令手册，也可以在 MySQL 客户端命令行下输入 `HELP UPDATE` 获取更多帮助信息。
\ No newline at end of file
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/aggregate-load-update.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-aggregate-model.md
similarity index 98%
rename from i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/aggregate-load-update.md
rename to i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-aggregate-model.md
index 0294be99aa54..004a9bc01ff1 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/aggregate-load-update.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-aggregate-model.md
@@ -38,7 +38,7 @@ Aggregate 表主要在预聚合场景使用而非数据更新的场景使用，
将需要进行列更新的字段对应的聚合函数设置为`REPLACE_IF_NOT_NULL`
-```Plain
+```sql
CREATE TABLE order_tbl (
order_id int(11) NULL,
order_amount int(11) REPLACE_IF_NOT_NULL NULL,
@@ -66,13 +66,13 @@ PROPERTIES (
与前面例子相同，对应的 Stream Load 命令为（不需要额外的 header）：
-```Plain
+```shell
curl --location-trusted -u root: -H "column_separator:," -H "columns:order_id,order_status" -T /tmp/update.csv http://127.0.0.1:48037/api/db1/order_tbl/_stream_load
```
对应的`INSERT INTO`语句为（不需要额外设置 session variable）：
-```Plain
+```sql
INSERT INTO order_tbl (order_id, order_status) values (1,'待发货');
```
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-load-update.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-unique-model.md
similarity index 99%
rename from i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-load-update.md
rename to i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-unique-model.md
index 713772ade099..23a1de4342a2 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/unique-load-update.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-of-unique-model.md
@@ -123,7 +123,7 @@ $ curl --location-trusted -u root: -H "partial_columns:true" -H "column_separat
若使用`INSRT INTO`可以通过如下方式进行更新：
-```Plain
+```sql
set enable_unique_key_partial_update=true;
INSERT INTO order_tbl (order_id, order_status) values (1,'待发货');
```
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-overview.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-overview.md
index 2f54e6014ab4..538338117cbe 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-overview.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/data-operate/update/update-overview.md
@@ -28,7 +28,7 @@ under the License.
## 主键（Unique）模型的更新
-Doris 主键 (unique) 模型，从 Doris 2.0 开始，除了原来的 Merge on Read（MoR），也引入了 Merge on Write（MoW）的存储方式，MoR 是为了写入做优化，而 MoW 是为了更快的分析性能做优化。在实际测试中，MoW 存储方式的典型表，分析性能可以是 MoR 方式的 5-10 倍。
+Doris 主键 (unique) 模型，从 Doris 2.0 开始，除了原来的 Merge-on-Read（MoR），也引入了 Merge-on-Write（MoW）的存储方式，MoR 是为了写入做优化，而 MoW 是为了更快的分析性能做优化。在实际测试中，MoW 存储方式的典型表，分析性能可以是 MoR 方式的 5-10 倍。
在 Doris 2.0，默认创建的 unique 模型依旧是 MoR 的，如果要创建 MoW 的，需要通过参数 "enable_unique_key_merge_on_write" = "true" 手动指定，如下示例：
@@ -72,7 +72,7 @@ Doris 支持 Stream Load、Broker Load、Routine Load、Insert Into 等多种导
- 如果更新的是部分列，主键模型采用的是 MoW，并且设置了 MySQL Session 变量 partial_columns = true 或者 HTTP Header partial_columns:true，则被更新的缺失列的值，不是再使用表 Schema 中缺失列的默认值，而是已经存在记录的对应缺失列的值。
-我们会分别在文档 主键模型的 Update 更新 和 主键模型的导入更新 详细介绍两种更新方式。
+我们会分别在文档 [主键模型的 Update 更新](../update/unique-update) 和 [主键模型的导入更新](../update/unique-load-update) 详细介绍两种更新方式。
### 主键模型的更新事务
@@ -90,4 +90,4 @@ New Agg Value = Agg Func ( Old Agg Value + New Column Value)
在定义聚合模型表的时候，如果把 value 列的聚合函数定义为 REPLACE_IF_NULL，也可以间接实现类似主键表的部分列更新能力。
-更多内容，请查看 聚合模型的导入更新。
\ No newline at end of file
+更多内容，请查看 [聚合模型的导入更新](../update/aggregate-load-update)。
\ No newline at end of file
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/cold-hot-separation.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/cold-hot-separation.md
index 94e38f175f25..780d2a11fd99 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/cold-hot-separation.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/cold-hot-separation.md
@@ -146,7 +146,7 @@ ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="te
:::tip
注意，如果用户在建表时给整张 Table 和部分 Partition 指定了不同的 Storage Policy，Partition 设置的 Storage policy 会被无视，整张表的所有 Partition 都会使用 table 的 Policy. 如果您需要让某个 Partition 的 Policy 和别的不同，则可以使用上文中对一个已存在的 Partition，关联 Storage policy 的方式修改。
-具体可以参考 Docs 目录下[RESOURCE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE)、 [POLICY](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY)、 [CREATE TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)、 [ALTER TABLE](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN)等文档，里面有详细介绍。
+具体可以参考 Docs 目录下[RESOURCE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE)、 [POLICY](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY)、 [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE)、 [ALTER TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN)等文档，里面有详细介绍。
:::
### 一些限制
@@ -161,19 +161,19 @@ ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="te
## 冷数据占用对象大小
-方式一：通过 show proc '/backends'可以查看到每个 be 上传到对象的大小，RemoteUsedCapacity 项，此方式略有延迟。
+方式一：通过 show proc '/backends'可以查看到每个 BE 上传到对象的大小，RemoteUsedCapacity 项，此方式略有延迟。
方式二：通过 show tablets from tableName 可以查看到表的每个 tablet 占用的对象大小，RemoteDataSize 项。
## 冷数据的 cache
-上文提到冷数据为了优化查询的性能和对象存储资源节省，引入了 cache 的概念。在冷却后首次命中，Doris 会将已经冷却的数据又重新加载到 be 的本地磁盘，cache 有以下特性：
+上文提到冷数据为了优化查询的性能和对象存储资源节省，引入了 cache 的概念。在冷却后首次命中，Doris 会将已经冷却的数据又重新加载到 BE 的本地磁盘，cache 有以下特性：
-- cache 实际存储于 be 磁盘，不占用内存空间。
+- cache 实际存储于 BE 磁盘，不占用内存空间。
- cache 可以限制膨胀，通过 LRU 进行数据的清理
-- cache 的实现和联邦查询 catalog 的 cache 是同一套实现，文档参考[此处](../../lakehouse/filecache)
+- cache 的实现和联邦查询 Catalog 的 cache 是同一套实现，文档参考[此处](../lakehouse/filecache)
## 冷数据的 Compaction
@@ -205,7 +205,7 @@ ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="te
3. 冷数据 Compaction 后，合并前的 rowset 因为还可能被其他 Replica 使用不能立即删除，但是最终 FollowerReplica 都使用了最新的合并后的 rowset，合并前的 rowset 成为垃圾数据。
-另外，对象上的垃圾数据并不会立即清理掉。BE 参数`remove_unused_remote_files_interval_sec`可以设置冷数据的垃圾回收的时间间隔，默认是 21600，单位：秒，即 6 个小时。。
+另外，对象上的垃圾数据并不会立即清理掉。BE 参数`remove_unused_remote_files_interval_sec`可以设置冷数据的垃圾回收的时间间隔，默认是 21600，单位：秒，即 6 个小时。
## 未尽事项
diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/schema-change.md b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/schema-change.md
index b54e1f669d6e..84c771c646af 100644
--- a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/schema-change.md
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/table-design/schema-change.md
@@ -76,7 +76,7 @@ under the License.
在开始转换历史数据之前，Doris 会获取一个最新的 Transaction ID。并等待这个 Transaction ID 之前的所有导入事务完成。这个 Transaction ID 成为分水岭。意思是，Doris 保证在分水岭之后的所有导入任务，都会同时为原表 /Index 和新表 /Index 生成数据。这样当历史数据转换完成后，可以保证新的表中的数据是完整的。
-创建 Schema Change 的具体语法可以查看帮助 [ALTER TABLE COLUMN](..//sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) 中 Schema Change 部分的说明。
+创建 Schema Change 的具体语法可以查看帮助 [ALTER TABLE COLUMN](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) 中 Schema Change 部分的说明。
## 向指定 Index 的指定位置添加一列
@@ -147,7 +147,7 @@ ALTER TABLE table_name ADD COLUMN (column_name1 column_type [KEY | agg_type] DEF
### 示例
-向 `example_rollup_index`添加多列 (聚合模型))
+向 `example_rollup_index`添加多列 (聚合模型)
```sql
ALTER TABLE example_db.my_table
@@ -486,4 +486,4 @@ ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");
## 更多参考
-关于 Schema Change 使用的更多详细语法及最佳实践，请参阅 [ALTER TABLE COLUMN](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) 命令手册，你也可以在 MySQL 客户端命令行下输入 `HELP ALTER TABLE COLUMN` 获取更多帮助信息。
\ No newline at end of file
+关于 Schema Change 使用的更多详细语法及最佳实践，请参阅 [ALTER TABLE COLUMN](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) 命令手册，你也可以在 MySQL 客户端命令行下输入 `HELP ALTER TABLE COLUMN` 获取更多帮助信息。
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/import/load-atomicity.md b/versioned_docs/version-2.0/data-operate/import/load-atomicity.md
index 9ffd79e5ed00..77ad0decb2b3 100644
--- a/versioned_docs/version-2.0/data-operate/import/load-atomicity.md
+++ b/versioned_docs/version-2.0/data-operate/import/load-atomicity.md
@@ -1,6 +1,6 @@
---
{
- "title": "Data Import Things and Atomicity",
+ "title": "Load Transaction and Atomicity",
"language": "en"
}
---
@@ -24,26 +24,210 @@ specific language governing permissions and limitations
under the License.
-->
-# Data import things and atomicity
+## Use Cases
-All import operations in Doris have atomicity guarantees, that is, the data in an import job either all succeed or all fail. It will not happen that only part of the data is imported successfully.
+All loading tasks in Doris are atomic, which means that a loading job either succeeds completely or fails completely. There won't be a situation where only part of the data is load successfully. Additionally, Doris ensures atomicity for loading multiple tables within the same loading task. Moreover, Doris ensures data loading without loss or duplication through the use of labels. For simple loading tasks, no additional configuration or operations are required. For materialized views asso [...]
-In [BROKER LOAD](../../../sql-manual/sql-reference/Data-Manipulation-Statements/Load/BROKER-LOAD) we can also implement atomic import of multiple tables .
+1. If a user needs to combine multiple `INSERT INTO` into a single transaction for the same target table, they can use the `BEGIN` and `COMMIT` commands.
-For the [materialized view](../../../query-acceleration/materialized-view) attached to the table, atomicity and consistency with the base table are also guaranteed.
+2. If a user needs to combine multiple stream load into a single transaction, they can use the two-phase commit mode of Stream Load.
-## Label mechanism
+3. Atomicity of multi-table load with Broker Load.
-Doris's import job can set a Label. This Label is usually a user-defined string with certain business logic attributes.
+## Basic Principles
-The main function of Label is to uniquely identify an import task, and to ensure that the same Label will only be successfully imported once.
+In a Doris loading task, the Backend (BE) submits the Tablet IDs of the successful writes to the Frontend (FE). FE determines the success of the load based on the number of successful tablet replicas. If loading is successful, the transaction is committed and the data load becomes visible. If it fails, the transaction is rolled back and the corresponding tablets are cleaned up.
-The Label mechanism can ensure that the imported data is not lost or heavy. If the upstream data source can guarantee the At-Least-Once semantics, with the Doris Label mechanism, the Exactly-Once semantics can be guaranteed.
+### Label Mechanism
-Label is unique under a database. The retention period for labels is 3 days by default. That is, after 3 days, the completed Label will be automatically cleaned up, and then the Label can be reused.
+Doris loading jobs can be assigned a label. This label is usually a user-defined string with certain business logic attributes.
+
+The main purpose of the label is to uniquely identify a loading task and ensure that the same label is loaded successfully only once.
+
+The label mechanism ensures that load data is not lost or duplicated. When combined with an upstream data source that guarantees At-Least-Once semantics, the label mechanism in Doris can ensure Exactly-Once semantics.
+
+Labels are unique within a database. The default retention period for labels is 3 days. After 3 days, completed labels are automatically cleaned up and can be reused.
+
+## Quick Start
+
+### Insert Into
+
+**1. Create Table**
+
+```sql
+CREATE TABLE testdb.test_table(
+ user_id BIGINT NOT NULL COMMENT "User ID",
+ name VARCHAR(20) NOT NULL COMMENT "User Name",
+ age INT COMMENT "User Age"
+)
+DUPLICATE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 10;
+```
+
+Create a table with the same schema for the failed example:
+
+```sql
+CREATE TABLE testdb.test_table2 LIKE testdb.test_table;
+```
+
+**2. Successful Load Example**
+
+```sql
+BEGIN;
+
+-- INSERT #1
+INSERT INTO testdb.test_table (user_id, name, age)
+VALUES (1, "Emily", 25),
+ (2, "Benjamin", 35),
+ (3, "Olivia", 28),
+ (4, "Alexander", 60),
+ (5, "Ava", 17);
+
+-- INSERT #2
+INSERT INTO testdb.test_table (user_id, name, age)
+VALUES (6, "William", 69),
+ (7, "Sophia", 32),
+ (8, "James", 64),
+ (9, "Emma", 37),
+ (10, "Liam", 64);
+
+COMMIT;
+```
+
+Load result: The loading task starts with the `PREPARE` status and becomes `VISIBLE` only after the COMMIT.
+
+```JSON
+// BEGIN
+Query OK, 0 rows affected (0.001 sec)
+{'label':'txn_insert_2aeac5519bd549a1-a72fe4001c56e10c', 'status':'PREPARE', 'txnId':''}
+
+// INSERT #1
+Query OK, 5 rows affected (0.017 sec)
+{'label':'txn_insert_2aeac5519bd549a1-a72fe4001c56e10c', 'status':'PREPARE', 'txnId':'10060'}
+
+// INSERT #2
+Query OK, 5 rows affected (0.007 sec)
+{'label':'txn_insert_2aeac5519bd549a1-a72fe4001c56e10c', 'status':'PREPARE', 'txnId':'10060'}
+
+// COMMIT
+Query OK, 0 rows affected (1.013 sec)
+{'label':'txn_insert_2aeac5519bd549a1-a72fe4001c56e10c', 'status':'VISIBLE', 'txnId':'10060'}
+```
+
+Verify the data:
+
+```JSON
+MySQL [testdb]> SELECT * FROM testdb.test_table2;
+Empty set (0.019 sec)
+```
+
+
+### Stream Load
+
+**1. Enable two-phase commit by setting `two_phase_commit:true` in the HTTP Header.**
+
+```Bash
+curl --location-trusted -u user:passwd -H "two_phase_commit:true" -T test.txt http://fe_host:http_port/api/{db}/{table}/_stream_load
+{
+ "TxnId": 18036,
+ "Label": "55c8ffc9-1c40-4d51-b75e-f2265b3602ef",
+ "TwoPhaseCommit": "true",
+ "Status": "Success",
+ "Message": "OK",
+ "NumberTotalRows": 100,
+ "NumberLoadedRows": 100,
+ "NumberFilteredRows": 0,
+ "NumberUnselectedRows": 0,
+ "LoadBytes": 1031,
+ "LoadTimeMs": 77,
+ "BeginTxnTimeMs": 1,
+ "StreamLoadPutTimeMs": 1,
+ "ReadDataTimeMs": 0,
+ "WriteDataTimeMs": 58,
+ "CommitAndPublishTimeMs": 0
+}
+```
+
+**2. Trigger the commit operation for a transaction (can be sent to FE or BE).**
+
+- Specify the transaction using the Transaction ID:
+
+ ```Bash
+ curl -X PUT --location-trusted -u user:passwd -H "txn_id:18036" -H "txn_operation:commit" http://fe_host:http_port/api/{db}/{table}/stream_load2pc
+ {
+ "status": "Success",
+ "msg": "transaction [18036] commit successfully."
+ }
+ ```
+
+- Specify the transaction using the label:
+
+ ```Bash
+ curl -X PUT --location-trusted -u user:passwd -H "label:55c8ffc9-1c40-4d51-b75e-f2265b3602ef" -H "txn_operation:commit" http://fe_host:http_port/api/{db}/{table}/_stream_load_2pc
+ {
+ "status": "Success",
+ "msg": "label [55c8ffc9-1c40-4d51-b75e-f2265b3602ef] commit successfully."
+ }
+ ```
+
+**3. Trigger the abort operation for a transaction (can be sent to FE or BE).**
+
+- Specify the transaction using the Transaction ID:
+
+ ```Bash
+ curl -X PUT --location-trusted -u user:passwd -H "txn_id:18037" -H "txn_operation:abort" http://fe_host:http_port/api/{db}/{table}/stream_load2pc
+ {
+ "status": "Success",
+ "msg": "transaction [18037] abort successfully."
+ }
+ ```
+
+- Specify the transaction using the label:
+
+ ```Bash
+ curl -X PUT --location-trusted -u user:passwd -H "label:55c8ffc9-1c40-4d51-b75e-f2265b3602ef" -H "txn_operation:abort" http://fe_host:http_port/api/{db}/{table}/stream_load2pc
+ {
+ "status": "Success",
+ "msg": "label [55c8ffc9-1c40-4d51-b75e-f2265b3602ef] abort successfully."
+ }
+ ```
+
+### Broker Load
+
+All Broker Load tasks are atomic and ensure atomicity even when loading multiple tables within the same task. The Label mechanism can be used to ensure data load without loss or duplication.
+
+The following example demonstrates loading data from HDFS by using wildcard patterns to match two sets of files and load them into two different tables.
+
+```sql
+LOAD LABEL example_db.label2
+(
+ DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-10*")
+ INTO TABLE `my_table1`
+ PARTITION (p1)
+ COLUMNS TERMINATED BY ","
+ (k1, tmp_k2, tmp_k3)
+ SET (
+ k2 = tmp_k2 + 1,
+ k3 = tmp_k3 + 1
+ )
+ DATA INFILE("hdfs://hdfs_host:hdfs_port/input/file-20*")
+ INTO TABLE `my_table2`
+ COLUMNS TERMINATED BY ","
+ (k1, k2, k3)
+)
+WITH BROKER hdfs
+(
+ "username"="hdfs_user",
+ "password"="hdfs_password"
+);
+```
+
+The wildcard pattern is used to match and load two sets of files, `file-10*` and `file-20*`, into `my_table1` and `my_table2` respectively. In the case of `my_table1`, the load is specified to the `p1` partition, and the values of thesecond and third columns in the source file are incremented by 1 before being loaded.
## Best Practices
-Labels are usually formatted as `business logic + time`. Such as `my_business1_20220330_125000`.
+Labels are typically set in the format of `business_logic+time`, such as `my_business1_20220330_125000`.
+
+This label is commonly used to represent a batch of data generated by the business logic `my_business1` at `2022-03-30 12:50:00`. By setting this label, the business can query the loading task status using the label to determine whether the data batch at that specific time has been successfully load. If the load fails, the label can be used to retry the load.
-This Label is usually used to represent: a batch of data generated by the business `my_business1` at `2022-03-30 12:50:00`. Through this Label setting, the business can query the import task status through the Label to clearly know whether the batch data has been imported successfully at this point in time. If unsuccessful, you can continue to retry the import using this Label
+INSERT INTO supports loading the result of a Doris query into another table. INSERT INTO is a synchronous load method that returns the load result after execution. The success or failure of the load can be determined based on the response. INSERT INTO ensures the atomicity of the loading task, either all succeed or all fail load.
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/import/load-data-convert.md b/versioned_docs/version-2.0/data-operate/import/load-data-convert.md
index e1a79fda32cb..ea073791ab28 100644
--- a/versioned_docs/version-2.0/data-operate/import/load-data-convert.md
+++ b/versioned_docs/version-2.0/data-operate/import/load-data-convert.md
@@ -1,6 +1,6 @@
---
{
- "title": "Data Transformation, Column Mapping and Filtering",
+ "title": "Load Data Convert",
"language": "en"
}
---
@@ -24,343 +24,404 @@ specific language governing permissions and limitations
under the License.
-->
-# Imported Data Transformation, Column Mapping and Filtering
+## Usage Scenarios
-## Supported import methods
+During the import process, Doris supports some transformations on the source data, including mapping, conversion, preceding filtering, and post-filtering.
-- [BROKER LOAD](../../../sql-manual/sql-reference/Data-Manipulation-Statements/Load/BROKER-LOAD)
+- Mapping: Import column A from the source data into column B in the target table.
- ```sql
- LOAD LABEL example_db.label1
- (
- DATA INFILE("bos://bucket/input/file")
- INTO TABLE `my_table`
- (k1, k2, tmpk3)
- PRECEDING FILTER k1 = 1
- SET (
- k3 = tmpk3 + 1
- )
- WHERE k1 > k2
- )
- WITH BROKER bos
- (
- ...
- );
- ````
+- Conversion: Calculate the values in the target column based on the columns in the source data using an expression. Custom functions are supported in the expression.
-- [STREAM LOAD](../../../sql-manual/sql-reference/Data-Manipulation-Statements/Load/STREAM-LOAD)
+- Preceding Filtering: Filter rows in the source data and only import rows that meet the filtering conditions.
- ```bash
- curl
- --location-trusted
- -u user:passwd
- -H "columns: k1, k2, tmpk3, k3 = tmpk3 + 1"
- -H "where: k1 > k2"
- -T file.txt
- http://host:port/api/testDb/testTbl/_stream_load
- ````
+- Post-Filtering: Filter rows in the result and only import rows that meet the filtering conditions.
-- [ROUTINE LOAD](../../../sql-manual/sql-reference/Data-Manipulation-Statements/Load/CREATE-ROUTINE-LOAD)
+## Quick Start
- ```sql
- CREATE ROUTINE LOAD example_db.label1 ON my_table
- COLUMNS(k1, k2, tmpk3, k3 = tmpk3 + 1),
- PRECEDING FILTER k1 = 1,
- WHERE k1 > k2
- ...
- ````
+### BROKER LOAD
-The above import methods all support column mapping, transformation and filtering operations on the source data:
+```sql
+LOAD LABEL example_db.label1
+(
+ DATA INFILE("bos://bucket/input/file")
+ INTO TABLE `my_table`
+ (k1, k2, tmpk3)
+ PRECEDING FILTER k1 = 1
+ SET (
+ k3 = tmpk3 + 1
+ )
+ WHERE k1 > k2
+)
+WITH BROKER bos
+(
+ ...
+);
+```
-- Pre-filtering: filter the read raw data once.
+### STREAM LOAD
- ````text
- PRECEDING FILTER k1 = 1
- ````
+```bash
+curl
+--location-trusted
+-u user:passwd
+-H "columns: k1, k2, tmpk3, k3 = tmpk3 + 1"
+-H "where: k1 > k2"
+-T file.txt
+http://host:port/api/testDb/testTbl/_stream_load
+```
-- Mapping: Define the columns in the source data. If the defined column name is the same as the column in the table, it is directly mapped to the column in the table. If different, the defined column can be used for subsequent transformation operations. As in the example above:
+### ROUTINE LOAD
- ````text
- (k1, k2, tmpk3)
- ````
+```sql
+CREATE ROUTINE LOAD example_db.label1 ON my_table
+COLUMNS(k1, k2, tmpk3, k3 = tmpk3 + 1),
+PRECEDING FILTER k1 = 1,
+WHERE k1 > k2
+...
+```
-- Conversion: Convert the mapped columns in the first step, you can use built-in expressions, functions, and custom functions for conversion, and remap them to the corresponding columns in the table. As in the example above:
+## Reference Manual
- ````text
- k3 = tmpk3 + 1
- ````
+### Loading Syntax
-- Post filtering: Filter the mapped and transformed columns by expressions. Filtered data rows are not imported into the system. As in the example above:
+**Stream Load**
- ````text
- WHERE k1 > k2
- ````
+Add `columns` and `where` parameters in the HTTP header.
-## column mapping
+- `columns` specify column mapping and value transformation.
-The purpose of column mapping is mainly to describe the information of each column in the import file, which is equivalent to defining the name of the column in the source data. By describing the column mapping relationship, we can import source files with different column order and different number of columns into Doris. Below we illustrate with an example:
+- `where` specify post-filtering.
-Assuming that the source file has 4 columns, the contents are as follows (the header column names are only for convenience, and there is no header actually):
+Stream load does not support preceding filtering.
-| Column 1 | Column 2 | Column 3 | Column 4 |
-| -------- | -------- | --------- | -------- |
-| 1 | 100 | beijing | 1.1 |
-| 2 | 200 | shanghai | 1.2 |
-| 3 | 300 | guangzhou | 1.3 |
-| 4 | \N | chongqing | 1.4 |
+Example:
-> Note: `\N` means null in the source file.
+```bash
+curl
+--location-trusted
+-u user:passwd
+-H "columns: k1, k2, tmpk3, k3 = tmpk3 + 1"
+-H "where: k1 > k2"
+-T file.txt
+http://host:port/api/testDb/testTbl/_stream_load
+```
-1. Adjust the mapping order
+**Broker Load**
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. The import mapping relationship we want is as follows:
+Define data transformation in the SQL statement, including:
- ````text
- column 1 -> k1
- column 2 -> k3
- column 3 -> k2
- column 4 -> k4
- ````
+- `(k1, k2, tmpk3)` specifies column mapping.
- Then the column mapping should be written in the following order:
+- `PRECEDING FILTER` specifies preceding filtering.
- ````text
- (k1, k3, k2, k4)
- ````
+- `SET` specifies column transformation.
-2. There are more columns in the source file than in the table
+- `WHERE` specifies post-filtering.
- Suppose there are 3 columns `k1,k2,k3` in the table. The import mapping relationship we want is as follows:
+```sql
+LOAD LABEL example_db.label1
+(
+ DATA INFILE("bos://bucket/input/file")
+ INTO TABLE `my_table`
+ (k1, k2, tmpk3)
+ PRECEDING FILTER k1 = 1
+ SET (
+ k3 = tmpk3 + 1
+ )
+ WHERE k1 > k2
+)
+WITH BROKER bos
+(
+ ...
+);
+```
- ````text
- column 1 -> k1
- column 2 -> k3
- column 3 -> k2
- ````
+**Routine Load**
- Then the column mapping should be written in the following order:
+Define data transformation in the SQL statement, including:
- ````text
- (k1, k3, k2, tmpk4)
- ````
+- `COLUMNS` specifies column mapping and column transformation.
- where `tmpk4` is a custom column name that does not exist in the table. Doris ignores this non-existing column name.
+- `PRECEDING FILTER` specifies preceding filtering.
-3. The number of columns in the source file is less than the number of columns in the table, fill with default values
+- `WHERE` specifies post-filtering.
- Suppose there are 5 columns `k1,k2,k3,k4,k5` in the table. The import mapping relationship we want is as follows:
+```sql
+CREATE ROUTINE LOAD example_db.label1 ON my_table
+COLUMNS(k1, k2, tmpk3, k3 = tmpk3 + 1),
+PRECEDING FILTER k1 = 1,
+WHERE k1 > k2
+...
+```
- ````text
- column 1 -> k1
- column 2 -> k3
- column 3 -> k2
- ````
+**Insert Into**
- Here we only use the first 3 columns from the source file. The two columns `k4,k5` want to be filled with default values.
+Insert Into can perform data transformation directly in the `SELECT` statement, and add a `WHERE` clause for data filtering.
- Then the column mapping should be written in the following order:
- ````text
- (k1, k3, k2)
- ````
+### Column Mapping
- If the `k4,k5` columns have default values, the default values will be populated. Otherwise, if it is a `nullable` column, it will be populated with a `null` value. Otherwise, the import job will report an error.
+The purpose of column mapping is to describe the information of each column in the load file, which is equivalent to defining names for the columns in the source data. By describing the column mapping relationship, we can load source files with different column orders and column numbers into Doris. Let's illustrate it through examples:
-## Column pre-filtering
+Suppose the source file has 4 columns with the following contents (the column names in the table header are for illustration purposes only and are not actually present in the file):
-Pre-filtering is to filter the read raw data once. Currently only BROKER LOAD and ROUTINE LOAD are supported.
+| Column 1 | Column 2 | Column 3 | Column 4 |
+| -------- | -------- | ---------- | -------- |
+| 1 | 100 | beijing | 1.1 |
+| 2 | 200 | shanghai | 1.2 |
+| 3 | 300 | guangzhou | 1.3 |
+| 4 | \N | chongqing | 1.4 |
-Pre-filtering has the following application scenarios:
+:::note
+Note: `\N` represents null in the source file.
+:::
-1. Filter before conversion
+1. Adjusting Mapping Order
- Scenarios where you want to filter before column mapping and transformation. It can filter out some unwanted data first.
+2. Suppose there are 4 columns in the table: `k1, k2, k3, k4`. The desired load mapping is as follows:
-2. The filter column does not exist in the table, it is only used as a filter identifier
+```Plain
+Column 1 -> k1
+Column 2 -> k3
+Column 3 -> k2
+Column 4 -> k4
+```
- For example, the source data stores the data of multiple tables (or the data of multiple tables is written to the same Kafka message queue). Each row in the data has a column name to identify which table the row of data belongs to. Users can filter the corresponding table data for import by pre-filtering conditions.
+3. The order of column mapping should be as follows:
-## Column conversion
+```Plain
+(k1, k3, k2, k4)
+```
-The column transformation function allows users to transform column values in the source file. Currently, Doris supports most of the built-in functions and user-defined functions for conversion.
+4. The number of columns in the source file is greater than the number of columns in the table.
-> Note: The user-defined function belongs to a certain database. When using the user-defined function for conversion, the user needs to have read permission to this database.
+5. Suppose there are 3 columns in the table: `k1, k2, k3`. The desired load mapping is as follows:
-Transformation operations are usually defined along with column mappings. That is, the columns are first mapped and then converted. Below we illustrate with an example:
+```Plain
+Column 1 -> k1
+Column 2 -> k3
+Column 3 -> k2
+```
-Assuming that the source file has 4 columns, the contents are as follows (the header column names are only for convenience, and there is no header actually):
+6. The order of column mapping should be as follows:
-| Column 1 | Column 2 | Column 3 | Column 4 |
-| -------- | -------- | --------- | -------- |
-| 1 | 100 | beijing | 1.1 |
-| 2 | 200 | shanghai | 1.2 |
-| 3 | 300 | guangzhou | 1.3 |
-| \N | 400 | chongqing | 1.4 |
+```Plain
+(k1, k3, k2, tmpk4)
+```
-1. Convert the column values in the source file and import them into the table
+7. Here, `tmpk4` is a custom column name that doesn't exist in the table. Doris will ignore this non-existing column name.
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. Our desired import mapping and transformation relationship is as follows:
+8. The number of columns in the source file is less than the number of columns in the table, and default values will be used to fill the missing columns.
- ````text
- column 1 -> k1
- column 2 * 100 -> k3
- column 3 -> k2
- column 4 -> k4
- ````
+9. Suppose there are 5 columns in the table: `k1, k2, k3, k4, k5`. The desired load mapping is as follows:
- Then the column mapping should be written in the following order:
+```Plain
+Column 1 -> k1
+Column 2 -> k3
+Column 3 -> k2
+```
- ````text
- (k1, tmpk3, k2, k4, k3 = tmpk3 * 100)
- ````
+10. Here, only the first 3 columns from the source file will be used. The columns `k4` and `k5` are expected to be filled with default values.
- This is equivalent to us naming the second column in the source file `tmpk3`, and specifying that the value of the `k3` column in the table is `tmpk3 * 100`. The data in the final table is as follows:
+11. The order of column mapping should be as follows:
- | k1 | k2 | k3 | k4 |
- | ---- | --------- | ----- | ---- |
- | 1 | beijing | 10000 | 1.1 |
- | 2 | shanghai | 20000 | 1.2 |
- | 3 | guangzhou | 30000 | 1.3 |
- | null | chongqing | 40000 | 1.4 |
+```Plain
+(k1, k3, k2)
+```
-2. Through the case when function, column conversion is performed conditionally.
+12. If the columns `k4` and `k5` have default values, they will be filled accordingly. Otherwise, if the columns are nullable, they will be filled with `null` values. Otherwise, the loading job will report an error.
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. We hope that `beijing, shanghai, guangzhou, chongqing` in the source data are converted to the corresponding region ids and imported:
+### Pre-filtering
- ````text
- column 1 -> k1
- column 2 -> k2
- Column 3 after region id conversion -> k3
- column 4 -> k4
- ````
+Pre-filtering is a process of filtering the raw data that is read. Currently, it is only supported in BROKER LOAD and ROUTINE LOAD.
- Then the column mapping should be written in the following order:
+Pre-filtering can be applied in the following scenarios:
- ````text
- (k1, k2, tmpk3, k4, k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
- ````
+1. Filtering before transformation: It allows filtering of data before performing column mapping and transformation. This way, unnecessary data can be filtered out in advance.
- The data in the final table is as follows:
+2. Filtering columns that do not exist in the table: It can be used as a filtering identifier when certain columns are not present in the table.
- | k1 | k2 | k3 | k4 |
- | ---- | ---- | ---- | ---- |
- | 1 | 100 | 1 | 1.1 |
- | 2 | 200 | 2 | 1.2 |
- | 3 | 300 | 3 | 1.3 |
- | null | 400 | 4 | 1.4 |
+3. Handling data from multiple tables: For example, if the source data contains data from multiple tables (or data from multiple tables is written to the same Kafka message queue), each row may include a column name that identifies which table the data belongs to. Users can use pre-filtering conditions to select and load the corresponding table data.
-3. Convert the null value in the source file to 0 and import it. At the same time, the region id conversion in Example 2 is also performed.
+### Column Transformation
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. While converting the region id, we also want to convert the null value of the k1 column in the source data to 0 and import:
+Column transformation enables users to modify the values of columns in the source files. Currently, Doris supports the use of built-in functions and user-defined functions for transformation.
- ````text
- Column 1 is converted to 0 if it is null -> k1
- column 2 -> k2
- column 3 -> k3
- column 4 -> k4
- ````
+:::note
+Note: User-defined functions belong to a specific database, and when using custom functions for transformation, users need to have read permissions on that database.
+:::
- Then the column mapping should be written in the following order:
+Transformation operations are typically defined in conjunction with column mapping. In the following example, we illustrate the process:
- ````text
- (tmpk1, k2, tmpk3, k4, k1 = ifnull(tmpk1, 0), k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
- ````
+Assume that the source file has 4 columns with the following content (the column names in the header are for descriptive purposes only and are not actually present in the file):
- The data in the final table is as follows:
+| Column 1 | Column 2 | Column 3 | Column 4 |
+| -------- | -------- | ----------- | -------- |
+| 1 | 100 | beijing | 1.1 |
+| 2 | 200 | shanghai | 1.2 |
+| 3 | 300 | guangzhou | 1.3 |
+| \N | 400 | chongqing | 1.4 |
- | k1 | k2 | k3 | k4 |
- | ---- | ---- | ---- | ---- |
- | 1 | 100 | 1 | 1.1 |
- | 2 | 200 | 2 | 1.2 |
- | 3 | 300 | 3 | 1.3 |
- | 0 | 400 | 4 | 1.4 |
+1. Load the transformed column values into the table from the source file.
-## List filter
+2. Assuming the table has 4 columns: `k1`, `k2`, `k3`, `k4`, and we want the following mapping and transformation relationships:
-After column mapping and transformation, we can filter the data that we do not want to import into Doris through filter conditions. Below we illustrate with an example:
+```Plain
+Column 1 -> k1
+Column 2 * 100 -> k3
+Column 3 -> k2
+Column 4 -> k4
+```
-Assuming that the source file has 4 columns, the contents are as follows (the header column names are only for convenience, and there is no header actually):
+3. The order of column mapping should be as follows:
-| Column 1 | Column 2 | Column 3 | Column 4 |
-| -------- | -------- | --------- | -------- |
-| 1 | 100 | beijing | 1.1 |
-| 2 | 200 | shanghai | 1.2 |
-| 3 | 300 | guangzhou | 1.3 |
-| \N | 400 | chongqing | 1.4 |
+```Plain
+(k1, tmpk3, k2, k4, k3 = tmpk3 * 100)
+```
-1. In the default case of column mapping and transformation, filter directly
+4. Here, we rename the second column in the source file as `tmpk3` and specify that the value of column `k3` in the table is `tmpk3 * 100`. The final data in the table would be as follows:
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. We can define filter conditions directly with default column mapping and transformation. If we want to import only the data rows whose fourth column in the source file is greater than 1.2, the filter conditions are as follows:
+| k1 | k2 | k3 | k4 |
+| ---- | --------- | ----- | ---- |
+| 1 | beijing | 10000 | 1.1 |
+| 2 | shanghai | 20000 | 1.2 |
+| 3 | guangzhou | 30000 | 1.3 |
+| null | chongqing | 40000 | 1.4 |
- ````text
- where k4 > 1.2
- ````
+5. Perform conditional column transformation using the `case when` function.
- The data in the final table is as follows:
+6. Assuming the table has 4 columns: `k1`, `k2`, `k3`, `k4`, and we want to transform the values `beijing`, `shanghai`, `guangzhou`, `chongqing` in the source data to their corresponding region IDs before loading:
- | k1 | k2 | k3 | k4 |
- | ---- | ---- | --------- | ---- |
- | 3 | 300 | guangzhou | 1.3 |
- | null | 400 | chongqing | 1.4 |
+```Plain
+Column 1 -> k1
+Column 2 -> k2
+Column 3 with region ID -> k3
+Column 4 -> k4
+```
- By default, Doris maps columns sequentially, so column 4 in the source file is automatically mapped to column `k4` in the table.
+7. The order of column mapping should be as follows:
-2. Filter the column-transformed data
+```Plain
+(k1, k2, tmpk3, k4, k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
+```
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. In the **column conversion** example, we converted province names to ids. Here we want to filter out the data with id 3. Then the conversion and filter conditions are as follows:
+8. The final data in the table would be as follows:
- ````text
- (k1, k2, tmpk3, k4, k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
- where k3 != 3
- ````
+| k1 | k2 | k3 | k4 |
+| ---- | ---- | ---- | ---- |
+| 1 | 100 | 1 | 1.1 |
+| 2 | 200 | 2 | 1.2 |
+| 3 | 300 | 3 | 1.3 |
+| null | 400 | 4 | 1.4 |
- The data in the final table is as follows:
+9. Transform null values in the source file to 0 during load. Also, perform the region ID transformation as shown in example 2.
- | k1 | k2 | k3 | k4 |
- | ---- | ---- | ---- | ---- |
- | 1 | 100 | 1 | 1.1 |
- | 2 | 200 | 2 | 1.2 |
- | null | 400 | 4 | 1.4 |
+10. Assuming the table has `k1, k2, k3, k4` as its four columns. While performing the region ID conversion, we also want to convert null values in the k1 column of the source data to 0 during load:
- Here we see that the column value when performing the filter is the final column value after mapping and transformation, not the original data.
+```Plain
+If Column 1 is null, then convert it to 0 -> k1
+Column 2 -> k2
+Column 3 -> k3
+Column 4 -> k4
+```
-3. Multi-condition filtering
+11. The order of column mapping should be as follows:
- Suppose there are 4 columns `k1,k2,k3,k4` in the table. We want to filter out the data whose `k1` column is `null`, and at the same time filter out the data whose `k4` column is less than 1.2, the filter conditions are as follows:
+```Plain
+(tmpk1, k2, tmpk3, k4, k1 = ifnull(tmpk1, 0), k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
+```
- ````text
- where k1 is not null and k4 >= 1.2
- ````
+12. The final data in the table would be as follows:
- The data in the final table is as follows:
+| k1 | k2 | k3 | k4 |
+| ---- | ---- | ---- | ---- |
+| 1 | 100 | 1 | 1.1 |
+| 2 | 200 | 2 | 1.2 |
+| 3 | 300 | 3 | 1.3 |
+| 0 | 400 | 4 | 1.4 |
- | k1 | k2 | k3 | k4 |
- | ---- | ---- | ---- | ---- |
- | 2 | 200 | 2 | 1.2 |
- | 3 | 300 | 3 | 1.3 |
+### Post-Filtering
-### Data Quality Issues and Filtering Thresholds
+After column mapping and transformation, we can filter out data that we don't want to load into Doris using filtering conditions. Let's illustrate this with an example:
-The rows of data processed in an import job can be divided into the following three types:
+Assume that the source file has 4 columns with the following content (the column names in the table header are for descriptive purposes only and are not actually present):
-1. Filtered Rows
+| Column 1 | Column 2 | Column 3 | Column 4 |
+| -------- | -------- | ---------- | -------- |
+| 1 | 100 | beijing | 1.1 |
+| 2 | 200 | shanghai | 1.2 |
+| 3 | 300 | guangzhou | 1.3 |
+| null | 400 | chongqing | 1.4 |
- Data that was filtered out due to poor data quality. Unqualified data quality includes data format problems such as type error, precision error, long string length, mismatched file column number, and data rows that are filtered out because there is no corresponding partition.
+1. Filtering with default column mapping and transformation.
-2. Unselected Rows
+2. Suppose the table has 4 columns: `k1, k2, k3, k4`. We can define filtering conditions directly without column mapping and transformation. For example, if we only want to load data rows from the source file where the value in the 4th column is greater than 1.2, the filtering condition would be:
- This part is the row of data that was filtered out due to `preceding filter` or `where` column filter conditions.
+```sql
+where k4 > 1.2
+```
-3. Loaded Rows
+3. The final data in the table would be as follows:
- Rows of data being imported correctly.
+| k1 | k2 | k3 | k4 |
+| ---- | ---- | --------- | ---- |
+| 3 | 300 | guangzhou | 1.3 |
+| null | 400 | chongqing | 1.4 |
-Doris's import task allows the user to set a maximum error rate (`max_filter_ratio`). If the error rate of the imported data is below the threshold, those erroneous rows will be ignored and other correct data will be imported.
+4. In the default case, Doris performs column mapping in sequential order, so the 4th column in the source file is automatically mapped to the `k4` column in the table.
-The error rate is calculated as:
+5. Filtering transformed data.
-````text
+6. Suppose the table has 4 columns: `k1, k2, k3, k4`. In the column transformation example, we converted province names to IDs. Now, let's say we want to filter out data with an ID of 3. The transformation and filtering conditions would be as follows:
+
+```Plain
+(k1, k2, tmpk3, k4, k3 = case tmpk3 when "beijing" then 1 when "shanghai" then 2 when "guangzhou" then 3 when "chongqing" then 4 else null end)
+where k3 != 3
+```
+
+7. The final data in the table would be as follows:
+
+| k1 | k2 | k3 | k4 |
+| ---- | ---- | ---- | ---- |
+| 1 | 100 | 1 | 1.1 |
+| 2 | 200 | 2 | 1.2 |
+| null | 400 | 4 | 1.4 |
+
+8. Here, we can observe that the column values used for filtering are the final transformed column values, not the original data.
+
+9. Filtering with multiple conditions.
+
+10. Suppose the table has 4 columns: `k1, k2, k3, k4`. We want to filter out data where the `k1` column is null and the `k4` column is less than 1.2. The filtering condition would be:
+
+```Plain
+where k1 is not null and k4 >= 1.2
+```
+
+11. The final data in the table would be as follows:
+
+| k1 | k2 | k3 | k4 |
+| ---- | ---- | ---- | ---- |
+| 2 | 200 | 2 | 1.2 |
+| 3 | 300 | 3 | 1.3 |
+
+## Best Practices
+
+### Data Quality Issues and Filtering Threshold
+
+The rows of data processed in the load job can be classified into the following three categories:
+
+- Filtered Rows: Data rows that are filtered out due to data quality issues. Data quality issues can include type errors, precision errors, strings exceeding length limits, mismatched file column counts, and data rows filtered out due to missing corresponding partitions.
+
+- Unselected Rows: These are data rows filtered out due to `preceding filter` or `where` column filtering conditions.
+
+- Loaded Rows: Data rows that are successfully loaded.
+
+Doris's load task allows users to set a maximum error rate (`max_filter_ratio`). If the error rate of the loaded data is below the threshold, the error rows will be ignored, and the other correct data will be loaded.
+
+The error rate is calculated as follows:
+
+```Plain
#Filtered Rows / (#Filtered Rows + #Loaded Rows)
-````
+```
-That is to say, `Unselected Rows` will not participate in the calculation of the error rate.
+This means that `Unselected Rows` are not included in the error rate calculation.
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/update/unique-load-update.md b/versioned_docs/version-2.0/data-operate/update/unique-load-update.md
deleted file mode 100644
index 5e30df922391..000000000000
--- a/versioned_docs/version-2.0/data-operate/update/unique-load-update.md
+++ /dev/null
@@ -1,233 +0,0 @@
----
-{
- "title": "Update for Uniuqe Load",
- "language": "en"
-}
----
-
-<!--
-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.
--->
-
-# Partial Update
-
-## Overview
-
-To perform partial column updates on your table, Doris provides two models: the Unique Key model and the Aggregate Key model.
-
-### Unique Key Model
-
-Doris's Unique Key Model provides default whole-row Upsert semantics. Before version 2.0, if users wanted to update certain columns of some rows, they could only use the `UPDATE` command. However, due to the granularity of locks in read-write transactions, the `UPDATE` command is not suitable for high-frequency data write scenarios. Therefore, in version 2.0, we introduced support for partial column updates in the Unique Key model.
-
-> Note:
->
-> 1. Partial update are only supported in the Unique Key Merge-on-Write implementation in version 2.0.0.
-> 2. Version 2.0.2 introduces support for performing partial column updates using `INSERT INTO`.
-> 4. Version 2.1.0 will offer more flexible column updates, as described in the "Usage Limitations" section below.
-
-### Aggregate Key Model
-
-Aggregate Key tables are primarily used in pre-aggregation scenarios rather than data update scenarios. However, column updates can also be achieved by setting the aggregation function to `REPLACE_IF_NOT_NULL`.
-
-## Applicable scenarios
-
-+ Real-time dynamic column updates. Where certain field values in the table need to be updated in real-time at high frequencies. For example, in a user label table generated by T+1, some fields containing information about the latest user behaviors need to be updated in real-time for real-time analysis and decision-making in advertising/recommendation systems.
-+ Combining multiple source tables into one large wide table.
-+ Data correction.
-
-## Fundamentals
-
-For more information about the principles of the Unique Key model and Aggregate Key model, please refer to the [Data Model](../../data-table/data-model.md) introduction.
-
-### Unique Key Model
-
-**The Unique Key model currently supports column updates only in the Merge-on-Write implementation.**
-
-Users write data for certain columns into Doris's Memtable through the regular load methods. At this point, the Memtable does not contain complete rows of data. When flushing the Memtable, Doris searches for historical data, fills in the entire row, then writes it to the data file, and marks the data rows with the same key in the historical data file as deleted.
-
-In the case of concurrent loads, Doris uses the MVCC mechanism to ensure data correctness. If two batches of loaded data update different columns with the same key, the load task with the higher system version will recomplete the data rows with the same key written by the lower version load task after the lower version load task succeeds.
-
-### Aggregate Key Model
-
-You can achieve partial column updates by setting the aggregation function to `REPLACE_IF_NOT_NULL`. For detailed usage examples, please refer to the following section.
-
-## Concurrent Writes and Data Visibility
-
-Partial column updates support high-frequency concurrent writes, and once the write is successful, the data becomes visible. The system automatically ensures the correctness of concurrent writes through the MVCC mechanism.
-
-## Performance
-
-Usage Recommendations:
-
-1. For users with high write performance requirements and low query performance requirements, it is recommended to use the Aggregate Key Model.
-2. For users with high query performance requirements and lower write performance requirements (e.g., data writes and updates are mainly completed during off-peak hours in the early morning), or for users with low write frequency, it is recommended to use the Unique Key Merge-on-Write implementation.
-
-### Unique Key Merge-on-Write Implementation
-
-Since the Merge-on-Write implementation requires filling in entire rows of data during data writes to ensure optimal query performance, performing partial column updates using the Merge-on-Write implementation may lead to noticeable load performance degradation.
-
-Write Performance Optimization Recommendations:
-
-1. Use SSDs equipped with NVMe or high-speed SSD cloud disks. Because reading historical data in large quantities occurs when filling in row data, might results in higher read IOPS and read throughput.
-2. Enabling row storage can significantly reduce the IOPS generated when filling in row data, leading to a noticeable improvement in load performance. Users can enable row storage when creating tables using the following property:
-
-```
-"store_row_column" = "true"
-```
-
-### Aggregate Key Model
-
-The Aggregate Key model does not perform any additional processing during the write process, so its write performance is not affected and is the same as regular data ingestion. However, there is a significant cost associated with aggregation during query execution, with typical aggregation query performance being 5-10 times lower compared to the Merge-on-Write implementation of the Unique Key model.
-
- ## Usage Instructions and Examples
-
-### Unique Key Model
-
-#### Table Creation
-
-When creating a table, you need to specify the following property to enable the Merge-on-Write implementation:
-
-```
-enable_unique_key_merge_on_write = true
-```
-
-#### StreamLoad/BrokerLoad/RoutineLoad
-
-If you are using StreamLoad/BrokerLoad/RoutineLoad, add the following header when loading:
-
-```
-partial_columns:true
-```
-
-Also, specify the columns to be loaded in the `columns` header (it must include all key columns, or else updates cannot be performed).
-
-### Flink Connector
-
-If you are using the Flink Connector, you need to add the following configuration:
-
-```
-'sink.properties.partial_columns' = 'true',
-```
-
-Also, specify the columns to be loaded in `sink.properties.columns` (it must include all key columns, or else updates cannot be performed).
-
-#### INSERT INTO
-
-In all data models, by default, when you use `INSERT INTO` with a given set of columns, the default behavior is to insert the entire row. To enable partial column updates in the Merge-on-Write implementation, you need to set the following session variable:
-
-```
-set enable_unique_key_partial_update=true
-```
-
-Please note that the default value of the session variable `enable_insert_strict`, which controls whether the insert statement operates in strict mode, is true. In other words, the insert statement is in strict mode by default, and in this mode, updating non-existing keys in partial column updates is not allowed. Therefore, when using the insert statement for partial columns update and wishing to insert non-existing keys, you need to set `enable_unique_key_partial_update` to true and sim [...]
-
-#### Example
-
-Suppose there is an order table `order_tbl` in Doris, where the order ID is the Key column, and the order status and order amount are Value columns. The data status is as follows:
-
-| Order ID | Order Amount | Order Status |
-| -------- | ------------ | --------------- |
-| 1 | 100 | Pending Payment |
-
-```sql
-+----------+--------------+-----------------+
-| order_id | order_amount | order_status |
-+----------+--------------+-----------------+
-| 1 | 100 | Pending Payment |
-+----------+--------------+-----------------+
-1 row in set (0.01 sec)
-```
-
-Now, when a user clicks to make a payment, he needs to change the order status of the order with Order ID '1' to 'Pending Delivery'.
-
-If you are using StreamLoad, you can perform the update as follows:
-
-```sql
-$ cat update.csv
-1,Pending Delivery
-
-$ curl --location-trusted -u root: -H "partial_columns:true" -H "column_separator:," -H "columns:order_id,order_status" -T /tmp/update.csv http://127.0.0.1:48037/api/db1/order_tbl/_stream_load
-```
-
-If you are using `INSERT INTO`, you can perform the update as follows:
-
-```sql
-set enable_unique_key_partial_update=true;
-INSERT INTO order_tbl (order_id, order_status) values (1,'Pending Delivery');
-```
-
-The updated result is as follows:
-
-```sql
-+----------+--------------+------------------+
-| order_id | order_amount | order_status |
-+----------+--------------+------------------+
-| 1 | 100 | Pending Delivery |
-+----------+--------------+------------------+
-1 row in set (0.01 sec)
-```
-
-### Aggregate Key Model
-
-#### Table Creation
-
-Set the aggregation function for the columns that need column updates to `REPLACE_IF_NOT_NULL` as follows:
-
-```sql
-CREATE TABLE `order_tbl` (
- `order_id` int(11) NULL,
- `order_amount` int(11) REPLACE_IF_NOT_NULL NULL,
- `order_status` varchar(100) REPLACE_IF_NOT_NULL NULL
-) ENGINE=OLAP
-AGGREGATE KEY(`order_id`)
-COMMENT 'OLAP'
-DISTRIBUTED BY HASH(`order_id`) BUCKETS 1
-PROPERTIES (
-"replication_allocation" = "tag.location.default: 1"
-);
-```
-
-#### Write Data
-
-Whether through load tasks or using `INSERT INTO`, you can directly write the data for the columns you want to update.
-
-#### Example
-
-Similar to the previous example, the corresponding Stream Load command (no additional header required) is:
-
-```shell
-curl --location-trusted -u root: -H "column_separator:," -H "columns:order_id,order_status" -T /tmp/update.csv http://127.0.0.1:48037/api/db1/order_tbl/_stream_load
-```
-
-The corresponding `INSERT INTO` statement (no need to set additional session variables) is:
-
-```sql
-INSERT INTO order_tbl (order_id, order_status) values (1,'Pending Delivery');
-```
-
-## Usage Limitations
-
-### Unique Key Merge-on-Write Implementation
-
-In version 2.0, all rows in the same batch of data write tasks (whether load tasks or `INSERT INTO`) can only update the same columns. If you need to update different columns of data, you will need to write them in separate batches.
-
-In version 2.1, we will support more flexible column updates, allowing users to update different columns for each row within the same batch load.
-
-### Aggregate Key Model
-
-Users cannot change a field from non-NULL to NULL; any NULL values written with the `REPLACE_IF_NOT_NULL` aggregation function will be automatically ignored during processing.
diff --git a/versioned_docs/version-2.0/data-operate/update/unique-update-transaction.md b/versioned_docs/version-2.0/data-operate/update/unique-update-transaction.md
index 33c3e0d202b7..4a9da23ea8aa 100644
--- a/versioned_docs/version-2.0/data-operate/update/unique-update-transaction.md
+++ b/versioned_docs/version-2.0/data-operate/update/unique-update-transaction.md
@@ -1,6 +1,6 @@
---
{
- "title": "Sequence Column",
+ "title": "Update Transaction of Unique Model",
"language": "en"
}
---
@@ -24,78 +24,98 @@ specific language governing permissions and limitations
under the License.
-->
-# Sequence Column
-The sequence column currently only supports the Uniq model. The Uniq model is mainly aimed at scenarios that require a unique primary key, which can guarantee the uniqueness constraint of the primary key. However, due to the REPLACE aggregation method, the replacement order of data imported in the same batch is not guaranteed. See [Data Model](../../data-table/data-model.md). If the replacement order cannot be guaranteed, the specific data finally imported into the table cannot be determ [...]
+## Update Concurrency Control
-In order to solve this problem, Doris supports the sequence column. The user specifies the sequence column when importing. Under the same key column, the REPLACE aggregation type column will be replaced according to the value of the sequence column. The larger value can replace the smaller value, and vice versa. Cannot be replaced. This method leaves the determination of the order to the user, who controls the replacement order.
+By default, concurrent updates on the same table are not allowed in Doris.
-## Applicable Scene
+The main reason is that Doris currently supports row-level updates, which means that even if the user specifies to update only a specific column (e.g., `SET v2 = 1`), all other value columns will be overwritten as well (even though their values remain unchanged).
-Sequence columns can only be used under the Uniq data model.
+This poses a problem when multiple update operations are performed concurrently on the same row. The behavior becomes unpredictable, and it may lead to inconsistent or "dirty" data.
-## Fundamental
+However, in practical applications, if the user can guarantee that concurrent updates will not operate on the same row simultaneously, they can manually enable concurrent update restrictions. This can be achieved by modifying the FE (Frontend) configuration `enable_concurrent_update`. When the configuration value is set to true, there are no restrictions on concurrent updates.
-By adding a hidden column `__DORIS_SEQUENCE_COL__`, the type of the column is specified by the user when creating the table, the specific value of the column is determined during import, and the REPLACE column is replaced according to this value.
+:::caution
+Note: Enabling the `enable_concurrent_update` configuration carries some performance risks.
+:::
-### Create Table
-When creating a Uniq table, a hidden column `__DORIS_SEQUENCE_COL__` will be automatically added according to the user-specified type.
+## Sequence Column
-### Import
+The Unique model primarily caters to scenarios that require unique primary keys, ensuring the uniqueness constraint. When loading data in the same batch or different batches, the replacement order is not guaranteed. The uncertainty in the replacement order results in ambiguity in the specific data loaded into the table.
-When importing, fe sets the value of the hidden column to the value of the `order by` expression (broker load and routine load), or the value of the `function_column.sequence_col` expression (stream load) during the parsing process, the value column will be Replace with this value. The value of the hidden column `__DORIS_SEQUENCE_COL__` can be set to either a column in the data source or a column in the table structure.
+To address this issue, Doris supports sequence columns. Users can specify a sequence column during data load, allowing the replacement order to be controlled by the user. The sequence column determines the order of replacements for rows with the same key column. A higher sequence value can replace a lower one, but not vice versa. This method delegates the determination of order to the user, enabling control over the replacement sequence.
-### Read
+:::note
+Sequence columns are currently supported only in the Uniq model.
+:::
-When the request contains the value column, the `__DORIS_SEQUENCE_COL__` column needs to be additionally read. This column is used as the basis for the replacement order of the REPLACE aggregate function under the same key column. The larger value can replace the smaller value, otherwise it cannot be replaced.
+### Basic Principles
-### Cumulative Compaction
+The basic principle is achieved by adding a hidden column called __**DORIS_SEQUENCE_COL__. The type of this column is specified by the user during table creation and its specific value is determined during data load. Based on this value, the row that takes effect is determined for rows with the same key column.
-The principle is the same as that of the reading process during Cumulative Compaction.
+**Table Creation**
-### Base Compaction
+When creating a Uniq table, an automatically added hidden column called __**DORIS_SEQUENCE_COL__ is created, based on the user-specified type.
-The principle is the same as the reading process during Base Compaction.
+**Data load**
-### Syntax
+During data load, the FE (Frontend) sets the value of the hidden column as the value of the `ORDER BY` expression (for broker load and routine load) or the value of the `function_column.sequence_col` expression (for stream load). The value column is replaced based on this sequence value. The value of the hidden column, `DORIS_SEQUENCE_COL`, can be set as a column in the data source or a column in the table structure.
-There are two ways to create a table with sequence column. One is to set the `sequence_col` attribute when creating a table, and the other is to set the `sequence_type` attribute when creating a table.
+**Read**
-#### Set `sequence_col`(Recommend)
-When you create the Uniq table, you can specify the mapping of sequence column to other columns
+When requesting the value column, an additional read operation is performed for the `DORIS_SEQUENCE_COL` column. This column is used as the basis for the REPLACE
-```text
+**Cumulative Compaction**
+
+The principles during Cumulative Compaction are the same as the read operation.
+
+**Base Compaction**
+
+The principles during Base Compaction are the same as the read operation.
+
+
+### Syntax Usage
+
+**Sequence Column has two ways to create a table, one is to set the `sequence_col` attribute when creating a table, and the other is to set the `sequence_type` attribute when creating a table.**
+
+**1. Set `sequence_col` (Recommended)**
+
+When creating a Uniq table, specify the mapping of the sequence column to other columns in the table.
+
+```Plain
PROPERTIES (
"function_column.sequence_col" = 'column_name',
);
```
-The sequence_col is used to specify the mapping of the sequence column to a column in the table, which can be integral and time (DATE, DATETIME). The type of this column cannot be changed after creation.
-The import method is the same as that without the sequence column. It is relatively simple and recommended.
+`sequence_col` is used to specify the mapping of the sequence column to a column in the table. The column can be of type integer or time type (DATE, DATETIME), and its type cannot be changed after creation.
+
+The load method is the same as when there is no sequence column, making it relatively simple. This method is recommended.
-### Set `sequence_type`
+**2. Set `sequence_type`**
-When you create the Uniq table, you can specify the sequence column type
+When creating a Uniq table, specify the type of the sequence column.
-```text
+```Plain
PROPERTIES (
"function_column.sequence_type" = 'Date',
);
```
-The sequence_type is used to specify the type of the sequence column, which can be integral and time (DATE / DATETIME).
-The mapping column needs to be specified when importing.
+`sequence_type` is used to specify the type of the sequence column, which can be integer or time type (DATE, DATETIME).
-#### Stream Load
+**During data load, you need to specify the mapping of the sequence column to other columns.**
-The syntax of the stream load is to add the mapping of hidden columns corresponding to source_sequence in the `function_column.sequence_col` field in the header, for example
+**1. Stream Load**
-```shell
+The syntax for stream load is to add the mapping of the hidden column `function_column.sequence_col` to the `source_sequence` in the header. Example:
+
+```Bash
curl --location-trusted -u root -H "columns: k1,k2,source_sequence,v1,v2" -H "function_column.sequence_col: source_sequence" -T testData http://host:port/api/testDb/testTbl/_stream_load
```
-#### Broker Load
+**2. Broker Load**
-Set the source_sequence field for the hidden column map at `ORDER BY`
+Set the mapping of the hidden column `source_sequence` in the `ORDER BY` clause.
```sql
LOAD LABEL db1.label1
@@ -115,18 +135,17 @@ PROPERTIES
(
"timeout" = "3600"
);
-
```
-#### Routine Load
+**3. Routine Load**
-The mapping method is the same as above, as shown below
+The mapping method is the same as above. Example:
```sql
- CREATE ROUTINE LOAD example_db.test1 ON example_tbl
+CREATE ROUTINE LOAD example_db.test1 ON example_tbl
[WITH MERGE|APPEND|DELETE]
COLUMNS(k1, k2, source_sequence, v1, v2),
- WHERE k1 > 100 and k2 like "%doris%"
+ WHERE k1 100 and k2 like "%doris%"
[ORDER BY source_sequence]
PROPERTIES
(
@@ -145,16 +164,21 @@ The mapping method is the same as above, as shown below
);
```
-## Enable Sequence Column Support
-If `function_column.sequence_col` or `function_column.sequence_type` is set when creating a new table, the new table will support sequence column. For a table that does not support sequence column, if you want to use this function, you can use the following statement: `ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")` to enable.
+### Enabling Sequence Column Support
+
+If `function_column.sequence_col` or `function_column.sequence_type` is set when creating a new table, the new table will support sequence columns.
+
+For a table that does not support sequence columns, if you want to use this feature, you can use the following statement: `ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")` to enable it.
+
+If you are unsure whether a table supports sequence columns, you can set a session variable to display hidden columns with `SET show_hidden_columns=true`, and then use `desc tablename`. If the output includes the `DORIS_SEQUENCE_COL` column, it means that the table supports sequence columns; otherwise, it does not.
- If you are not sure whether a table supports sequence column, you can display hidden columns by setting a session variable `SET show_hidden_columns=true`, then use `desc tablename`, if there is a `__DORIS_SEQUENCE_COL__` column in the output, it is supported, if not, it is not supported .
+### Usage Example
-## Usage Examples
-Let's take the stream Load as an example to show how to use it
-1. Create a table that supports sequence column.
+Here is an example using Stream Load to demonstrate the usage:
-Create the test_table data table of the unique model and specify that the sequence column maps to the `modify_date` column in the table.
+**1. Create a table with sequence col support**
+
+Create a unique model `test_table` and specify the sequence column mapping to the `modify_date` column in the table.
```sql
CREATE TABLE test.test_table
@@ -174,9 +198,10 @@ PROPERTIES(
);
```
-The table structure is shown below
+Table structure:
+
```sql
-MySQL > desc test_table;
+MySQL desc test_table;
+-------------+--------------+------+-------+---------+---------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-------+---------+---------+
@@ -188,10 +213,11 @@ MySQL > desc test_table;
+-------------+--------------+------+-------+---------+---------+
```
-2. Import data normally:
+**2. Load data normally:**
-Import the following data
-```
+Load the following data:
+
+```Plain
1 2020-02-22 1 2020-02-21 a
1 2020-02-22 1 2020-02-22 b
1 2020-02-22 1 2020-03-05 c
@@ -199,58 +225,76 @@ Import the following data
1 2020-02-22 1 2020-02-23 e
1 2020-02-22 1 2020-02-24 b
```
-Take the Stream Load as an example here and map the sequence column to the modify_date column
-```shell
+
+Here is an example using Stream Load:
+
+```Bash
curl --location-trusted -u root: -T testData http://host:port/api/test/test_table/_stream_load
```
-The results is
+
+The result is:
+
```sql
-MySQL > select * from test_table;
+MySQL select * from test_table;
+---------+------------+----------+-------------+---------+
| user_id | date | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
| 1 | 2020-02-22 | 1 | 2020-03-05 | c |
+---------+------------+----------+-------------+---------+
```
-In this import, the c is eventually retained in the keyword column because the value of the sequence column (the value in modify_date) is the maximum value: '2020-03-05'.
-3. Guarantee of substitution order
+In the data load, because the value of the sequence column (i.e., modify_date) '2020-03-05' is the maximum, the keyword column retains the value 'c'.
+
+**3. Guarantee the order of replacement**
-After the above steps are completed, import the following data
-```text
+After completing the above steps, load the following data:
+
+```Plain
1 2020-02-22 1 2020-02-22 a
1 2020-02-22 1 2020-02-23 b
```
-Query data
+
+Query the data:
+
```sql
-MySQL [test]> select * from test_table;
+MySQL [test] select * from test_table;
+---------+------------+----------+-------------+---------+
| user_id | date | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
| 1 | 2020-02-22 | 1 | 2020-03-05 | c |
+---------+------------+----------+-------------+---------+
```
-In this import, the c is eventually retained in the keyword column because the value of the sequence column (the value in modify_date) in all imports is the maximum value: '2020-03-05'.
-Try importing the following data again
-```
+In the loaded data, the sequence column (modify_date) of all previously loaded data is compared, and '2020-03-05' is the maximum. Therefore, the keyword column retains the value 'c'.
+
+**4. Try loading the following data again**
+
+```Plain
1 2020-02-22 1 2020-02-22 a
1 2020-02-22 1 2020-03-23 w
```
-Query data
+
+Query the data:
+
```sql
-MySQL [test]> select * from test_table;
+MySQL [test] select * from test_table;
+---------+------------+----------+-------------+---------+
| user_id | date | group_id | modify_date | keyword |
+---------+------------+----------+-------------+---------+
| 1 | 2020-02-22 | 1 | 2020-03-23 | w |
+---------+------------+----------+-------------+---------+
```
-At this point, you can replace the original data in the table. To sum up, the sequence column will be compared among all the batches, the largest value of the same key will be imported into Doris table.
-## Note
-1. To prevent misuse, in load tasks like StreamLoad/BrokerLoad and row updates with insert statements, user must explicitly specify the sequence column (unless the sequence column's default value is CURRENT_TIMESTAMP); otherwise, user will receive the following error message:
-```
-Table test_tbl has a sequence column, need to specify the sequence column
+Now the original data in the table can be replaced. In summary, during thestream load process, the sequence column is used to determine the order of replacement for duplicate records. The record with the maximum value in the sequence column will be retained in the table.
+
+### Note
+
+1. To prevent misuse, users must explicitly specify the sequence column in loading tasks such as StreamLoad/BrokerLoad and in insert statements for row updates (unless the default value of the sequence column is CURRENT_TIMESTAMP). Otherwise, the following error message will be received:
+
+```Plain
+Table test_tbl has sequence column, need to specify the sequence column
```
-2 Starting from version 2.0, Doris supports partial column updates for Merge-on-Write implementation on Unique Key tables. In load tasks with partial column update, users can update only a subset of columns at a time, so it is not mandatory to include the sequence column. If the import task submitted by the user includes the sequence column, it will have no effect on the behavior. However, if the import task does not include the sequence column, Doris will use the matching historical dat [...]
+
+2. Since version 2.0, Doris has supported partial column updates for Merge-on-Write implementation of Unique Key tables. In partial column update, users can update only a subset of columns each time, so it is not necessary to include the sequence column. If the loading task submitted by the user includes the sequence column, it has no effect. If the loading task submitted by the user does not include the sequence column, Doris will use the value of the matching sequence column from the h [...]
+
+3. In cases of concurrent data load, Doris utilizes MVCC (Multi-Version Concurrency Control) mechanism to ensure data correctness. If two batches of loaded data update different columns of the same key, the load task with a higher system version will reapply the data for the same key written by the load task with a lower version after the lower version load task succeeds.
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/update/unique-update.md b/versioned_docs/version-2.0/data-operate/update/unique-update.md
index 2f8f27f9e500..1a15bef4759d 100644
--- a/versioned_docs/version-2.0/data-operate/update/unique-update.md
+++ b/versioned_docs/version-2.0/data-operate/update/unique-update.md
@@ -1,6 +1,6 @@
---
{
- "title": "Unique update",
+ "title": "UPDATE Command to Update Data in Unique Model",
"language": "en"
}
---
@@ -24,37 +24,35 @@ specific language governing permissions and limitations
under the License.
-->
-The Update command is used to update data in Doris. It can only be executed on tables that follow the Unique data model.
+The main topic is how to use the UPDATE command to update data in Doris. The UPDATE command can only be executed on tables in the Unique data model.
-## Use cases
+## Use Cases
-- Modifying the values of rows that meet certain conditions.
+- Modify the values of rows that meet certain conditions.
-- This is suitable for updating a small amount of data that is not frequently updated.
+- Suitable for updating a small amount of data that is not frequently updated.
-## Principles
+## Basic Principles
-The Update command uses the filtering logic of the query engine's WHERE clause to select the rows that need to be updated from the target table. It then uses the inherent value column of the Unique data model to replace the old data with the new data. After modifying the selected rows, they are reinserted into the table, thereby achieving row-level updates.
+By utilizing the filtering logic of the query engine's WHERE clause, the UPDATE command selects the rows that need to be updated from the target table. Then, using the built-in logic of the Value column in the Unique model, the old data is replaced with the new data, and the updated rows are reinserted into the table, thus achieving row-level updates.
### Synchronization
-The Update syntax in Doris is synchronous. This means that once the Update statement is successfully executed, the update operation is completed, and the data is immediately visible.
+The UPDATE syntax in Doris is synchronous, meaning that when an UPDATE statement is executed successfully, the update operation is completed, and the data is immediately visible.
### Performance
-The performance of the Update statement depends on the number of rows to be updated and the efficiency of the condition used for retrieval.
+The performance of the UPDATE statement depends on the number of rows to be updated and the efficiency of the condition retrieval.
-- Number of rows to be updated: The more rows that need to be updated, the slower the Update statement will be. Update is suitable for scenarios where only a few rows need to be modified, such as updating the values of individual rows. It is not recommended for bulk data modification.
+- Number of rows to be updated: The more rows that need to be updated, the slower the UPDATE statement will be. The UPDATE command is suitable for scenarios where occasional updates are required, such as modifying values for individual rows. It is not suitable for bulk data modifications.
-- Efficiency of the condition: The Update operation first reads the rows that satisfy the given condition. Therefore, if the condition can be efficiently retrieved, the update speed will be faster. It is recommended to have the condition column hit an index or perform partition and bucket pruning. This helps Doris to quickly locate the rows to be updated and improves update efficiency. It is strongly discouraged to have the condition column dependent on the value column.
+- Efficiency of condition retrieval: The UPDATE operation reads the rows that satisfy the condition first. Therefore, if the condition retrieval is efficient, the UPDATE operation will be faster. It is recommended to have the condition column indexed or utilize partitioning and bucketing pruning to quickly locate the rows to be updated, thus improving the update efficiency. It is strongly advised not to include the value column in the condition column.
-## Examples
+## Example
-Assuming there is an order table in Doris, where the order ID is the key column and the order status and order amount are the value columns.
+Suppose there is an order table in Doris, where the order_id is the key column, and the order status and order amount are the Value columns. The data looks as follows:
-Here is an example of the data:
-
-| Order ID | Order Amount | Order Status |
+| order_id | order_amount | order_status |
| -------- | ------------ | ------------ |
| 1 | 100 | Pending |
@@ -67,7 +65,7 @@ Here is an example of the data:
1 row in set (0.01 sec)
```
-Now, when a user clicks on the "Pay" button, the Doris system needs to update the order status of order ID '1' to 'To be shipped'. This requires the use of the Update functionality.
+Now, when a user clicks on the payment, the Doris system needs to update the order status of the order with ID '1' to 'To be shipped'. This requires using the UPDATE functionality.
```sql
mysql> UPDATE test_order SET order_status = 'To be shipped' WHERE order_id = 1;
@@ -75,7 +73,7 @@ Query OK, 1 row affected (0.11 sec)
{'label':'update_20ae22daf0354fe0-b5aceeaaddc666c5', 'status':'VISIBLE', 'txnId':'33', 'queryId':'20ae22daf0354fe0-b5aceeaaddc666c5'}
```
-After the update, the result will be as follows:
+The updated result is as follows:
```sql
+----------+--------------+--------------+
@@ -86,6 +84,6 @@ After the update, the result will be as follows:
1 row in set (0.01 sec)
```
-## More details
+## More Details
-For more detailed syntax and usage information on data updates, please refer to the [Update](../../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) command manual. You can also enter `HELP UPDATE` in the MySQL client command-line interface to get more help information.
\ No newline at end of file
+For more detailed syntax on data updates, please refer to the [UPDATE](../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE) command manual. You can also enter `HELP UPDATE` in the MySQL client command line for more information and assistance.
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/update/aggregate-load-update.md b/versioned_docs/version-2.0/data-operate/update/update-of-aggregate-model.md
similarity index 100%
rename from versioned_docs/version-2.0/data-operate/update/aggregate-load-update.md
rename to versioned_docs/version-2.0/data-operate/update/update-of-aggregate-model.md
diff --git a/versioned_docs/version-2.0/data-operate/update/update-of-unique-model.md b/versioned_docs/version-2.0/data-operate/update/update-of-unique-model.md
new file mode 100644
index 000000000000..cfd8d040198c
--- /dev/null
+++ b/versioned_docs/version-2.0/data-operate/update/update-of-unique-model.md
@@ -0,0 +1,162 @@
+---
+{
+ "title": "Update for Unqiue Load",
+ "language": "en"
+}
+---
+
+<!--
+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.
+-->
+
+This document primarily introduces the updates based on the load data on the Doris Unique Key model.
+
+## Updates on All Columns
+
+When load data into the primary key model (Unique model) in Doris using supported load methods such as Stream Load, Broker Load, Routine Load, Insert Into, etc., if there are no corresponding data rows with the primary key, new data will be inserted. If there are corresponding data rows with the primary key, the data will be updated. In other words, load data into the Doris primary key model follows an "upsert" mode. Based on the import, updating existing records is by default the same a [...]
+
+## Partial Update
+
+Updating partial columns mainly refers to directly updating certain field values in a table instead of updating all field values. This can be done using the Update statement, which typically involves reading the entire row data, updating specific field values, and then writing it back. This read-write transaction is time-consuming and not suitable for writing large amounts of data. In the context of load updates on the primary key model, Doris provides a functionality to directly insert [...]
+
+:::caution
+Note:
+
+1. Partial updates are only supported in the Merge-on-Write implementation of the Unique Key starting from version 2.0.
+
+2. Starting from version 2.0.2, partial updates are supported using INSERT INTO.
+
+3. Version 2.1.0 will support more flexible column updates.
+:::
+
+### Use Cases
+
+- Real-time dynamic column updates that require high-frequency updates on certain fields in the table. For example, in a user tag table, there are fields containing the latest user behavior information that needs real-time updates to enable real-time analysis and decision-making in advertising/recommendation systems.
+
+- Combining multiple source tables into a large denormalized table.
+
+- Data correction.
+
+### Usage
+
+**Table Creation**
+
+When creating the table, the following property needs to be specified to enable the Merge-on-Write implementation:
+
+```Plain
+enable_unique_key_merge_on_write = true
+```
+
+**StreamLoad/BrokerLoad/RoutineLoad**
+
+If you are using Stream Load/Broker Load/Routine Load, add the following header during the load:
+
+```Plain
+partial_columns: true
+```
+
+Also, specify the columns to be loaded in the `columns` section (all key columns must be included, otherwise updates won't be possible).
+
+**Flink Connector**
+
+If you are using the Flink Connector, add the following configuration:
+
+```Plain
+'sink.properties.partial_columns' = 'true',
+```
+
+Also, specify the columns to be loaded in `sink.properties.column` (all key columns must be included, otherwise updates won't be possible).
+
+**INSERT INTO**
+
+In all data models, when using `INSERT INTO` with a subset of columns, the default behavior is to insert the entire row. To enable partial column updates in the Merge-on-Write implementation, the following session variable needs to be set:
+
+```sql
+set enable_unique_key_partial_update=true
+```
+
+Note that the default value for the session variable `enable_insert_strict`, which controls whether the insert statement operates in strict mode, is true. In strict mode, updating non-existing keys during partial column updates is not allowed. So, if you want to insert non-existing keys during partial column updates using the insert statement, you need to set `enable_unique_key_partial_update` to true and also set `enable_insert_strict` to false.
+
+### Example
+
+Suppose there is an order table named `order_tbl` in Doris, where the order ID is a key column, and the order status and order amount are value columns. The data is as follows:
+
+| Order ID | Order Amount | Order Status |
+| -------- | ------------ | ------------ |
+| 1 | 100 | Pending |
+
+```sql
++----------+--------------+--------------+
+| order_id | order_amount | order_status |
++----------+--------------+--------------+
+| 1 | 100 | Pending |
++----------+--------------+--------------+
+1 row in set (0.01 sec)
+```
+
+Now, when a user clicks on payment, the Doris system needs to update the order status of the order with ID '1' to 'To be shipped'.
+
+If you are using Stream Load, you can update as follows:
+
+```sql
+$ cat update.csv
+
+1,To be shipped
+
+$ curl --location-trusted -u root: -H "partial_columns:true" -H "column_separator:," -H "columns:order_id,order_status" -T /tmp/update.csv http://127.0.0.1:48037/api/db1/order_tbl/_stream_load
+```
+
+If you are using `INSERT INTO`, you can update as following methods:
+
+```sql
+set enable_unique_key_partial_update=true;
+INSERT INTO order_tbl (order_id, order_status) values (1,'待发货');
+```
+
+The translated version in English:
+
+After the update, the result is as follows:
+
+```sql
++----------+--------------+--------------+
+| order_id | order_amount | order_status |
++----------+--------------+--------------+
+| 1 | 100 | To be shipped |
++----------+--------------+--------------+
+1 row in set (0.01 sec)
+```
+
+Partial Column Updates in the Primary Key Model
+
+### Notes
+
+Due to the Merge-on-Write implementation requiring data completion during data writing to ensure optimal query performance, performing partial column updates using the Merge-on-Write implementation may result in a decrease in load performance.
+
+Suggestions for improving load performance:
+
+- Use SSDs equipped with NVMe or high-speed SSD cloud disks. Reading historical data in large quantities during data completion will generate high read IOPS and read throughput.
+
+- Enabling row storage can significantly reduce the IOPS generated during data completion, resulting in noticeable improvements in load performance. Users can enable row storage by using the following property when creating the table:
+
+```Plain
+"store_row_column" = "true"
+```
+
+In version 2.0, all rows in a batch write task (whether it is an load task or `INSERT INTO`) can only update the same columns. If you need to update different columns, you will need to perform separate batch writes.
+
+In version 2.1, flexible column updates will be supported, allowing users to update different columns for each row within the same batch load.
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/data-operate/update/update-overview.md b/versioned_docs/version-2.0/data-operate/update/update-overview.md
index 40746988eb4e..9f61cdc3e8c6 100644
--- a/versioned_docs/version-2.0/data-operate/update/update-overview.md
+++ b/versioned_docs/version-2.0/data-operate/update/update-overview.md
@@ -1,6 +1,6 @@
---
{
- "title": "Update",
+ "title": "Update Overview",
"language": "en"
}
---
@@ -24,109 +24,66 @@ specific language governing permissions and limitations
under the License.
-->
-# Update
+Data update primarily refers to the modification of the value column of data with the same key. For the primary(unique) key model, this update involves replacing the existing value, while for the aggregate model, it involves aggregating the values in the value column.
-This article mainly describes how to use the UPDATE command to operate if we need to modify or update the data in Doris. The data update is limited to the version of Doris and can only be used in Doris **Version 0.15.x +**.
+## Update in Primary Key (Unique) Model
-## Applicable scenarios
+Starting from Doris 2.0, Doris primary key (unique) model supports both Merge-on-Read (MoR) and Merge-on-Write (MoW) storage modes. MoR is optimized for write operations, while MoW is optimized for faster analysis performance. In actual tests, the analysis performance of MoW storage can be 5-10 times faster than MoR.
-+ Modify its value for rows that meet certain conditions;
-+ Point update, small range update, the row to be updated is preferably a very small part of the entire table;
-+ The update command can only be executed on a table with a Unique data model.
+By default, in Doris 2.0, the unique key model is still based on MoR. To create a MoW model, you need to manually specify the parameter "enable_unique_key_merge_on_write" as "true". Here's an example:
-## Fundamentals
-
-Use the query engine's own where filtering logic to filter the rows that need to be updated from the table to be updated. Then use the Unique model's own Value column replacement logic to change the rows to be updated and reinsert them into the table. This enables row-level updates.
-
-### Synchronization
-
-The Update syntax is a synchronization syntax in Doris. If the Update statement succeeds, the update succeeds and the data is visible.
-
-### Performance
-
-The performance of the Update statement is closely related to the number of rows to be updated and the retrieval efficiency of the condition.
-
-+ Number of rows to be updated: The more rows to be updated, the slower the Update statement will be. This is consistent with the principle of importing.
- Doris updates are more suitable for occasional update scenarios, such as changing the values of individual rows.
- Doris is not suitable for large batches of data changes. Large modifications can make Update statements take a long time to run.
-
-+ Condition retrieval efficiency: Doris Update implements the principle of reading the rows that satisfy the condition first, so if the condition retrieval efficiency is high, the Update will be faster.
- The condition column should ideally be hit, indexed, or bucket clipped. This way Doris does not need to scan the entire table and can quickly locate the rows that need to be updated. This improves update efficiency.
- It is strongly discouraged to include the UNIQUE model value column in the condition column.
-
-### Concurrency Control
-
-By default, multiple concurrent Update operations on the same table are not allowed at the same time.
-
-The main reason for this is that Doris currently supports row updates, which means that even if the user declares ``SET v2 = 1``, virtually all other Value columns will be overwritten (even though the values are not changed).
-
-This presents a problem in that if two Update operations update the same row at the same time, the behavior may be indeterminate. That is, there may be dirty data.
-
-However, in practice, the concurrency limit can be turned on manually if the user himself can guarantee that even if concurrent updates are performed, they will not operate on the same row at the same time. This is done by modifying the FE configuration ``enable_concurrent_update``. When the configuration value is true, there is no limit on concurrent updates.
-> Note: After enabling the configuration, there will be certain performance risks. You can refer to the performance section above to improve update efficiency.
+```sql
+CREATE TABLE IF NOT EXISTS example_tbl_unique_merge_on_write
+(
+ `user_id` LARGEINT NOT NULL,
+ `username` VARCHAR(50) NOT NULL ,
+ `city` VARCHAR(20),
+ `age` SMALLINT,
+ `sex` TINYINT,
+ `phone` LARGEINT,
+ `address` VARCHAR(500),
+ `register_time` DATETIME
+)
+UNIQUE KEY(`user_id`, `username`)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1",
+"enable_unique_key_merge_on_write" = "true"
+);
+```
-## Risks of Use
+:::caution
+Starting from Doris 2.1, write merge will be the default mode for the unique key model. So, if you are using Doris 2.1, make sure to read the relevant table creation documentation.
+:::
-Since Doris currently supports row updates and uses a two-step read-and-write operation, there is uncertainty about the outcome of an Update statement if it modifies the same row as another Import or Delete statement.
+### Two Update Methods in Unique Key Model
-Therefore, when using Doris, you must be careful to control the concurrency of Update statements and other DML statements on the **user side itself**.
+- Update statement: This method is used to update a specific column and is suitable for infrequent updates with a small amount of data.
-## Usage Examples
+- Batch update based on load: Doris supports various load methods such as Stream Load, Broker Load, Routine Load, and Insert Into. For unique key tables, all load have the "UPSERT" semantics, meaning that if a row with the same key does not exist, it will be inserted, and if it already exists, it will be updated.
-Suppose there is an order table in Doris, where the order id is the Key column, the order status and the order amount are the Value column. The data status is as follows:
+- If all columns are updated, MoR and MoW have the same semantics, which is to replace all value columns for the same key.
-| order id | order amount | order status |
-| -------- | ------------ | --------------- |
-| 1 | 100 | Pending Payment |
+- If only some columns are updated, the default semantics for MoR and MoW are the same. In this case, the missing columns in the table schema will be updated with their default values, overwriting the old records.
-```sql
-+----------+--------------+-----------------+
-| order_id | order_amount | order_status |
-+----------+--------------+-----------------+
-| 1 | 100 | Pending Payment |
-+----------+--------------+-----------------+
-1 row in set (0.01 sec)
-```
+- If only some columns are updated and MoW is used in the unique key model, and the MySQL session variable "partial_columns" is set to true, or the HTTP header "partial_columns" is set to true, the missing columns will be updated with the corresponding missing column values from the existing record, instead of using the default values from the table schema.
-At this time, after the user clicks to pay, the Doris system needs to change the status of the order with the order id '1' to 'Pending Shipping', and the Update function needs to be used.
+We will provide detailed explanations of these two update methods in the documentation: [Update in Unique Key Model](../update/unique-update) and [Load Update in Unique Key Model](../update/unique-load-update).
-```sql
-mysql> UPDATE test_order SET order_status = 'Pending Shipping' WHERE order_id = 1;
-Query OK, 1 row affected (0.11 sec)
-{'label':'update_20ae22daf0354fe0-b5aceeaaddc666c5', 'status':'VISIBLE', 'txnId':'33', 'queryId':'20ae22daf0354fe0-b5aceeaaddc666c5'}
-```
-
-The result after the update is as follows
-
-```sql
-+----------+--------------+------------------+
-| order_id | order_amount | order_status |
-+----------+--------------+------------------+
-| 1 | 100 | Pending Shipping |
-+----------+--------------+------------------+
-1 row in set (0.01 sec)
-```
+### Update Transactions in Unique Key Model
-After the user executes the UPDATE command, the system performs the following three steps.
+Whether you use the update statement or the batch update based on load, there may be multiple update statements or load jobs in progress. In such cases, it is important to ensure the effectiveness of multiple updates, maintain atomicity, and prevent data inconsistency. This is where update transactions in the unique key model come into play.
- Step 1: Read the rows that satisfy WHERE order id=1 (1, 100, 'pending payment')
- Step 2: Change the order status of the row from 'Pending Payment' to 'Pending Shipping' (1, 100, 'Pending shipment')
- Step 3: Insert the updated row back into the table to achieve the updated effect.
+The documentation on update transactions in the unique key model will cover these aspects. In this document, we will focus on how to control the effectiveness of updates by introducing the hidden column __**DORIS_SEQUENCE_COL__, allowing developers to coordinate and achieve better update transactions.
- | order id | order amount | order status |
- | ---| ---| ---|
- | 1 | 100| Pending Payment |
- | 1 | 100 | Pending shipments |
+## Update in Aggregate Model
-Since the table order is a UNIQUE model, the rows with the same Key, after which the latter will take effect, so the final effect is as follows.
+The update in the aggregate model refers to the process of generating new aggregate values by combining new column values with existing aggregate values, according to the requirements of the aggregate functions.
- | order id | order amount | order status |
- |---|---|---|
- | 1 | 100 | Pending shipments |
+New Agg Value = Agg Func ( Old Agg Value + New Column Value)
-## Update Primary Key Column
-Currently, the Update operation only supports updating the Value column, and the update of the Key column can refer to [Using FlinkCDC to update key column](../../ecosystem/flink-doris-connector.md#Use-FlinkCDC-to-update-Key-column)
+The update in the aggregate model is only supported through load methods and does not support the use of Update statements.
-## More Help
+When defining a table in the aggregate model, if the aggregation function for the value column is defined as REPLACE_IF_NULL, it indirectly achieves partial column update capabilities similar to the unique key model.
-For more detailed syntax used by **data update**, please refer to the [update](../../sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/UPDATE.md) command manual , you can also enter `HELP UPDATE` in the Mysql client command line to get more help information.
+For more details, please refer to the documentation on [Load Update in the Aggregate Model](../update/aggregate-load-update).
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/table-design/cold-hot-separation.md b/versioned_docs/version-2.0/table-design/cold-hot-separation.md
index 308b585e0297..81a82e00616c 100644
--- a/versioned_docs/version-2.0/table-design/cold-hot-separation.md
+++ b/versioned_docs/version-2.0/table-design/cold-hot-separation.md
@@ -24,43 +24,49 @@ specific language governing permissions and limitations
under the License.
-->
-# [Experimental] Cold Hot Separation
+## Use Case
-## Demand Scenarios
+One significant use case in the future is similar to ES log storage, where data in the log scenario is split based on dates. Many of the data are cold data with infrequent queries, requiring a reduction in storage costs for such data. Considering cost-saving:
-A big usage scenario in the future is similar to the es log storage. In the log scenario, the data will be cut by date. Many data are cold data, with few queries. Therefore, the storage cost of such data needs to be reduced. From the perspective of saving storage costs
-1. The price of ordinary cloud disks of cloud manufacturers is higher than that of object storage
-2. In the actual online use of the doris cluster, the utilization rate of ordinary cloud disks cannot reach 100%
-3. Cloud disk is not paid on demand, but object storage can be paid on demand
-4. High availability based on ordinary cloud disks requires multiple replicas, and a replica migration is required for a replica exception. This problem does not exist when data is placed on the object store, because the object store is shared.
+- The pricing of regular cloud disks from various vendors is more expensive than object storage.
+
+- In actual online usage of the Doris Cluster, the utilization of regular cloud disks cannot reach 100%.
+
+- Cloud disks are not billed on demand, while object storage can be billed on demand.
+
+- Using regular cloud disks for high availability requires multiple replicas and replica migration in case of failures. In contrast, storing data on object storage eliminates these issues as it is shared.
## Solution
-Set the freeze time on the partition level to indicate how long the partition will be frozen, and define the location of remote storage stored after the freeze. On the be, the daemon thread will periodically determine whether the table needs to be frozen. If it does, it will upload the data to s3.
-The cold and hot separation supports all doris functions, but only places some data on object storage to save costs without sacrificing functions. Therefore, it has the following characteristics:
+Set the freeze time at the partition level, which indicates how long a partition will be frozen, and define the location of remote storage for storing data after freezing. In the BE (Backend) daemon thread, the table's freeze condition is periodically checked. If a freeze condition is met, the data will be uploaded to object storage compatible with the S3 protocol and HDFS.
-- When cold data is stored on object storage, users need not worry about data consistency and data security
-- Flexible freeze policy, cooling remote storage property can be applied to table and partition levels
-- Users query data without paying attention to the data distribution location. If the data is not local, they will pull the data on the object and cache it to be local
-- Optimization of replica clone. If the stored data is on the object, the replica clone does not need to pull the stored data locally
-- Remote object space recycling recycler. If the table and partition are deleted, or the space is wasted due to abnormal conditions in the cold and hot separation process, the recycler thread will periodically recycle, saving storage resources
-- Cache optimization, which caches the accessed cold data to be local, achieving the query performance of non cold and hot separation
-- Be thread pool optimization, distinguish whether the data source is local or object storage, and prevent the delay of reading objects from affecting query performance
-- newly created materialized view would inherit storage policy from it's base table's corresponding partition
+Cold-hot tiering supports all Doris functionalities and only moves some data to object storage to save costs without sacrificing functionality. Therefore, it has the following characteristics:
-## Storage policy
+- Cold data is stored on object storage, and users do not need to worry about data consistency and security.
-The storage policy is the entry to use the cold and hot separation function. Users only need to associate a storage policy with a table or partition during table creation or doris use. that is, they can use the cold and hot separation function.
+- Flexible freeze strategy, where the cold remote storage property can be applied to both table and partition levels.
-<version since="dev"></version> When creating an S3 RESOURCE, the S3 remote link verification will be performed to ensure that the RESOURCE is created correctly.
+- Users can query data without worrying about data distribution. If the data is not local, it will be pulled from the object storage and cached locally in the BE (Backend).
-In addition, fe configuration needs to be added: `enable_storage_policy=true`
+- Replica clone optimization. If the stored data is on object storage, there is no need to fetch the stored data locally during replica cloning.
-Note: This property will not be synchronized by CCR. If this table is copied by CCR, that is, PROPERTIES contains `is_being_synced = true`, this property will be erased in this table.
+- Remote object space recycling. If a table or partition is deleted or if space waste occurs during the cold-hot tiering process due to exceptional situations, a recycler thread will periodically recycle the space, saving storage resources.
-This is an instance that how to create S3 RESOURCE：
+- Cache optimization, caching accessed cold data locally in the BE to achieve query performance similar to non-cold-hot tiering.
-```
+- BE thread pool optimization, distinguishing between data sources from local and object storage to prevent delays in reading objects from impacting query performance.
+
+## Usage of Storage Policy
+
+The storage policy is the entry point for using the cold-hot tiering feature. Users only need to associate the storage policy with a table or partition during table creation or when using Doris.
+
+:::tip
+When creating an S3 resource, a remote S3 connection validation is performed to ensure the correct creation of the resource.
+:::
+
+Here is an example of creating an S3 resource:
+
+```Plain
CREATE RESOURCE "remote_s3"
PROPERTIES
(
@@ -94,8 +100,10 @@ PROPERTIES(
"storage_policy" = "test_policy"
);
```
-and how to create HDFS RESOURCE：
-```
+
+And here is an example of creating an HDFS resource:
+
+```Plain
CREATE RESOURCE "remote_hdfs" PROPERTIES (
"type"="hdfs",
"fs.defaultFS"="fs_host:default_fs_port",
@@ -104,92 +112,116 @@ CREATE RESOURCE "remote_hdfs" PROPERTIES (
"dfs.nameservices" = "my_ha",
"dfs.ha.namenodes.my_ha" = "my_namenode1, my_namenode2",
"dfs.namenode.rpc-address.my_ha.my_namenode1" = "nn1_host:rpc_port",
- "dfs.namenode.rpc-address.my_ha.my_namenode2" = "nn2_host:rpc_prot",
+ "dfs.namenode.rpc-address.my_ha.my_namenode2" = "nn2_host:rpc_port",
"dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
);
- CREATE STORAGE POLICY test_policy PROPERTIES (
- "storage_resource" = "remote_hdfs",
- "cooldown_ttl" = "300"
- )
+CREATE STORAGE POLICY test_policy PROPERTIES (
+ "storage_resource" = "remote_hdfs",
+ "cooldown_ttl" = "300"
+)
- CREATE TABLE IF NOT EXISTS create_table_use_created_policy (
+CREATE TABLE IF NOT EXISTS create_table_use_created_policy (
k1 BIGINT,
- k2 LARGEINT,
- v1 VARCHAR(2048)
- )
- UNIQUE KEY(k1)
- DISTRIBUTED BY HASH (k1) BUCKETS 3
- PROPERTIES(
+ k2 LARGEINTv1 VARCHAR(2048)
+)
+UNIQUE KEY(k1)
+DISTRIBUTED BY HASH (k1) BUCKETS 3
+PROPERTIES(
"storage_policy" = "test_policy"
- );
-```
-Or for an existing table, associate the storage policy
-```
-ALTER TABLE create_table_not_have_policy set ("storage_policy" = "test_policy");
+);
```
-Or associate a storage policy with an existing partition
+
+Associate a storage policy with an existing table by using the following command:
+
+```Plain
+ALTER TABLE create_table_not_have_policy SET ("storage_policy" = "test_policy");
```
-ALTER TABLE create_table_partition MODIFY PARTITION (*) SET("storage_policy"="test_policy");
+
+Associate a storage policy with an existing partition by using the following command:
+
+```Plain
+ALTER TABLE create_table_partition MODIFY PARTITION (*) SET ("storage_policy" = "test_policy");
```
-**Note**: If the user specifies different storage policies for the entire table and certain partitions during table creation, the storage policy set for the partitions will be ignored, and all partitions of the table will use the table's policy. If you need a specific partition to have a different policy than the others, you can modify it by associating the partition with the desired storage policy, as mentioned earlier in the context of modifying an existing partition.
-For details, please refer to the [resource](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE.md), [policy](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY.md), create table, alter and other documents in the docs directory
-### Some restrictions
+:::tip
+If you specify different storage policies for the entire table and some partitions during table creation, the storage policy set for the partitions will be ignored, and all partitions of the table will use the table's storage policy. If you want a specific partition to have a different storage policy than the others, you can use the method mentioned above to modify the association for that specific partition.
+
+For more details, please refer to the following documents in the Docs directory: [RESOURCE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-RESOURCE), [POLICY](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-POLICY), [CREATE TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE), [ALTER TABLE](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN), which provide detailed explanations.
+:::
+
+### Limitations
+
+- A single table or partition can only be associated with one storage policy. Once associated, the storage policy cannot be dropped without first removing the association between them.
+
+- The object information associated with a storage policy does not support modifying the data storage path, such as bucket, endpoint, root_path, and other information.
+
+- Storage policies support creation, modification, and deletion. Before deleting a storage policy, ensure that no tables are referencing the storage policy.
+
+- When the Merge-on-Write feature is enabled, the Unique model does not support setting a storage policy.
+
+
+## Occupied Size of Cold Data Objects
+
+Method 1: You can use the `show proc '/backends'` command to view the size of each backend's uploaded objects. Look for the `RemoteUsedCapacity` field. Please note that this method may have some latency.
+
+Method 2: You can use the `show tablets from tableName` command to view the size of each tablet in a table, indicated by the `RemoteDataSize` field.
+
+## Cache for Cold Data
-- A single table or a single partition can only be associated with one storage policy. After association, the storage policy cannot be dropped，need to solve the relationship between the two.
-- The object information associated with the storage policy does not support modifying the data storage path information, such as bucket, endpoint, and root_ Path and other information
-- Currently, the storage policy only supports creation,modification and deletion. before deleting, you need to ensure that no table uses this storage policy.
-- The Unique model does not support storage policy setting when the Merge-On-Write feature is enabled.
+As mentioned earlier, caching is introduced for cold data to optimize query performance and save object storage resources. When cold data is first accessed after cooling, Doris reloads the cooled data onto the local disk of the backend (BE). The cold data cache has the following characteristics:
-## Show size of objects occupied by cold data
-1. Through show proc '/backends', you can view the size of each object being uploaded to, and the RemoteUsedCapacity item.There is a slight delay in this method
+- The cache is stored on the BE's disk and does not occupy memory space.
-2. Through show tables from tableName, you can view the object size occupied by each table, and the RemoteDataSize item.
+- The cache can be limited in size and uses LRU (Least Recently Used) for data eviction.
-## cold data cache
+- The implementation of the cache for cold data is the same as the cache for federated query catalog. Please refer to the documentation at [Filecache](../lakehouse/filecache) for more details.
-As above, cold data introduces the cache in order to optimize query performance. After the first hit after cooling, Doris will reload the cooled data to be's local disk. The cache has the following characteristics:
-- The cache is actually stored on the be local disk and does not occupy memory.
-- The cache can limit expansion and clean up data through LRU
-- The implementation of the cache is the same as the cache of the federated query catalog. The documentation is [here](../lakehouse/filecache.md)
+## Compaction of Cold Data
-## cold data compaction
-The time when cold data is imported is from the moment when the data rowset file is written to the local disk, plus the cooling time. Since the data is not written and cooled at one time, to avoid the problem of small files in the object storage, doris will also perform compaction of cold data.
-However, the frequency of cold data compaction and the priority of resource occupation are not very high, let the local hot data be compacted as much as possible before performing cooling. Specifically, it can be adjusted by the following be parameters:
-- The be parameter `cold_data_compaction_thread_num` can set the concurrency of executing cold data compaction, the default is 2.
-- The be parameter `cold_data_compaction_interval_sec` can set the time interval for executing cold data compaction, the default is 1800, unit: second, that is, half an hour.
+The time at which cold data enters is counted from the moment the data rowset file is written to the local disk, plus the cooling duration. Since data is not written and cooled all at once, Doris performs compaction on cold data to avoid the issue of small files within object storage. However, the frequency and resource prioritization of cold data compaction are not very high. It is recommended to perform compaction on local hot data before cooling. You can adjust the following BE parameters:
+- The BE parameter `cold_data_compaction_thread_num` sets the concurrency for cold data compaction. The default value is 2.
-## cold data schema change
-The supported schema change types after data cooling are as follows:
-* Add and delete columns
-* Modify column type
-* Adjust column order
-* Add and modify Bloom Filter
+- The BE parameter `cold_data_compaction_interval_sec` sets the time interval for cold data compaction. The default value is 1800 seconds (30 minutes).
-## cold data Garbage collection
-The garbage data of cold data refers to the data that is not used by any Replica. Object storage may have garbage data generated by the following situations:
-1. Failed to upload rowset but uploaded some segments successfully.
-2. After the FE re-selects the CooldownReplica, the rowset versions of the old and new CooldownReplicas are inconsistent, and the FollowerReplicas synchronize the CooldownMeta of the new CooldownReplica. The rowsets with inconsistent versions in the old CooldownReplica are not used by the Replica and become garbage data.
-3. After the cold data compaction, the rowset before the merge cannot be deleted immediately because it may be used by other Replicas, but in the end FollowerReplicas all use the latest merged rowset, and the rowset before the merge becomes garbage data.
+## Schema Change for Cold Data
-In addition, the garbage data on the object will not be cleaned up immediately.
-The be parameter `remove_unused_remote_files_interval_sec` can set the garbage collection interval of cold data, the default is 21600, unit: second, that is, 6 hours.
+The following schema change types are supported for cold data:
+- Adding or deleting columns
-## Unfinished Matters
+- Modifying column types
-- Currently, there is no way to query the tables associated with a specific storage policy.
-- The acquisition of some remote occupancy indicators is not perfect enough.
+- Adjusting column order
-## FAQ
+- Adding or modifying indexes
-1. ERROR 1105 (HY000): errCode = 2, detailMessage = Failed to create repository: connect to s3 failed: Unable to marshall request to JSON: host must not be null.
+## Garbage Collection of Cold Data
-S3 SDK uses virtual-hosted style by default. However, some object storage systems (such as minio) may not be enabled or support virtual-hosted style access. In this case, we can add the use_path_style parameter to force the use of path style:
+Garbage data for cold data refers to data that is not used by any replica. The following situations may generate garbage data on object storage:
-```text
+1. Partial segment upload succeeds while the upload of the rowset fails.
+
+2. After the FE reselects the CooldownReplica, the rowset versions of the old and new CooldownReplica do not match. FollowerReplicas synchronize the CooldownMeta of the new CooldownReplica, and the rowsets with inconsistent versions in the old CooldownReplica become garbage data.
+
+3. After cold data compaction, the rowsets before merging cannot be immediately deleted because they may still be used by other replicas. However, eventually, all FollowerReplicas use the latest merged rowset, and the rowsets before merging become garbage data.
+
+Furthermore, the garbage data on objects is not immediately cleaned up. The BE parameter `remove_unused_remote_files_interval_sec` sets the time interval for garbage collection of cold data. The default value is 21600 seconds (6 hours).
+
+## TODOs
+
+- Currently, there is no way to query tables associated with a specific storage policy.
+
+- Some remote occupancy metrics may not have comprehensive update retrieval.
+
+## FAQs
+
+1. `ERROR 1105 (HY000): errCode = 2, detailMessage = Failed to create repository: connect to s3 failed: Unable to marshall request to JSON: host must not be null.`
+
+The S3 SDK defaults to using the virtual-hosted style. However, some object storage systems (e.g., MinIO) may not have virtual-hosted style access enabled or supported. In such cases, you can add the `use_path_style` parameter to force the use of path-style access:
+
+```Plain
CREATE RESOURCE "remote_s3"
PROPERTIES
(
@@ -205,5 +237,4 @@ PROPERTIES
"s3.connection.timeout" = "1000",
"use_path_style" = "true"
);
-```
-
+```
\ No newline at end of file
diff --git a/versioned_docs/version-2.0/table-design/schema-change.md b/versioned_docs/version-2.0/table-design/schema-change.md
index 7a22b8e694df..5178d029524f 100644
--- a/versioned_docs/version-2.0/table-design/schema-change.md
+++ b/versioned_docs/version-2.0/table-design/schema-change.md
@@ -24,32 +24,42 @@ specific language governing permissions and limitations
under the License.
-->
-# Schema Change
+Users can modify the schema of an existing table through the Schema Change operation. The schema of a table mainly includes the modification of columns and the modification of indexes. Here we mainly introduce the column-related Scheme changes. For index-related changes, you can check the data table design/table index to see the change method of each index.
-Users can modify the schema of existing tables through the Schema Change operation. Doris currently supports the following modifications:
+## Glossary
-* Add and delete columns
-* Modify column type
-* Adjust column order
-* Add and delete index
+- Base Table: When each table is created, it corresponds to a base table.
-This document mainly describes how to create a Schema Change job, as well as some considerations and frequently asked questions about Schema Change.
-## Glossary
+- Rollup: A roll-up table created based on a base table or other rollup.
-* Base Table: When each table is created, it corresponds to a base table. The base table stores the complete data of this table. Rollups are usually created based on the data in the base table (and can also be created from other rollups).
-* Index: Materialized index. Rollup or Base Table are both called materialized indexes.
-* Transaction: Each import task is a transaction, and each transaction has a unique incrementing Transaction ID.
-* Rollup: Roll-up tables based on base tables or other rollups.
+- Index: materialized index. Rollup or base table are both called materialized indexes.
-## Basic Principles
+- Transaction: Each import task is a transaction, and each transaction has a unique increasing transaction ID.
-The basic process of executing a Schema Change is to generate a copy of the index data of the new schema from the data of the original index. Among them, two parts of data conversion are required. One is the conversion of existing historical data, and the other is the conversion of newly arrived imported data during the execution of Schema Change.
-```
+## Introduction
+
+**Light Schema Change**
+
+Before introduction, it is necessary to know the three Schema Change implementations before the Apache Doris 1.2.0 version, all of which are asynchronous:
+
+- Hard linked schema change mainly acts on addition and subtraction of value columns and does not require modification of the data file.
+
+- Direct schema change is mainly used to change the type of value column, which needs to rewrite the data, but does not involve the key column, and does not need to be reordered.
+
+- Sort schema change is mainly used for the key column schema change, because the key column addition/subtraction/modification type and other operations will affect the sorting of existing data, so the data needs to be read out again, modified, and then sort.
+
+Since Apache Doris 1.2.0 , for the first type, the new feature of light schema change has been introduced. The new light schema change allows the addition and subtraction of the value column to be completed in milliseconds. Starting from Apache Doris 2.0.0, all newly created tables have enabled light schema change by default.
+
+**In addition to adding and deleting the value column, the main principles of other types of schema changes are as follows**
+
+The basic process of executing schema change is to generate a new schema table from the data /Index in the original table /Index data. There are mainly two parts of data conversion, one is the conversion of existing historical data, and the other is the conversion of newly arrived imported data during the execution of schema change.
+
+```Plain
+----------+
| Load Job |
+----+-----+
|
- | Load job generates both origin and new index data
+ | Load job generates both origin and new Index data
|
| +------------------+ +---------------+
| | Origin Index | | Origin Index |
@@ -64,65 +74,210 @@ The basic process of executing a Schema Change is to generate a copy of the inde
+------------------+ +---------------+
```
-Before starting the conversion of historical data, Doris will obtain a latest transaction ID. And wait for all import transactions before this Transaction ID to complete. This Transaction ID becomes a watershed. This means that Doris guarantees that all import tasks after the watershed will generate data for both the original Index and the new Index. In this way, when the historical data conversion is completed, the data in the new Index can be guaranteed to be complete.
-## Create Job
+Before starting to convert historical data, Doris will obtain a latest transaction ID and wait for all import transactions before this transaction ID to complete. This transaction ID becomes a watershed. This means that Doris ensures that all import tasks after the watershed will generate data for the original table /Index and the new table /Index at the same time. This way, when the historical data conversion is completed, the data in the new table can be guaranteed to be complete.
-The specific syntax for creating a Schema Change can be found in the help [ALTER TABLE COLUMN](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md) for the description of the Schema Change section .
+The specific syntax for creating schema changes can be found in the schema change section of the help [ALTER TABLE COLUMN](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN)
-The creation of Schema Change is an asynchronous process. After the job is submitted successfully, the user needs to view the job progress through the `SHOW ALTER TABLE COLUMN` command.
-## View Job
+## Adding a column at a specified position to a specified index
-`SHOW ALTER TABLE COLUMN` You can view the Schema Change jobs that are currently executing or completed. When multiple indexes are involved in a Schema Change job, the command displays multiple lines, each corresponding to an index. For example:
+### Syntax
```sql
-mysql> SHOW ALTER TABLE COLUMN\G;
-*************************** 1. row ***************************
- JobId: 20021
- TableName: tbl1
- CreateTime: 2019-08-05 23:03:13
- FinishTime: 2019-08-05 23:03:42
- IndexName: tbl1
- IndexId: 20022
-OriginIndexId: 20017
-SchemaVersion: 2:792557838
-TransactionId: 10023
- State: FINISHED
- Msg:
- Progress: NULL
- Timeout: 86400
-1 row in set (0.00 sec)
+ALTER TABLE table_name ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
+[AFTER column_name|FIRST]
+[TO rollup_index_name]
+[PROPERTIES ("key"="value", ...)]
```
-* JobId: A unique ID for each Schema Change job.
-* TableName: The table name of the base table corresponding to Schema Change.
-* CreateTime: Job creation time.
-* FinishedTime: The end time of the job. If it is not finished, "N/A" is displayed.
-* IndexName: The name of an Index involved in this modification.
-* IndexId: The unique ID of the new Index.
-* OriginIndexId: The unique ID of the old Index.
-* SchemaVersion: Displayed in M: N format. M is the version of this Schema Change, and N is the corresponding hash value. With each Schema Change, the version is incremented.
-* TransactionId: the watershed transaction ID of the conversion history data.
-* State: The phase of the operation.
- * PENDING: The job is waiting in the queue to be scheduled.
- * WAITING_TXN: Wait for the import task before the watershed transaction ID to complete.
- * RUNNING: Historical data conversion.
- * FINISHED: The operation was successful.
- * CANCELLED: The job failed.
-* Msg: If the job fails, a failure message is displayed here.
-* Progress: operation progress. Progress is displayed only in the RUNNING state. Progress is displayed in M/N. Where N is the total number of copies involved in the Schema Change. M is the number of copies of historical data conversion completed.
-* Timeout: Job timeout time. Unit of second.
+- For aggregate models, if adding a value column, specify `agg_type`.
-## Cancel Job
+- For non-aggregate models (e.g., DUPLICATE KEY), if adding a key column, specify the `KEY` keyword.
-In the case that the job status is not FINISHED or CANCELLED, you can cancel the Schema Change job with the following command:
-`CANCEL ALTER TABLE COLUMN FROM tbl_name;`
+- You cannot add a column to a rollup index that already exists in the base index (if needed, you can create a new rollup index).
-## Best Practice
+### Examples
-Schema Change can make multiple changes to multiple indexes in one job. For example:
-Source Schema:
+**1. Adding a key column `new_col` (non-aggregate model) after col1 to `example_rollup_index`**
+
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
+TO example_rollup_index;
+```
+
+**2. Adding a value column `new_col` (non-aggregate model) with a default value of 0 after col1 to `example_rollup_index`**
+
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN new_col INT DEFAULT "0" AFTER col1
+TO example_rollup_index;
+```
+
+**3. Adding a Key column `new_col` (aggregate model) after col1 to `example_rollup_index`**
+
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN new_col INT DEFAULT "0" AFTER col1
+TO example_rollup_index;
+```
+
+**4. Adding a value column `new_col` with SUM aggregation type (aggregate model) after col1 to `example_rollup_index`**
+
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
+TO example_rollup_index;
+```
+
+## Adding multiple columns to a specified index
+
+### Syntax
+```sql
+ALTER TABLE table_name ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
+[TO rollup_index_name]
+[PROPERTIES ("key"="value", ...)]
+```
+
+- For aggregate models, if adding a value column, specify `agg_type`.
+
+- For aggregate models, if adding a key column, specify the KEY keyword.
+
+- You cannot add a column to a rollup index that already exists in the base index (if needed, you can create a new rollup index).
+
+### Example
+
+Adding multiple columns (aggregate model) to `example_rollup_index`:
+
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
+TO example_rollup_index;
+```
+
+## Removing a column from a specified index
+
+### Syntax
+
+```sql
+ALTER TABLE table_name DROP COLUMN column_name
+[FROM rollup_index_name]
+```
+
+- You cannot delete a partition column.
+
+- If you delete a column from the base index, it will also be removed from the rollup index if it contains the column.
+
+### Example
+
+Removing column col2 from `example_rollup_index`:
+
+```sql
+ALTER TABLE example_db.my_table
+DROP COLUMN col2
+FROM example_rollup_index;
+```
+
+## Modifying the column type and position of a specified index
+
+### Syntax
+
+```sql
+ALTER TABLE table_name MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
+[AFTER column_name|FIRST]
+[FROM rollup_index_name]
+[PROPERTIES ("key"="value", ...)]
+```
+
+- For aggregate models, if modifying a value column, specify `agg_type`.
+
+- For non-aggregate models, if modifying a key column, specify the **KEY** keyword.
+
+- Only the column type can be modified, other column properties remain the same (i.e., other properties need to be explicitly written in the statement, see Example 8).
+
+- Partition and bucket columns cannot be modified.
+
+- Currently, the following type conversions are supported (users need to ensure precision loss):
+
+ - Conversion from TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE to a larger numeric type.
+
+ - Conversion from TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL to VARCHAR.
+
+ - Modification of maximum length for VARCHAR.
+
+ - Conversion from VARCHAR/CHAR to TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE.
+
+ - Conversion from VARCHAR/CHAR to DATE (supports six formatting formats: "%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d", "%Y/%m/%d", "%y/%m/%d").
+
+ - Conversion from DATETIME to DATE (only retains year-month-day information, e.g., `2019-12-09 21:47:05` <--> `2019-12-09`).
+
+ - Conversion from DATE to DATETIME (automatically adds zeros for hours, minutes, and seconds, e.g., `2019-12-09` <--> `2019-12-09 00:00:00`).
+
+ - Conversion from FLOAT to DOUBLE.
+
+ - Conversion from INT to DATE (if the INT type data is invalid, the conversion fails, and the original data remains unchanged).
+
+ - All types except DATE and DATETIME can be converted to STRING, but STRING cannot be converted to any other type.
+
+### Examples
+
+**1. Modifying the column type of Key column col1 to BIGINT in the base index and moving it after column col2**
+
+```sql
+ALTER TABLE example_db.my_table
+MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
```
+
+Note: whether modifying a key column or a value column, the complete column information needs to be declared.
+
+**2. Modifying the maximum length of column val1 in the Base Index. The original val1 is (val1 VARCHAR(32) REPLACE DEFAULT "abc")**
+
+```sql
+ALTER TABLE example_db.my_table
+MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc"
+```
+
+Note: only the column type can be modified while keeping the other properties of the column unchanged.
+
+**3. Modifying the length of a field in the key column of a duplicate key table**
+
+```sql
+alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
+```
+
+## Reorder columns for a specified index
+
+### Syntax
+
+```sql
+ALTER TABLE table_name ORDER BY (column_name1, column_name2, ...)
+[FROM rollup_index_name]
+[PROPERTIES ("key"="value", ...)]
+```
+
+- All columns in the index should be listed.
+
+- Value columns come after key columns.
+
+### Example
+
+Reorder columns in the index `example_rollup_index` (assuming the original column order is: k1, k2, k3, v1, v2).
+
+```sql
+ALTER TABLE example_db.my_table
+ORDER BY (k3,k1,k2,v2,v1)
+FROM example_rollup_index;
+```
+
+## Perform multiple changes in one submission
+
+Schema change can modify multiple indexes in a single job.
+
+### Example 1
+
+Source Schema:
+
+```Plain
+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
@@ -137,17 +292,18 @@ Source Schema:
+-----------+-------+------+------+------+---------+-------+
```
-You can add a row k4 to both rollup1 and rollup2 by adding the following k5 to rollup2:
-```
+You can use the following command to add a column k4 to rollup1 and rollup2, and add an additional column k5 to rollup2:
+
+```sql
ALTER TABLE tbl1
ADD COLUMN k4 INT default "1" to rollup1,
ADD COLUMN k4 INT default "1" to rollup2,
ADD COLUMN k5 INT default "1" to rollup2;
```
-When completion, the Schema becomes:
+After completion, the schema becomes:
-```
+```Plain
+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
@@ -167,121 +323,165 @@ When completion, the Schema becomes:
+-----------+-------+------+------+------+---------+-------+
```
-As you can see, the base table tbl1 also automatically added k4, k5 columns. That is, columns added to any rollup are automatically added to the Base table.
+As seen, the base table tbl1 automatically includes the columns k4 and k5. Any columns added to a rollup will automatically be added to the base table.
-At the same time, columns that already exist in the Base table are not allowed to be added to Rollup. If you need to do this, you can re-create a Rollup with the new columns and then delete the original Rollup.
+Additionally, it is not allowed to add columns to a rollup that already exist in the base table. If a user needs to do so, they can create a new rollup with the additional columns and then delete the original rollup.
-### Modify Key column
+### Example 2
-Modifying the Key column of a table is done through the `key` keyword. Let's take a look at an example below.
+```sql
+ALTER TABLE example_db.my_table
+ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
+ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;
+```
-**This usage is only for the key column of the duplicate key table**
+## Rename Column
-Source Schema :
+Syntax
-```text
-+-----------+-------+-------------+------+------+---------+-------+
-| IndexName | Field | Type | Null | Key | Default | Extra |
-+-----------+-------+-------------+------+------+---------+-------+
-| tbl1 | k1 | INT | No | true | N/A | |
-| | k2 | INT | No | true | N/A | |
-| | k3 | varchar(20) | No | true | N/A | |
-| | k4 | INT | No | false| N/A | |
-+-----------+-------+-------------+------+------+---------+-------+
+```sql
+ALTER TABLE RENAME COLUMN old_column_name new_column_name;
```
-The modification statement is as follows, we will change the degree of the k3 column to 50
+## Check Job Status
+
+The creation of a schema change is an asynchronous process. After a job is successfully submitted, users need to use the `SHOW ALTER TABLE COLUMN` command to check the progress of the job.
+`SHOW ALTER TABLE COLUMN ` allows you to view the currently executing or completed schema Change jobs. When a schema change job involves multiple indexes, the command will display multiple rows, with each row corresponding to an index. For example:
```sql
-alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'
-````
-
-When done, the Schema becomes:
-
-```text
-+-----------+-------+-------------+------+------+---------+-------+
-| IndexName | Field | Type | Null | Key | Default | Extra |
-+-----------+-------+-------------+------+------+---------+-------+
-| tbl1 | k1 | INT | No | true | N/A | |
-| | k2 | INT | No | true | N/A | |
-| | k3 | varchar(50) | No | true | N/A | |
-| | k4 | INT | No | false| N/A | |
-+-----------+-------+-------------+------+------+---------+-------+
+mysql SHOW ALTER TABLE COLUMN\G;
+*************************** 1. row ***************************
+ JobId: 20021
+ TableName: tbl1
+ CreateTime: 2019-08-05 23:03:13
+ FinishTime: 2019-08-05 23:03:42
+ IndexName: tbl1
+ IndexId: 20022
+OriginIndexId: 20017
+SchemaVersion: 2:792557838
+TransactionId: 10023
+ State: FINISHED
+ Msg:
+ Progress: NULL
+ Timeout: 86400
+1 row in set (0.00 sec)
```
-Because the Schema Change job is an asynchronous operation, only one Schema change job can be performed on the same table at the same time. To check the operation status of the job, you can use the following command
+- JobId: unique ID for each schema change job.
+
+- TableName: name of the base table associated with schema change.
+
+- CreateTime: time when the job was created.
+
+- FinishTime: time when the job is finished. If not finished, it displays "N/A".
+
+- IndexName: name of one of the indexes involved in the modification.
+
+- IndexId: unique ID of the new index.
+
+- OriginIndexId: unique ID of the old index.
+
+- SchemaVersion: displayed in the format M:N, where M represents the version of the schema change modification, and N represents the corresponding hash value. The version increments with each schema change.
+
+- TransactionId: transaction ID that serves as the boundary for transforming historical data.
+
+- State: current state of the job.
+
+ - PENDING: the job is waiting in the queue to be scheduled.
+
+ - WAITING_TXN: waiting for import tasks before the boundary transaction ID to complete.
+
+ - RUNNING: historical data transformation is in progress.
+
+ - FINISHED: the job completed successfully.
+
+ - CANCELLED: the job was cancelled.
+
+- Msg: if the job fails, this field displays the failure message.
+
+- Progress: job progress. It is only displayed when the state is RUNNING. Progress is shown in the format M/N, where N is the total number of replicas involved in the schema change, and M is the number of replicas that have completed historical data transformation.
+
+- Timeout: job timeout in seconds.
+
+## Cancel Job
+
+In cases where the job state is not FINISHED or CANCELLED, you can cancel the schema change job using the following command:
```sql
-SHOW ALTER TABLE COLUMN\G;
-````
+CANCEL ALTER TABLE COLUMN FROM tbl_name;
+```
-## Notice
+## Notes
-* Only one Schema Change job can be running on a table at a time.
+- Only one schema change job can run on a table at a time.
-* Schema Change operation does not block import and query operations.
+- Schema change operations do not block import and query operations.
-* The partition column and bucket column cannot be modified.
+- Partition and bucket columns cannot be modified.
-* If there is a value column aggregated by REPLACE in the schema, the Key column is not allowed to be deleted.
+- If the schema includes value columns aggregated using the REPLACE method, it is not allowed to delete key columns.
- If the Key column is deleted, Doris cannot determine the value of the REPLACE column.
-
- All non-Key columns of the Unique data model table are REPLACE aggregated.
-
-* When adding a value column whose aggregation type is SUM or REPLACE, the default value of this column has no meaning to historical data.
+- If a key column is deleted, Doris cannot determine the values for the REPLACE columns.
- Because the historical data has lost the detailed information, the default value cannot actually reflect the aggregated value.
-
-* When modifying the column type, fields other than Type need to be completed according to the information on the original column.
+- All non-key columns in a unique data model table are aggregated using the REPLACE method.
- If you modify the column `k1 INT SUM NULL DEFAULT" 1 "` as type BIGINT, you need to execute the following command:
-
- ```ALTER TABLE tbl1 MODIFY COLUMN `k1` BIGINT SUM NULL DEFAULT "1"; ```
+- When adding a value column with an aggregation type of SUM or REPLACE, the default value for that column has no meaning for historical data.
- Note that in addition to the new column types, such as the aggregation mode, Nullable attributes, and default values must be completed according to the original information.
-
-* Modifying column names, aggregation types, nullable attributes, default values, and column comments is not supported.
+- Since the historical data has lost detailed information, the default value does not reflect the actual aggregated value.
-## FAQ
+- When modifying the column type, all fields except Type need to be completed based on the information from the original column.
-* the execution speed of Schema Change
+- For example, to modify the column `k1 from INT SUM NULL DEFAULT "1"` to BIGINT, the command would be: `ALTER TABLE tbl1 MODIFY COLUMN k1 BIGINT SUM NULL DEFAULT "1"`;
- At present, the execution speed of Schema Change is estimated to be about 10MB / s according to the worst efficiency. To be conservative, users can set the timeout for jobs based on this rate.
+- Note that apart from the new column type, other attributes such as the aggregation method, nullable property, and default value should be completed based on the original information.
-* Submit job error `Table xxx is not stable. ...`
+- It is not supported to modify column names, aggregation types, nullable properties, default values, or column comments.
- Schema Change can only be started when the table data is complete and unbalanced. If some data shard copies of the table are incomplete, or if some copies are undergoing an equalization operation, the submission is rejected.
-
- Whether the data shard copy is complete can be checked with the following command:
- ```SHOW REPLICA STATUS FROM tbl WHERE STATUS != "OK";```
-
- If a result is returned, there is a problem with the copy. These problems are usually fixed automatically by the system. You can also use the following commands to repair this table first:
- ```ADMIN REPAIR TABLE tbl1;```
-
- You can check if there are running balancing tasks with the following command:
-
- ```SHOW PROC "/cluster_balance/pending_tablets";```
-
- You can wait for the balancing task to complete, or temporarily disable the balancing operation with the following command:
-
- ```ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");```
-
-## Configurations
+## FAQs
+
+**Schema change execution speed**
+
+For a Light Schema Change, such as adding or deleting Value columns, the execution speed can be in the millisecond range. For other types of Schema Change, the execution speed is estimated to be around 10MB/s in the worst-case scenario. As a conservative measure, users can set the job timeout based on this speed.
+
+**Error:** **`"Table xxx is not stable"`** **when submitting a job**
+
+Schema Change can only be initiated when the table data is complete and in a balanced state. If some data shard replicas of the table are incomplete or if some replicas are undergoing balancing operations, the submission will be rejected. You can check if the data shard replicas are complete using the following command:
+
+```sql
+SHOW REPLICA STATUS FROM tbl WHERE STATUS != "OK";
+```
+
+If there are any results returned, it indicates that there are issues with the replicas. Typically, the system will automatically repair these issues, but users can prioritize the repair for a specific table using the following command:
-### FE Configurations
+```sql
+ADMIN REPAIR TABLE tbl1;
+```
+
+You can check if there are any running balancing tasks using the following command:
+
+```sql
+SHOW PROC "/cluster_balance/pending_tablets";
+```
-* `alter_table_timeout_second`: The default timeout for the job is 86400 seconds.
+You can wait for the balancing tasks to complete or temporarily disable balancing operations using the following command:
+
+```sql
+ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");
+```
+
+## Configurations
-### BE Configurations
+### FE Configuration
-* `alter_tablet_worker_count`: Number of threads used to perform historical data conversion on the BE side. The default is 3. If you want to speed up the Schema Change job, you can increase this parameter appropriately and restart the BE. But too many conversion threads can cause increased IO pressure and affect other operations. This thread is shared with the Rollup job.
+- `alter_table_timeout_second`: Default timeout for jobs, set to 86400 seconds.
+### BE Configuration
-* `alter_index_worker_count`: Number of threads used to perform historical data build index on the BE size (note: only inverted index is supported now). The default is 3. If you want to speed up the Index Change job, you can increase this parameter appropriately and restart the BE. But too many threads can cause increased IO pressure and affect other operations.
+- `alter_tablet_worker_count`: Number of threads used on the BE side for executing historical data transformation. The default is 3. If you want to speed up Schema Change jobs, you can increase this parameter and restart the BE. However, having too many transformation threads may increase IO pressure and affect other operations. This thread is shared with Rollup jobs.
-## More Help
+- `alter_index_worker_count`: Number of threads used on the BE side for building indexes on historical data (currently only supports inverted indexes). The default is 3. If you want to speed up Index Change jobs, you can increase this parameter and restart the BE. However, having too many threads may increase IO pressure and affect other operations.
-For more detailed syntax and best practices used by Schema Change, see [ALTER TABLE COLUMN](../../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN.md) command manual, you can also enter `HELP ALTER TABLE COLUMN` in the MySql client command line for more help information.
+## More Details
+For more detailed syntax and best practices regarding Schema Change, please refer to the [ALTER TABLE COLUMN](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-COLUMN) command manual. You can also enter `HELP ALTER TABLE COLUMN `in the MySQL client command line for more help information.
\ No newline at end of file
diff --git a/versioned_sidebars/version-2.0-sidebars.json b/versioned_sidebars/version-2.0-sidebars.json
index 0993557703b6..5a4cd65b7699 100644
--- a/versioned_sidebars/version-2.0-sidebars.json
+++ b/versioned_sidebars/version-2.0-sidebars.json
@@ -119,8 +119,8 @@
"items": [
"data-operate/update/update-overview",
"data-operate/update/unique-update",
- "data-operate/update/unique-load-update",
- "data-operate/update/aggregate-load-update",
+ "data-operate/update/update-of-unique-model",
+ "data-operate/update/update-of-aggregate-model",
"data-operate/update/unique-update-transaction"
]
},
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org