This is an automated email from the ASF dual-hosted git repository.
morningman 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 526c9861051 [docs](iceberg) Add Iceberg row-level DML examples. (#3504)
526c9861051 is described below
commit 526c9861051a0dad556d6ddce2eade71d2fa014a
Author: Qi Chen <[email protected]>
AuthorDate: Tue Mar 31 02:31:17 2026 +0800
[docs](iceberg) Add Iceberg row-level DML examples. (#3504)
---
docs/lakehouse/best-practices/doris-iceberg.md | 118 ++++++++++-
docs/lakehouse/catalogs/iceberg-catalog.mdx | 217 +++++++++++++++++++-
.../lakehouse/best-practices/doris-iceberg.md | 119 ++++++++++-
.../current/lakehouse/catalogs/iceberg-catalog.mdx | 218 ++++++++++++++++++++-
.../lakehouse/best-practices/doris-iceberg.md | 119 ++++++++++-
.../lakehouse/catalogs/iceberg-catalog.mdx | 218 ++++++++++++++++++++-
.../lakehouse/best-practices/doris-iceberg.md | 119 ++++++++++-
.../lakehouse/catalogs/iceberg-catalog.mdx | 218 ++++++++++++++++++++-
.../lakehouse/best-practices/doris-iceberg.md | 118 ++++++++++-
.../lakehouse/catalogs/iceberg-catalog.mdx | 217 +++++++++++++++++++-
.../lakehouse/best-practices/doris-iceberg.md | 118 ++++++++++-
.../lakehouse/catalogs/iceberg-catalog.mdx | 217 +++++++++++++++++++-
12 files changed, 1977 insertions(+), 39 deletions(-)
diff --git a/docs/lakehouse/best-practices/doris-iceberg.md
b/docs/lakehouse/best-practices/doris-iceberg.md
index 2306418eb9a..159a544c0d6 100644
--- a/docs/lakehouse/best-practices/doris-iceberg.md
+++ b/docs/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris provides native support for several core
features of Iceberg:
- Supports querying Iceberg table snapshot history through table functions.
- Supports Time Travel functionality.
- Native support for the Iceberg table engine. It allows Apache Doris to
directly create, manage, and write data to Iceberg tables. It supports
comprehensive partition Transform functions, providing capabilities like hidden
partitioning and partition layout evolution.
+- Supports row-level DML on Iceberg V2 tables, including `UPDATE`, `DELETE`,
and `MERGE INTO`.
Users can quickly build an efficient Data Lakehouse solution based on Apache
Doris + Apache Iceberg to flexibly address various real-time data analysis and
processing needs.
@@ -34,7 +35,7 @@ Users can quickly build an efficient Data Lakehouse solution
based on Apache Dor
- Manage and build Iceberg tables directly through Doris, complete data
cleaning, processing, and writing to Iceberg tables in Doris, building a
**unified data processing platform for data lakes**.
- Share Doris data with other upstream and downstream systems for further
processing through the Iceberg table engine, building a **unified open data
storage platform**.
-In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris will also gradually support
more advanced features of Apache Iceberg, including Update/Delete/Merge,
sorting during write-back, incremental data reading, metadata management, etc.,
to jointly build a unified, high-performance, real-time data lake platform.
+In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris already supports core Iceberg
row-level DML, including `INSERT`, `UPDATE`, `DELETE`, and `MERGE INTO`, and
will continue to improve capabilities such as write-side sorting, incremental
data reading, and metadata management to jointly build a unified,
high-performance, real-time data lake platform.
For more information, please refer to [Iceberg
Catalog](../catalogs/iceberg-catalog.mdx)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 Interacting with PyIceberg
+### 07 Iceberg Row-level DML
+
+For row-level DML (`UPDATE`, `DELETE`, and `MERGE INTO`), the target table
must use Iceberg V2. To keep the `iceberg.nyc.taxis` state from the Time Travel
section unchanged, the following example initializes a dedicated table from
`iceberg.nyc.taxis2`.
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+Update rows in the Iceberg table:
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+Delete rows from the Iceberg table:
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+Use `MERGE INTO` to update existing rows and insert new rows in one statement:
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 Interacting with PyIceberg
> Please use Doris 2.1.8/3.0.4 or above.
+To keep the previous examples unchanged, the examples below use `nyc.taxis2`.
+
Load an iceberg table:
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Read table as `Arrow Table`:
@@ -363,7 +471,7 @@ shape: (4, 6)
> Write iceberg table by PyIceberg, please see
> [step](#write-iceberg-table-by-pyiceberg)
-### 08 Appendix
+### 09 Appendix
#### Write iceberg table by PyIceberg
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Write table with `Arrow Table` :
diff --git a/docs/lakehouse/catalogs/iceberg-catalog.mdx
b/docs/lakehouse/catalogs/iceberg-catalog.mdx
index f8c2b78d051..c21e0355b12 100644
--- a/docs/lakehouse/catalogs/iceberg-catalog.mdx
+++ b/docs/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables.
+Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables,
including INSERT, INSERT OVERWRITE, UPDATE, DELETE, and MERGE INTO.
[Quick start with Apache Doris and Apache
Iceberg](../best-practices/doris-iceberg.md).
@@ -1386,6 +1386,37 @@ Supports querying Iceberg views. View queries work the
same way as regular table
- Only `hms` type Iceberg Catalog is supported.
- The view definition SQL must be compatible with Doris SQL dialect, otherwise
parsing errors will occur. (Dialect conversion functionality will be provided
in future versions).
+### Iceberg V3 Hidden Columns (Row Lineage)
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+Doris supports querying Row Lineage hidden columns of Iceberg V3 tables.
Iceberg V3 introduces system hidden columns that contain row lineage data,
which is used to track data change records. This is highly suitable for
incremental synchronization or data auditing:
+
+- `_row_id`: A numerical value that uniquely identifies a row of data
(generated based on the file and position where the data is written).
+- `_last_updated_sequence_number`: The Sequence Number of the last
modification to this row. When an `UPDATE` or `MERGE INTO` operation updates
the data, this numerical value automatically increments along with the version
number.
+
+**How to query hidden columns:**
+
+By default, hidden columns are not visible when using `DESC` or `SELECT *`.
You can explicitly query them by specifying the column names directly:
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+You can also make hidden columns visible in regular displays (like `DESC` and
`SELECT *`) by enabling the session variable `show_hidden_columns`:
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**Notes:**
+
+1. The Iceberg table's format version (`format-version`) must be configured as
`3` for these row-level parameters to be generated. Querying row lineage
columns on V1 and V2 format tables may result in errors.
+2. Hidden columns are maintained by the system. Users are not allowed to
explicitly specify or write to the `_row_id` or `_last_updated_sequence_number`
columns when using writing statements like `INSERT`.
+
## System Tables
> Since 3.1.0
@@ -1693,6 +1724,190 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `DELETE` operation is used to delete rows of data in an Iceberg table that
meet specified conditions. This operation is primarily based on the following
underlying mechanisms: for V2 format tables, the system writes Position Delete
files; for V3 format tables, the system writes Puffin-format Deletion Vectors
files.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `DELETE` operation.
+
+**Syntax:**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Delete a single row
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- Delete using multiple conditions
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- Delete using a subquery
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `UPDATE` operation is used to modify rows of data in an Iceberg table that
meet specified conditions. Internally, the implementation involves first
writing deletion tracking information (using Position Delete files for V2, and
Puffin-format Deletion Vectors for V3) to mark the old data rows as invalid,
and then appending the updated new data files (if the V3 format option is
enabled, the newly inserted row's tracking value
`_last_updated_sequence_number` will automatically advance).
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `UPDATE` operation.
+
+**Syntax:**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Update a single column
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- Update multiple columns
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- Update using expressions
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- Update using a subquery as condition
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `MERGE INTO` operation is used to match source data with a target Iceberg
table and perform insert, update, or delete operations based on the matching
results. This is suitable for scenarios such as data synchronization and
incremental updates.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `MERGE INTO` operation.
+
+**Syntax:**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+Where `<source>` can be a subquery or another table.
+
+**Examples:**
+
+- Full match operation (includes update, delete, and insert):
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ In the above example:
+ * The row with `id = 2` is deleted because `flag = 'D'`.
+ * The row with `id = 1` is updated with new `name` and `age`.
+ * The row with `id = 4` is inserted because it does not exist in the
target table.
+
+- Execute matching updates only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- Execute inserts on not matched only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- Use a subquery as the data source:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- `MERGE INTO` operation for partitioned tables:
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**Notes:**
+
+1. The above three operations (`DELETE`, `UPDATE`, `MERGE INTO`) all require
the Iceberg table's format version (`format-version`) to be 2 or higher.
Attempting these operations on a V1 format table will result in the error:
`must have format version 2 or higher for position deletes`. When executed on a
V3 format table, the system not only uses Puffin-format Deletion Vectors to
replace the original Position Delete data, but also automatically adjusts the
lineage lifecycle attributes (`_l [...]
+2. These operations support both Parquet and ORC file formats.
+3. In concurrent writing scenarios, Iceberg provides an optimistic concurrency
control mechanism. When write conflicts occur, the operation may throw a
transaction conflict exception.
+4. The `UPDATE` operation, both independently and within `MERGE INTO`,
supports using expressions and functions, such as `age = age * 2 + 1` or `name
= concat(name, '-modified')`.
+5. These operations apply to both partitioned and non-partitioned tables.
+
### Related Parameters
* BE (Backend)
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/best-practices/doris-iceberg.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/best-practices/doris-iceberg.md
index cfb284aeed5..030834a1741 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/best-practices/doris-iceberg.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 支持通过表函数查询 Iceberg 表快照历史。
- 支持时间旅行（Time Travel）功能。
- 原生支持 Iceberg 表引擎。可以通过 Apache Doris 直接创建、管理以及将数据写入到 Iceberg 表。支持完善的分区
Transform 函数，从而提供隐藏分区和分区布局演进等能力。
+- 支持 Iceberg V2 表上的行级 DML，包括 `UPDATE`、`DELETE` 和 `MERGE INTO`。
用户可以基于 Apache Doris + Apache Iceberg 快速构建高效的湖仓一体解决方案，以灵活应对实时数据分析与处理的各种需求：
@@ -34,7 +35,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 通过 Doris 直接管理和构建 Iceberg 表，在 Doris 中完成对数据的清洗、加工并写入到 Iceberg
表，构建**统一的湖仓数据处理平台**。
- 通过 Iceberg 表引擎，将 Doris 数据共享给其他上下游系统做进一步处理，构建**统一的开放数据存储平台**。
-未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
也将逐步支持包括 Update/Delete/Merge、写回时排序、增量数据读取、元数据管理等 Apache Iceberg
更多高级特性，共同构建统一、高性能、实时的湖仓平台。
+未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
已经支持 Iceberg 的核心行级 DML，包括 `INSERT`、`UPDATE`、`DELETE` 和 `MERGE
INTO`，并将继续增强写回时排序、增量数据读取、元数据管理等能力，共同构建统一、高性能、实时的湖仓平台。
关于更多说明，请参阅 [Iceberg Catalog](../catalogs/iceberg-catalog)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 与 PyIceberg 交互
+### 07 Iceberg 行级 DML
+
+对于行级 DML（`UPDATE`、`DELETE` 和 `MERGE INTO`），目标表需要使用 Iceberg V2。为了保持 Time Travel
章节中 `iceberg.nyc.taxis` 的当前状态不变，下面的示例使用 `iceberg.nyc.taxis2` 初始化一张独立的表。
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+使用 `UPDATE` 更新 Iceberg 表中的数据：
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+使用 `DELETE` 删除 Iceberg 表中的数据：
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+使用 `MERGE INTO` 在一条语句中同时完成更新和插入：
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 与 PyIceberg 交互
> 请使用 Doris 2.1.8/3.0.4 以上版本。
+为了保持前面示例中的数据不变，下面的 PyIceberg 示例使用 `nyc.taxis2`。
+
加载 Iceberg 表：
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
读取为 Arrow Table：
@@ -363,7 +471,7 @@ shape: (4, 6)
> 通过 pyiceberg 写入 iceberg 数据，请参阅[步骤](#通过-pyiceberg-写入数据)
-### 08 附录
+### 09 附录
#### 通过 PyIceberg 写入数据
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Arrow Table 写入 Iceberg：
@@ -451,4 +559,3 @@ df = pl.DataFrame(
).with_columns(pl.col("ts").str.strptime(pl.Datetime, "%Y-%m-%d %H:%M:%S"))
table.append(df.to_arrow())
```
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/catalogs/iceberg-catalog.mdx
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/catalogs/iceberg-catalog.mdx
index b41d9736349..a30bc74ea0e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/catalogs/iceberg-catalog.mdx
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作。
+Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作，包括
INSERT、INSERT OVERWRITE、UPDATE、DELETE 和 MERGE INTO。
[使用 Docker 快速体验 Apache Doris & Iceberg](../best-practices/doris-iceberg.md)
@@ -1397,6 +1397,37 @@ SELECT * FROM iceberg_tbl FOR VERSION AS OF 'tag1';
- 仅支持 `hms` 类型的 Iceberg Catalog。
- 视图的定义 SQL 需要与 Doris SQL 方言兼容，否则会出现解析错误（后续版本会提供方言转换功能）。
+### Iceberg V3 隐藏列（Row Lineage）
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+Doris 支持查询 Iceberg V3 表的行血缘（Row Lineage）隐藏列。Iceberg V3
引入了包含行血缘数据的系统隐藏列，用于追踪数据的变更记录，非常适合用于增量同步或数据审查：
+
+- `_row_id`：唯一标识数据行的数值（基于数据写入时所在文件和位置生成）。
+- `_last_updated_sequence_number`：最后一次修改此行的 Sequence Number。当执行 `UPDATE` 或
`MERGE INTO` 操作更新数据时，该数值会随着版本号自动递增。
+
+**如何查询隐藏列：**
+
+默认情况下，使用 `DESC` 或 `SELECT *` 时隐藏列不可见。你可以通过直接指定列名来显式查询它们：
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+你也可以通过开启会话变量 `show_hidden_columns` 来使隐藏列在常规展示（如 `DESC` 和 `SELECT *` 中）可见：
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**注意事项：**
+
+1. 必须确保 Iceberg 表的格式版本（`format-version`）配置为 `3` 时，才会生成上述行级别参数。对于 V1 和 V2
格式表查询行血缘列可能报错。
+2. 隐藏列由系统进行维护，不允许用户在使用 `INSERT` 等写入语句时显式指定写入 `_row_id` 或
`_last_updated_sequence_number` 列。
+
## 系统表
> 该功能自 3.1.0 版本支持
@@ -1703,6 +1734,191 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+DELETE 操作用于删除 Iceberg 表中满足指定条件的数据行。主要基于以下底层机制实现：对于 V2 格式表，系统将写入 Position
Delete 文件；对于 V3 格式表，系统将写入 Puffin 格式的 Deletion Vectors 文件。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 DELETE 操作。
+
+**语法：**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 删除单行
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- 使用复合条件删除
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- 使用子查询删除
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+分区表的 DELETE 操作用法一致：
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+UPDATE 操作用于修改 Iceberg 表中满足指定条件的数据行。内部实现为先写入删除标记信息（V2 使用 Position Delete 文件，V3
使用 Puffin 格式的 Deletion Vectors 文件）标记旧数据行失效，然后追加写入更新后的新数据文件（如果开启的是 V3
格式选项，新插入行的提取追踪值 `_last_updated_sequence_number` 将会自动推进）。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 UPDATE 操作。
+
+**语法：**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 更新单列
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- 更新多列
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- 使用表达式更新
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- 使用子查询作为条件
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+分区表的 UPDATE 操作用法一致：
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+MERGE INTO 操作用于将源数据与目标 Iceberg 表进行匹配，并根据匹配结果执行插入、更新或删除操作。适合用于数据同步、增量更新等场景。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 MERGE INTO 操作。
+
+**语法：**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+其中 `<source>` 可以是子查询或另一张表。
+
+**示例：**
+
+- 完整匹配操作（包含更新、删除和插入）：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ 上述示例中：
+
+ * `id = 2` 的行因 `flag = 'D'` 被删除。
+ * `id = 1` 的行被更新为新的 `name` 和 `age`。
+ * `id = 4` 的行因在目标表中不存在而被插入。
+
+- 仅执行匹配更新：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- 仅执行不匹配时的插入：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- 使用子查询作为数据源：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- 分区表的 MERGE INTO 操作：
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**注意事项：**
+
+1. 以上三种操作（DELETE、UPDATE、MERGE INTO）均要求 Iceberg 表的格式版本（`format-version`）为 2
或更高版本。如果对 V1 格式的表执行这些操作，会报错：`must have format version 2 or higher for position
deletes`。当执行于 V3 格式表时，系统不仅会使用 Puffin 格式存储 Deletion Vectors 代替原有的 Position
Delete 数据，如果涉及内容更新，还会自动调整写入记录的血缘生命周期属性（`_last_updated_sequence_number`）。
+2. 这些操作支持 Parquet 和 ORC 两种文件格式。
+3. 并发写入场景下，Iceberg 提供了乐观并发控制机制。当发生写入冲突时，操作可能会抛出事务冲突异常。
+4. UPDATE 和 MERGE INTO 中的 UPDATE 操作支持使用表达式和函数，例如 `age = age * 2 + 1` 或 `name =
concat(name, '-modified')`。
+5. 这些操作同时适用于分区表和非分区表。
+
### 相关参数
* BE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
index cfb284aeed5..030834a1741 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 支持通过表函数查询 Iceberg 表快照历史。
- 支持时间旅行（Time Travel）功能。
- 原生支持 Iceberg 表引擎。可以通过 Apache Doris 直接创建、管理以及将数据写入到 Iceberg 表。支持完善的分区
Transform 函数，从而提供隐藏分区和分区布局演进等能力。
+- 支持 Iceberg V2 表上的行级 DML，包括 `UPDATE`、`DELETE` 和 `MERGE INTO`。
用户可以基于 Apache Doris + Apache Iceberg 快速构建高效的湖仓一体解决方案，以灵活应对实时数据分析与处理的各种需求：
@@ -34,7 +35,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 通过 Doris 直接管理和构建 Iceberg 表，在 Doris 中完成对数据的清洗、加工并写入到 Iceberg
表，构建**统一的湖仓数据处理平台**。
- 通过 Iceberg 表引擎，将 Doris 数据共享给其他上下游系统做进一步处理，构建**统一的开放数据存储平台**。
-未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
也将逐步支持包括 Update/Delete/Merge、写回时排序、增量数据读取、元数据管理等 Apache Iceberg
更多高级特性，共同构建统一、高性能、实时的湖仓平台。
+未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
已经支持 Iceberg 的核心行级 DML，包括 `INSERT`、`UPDATE`、`DELETE` 和 `MERGE
INTO`，并将继续增强写回时排序、增量数据读取、元数据管理等能力，共同构建统一、高性能、实时的湖仓平台。
关于更多说明，请参阅 [Iceberg Catalog](../catalogs/iceberg-catalog)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 与 PyIceberg 交互
+### 07 Iceberg 行级 DML
+
+对于行级 DML（`UPDATE`、`DELETE` 和 `MERGE INTO`），目标表需要使用 Iceberg V2。为了保持 Time Travel
章节中 `iceberg.nyc.taxis` 的当前状态不变，下面的示例使用 `iceberg.nyc.taxis2` 初始化一张独立的表。
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+使用 `UPDATE` 更新 Iceberg 表中的数据：
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+使用 `DELETE` 删除 Iceberg 表中的数据：
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+使用 `MERGE INTO` 在一条语句中同时完成更新和插入：
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 与 PyIceberg 交互
> 请使用 Doris 2.1.8/3.0.4 以上版本。
+为了保持前面示例中的数据不变，下面的 PyIceberg 示例使用 `nyc.taxis2`。
+
加载 Iceberg 表：
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
读取为 Arrow Table：
@@ -363,7 +471,7 @@ shape: (4, 6)
> 通过 pyiceberg 写入 iceberg 数据，请参阅[步骤](#通过-pyiceberg-写入数据)
-### 08 附录
+### 09 附录
#### 通过 PyIceberg 写入数据
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Arrow Table 写入 Iceberg：
@@ -451,4 +559,3 @@ df = pl.DataFrame(
).with_columns(pl.col("ts").str.strptime(pl.Datetime, "%Y-%m-%d %H:%M:%S"))
table.append(df.to_arrow())
```
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
index b41d9736349..a30bc74ea0e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作。
+Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作，包括
INSERT、INSERT OVERWRITE、UPDATE、DELETE 和 MERGE INTO。
[使用 Docker 快速体验 Apache Doris & Iceberg](../best-practices/doris-iceberg.md)
@@ -1397,6 +1397,37 @@ SELECT * FROM iceberg_tbl FOR VERSION AS OF 'tag1';
- 仅支持 `hms` 类型的 Iceberg Catalog。
- 视图的定义 SQL 需要与 Doris SQL 方言兼容，否则会出现解析错误（后续版本会提供方言转换功能）。
+### Iceberg V3 隐藏列（Row Lineage）
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+Doris 支持查询 Iceberg V3 表的行血缘（Row Lineage）隐藏列。Iceberg V3
引入了包含行血缘数据的系统隐藏列，用于追踪数据的变更记录，非常适合用于增量同步或数据审查：
+
+- `_row_id`：唯一标识数据行的数值（基于数据写入时所在文件和位置生成）。
+- `_last_updated_sequence_number`：最后一次修改此行的 Sequence Number。当执行 `UPDATE` 或
`MERGE INTO` 操作更新数据时，该数值会随着版本号自动递增。
+
+**如何查询隐藏列：**
+
+默认情况下，使用 `DESC` 或 `SELECT *` 时隐藏列不可见。你可以通过直接指定列名来显式查询它们：
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+你也可以通过开启会话变量 `show_hidden_columns` 来使隐藏列在常规展示（如 `DESC` 和 `SELECT *` 中）可见：
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**注意事项：**
+
+1. 必须确保 Iceberg 表的格式版本（`format-version`）配置为 `3` 时，才会生成上述行级别参数。对于 V1 和 V2
格式表查询行血缘列可能报错。
+2. 隐藏列由系统进行维护，不允许用户在使用 `INSERT` 等写入语句时显式指定写入 `_row_id` 或
`_last_updated_sequence_number` 列。
+
## 系统表
> 该功能自 3.1.0 版本支持
@@ -1703,6 +1734,191 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+DELETE 操作用于删除 Iceberg 表中满足指定条件的数据行。主要基于以下底层机制实现：对于 V2 格式表，系统将写入 Position
Delete 文件；对于 V3 格式表，系统将写入 Puffin 格式的 Deletion Vectors 文件。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 DELETE 操作。
+
+**语法：**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 删除单行
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- 使用复合条件删除
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- 使用子查询删除
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+分区表的 DELETE 操作用法一致：
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+UPDATE 操作用于修改 Iceberg 表中满足指定条件的数据行。内部实现为先写入删除标记信息（V2 使用 Position Delete 文件，V3
使用 Puffin 格式的 Deletion Vectors 文件）标记旧数据行失效，然后追加写入更新后的新数据文件（如果开启的是 V3
格式选项，新插入行的提取追踪值 `_last_updated_sequence_number` 将会自动推进）。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 UPDATE 操作。
+
+**语法：**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 更新单列
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- 更新多列
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- 使用表达式更新
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- 使用子查询作为条件
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+分区表的 UPDATE 操作用法一致：
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+MERGE INTO 操作用于将源数据与目标 Iceberg 表进行匹配，并根据匹配结果执行插入、更新或删除操作。适合用于数据同步、增量更新等场景。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 MERGE INTO 操作。
+
+**语法：**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+其中 `<source>` 可以是子查询或另一张表。
+
+**示例：**
+
+- 完整匹配操作（包含更新、删除和插入）：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ 上述示例中：
+
+ * `id = 2` 的行因 `flag = 'D'` 被删除。
+ * `id = 1` 的行被更新为新的 `name` 和 `age`。
+ * `id = 4` 的行因在目标表中不存在而被插入。
+
+- 仅执行匹配更新：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- 仅执行不匹配时的插入：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- 使用子查询作为数据源：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- 分区表的 MERGE INTO 操作：
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**注意事项：**
+
+1. 以上三种操作（DELETE、UPDATE、MERGE INTO）均要求 Iceberg 表的格式版本（`format-version`）为 2
或更高版本。如果对 V1 格式的表执行这些操作，会报错：`must have format version 2 or higher for position
deletes`。当执行于 V3 格式表时，系统不仅会使用 Puffin 格式存储 Deletion Vectors 代替原有的 Position
Delete 数据，如果涉及内容更新，还会自动调整写入记录的血缘生命周期属性（`_last_updated_sequence_number`）。
+2. 这些操作支持 Parquet 和 ORC 两种文件格式。
+3. 并发写入场景下，Iceberg 提供了乐观并发控制机制。当发生写入冲突时，操作可能会抛出事务冲突异常。
+4. UPDATE 和 MERGE INTO 中的 UPDATE 操作支持使用表达式和函数，例如 `age = age * 2 + 1` 或 `name =
concat(name, '-modified')`。
+5. 这些操作同时适用于分区表和非分区表。
+
### 相关参数
* BE
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
index cfb284aeed5..030834a1741 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 支持通过表函数查询 Iceberg 表快照历史。
- 支持时间旅行（Time Travel）功能。
- 原生支持 Iceberg 表引擎。可以通过 Apache Doris 直接创建、管理以及将数据写入到 Iceberg 表。支持完善的分区
Transform 函数，从而提供隐藏分区和分区布局演进等能力。
+- 支持 Iceberg V2 表上的行级 DML，包括 `UPDATE`、`DELETE` 和 `MERGE INTO`。
用户可以基于 Apache Doris + Apache Iceberg 快速构建高效的湖仓一体解决方案，以灵活应对实时数据分析与处理的各种需求：
@@ -34,7 +35,7 @@ Apache Doris 对 Iceberg 多项核心特性提供了原生支持：
- 通过 Doris 直接管理和构建 Iceberg 表，在 Doris 中完成对数据的清洗、加工并写入到 Iceberg
表，构建**统一的湖仓数据处理平台**。
- 通过 Iceberg 表引擎，将 Doris 数据共享给其他上下游系统做进一步处理，构建**统一的开放数据存储平台**。
-未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
也将逐步支持包括 Update/Delete/Merge、写回时排序、增量数据读取、元数据管理等 Apache Iceberg
更多高级特性，共同构建统一、高性能、实时的湖仓平台。
+未来，Apache Iceberg 将作为 Apache Doris 的原生表引擎之一，提供更加完善的湖格式数据的分析、管理功能。Apache Doris
已经支持 Iceberg 的核心行级 DML，包括 `INSERT`、`UPDATE`、`DELETE` 和 `MERGE
INTO`，并将继续增强写回时排序、增量数据读取、元数据管理等能力，共同构建统一、高性能、实时的湖仓平台。
关于更多说明，请参阅 [Iceberg Catalog](../catalogs/iceberg-catalog)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 与 PyIceberg 交互
+### 07 Iceberg 行级 DML
+
+对于行级 DML（`UPDATE`、`DELETE` 和 `MERGE INTO`），目标表需要使用 Iceberg V2。为了保持 Time Travel
章节中 `iceberg.nyc.taxis` 的当前状态不变，下面的示例使用 `iceberg.nyc.taxis2` 初始化一张独立的表。
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+使用 `UPDATE` 更新 Iceberg 表中的数据：
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+使用 `DELETE` 删除 Iceberg 表中的数据：
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+使用 `MERGE INTO` 在一条语句中同时完成更新和插入：
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 与 PyIceberg 交互
> 请使用 Doris 2.1.8/3.0.4 以上版本。
+为了保持前面示例中的数据不变，下面的 PyIceberg 示例使用 `nyc.taxis2`。
+
加载 Iceberg 表：
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
读取为 Arrow Table：
@@ -363,7 +471,7 @@ shape: (4, 6)
> 通过 pyiceberg 写入 iceberg 数据，请参阅[步骤](#通过-pyiceberg-写入数据)
-### 08 附录
+### 09 附录
#### 通过 PyIceberg 写入数据
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Arrow Table 写入 Iceberg：
@@ -451,4 +559,3 @@ df = pl.DataFrame(
).with_columns(pl.col("ts").str.strptime(pl.Datetime, "%Y-%m-%d %H:%M:%S"))
table.append(df.to_arrow())
```
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
index b41d9736349..a30bc74ea0e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作。
+Doris 支持通过多种元数据服务访问 Iceberg 表数据。除支持数据读取外，Doris 也支持对 Iceberg 表进行写入操作，包括
INSERT、INSERT OVERWRITE、UPDATE、DELETE 和 MERGE INTO。
[使用 Docker 快速体验 Apache Doris & Iceberg](../best-practices/doris-iceberg.md)
@@ -1397,6 +1397,37 @@ SELECT * FROM iceberg_tbl FOR VERSION AS OF 'tag1';
- 仅支持 `hms` 类型的 Iceberg Catalog。
- 视图的定义 SQL 需要与 Doris SQL 方言兼容，否则会出现解析错误（后续版本会提供方言转换功能）。
+### Iceberg V3 隐藏列（Row Lineage）
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+Doris 支持查询 Iceberg V3 表的行血缘（Row Lineage）隐藏列。Iceberg V3
引入了包含行血缘数据的系统隐藏列，用于追踪数据的变更记录，非常适合用于增量同步或数据审查：
+
+- `_row_id`：唯一标识数据行的数值（基于数据写入时所在文件和位置生成）。
+- `_last_updated_sequence_number`：最后一次修改此行的 Sequence Number。当执行 `UPDATE` 或
`MERGE INTO` 操作更新数据时，该数值会随着版本号自动递增。
+
+**如何查询隐藏列：**
+
+默认情况下，使用 `DESC` 或 `SELECT *` 时隐藏列不可见。你可以通过直接指定列名来显式查询它们：
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+你也可以通过开启会话变量 `show_hidden_columns` 来使隐藏列在常规展示（如 `DESC` 和 `SELECT *` 中）可见：
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**注意事项：**
+
+1. 必须确保 Iceberg 表的格式版本（`format-version`）配置为 `3` 时，才会生成上述行级别参数。对于 V1 和 V2
格式表查询行血缘列可能报错。
+2. 隐藏列由系统进行维护，不允许用户在使用 `INSERT` 等写入语句时显式指定写入 `_row_id` 或
`_last_updated_sequence_number` 列。
+
## 系统表
> 该功能自 3.1.0 版本支持
@@ -1703,6 +1734,191 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+DELETE 操作用于删除 Iceberg 表中满足指定条件的数据行。主要基于以下底层机制实现：对于 V2 格式表，系统将写入 Position
Delete 文件；对于 V3 格式表，系统将写入 Puffin 格式的 Deletion Vectors 文件。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 DELETE 操作。
+
+**语法：**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 删除单行
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- 使用复合条件删除
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- 使用子查询删除
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+分区表的 DELETE 操作用法一致：
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+UPDATE 操作用于修改 Iceberg 表中满足指定条件的数据行。内部实现为先写入删除标记信息（V2 使用 Position Delete 文件，V3
使用 Puffin 格式的 Deletion Vectors 文件）标记旧数据行失效，然后追加写入更新后的新数据文件（如果开启的是 V3
格式选项，新插入行的提取追踪值 `_last_updated_sequence_number` 将会自动推进）。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 UPDATE 操作。
+
+**语法：**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**示例：**
+
+```sql
+-- 更新单列
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- 更新多列
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- 使用表达式更新
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- 使用子查询作为条件
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+分区表的 UPDATE 操作用法一致：
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip 提示
+该功能为实验功能，自 4.1.0 版本支持。
+:::
+
+MERGE INTO 操作用于将源数据与目标 Iceberg 表进行匹配，并根据匹配结果执行插入、更新或删除操作。适合用于数据同步、增量更新等场景。
+
+**前提条件：**
+
+- Iceberg 表的格式版本（format-version）必须为 2 或更高版本。V1 格式的表不支持 MERGE INTO 操作。
+
+**语法：**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+其中 `<source>` 可以是子查询或另一张表。
+
+**示例：**
+
+- 完整匹配操作（包含更新、删除和插入）：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ 上述示例中：
+
+ * `id = 2` 的行因 `flag = 'D'` 被删除。
+ * `id = 1` 的行被更新为新的 `name` 和 `age`。
+ * `id = 4` 的行因在目标表中不存在而被插入。
+
+- 仅执行匹配更新：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- 仅执行不匹配时的插入：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- 使用子查询作为数据源：
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- 分区表的 MERGE INTO 操作：
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**注意事项：**
+
+1. 以上三种操作（DELETE、UPDATE、MERGE INTO）均要求 Iceberg 表的格式版本（`format-version`）为 2
或更高版本。如果对 V1 格式的表执行这些操作，会报错：`must have format version 2 or higher for position
deletes`。当执行于 V3 格式表时，系统不仅会使用 Puffin 格式存储 Deletion Vectors 代替原有的 Position
Delete 数据，如果涉及内容更新，还会自动调整写入记录的血缘生命周期属性（`_last_updated_sequence_number`）。
+2. 这些操作支持 Parquet 和 ORC 两种文件格式。
+3. 并发写入场景下，Iceberg 提供了乐观并发控制机制。当发生写入冲突时，操作可能会抛出事务冲突异常。
+4. UPDATE 和 MERGE INTO 中的 UPDATE 操作支持使用表达式和函数，例如 `age = age * 2 + 1` 或 `name =
concat(name, '-modified')`。
+5. 这些操作同时适用于分区表和非分区表。
+
### 相关参数
* BE
diff --git
a/versioned_docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
b/versioned_docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
index 2306418eb9a..159a544c0d6 100644
--- a/versioned_docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
+++ b/versioned_docs/version-3.x/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris provides native support for several core
features of Iceberg:
- Supports querying Iceberg table snapshot history through table functions.
- Supports Time Travel functionality.
- Native support for the Iceberg table engine. It allows Apache Doris to
directly create, manage, and write data to Iceberg tables. It supports
comprehensive partition Transform functions, providing capabilities like hidden
partitioning and partition layout evolution.
+- Supports row-level DML on Iceberg V2 tables, including `UPDATE`, `DELETE`,
and `MERGE INTO`.
Users can quickly build an efficient Data Lakehouse solution based on Apache
Doris + Apache Iceberg to flexibly address various real-time data analysis and
processing needs.
@@ -34,7 +35,7 @@ Users can quickly build an efficient Data Lakehouse solution
based on Apache Dor
- Manage and build Iceberg tables directly through Doris, complete data
cleaning, processing, and writing to Iceberg tables in Doris, building a
**unified data processing platform for data lakes**.
- Share Doris data with other upstream and downstream systems for further
processing through the Iceberg table engine, building a **unified open data
storage platform**.
-In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris will also gradually support
more advanced features of Apache Iceberg, including Update/Delete/Merge,
sorting during write-back, incremental data reading, metadata management, etc.,
to jointly build a unified, high-performance, real-time data lake platform.
+In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris already supports core Iceberg
row-level DML, including `INSERT`, `UPDATE`, `DELETE`, and `MERGE INTO`, and
will continue to improve capabilities such as write-side sorting, incremental
data reading, and metadata management to jointly build a unified,
high-performance, real-time data lake platform.
For more information, please refer to [Iceberg
Catalog](../catalogs/iceberg-catalog.mdx)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 Interacting with PyIceberg
+### 07 Iceberg Row-level DML
+
+For row-level DML (`UPDATE`, `DELETE`, and `MERGE INTO`), the target table
must use Iceberg V2. To keep the `iceberg.nyc.taxis` state from the Time Travel
section unchanged, the following example initializes a dedicated table from
`iceberg.nyc.taxis2`.
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+Update rows in the Iceberg table:
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+Delete rows from the Iceberg table:
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+Use `MERGE INTO` to update existing rows and insert new rows in one statement:
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 Interacting with PyIceberg
> Please use Doris 2.1.8/3.0.4 or above.
+To keep the previous examples unchanged, the examples below use `nyc.taxis2`.
+
Load an iceberg table:
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Read table as `Arrow Table`:
@@ -363,7 +471,7 @@ shape: (4, 6)
> Write iceberg table by PyIceberg, please see
> [step](#write-iceberg-table-by-pyiceberg)
-### 08 Appendix
+### 09 Appendix
#### Write iceberg table by PyIceberg
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Write table with `Arrow Table` :
diff --git a/versioned_docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
b/versioned_docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
index f8c2b78d051..c21e0355b12 100644
--- a/versioned_docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
+++ b/versioned_docs/version-3.x/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables.
+Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables,
including INSERT, INSERT OVERWRITE, UPDATE, DELETE, and MERGE INTO.
[Quick start with Apache Doris and Apache
Iceberg](../best-practices/doris-iceberg.md).
@@ -1386,6 +1386,37 @@ Supports querying Iceberg views. View queries work the
same way as regular table
- Only `hms` type Iceberg Catalog is supported.
- The view definition SQL must be compatible with Doris SQL dialect, otherwise
parsing errors will occur. (Dialect conversion functionality will be provided
in future versions).
+### Iceberg V3 Hidden Columns (Row Lineage)
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+Doris supports querying Row Lineage hidden columns of Iceberg V3 tables.
Iceberg V3 introduces system hidden columns that contain row lineage data,
which is used to track data change records. This is highly suitable for
incremental synchronization or data auditing:
+
+- `_row_id`: A numerical value that uniquely identifies a row of data
(generated based on the file and position where the data is written).
+- `_last_updated_sequence_number`: The Sequence Number of the last
modification to this row. When an `UPDATE` or `MERGE INTO` operation updates
the data, this numerical value automatically increments along with the version
number.
+
+**How to query hidden columns:**
+
+By default, hidden columns are not visible when using `DESC` or `SELECT *`.
You can explicitly query them by specifying the column names directly:
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+You can also make hidden columns visible in regular displays (like `DESC` and
`SELECT *`) by enabling the session variable `show_hidden_columns`:
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**Notes:**
+
+1. The Iceberg table's format version (`format-version`) must be configured as
`3` for these row-level parameters to be generated. Querying row lineage
columns on V1 and V2 format tables may result in errors.
+2. Hidden columns are maintained by the system. Users are not allowed to
explicitly specify or write to the `_row_id` or `_last_updated_sequence_number`
columns when using writing statements like `INSERT`.
+
## System Tables
> Since 3.1.0
@@ -1693,6 +1724,190 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `DELETE` operation is used to delete rows of data in an Iceberg table that
meet specified conditions. This operation is primarily based on the following
underlying mechanisms: for V2 format tables, the system writes Position Delete
files; for V3 format tables, the system writes Puffin-format Deletion Vectors
files.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `DELETE` operation.
+
+**Syntax:**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Delete a single row
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- Delete using multiple conditions
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- Delete using a subquery
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `UPDATE` operation is used to modify rows of data in an Iceberg table that
meet specified conditions. Internally, the implementation involves first
writing deletion tracking information (using Position Delete files for V2, and
Puffin-format Deletion Vectors for V3) to mark the old data rows as invalid,
and then appending the updated new data files (if the V3 format option is
enabled, the newly inserted row's tracking value
`_last_updated_sequence_number` will automatically advance).
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `UPDATE` operation.
+
+**Syntax:**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Update a single column
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- Update multiple columns
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- Update using expressions
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- Update using a subquery as condition
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `MERGE INTO` operation is used to match source data with a target Iceberg
table and perform insert, update, or delete operations based on the matching
results. This is suitable for scenarios such as data synchronization and
incremental updates.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `MERGE INTO` operation.
+
+**Syntax:**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+Where `<source>` can be a subquery or another table.
+
+**Examples:**
+
+- Full match operation (includes update, delete, and insert):
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ In the above example:
+ * The row with `id = 2` is deleted because `flag = 'D'`.
+ * The row with `id = 1` is updated with new `name` and `age`.
+ * The row with `id = 4` is inserted because it does not exist in the
target table.
+
+- Execute matching updates only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- Execute inserts on not matched only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- Use a subquery as the data source:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- `MERGE INTO` operation for partitioned tables:
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**Notes:**
+
+1. The above three operations (`DELETE`, `UPDATE`, `MERGE INTO`) all require
the Iceberg table's format version (`format-version`) to be 2 or higher.
Attempting these operations on a V1 format table will result in the error:
`must have format version 2 or higher for position deletes`. When executed on a
V3 format table, the system not only uses Puffin-format Deletion Vectors to
replace the original Position Delete data, but also automatically adjusts the
lineage lifecycle attributes (`_l [...]
+2. These operations support both Parquet and ORC file formats.
+3. In concurrent writing scenarios, Iceberg provides an optimistic concurrency
control mechanism. When write conflicts occur, the operation may throw a
transaction conflict exception.
+4. The `UPDATE` operation, both independently and within `MERGE INTO`,
supports using expressions and functions, such as `age = age * 2 + 1` or `name
= concat(name, '-modified')`.
+5. These operations apply to both partitioned and non-partitioned tables.
+
### Related Parameters
* BE (Backend)
diff --git
a/versioned_docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
b/versioned_docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
index 2306418eb9a..159a544c0d6 100644
--- a/versioned_docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
+++ b/versioned_docs/version-4.x/lakehouse/best-practices/doris-iceberg.md
@@ -27,6 +27,7 @@ Apache Doris provides native support for several core
features of Iceberg:
- Supports querying Iceberg table snapshot history through table functions.
- Supports Time Travel functionality.
- Native support for the Iceberg table engine. It allows Apache Doris to
directly create, manage, and write data to Iceberg tables. It supports
comprehensive partition Transform functions, providing capabilities like hidden
partitioning and partition layout evolution.
+- Supports row-level DML on Iceberg V2 tables, including `UPDATE`, `DELETE`,
and `MERGE INTO`.
Users can quickly build an efficient Data Lakehouse solution based on Apache
Doris + Apache Iceberg to flexibly address various real-time data analysis and
processing needs.
@@ -34,7 +35,7 @@ Users can quickly build an efficient Data Lakehouse solution
based on Apache Dor
- Manage and build Iceberg tables directly through Doris, complete data
cleaning, processing, and writing to Iceberg tables in Doris, building a
**unified data processing platform for data lakes**.
- Share Doris data with other upstream and downstream systems for further
processing through the Iceberg table engine, building a **unified open data
storage platform**.
-In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris will also gradually support
more advanced features of Apache Iceberg, including Update/Delete/Merge,
sorting during write-back, incremental data reading, metadata management, etc.,
to jointly build a unified, high-performance, real-time data lake platform.
+In the future, Apache Iceberg will serve as one of the native table engines
for Apache Doris, providing more comprehensive analysis and management
functions for lake-formatted data. Apache Doris already supports core Iceberg
row-level DML, including `INSERT`, `UPDATE`, `DELETE`, and `MERGE INTO`, and
will continue to improve capabilities such as write-side sorting, incremental
data reading, and metadata management to jointly build a unified,
high-performance, real-time data lake platform.
For more information, please refer to [Iceberg
Catalog](../catalogs/iceberg-catalog.mdx)
@@ -286,10 +287,117 @@ mysql> SELECT * FROM iceberg.nyc.taxis FOR TIME AS OF
"2024-07-29 03:40:22";
4 rows in set (0.05 sec)
```
-### 07 Interacting with PyIceberg
+### 07 Iceberg Row-level DML
+
+For row-level DML (`UPDATE`, `DELETE`, and `MERGE INTO`), the target table
must use Iceberg V2. To keep the `iceberg.nyc.taxis` state from the Time Travel
section unchanged, the following example initializes a dedicated table from
`iceberg.nyc.taxis2`.
+
+```sql
+mysql> CREATE TABLE iceberg.nyc.taxis_dml
+ (
+ vendor_id BIGINT,
+ trip_id BIGINT,
+ trip_distance FLOAT,
+ fare_amount DOUBLE,
+ store_and_fwd_flag STRING,
+ ts DATETIME
+ )
+ PARTITION BY LIST (vendor_id, DAY(ts)) ()
+ PROPERTIES (
+ "format-version" = "2",
+ "compression-codec" = "zstd",
+ "write-format" = "parquet"
+ );
+Query OK, 0 rows affected (0.18 sec)
+
+mysql> INSERT INTO iceberg.nyc.taxis_dml SELECT * FROM iceberg.nyc.taxis2;
+Query OK, 4 rows affected (0.32 sec)
+{'status':'COMMITTED', 'txnId':'10089'}
+```
+
+Update rows in the Iceberg table:
+
+```sql
+mysql> UPDATE iceberg.nyc.taxis_dml
+ SET fare_amount = fare_amount + 3,
+ store_and_fwd_flag = 'Y'
+ WHERE trip_id = 1000371;
+Query OK, 1 row affected (0.44 sec)
+{'status':'COMMITTED', 'txnId':'10090'}
+
+mysql> SELECT trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000371;
++---------+-------------+--------------------+
+| trip_id | fare_amount | store_and_fwd_flag |
++---------+-------------+--------------------+
+| 1000371 | 18.32 | Y |
++---------+-------------+--------------------+
+1 row in set (0.07 sec)
+```
+
+Delete rows from the Iceberg table:
+
+```sql
+mysql> DELETE FROM iceberg.nyc.taxis_dml
+ WHERE trip_id = 1000373;
+Query OK, 1 row affected (0.31 sec)
+{'status':'COMMITTED', 'txnId':'10091'}
+
+mysql> SELECT trip_id
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++---------+
+| trip_id |
++---------+
+| 1000371 |
+| 1000372 |
+| 1000374 |
++---------+
+3 rows in set (0.06 sec)
+```
+
+Use `MERGE INTO` to update existing rows and insert new rows in one statement:
+
+```sql
+mysql> MERGE INTO iceberg.nyc.taxis_dml t
+ USING (
+ SELECT 2 AS vendor_id, 1000372 AS trip_id, 2.5 AS trip_distance,
+ 30.00 AS fare_amount, 'Y' AS store_and_fwd_flag,
+ CAST('2024-01-02 12:10:11' AS DATETIME) AS ts
+ UNION ALL
+ SELECT 3, 1000380, 6.7, 28.50, 'N', CAST('2024-01-04 10:20:00' AS
DATETIME)
+ ) s
+ ON t.trip_id = s.trip_id
+ WHEN MATCHED THEN UPDATE SET
+ fare_amount = s.fare_amount,
+ store_and_fwd_flag = s.store_and_fwd_flag
+ WHEN NOT MATCHED THEN INSERT
+ (vendor_id, trip_id, trip_distance, fare_amount,
store_and_fwd_flag, ts)
+ VALUES
+ (s.vendor_id, s.trip_id, s.trip_distance, s.fare_amount,
s.store_and_fwd_flag, s.ts);
+Query OK, 2 rows affected (0.53 sec)
+{'status':'COMMITTED', 'txnId':'10092'}
+
+mysql> SELECT vendor_id, trip_id, fare_amount, store_and_fwd_flag
+ FROM iceberg.nyc.taxis_dml
+ ORDER BY trip_id;
++-----------+---------+-------------+--------------------+
+| vendor_id | trip_id | fare_amount | store_and_fwd_flag |
++-----------+---------+-------------+--------------------+
+| 1 | 1000371 | 18.32 | Y |
+| 2 | 1000372 | 30.00 | Y |
+| 1 | 1000374 | 42.13 | Y |
+| 3 | 1000380 | 28.50 | N |
++-----------+---------+-------------+--------------------+
+4 rows in set (0.09 sec)
+```
+
+### 08 Interacting with PyIceberg
> Please use Doris 2.1.8/3.0.4 or above.
+To keep the previous examples unchanged, the examples below use `nyc.taxis2`.
+
Load an iceberg table:
```python
@@ -305,7 +413,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Read table as `Arrow Table`:
@@ -363,7 +471,7 @@ shape: (4, 6)
> Write iceberg table by PyIceberg, please see
> [step](#write-iceberg-table-by-pyiceberg)
-### 08 Appendix
+### 09 Appendix
#### Write iceberg table by PyIceberg
@@ -382,7 +490,7 @@ catalog = load_catalog(
"s3.endpoint" = "http://minio:9000";
},
)
-table = catalog.load_table("nyc.taxis")
+table = catalog.load_table("nyc.taxis2")
```
Write table with `Arrow Table` :
diff --git a/versioned_docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
b/versioned_docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
index f8c2b78d051..c21e0355b12 100644
--- a/versioned_docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
+++ b/versioned_docs/version-4.x/lakehouse/catalogs/iceberg-catalog.mdx
@@ -9,7 +9,7 @@
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
-Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables.
+Doris supports accessing Iceberg table data through various metadata services.
In addition to reading data, Doris also supports writing to Iceberg tables,
including INSERT, INSERT OVERWRITE, UPDATE, DELETE, and MERGE INTO.
[Quick start with Apache Doris and Apache
Iceberg](../best-practices/doris-iceberg.md).
@@ -1386,6 +1386,37 @@ Supports querying Iceberg views. View queries work the
same way as regular table
- Only `hms` type Iceberg Catalog is supported.
- The view definition SQL must be compatible with Doris SQL dialect, otherwise
parsing errors will occur. (Dialect conversion functionality will be provided
in future versions).
+### Iceberg V3 Hidden Columns (Row Lineage)
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+Doris supports querying Row Lineage hidden columns of Iceberg V3 tables.
Iceberg V3 introduces system hidden columns that contain row lineage data,
which is used to track data change records. This is highly suitable for
incremental synchronization or data auditing:
+
+- `_row_id`: A numerical value that uniquely identifies a row of data
(generated based on the file and position where the data is written).
+- `_last_updated_sequence_number`: The Sequence Number of the last
modification to this row. When an `UPDATE` or `MERGE INTO` operation updates
the data, this numerical value automatically increments along with the version
number.
+
+**How to query hidden columns:**
+
+By default, hidden columns are not visible when using `DESC` or `SELECT *`.
You can explicitly query them by specifying the column names directly:
+
+```sql
+SELECT id, _row_id, _last_updated_sequence_number FROM iceberg_tbl;
+```
+
+You can also make hidden columns visible in regular displays (like `DESC` and
`SELECT *`) by enabling the session variable `show_hidden_columns`:
+
+```sql
+SET show_hidden_columns = true;
+DESC iceberg_tbl;
+```
+
+**Notes:**
+
+1. The Iceberg table's format version (`format-version`) must be configured as
`3` for these row-level parameters to be generated. Querying row lineage
columns on V1 and V2 format tables may result in errors.
+2. Hidden columns are maintained by the system. Users are not allowed to
explicitly specify or write to the `_row_id` or `_last_updated_sequence_number`
columns when using writing statements like `INSERT`.
+
## System Tables
> Since 3.1.0
@@ -1693,6 +1724,190 @@ INSERT INTO iceberg_table@branch(b1) SELECT * FROM
other_table;
INSERT OVERWRITE TABLE iceberg_table@branch(b1) SELECT * FROM other_table;
```
+### DELETE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `DELETE` operation is used to delete rows of data in an Iceberg table that
meet specified conditions. This operation is primarily based on the following
underlying mechanisms: for V2 format tables, the system writes Position Delete
files; for V3 format tables, the system writes Puffin-format Deletion Vectors
files.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `DELETE` operation.
+
+**Syntax:**
+
+```sql
+DELETE FROM iceberg_tbl WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Delete a single row
+DELETE FROM iceberg_tbl WHERE id = 1;
+
+-- Delete using multiple conditions
+DELETE FROM iceberg_tbl WHERE age > 30 AND name = 'Bob';
+
+-- Delete using a subquery
+DELETE FROM iceberg_tbl WHERE id IN (SELECT id FROM other_table WHERE status =
'inactive');
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+DELETE FROM iceberg_partition_tbl WHERE id = 10;
+```
+
+### UPDATE
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `UPDATE` operation is used to modify rows of data in an Iceberg table that
meet specified conditions. Internally, the implementation involves first
writing deletion tracking information (using Position Delete files for V2, and
Puffin-format Deletion Vectors for V3) to mark the old data rows as invalid,
and then appending the updated new data files (if the V3 format option is
enabled, the newly inserted row's tracking value
`_last_updated_sequence_number` will automatically advance).
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `UPDATE` operation.
+
+**Syntax:**
+
+```sql
+UPDATE iceberg_tbl SET <column1> = <value1> [, <column2> = <value2>, ...]
WHERE <condition>;
+```
+
+**Examples:**
+
+```sql
+-- Update a single column
+UPDATE iceberg_tbl SET name = 'Alice_new' WHERE id = 1;
+
+-- Update multiple columns
+UPDATE iceberg_tbl SET name = 'Updated', age = 28 WHERE id = 2;
+
+-- Update using expressions
+UPDATE iceberg_tbl SET age = age * 2 + 1, name = concat(name, '-modified')
WHERE id = 3;
+
+-- Update using a subquery as condition
+UPDATE iceberg_tbl SET name = 'UpdatedViaSubquery' WHERE id IN (SELECT id FROM
other_table);
+```
+
+The usage is identical for partitioned tables:
+
+```sql
+UPDATE iceberg_partition_tbl SET name = 'Updated' WHERE id = 10;
+```
+
+### MERGE INTO
+
+:::tip
+This is an experimental feature, supported since version 4.1.0.
+:::
+
+The `MERGE INTO` operation is used to match source data with a target Iceberg
table and perform insert, update, or delete operations based on the matching
results. This is suitable for scenarios such as data synchronization and
incremental updates.
+
+**Prerequisites:**
+
+- The Iceberg table's format version (`format-version`) must be 2 or higher.
V1 format tables do not support the `MERGE INTO` operation.
+
+**Syntax:**
+
+```sql
+MERGE INTO target_table t
+USING <source> s
+ON <merge_condition>
+[WHEN MATCHED [AND <condition>] THEN DELETE]
+[WHEN MATCHED [AND <condition>] THEN UPDATE SET <column1> = <value1> [, ...]]
+[WHEN NOT MATCHED [AND <condition>] THEN INSERT (<columns>) VALUES (<values>)]
+```
+
+Where `<source>` can be a subquery or another table.
+
+**Examples:**
+
+- Full match operation (includes update, delete, and insert):
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age)
+ VALUES (s.id, s.name, s.age);
+ ```
+
+ In the above example:
+ * The row with `id = 2` is deleted because `flag = 'D'`.
+ * The row with `id = 1` is updated with new `name` and `age`.
+ * The row with `id = 4` is inserted because it does not exist in the
target table.
+
+- Execute matching updates only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 1 AS id, 'Alice_matched' AS name, 26 AS age) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = s.name, age = s.age;
+ ```
+
+- Execute inserts on not matched only:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT 4 AS id, 'Dora' AS name, 40 AS age) s
+ ON t.id = s.id
+ WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
+ ```
+
+- Use a subquery as the data source:
+
+ ```sql
+ MERGE INTO iceberg_tbl t
+ USING (SELECT id FROM other_table WHERE id = 2) s
+ ON t.id = s.id
+ WHEN MATCHED THEN UPDATE SET name = 'UpdatedViaSubquery';
+ ```
+
+- `MERGE INTO` operation for partitioned tables:
+
+ ```sql
+ MERGE INTO iceberg_partition_tbl t
+ USING (
+ SELECT 1 AS id, 'Alice_new' AS name, 26 AS age, DATE '2024-01-01' AS
dt, 'U' AS flag
+ UNION ALL
+ SELECT 2, 'Bob', 30, DATE '2024-01-02', 'D'
+ UNION ALL
+ SELECT 4, 'Dora', 28, DATE '2024-01-04', 'I'
+ ) s
+ ON t.id = s.id
+ WHEN MATCHED AND s.flag = 'D' THEN DELETE
+ WHEN MATCHED THEN UPDATE SET
+ name = s.name,
+ age = s.age
+ WHEN NOT MATCHED THEN INSERT (id, name, age, dt)
+ VALUES (s.id, s.name, s.age, s.dt);
+ ```
+
+**Notes:**
+
+1. The above three operations (`DELETE`, `UPDATE`, `MERGE INTO`) all require
the Iceberg table's format version (`format-version`) to be 2 or higher.
Attempting these operations on a V1 format table will result in the error:
`must have format version 2 or higher for position deletes`. When executed on a
V3 format table, the system not only uses Puffin-format Deletion Vectors to
replace the original Position Delete data, but also automatically adjusts the
lineage lifecycle attributes (`_l [...]
+2. These operations support both Parquet and ORC file formats.
+3. In concurrent writing scenarios, Iceberg provides an optimistic concurrency
control mechanism. When write conflicts occur, the operation may throw a
transaction conflict exception.
+4. The `UPDATE` operation, both independently and within `MERGE INTO`,
supports using expressions and functions, such as `age = age * 2 + 1` or `name
= concat(name, '-modified')`.
+5. These operations apply to both partitioned and non-partitioned tables.
+
### Related Parameters
* BE (Backend)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
