This is an automated email from the ASF dual-hosted git repository.
zhangstar333 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 5a1e051e09bf [insert-overwrite] Sync insert overwrite doc with doris
master (#465)
5a1e051e09bf is described below
commit 5a1e051e09bf4a4d926c8057ccbdebbe147fc5fd
Author: zclllyybb <[email protected]>
AuthorDate: Wed Mar 27 10:12:03 2024 +0800
[insert-overwrite] Sync insert overwrite doc with doris master (#465)
---
.../Manipulation/INSERT-OVERWRITE.md | 70 +++++++++++++++++++--
.../Manipulation/INSERT-OVERWRITE.md | 69 +++++++++++++++++++--
.../Manipulation/INSERT-OVERWRITE.md | 9 ++-
.../Manipulation/INSERT-OVERWRITE.md | 71 +++++++++++++++++++--
.../Manipulation/INSERT-OVERWRITE.md | 9 ++-
.../Manipulation/INSERT-OVERWRITE.md | 72 ++++++++++++++++++++--
6 files changed, 278 insertions(+), 22 deletions(-)
diff --git
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index f94e37f5e01c..7293785ba536 100644
---
a/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/docs/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,11 +33,11 @@ INSERT OVERWRITE
### Description
-The function of this statement is to overwrite a table or a partition of a
table
+The function of this statement is to overwrite a table or some partitions of a
table
```sql
INSERT OVERWRITE table table_name
- [ PARTITION (p1, ...) ]
+ [ PARTITION (p1, ... | *) ]
[ WITH LABEL label]
[ (column [, ...]) ]
[ [ hint [, ...] ] ]
@@ -48,7 +48,10 @@ INSERT OVERWRITE table table_name
> table_name: the destination table to overwrite. This table must exist. It
> can be of the form `db_name.table_name`
>
-> partitions: the table partition that needs to be overwritten must be one of
the existing partitions in `table_name` separated by a comma
+> partitions: the table partitions that needs to be overwritten. The following
two formats are supported
+>
+>> 1. partition names. must be one of the existing partitions in `table_name`
separated by a comma
+>> 2. asterisk(*)。Enable
[auto-detect-partition](#overwrite-auto-detect-partition). The write operation
will automatically detect the partitions involved in the data and overwrite
those partitions.
>
> label: specify a label for the Insert task
>
@@ -69,7 +72,7 @@ INSERT OVERWRITE table table_name
Notice:
1. In the current version, the session variable `enable_insert_strict` is set
to `true` by default. If some data that does not conform to the format of the
target table is filtered out during the execution of the `INSERT OVERWRITE`
statement, such as when overwriting a partition and not all partition
conditions are satisfied, overwriting the target table will fail.
-2. If the target table of the INSERT OVERWRITE is an
[AUTO-PARTITION-table](../../../../advanced/partition/auto-partition), then new
partitions can be created if PARTITION is not specified (that is, rewrite the
whole table). If PARTITION for overwrite is specified, then the AUTO PARTITION
table behaves as if it were a normal partitioned table during this process, and
data that does not satisfy the existing partition conditions is filtered
instead of creating a new partition.
+2. If the target table of the INSERT OVERWRITE is an
[AUTO-PARTITION-table](../../../../advanced/partition/auto-partition), then new
partitions can be created if PARTITION is not specified (that is, rewrite the
whole table). If PARTITION for overwrite is specified(Includes automatic
detection and overwriting of partitions through the `partition(*)` syntax),
then the AUTO PARTITION table behaves as if it were a normal partitioned table
during this process, and data that does not satisfy t [...]
3. The `INSERT OVERWRITE` statement first creates a new table, inserts the
data to be overwritten into the new table, and then atomically replaces the old
table with the new table and modifies its name. Therefore, during the process
of overwriting the table, the data in the old table can still be accessed
normally until the overwriting is completed.
### Example
@@ -138,6 +141,13 @@ PROPERTIES (
#### Overwrite Table Partition
+When using INSERT OVERWRITE to rewrite partitions, we actually encapsulate the
following three steps into a single transaction and execute it. If it fails
halfway through, the operations that have been performed will be rolled back:
+1. Assuming that partition `p1` is specified to be rewritten, first create an
empty temporary partition `pTMP` with the same structure as the target
partition to be rewritten.
+2. Write data to `pTMP`.
+3. replace `p1` with the `pTMP` atom
+
+The following is examples:
+
1. Overwrite partitions `P1` and `P2` of the `test` table using the form of
`VALUES`.
```sql
@@ -175,6 +185,56 @@ PROPERTIES (
INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label4` (c1, c2)
SELECT * from test2;
```
+
+#### Overwrite Auto Detect Partition
+
+When the PARTITION clause specified by the INSERT OVERWRITE command is
`PARTITION(*)`, this overwrite will automatically detect the partition where
the data is located. Example:
+
+```sql
+mysql> create table test(
+ -> k0 int null
+ -> )
+ -> partition by range (k0)
+ -> (
+ -> PARTITION p10 values less than (10),
+ -> PARTITION p100 values less than (100),
+ -> PARTITION pMAX values less than (maxvalue)
+ -> )
+ -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ -> properties("replication_num" = "1");
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> insert into test values (1), (2), (15), (100), (200);
+Query OK, 5 rows affected (0.29 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 1 |
+| 2 |
+| 15 |
+| 100 |
+| 200 |
++------+
+5 rows in set (0.23 sec)
+
+mysql> insert overwrite table test partition(*) values (3), (1234);
+Query OK, 2 rows affected (0.24 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 3 |
+| 15 |
+| 1234 |
++------+
+3 rows in set (0.20 sec)
+```
+
+As you can see, all data in partitions `p10` and `pMAX`, where data 3 and 1234
are located, are overwritten, while partition `p100` remains unchanged. This
operation can be interpreted as syntactic sugar for specifying a specific
partition to be overwritten by the PARTITION clause during an INSERT OVERWRITE
operation, which is implemented in the same way as [specify a partition to
overwrite](#overwrite-table-partition). The `PARTITION(*)` syntax eliminates
the need to manually fill in al [...]
+
### Keywords
- INSERT OVERWRITE, OVERWRITE
+ INSERT OVERWRITE, OVERWRITE, AUTO DETECT
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index c506029155b1..587a981c24b1 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,11 +33,11 @@ INSERT OVERWRITE
### Description
-该语句的功能是重写表或表的某个分区
+该语句的功能是重写表或表的某些分区
```sql
INSERT OVERWRITE table table_name
- [ PARTITION (p1, ...) ]
+ [ PARTITION (p1, ... | *) ]
[ WITH LABEL label]
[ (column [, ...]) ]
[ [ hint [, ...] ] ]
@@ -48,7 +48,10 @@ INSERT OVERWRITE table table_name
> table_name: 需要重写的目的表。这个表必须存在。可以是 `db_name.table_name` 形式
>
-> partitions: 需要重写的表分区，必须是 `table_name` 中存在的分区，多个分区名称用逗号分隔
+> partitions: 需要重写的目标分区，支持两种形式：
+>
+>> 1. 分区名。必须是 `table_name` 中存在的分区，多个分区名称用逗号分隔。
+>> 2.
星号(*)。开启[自动检测分区](#overwrite-auto-detect-partition)功能。写入操作将会自动检测数据所涉及的分区，并覆写这些分区。
>
> label: 为 Insert 任务指定一个 label
>
@@ -69,7 +72,7 @@ INSERT OVERWRITE table table_name
注意：
1. 在当前版本中，会话变量 `enable_insert_strict` 默认为 `true`，如果执行 `INSERT OVERWRITE`
语句时，对于有不符合目标表格式的数据被过滤掉的话会重写目标表失败（比如重写分区时，不满足所有分区条件的数据会被过滤）。
-2. 如果INSERT
OVERWRITE的目标表是[AUTO-PARTITION表](../../../../advanced/partition/auto-partition)，若未指定PARTITION（重写整表），那么可以创建新的分区。如果指定了覆写的PARTITION，那么在此过程中，AUTO
PARTITION表表现得如同普通分区表一样，不满足现有分区条件的数据将被过滤，而非创建新的分区。
+2. 如果INSERT
OVERWRITE的目标表是[AUTO-PARTITION表](../../../../advanced/partition/auto-partition)，若未指定PARTITION（重写整表），那么可以创建新的分区。如果指定了覆写的PARTITION（包括通过
`partition(*)` 语法自动检测并覆盖分区），那么在此过程中，AUTO
PARTITION表表现得如同普通分区表一样，不满足现有分区条件的数据将被过滤，而非创建新的分区。
3. INSERT
OVERWRITE语句会首先创建一个新表，将需要重写的数据插入到新表中，最后原子性的用新表替换旧表并修改名称。因此，在重写表的过程中，旧表中的数据在重写完毕之前仍然可以正常访问。
### Example
@@ -139,6 +142,13 @@ PROPERTIES (
#### Overwrite Table Partition
+使用 INSERT OVERWRITE 重写分区时，实际我们是将如下三步操作封装为一个事务并执行，如果中途失败，已进行的操作将会回滚：
+1. 假设指定重写分区 p1，首先创建一个与重写的目标分区结构相同的空临时分区 `pTMP`
+2. 向 `pTMP` 中写入数据
+3. 使用 `pTMP` 原子替换 `p1` 分区
+
+举例如下：
+
1. VALUES的形式重写`test`表分区`P1`和`p2`
```sql
@@ -176,7 +186,56 @@ PROPERTIES (
INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label4` (c1, c2)
SELECT * from test2;
```
+#### Overwrite Auto Detect Partition
+
+当 INSERT OVERWRITE 命令指定的 PARTITION 子句为 `PARTITION(*)` 时，此次覆写将会自动检测分区数据所在的分区。例如：
+
+```sql
+mysql> create table test(
+ -> k0 int null
+ -> )
+ -> partition by range (k0)
+ -> (
+ -> PARTITION p10 values less than (10),
+ -> PARTITION p100 values less than (100),
+ -> PARTITION pMAX values less than (maxvalue)
+ -> )
+ -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ -> properties("replication_num" = "1");
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> insert into test values (1), (2), (15), (100), (200);
+Query OK, 5 rows affected (0.29 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 1 |
+| 2 |
+| 15 |
+| 100 |
+| 200 |
++------+
+5 rows in set (0.23 sec)
+
+mysql> insert overwrite table test partition(*) values (3), (1234);
+Query OK, 2 rows affected (0.24 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 3 |
+| 15 |
+| 1234 |
++------+
+3 rows in set (0.20 sec)
+```
+
+可以看到，数据 3、1234 所在的分区 `p10` 和 `pMAX` 中的全部数据均被覆写，而 `p100` 分区未发生变化。该操作可以理解为
INSERT OVERWRITE 操作时通过 PARTITION
子句指定覆写特定分区的语法糖，它的实现原理与[指定重写特定分区](#overwrite-table-partition)相同。通过
`PARTITION(*)` 的语法，在覆写大量分区数据时我们可以免于手动填写全部分区名的繁琐。
+
### Keywords
- INSERT OVERWRITE, OVERWRITE
+ INSERT OVERWRITE, OVERWRITE, AUTO DETECT
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index f35a65778f02..33764131589e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,7 +33,7 @@ INSERT OVERWRITE
### Description
-该语句的功能是重写表或表的某个分区
+该语句的功能是重写表或表的某些分区
```sql
INSERT OVERWRITE table table_name
@@ -139,6 +139,13 @@ PROPERTIES (
#### Overwrite Table Partition
+使用 INSERT OVERWRITE 重写分区时，实际我们是将如下三步操作封装为一个事务并执行，如果中途失败，已进行的操作将会回滚：
+1. 假设指定重写分区 p1，首先创建一个与重写的目标分区结构相同的空临时分区 `pTMP`
+2. 向 `pTMP` 中写入数据
+3. 使用 `pTMP` 原子替换 `p1` 分区
+
+举例如下：
+
1. VALUES的形式重写`test`表分区`P1`和`p2`
```sql
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index c506029155b1..87a7442e3a87 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,11 +33,11 @@ INSERT OVERWRITE
### Description
-该语句的功能是重写表或表的某个分区
+该语句的功能是重写表或表的某些分区
```sql
INSERT OVERWRITE table table_name
- [ PARTITION (p1, ...) ]
+ [ PARTITION (p1, ... | *) ]
[ WITH LABEL label]
[ (column [, ...]) ]
[ [ hint [, ...] ] ]
@@ -48,7 +48,10 @@ INSERT OVERWRITE table table_name
> table_name: 需要重写的目的表。这个表必须存在。可以是 `db_name.table_name` 形式
>
-> partitions: 需要重写的表分区，必须是 `table_name` 中存在的分区，多个分区名称用逗号分隔
+> partitions: 需要重写的目标分区，支持两种形式：
+>
+>> 1. 分区名。必须是 `table_name` 中存在的分区，多个分区名称用逗号分隔。
+>> 2.
星号(*)。开启[自动检测分区](#overwrite-auto-detect-partition)功能。写入操作将会自动检测数据所涉及的分区，并覆写这些分区。
>
> label: 为 Insert 任务指定一个 label
>
@@ -69,7 +72,7 @@ INSERT OVERWRITE table table_name
注意：
1. 在当前版本中，会话变量 `enable_insert_strict` 默认为 `true`，如果执行 `INSERT OVERWRITE`
语句时，对于有不符合目标表格式的数据被过滤掉的话会重写目标表失败（比如重写分区时，不满足所有分区条件的数据会被过滤）。
-2. 如果INSERT
OVERWRITE的目标表是[AUTO-PARTITION表](../../../../advanced/partition/auto-partition)，若未指定PARTITION（重写整表），那么可以创建新的分区。如果指定了覆写的PARTITION，那么在此过程中，AUTO
PARTITION表表现得如同普通分区表一样，不满足现有分区条件的数据将被过滤，而非创建新的分区。
+2. 如果INSERT
OVERWRITE的目标表是[AUTO-PARTITION表](../../../../advanced/partition/auto-partition)，若未指定PARTITION（重写整表），那么可以创建新的分区。如果指定了覆写的PARTITION（包括通过
`partition(*)` 语法自动检测并覆盖分区），那么在此过程中，AUTO
PARTITION表表现得如同普通分区表一样，不满足现有分区条件的数据将被过滤，而非创建新的分区。
3. INSERT
OVERWRITE语句会首先创建一个新表，将需要重写的数据插入到新表中，最后原子性的用新表替换旧表并修改名称。因此，在重写表的过程中，旧表中的数据在重写完毕之前仍然可以正常访问。
### Example
@@ -139,6 +142,15 @@ PROPERTIES (
#### Overwrite Table Partition
+> 该功能自 2.1.2 版本起可用。
+
+使用 INSERT OVERWRITE 重写分区时，实际我们是将如下三步操作封装为一个事务并执行，如果中途失败，已进行的操作将会回滚：
+1. 假设指定重写分区 p1，首先创建一个与重写的目标分区结构相同的空临时分区 `pTMP`
+2. 向 `pTMP` 中写入数据
+3. 使用 `pTMP` 原子替换 `p1` 分区
+
+举例如下：
+
1. VALUES的形式重写`test`表分区`P1`和`p2`
```sql
@@ -176,7 +188,56 @@ PROPERTIES (
INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label4` (c1, c2)
SELECT * from test2;
```
+#### Overwrite Auto Detect Partition
+
+当 INSERT OVERWRITE 命令指定的 PARTITION 子句为 `PARTITION(*)` 时，此次覆写将会自动检测分区数据所在的分区。例如：
+
+```sql
+mysql> create table test(
+ -> k0 int null
+ -> )
+ -> partition by range (k0)
+ -> (
+ -> PARTITION p10 values less than (10),
+ -> PARTITION p100 values less than (100),
+ -> PARTITION pMAX values less than (maxvalue)
+ -> )
+ -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ -> properties("replication_num" = "1");
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> insert into test values (1), (2), (15), (100), (200);
+Query OK, 5 rows affected (0.29 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 1 |
+| 2 |
+| 15 |
+| 100 |
+| 200 |
++------+
+5 rows in set (0.23 sec)
+
+mysql> insert overwrite table test partition(*) values (3), (1234);
+Query OK, 2 rows affected (0.24 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 3 |
+| 15 |
+| 1234 |
++------+
+3 rows in set (0.20 sec)
+```
+
+可以看到，数据 3、1234 所在的分区 `p10` 和 `pMAX` 中的全部数据均被覆写，而 `p100` 分区未发生变化。该操作可以理解为
INSERT OVERWRITE 操作时通过 PARTITION
子句指定覆写特定分区的语法糖，它的实现原理与[指定重写特定分区](#overwrite-table-partition)相同。通过
`PARTITION(*)` 的语法，在覆写大量分区数据时我们可以免于手动填写全部分区名的繁琐。
+
### Keywords
- INSERT OVERWRITE, OVERWRITE
+ INSERT OVERWRITE, OVERWRITE, AUTO DETECT
diff --git
a/versioned_docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/versioned_docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index 8ba6d71e4172..eb0f6b3f3c96 100644
---
a/versioned_docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/versioned_docs/version-2.0/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,7 +33,7 @@ INSERT OVERWRITE
### Description
-The function of this statement is to overwrite a table or a partition of a
table
+The function of this statement is to overwrite a table or some partitions of a
table
```sql
INSERT OVERWRITE table table_name
@@ -138,6 +138,13 @@ PROPERTIES (
#### Overwrite Table Partition
+When using INSERT OVERWRITE to rewrite partitions, we actually encapsulate the
following three steps into a single transaction and execute it. If it fails
halfway through, the operations that have been performed will be rolled back:
+1. Assuming that partition `p1` is specified to be rewritten, first create an
empty temporary partition `pTMP` with the same structure as the target
partition to be rewritten.
+2. Write data to `pTMP`.
+3. replace `p1` with the `pTMP` atom
+
+The following is examples:
+
1. Overwrite partitions `P1` and `P2` of the `test` table using the form of
`VALUES`.
```sql
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
index f94e37f5e01c..57bddc226ad7 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Manipulation-Statements/Manipulation/INSERT-OVERWRITE.md
@@ -33,11 +33,11 @@ INSERT OVERWRITE
### Description
-The function of this statement is to overwrite a table or a partition of a
table
+The function of this statement is to overwrite a table or some partitions of a
table
```sql
INSERT OVERWRITE table table_name
- [ PARTITION (p1, ...) ]
+ [ PARTITION (p1, ... | *) ]
[ WITH LABEL label]
[ (column [, ...]) ]
[ [ hint [, ...] ] ]
@@ -48,7 +48,10 @@ INSERT OVERWRITE table table_name
> table_name: the destination table to overwrite. This table must exist. It
> can be of the form `db_name.table_name`
>
-> partitions: the table partition that needs to be overwritten must be one of
the existing partitions in `table_name` separated by a comma
+> partitions: the table partitions that needs to be overwritten. The following
two formats are supported
+>
+>> 1. partition names. must be one of the existing partitions in `table_name`
separated by a comma
+>> 2. asterisk(*)。Enable
[auto-detect-partition](#overwrite-auto-detect-partition). The write operation
will automatically detect the partitions involved in the data and overwrite
those partitions.
>
> label: specify a label for the Insert task
>
@@ -69,7 +72,7 @@ INSERT OVERWRITE table table_name
Notice:
1. In the current version, the session variable `enable_insert_strict` is set
to `true` by default. If some data that does not conform to the format of the
target table is filtered out during the execution of the `INSERT OVERWRITE`
statement, such as when overwriting a partition and not all partition
conditions are satisfied, overwriting the target table will fail.
-2. If the target table of the INSERT OVERWRITE is an
[AUTO-PARTITION-table](../../../../advanced/partition/auto-partition), then new
partitions can be created if PARTITION is not specified (that is, rewrite the
whole table). If PARTITION for overwrite is specified, then the AUTO PARTITION
table behaves as if it were a normal partitioned table during this process, and
data that does not satisfy the existing partition conditions is filtered
instead of creating a new partition.
+2. If the target table of the INSERT OVERWRITE is an
[AUTO-PARTITION-table](../../../../advanced/partition/auto-partition), then new
partitions can be created if PARTITION is not specified (that is, rewrite the
whole table). If PARTITION for overwrite is specified(Includes automatic
detection and overwriting of partitions through the `partition(*)` syntax),
then the AUTO PARTITION table behaves as if it were a normal partitioned table
during this process, and data that does not satisfy t [...]
3. The `INSERT OVERWRITE` statement first creates a new table, inserts the
data to be overwritten into the new table, and then atomically replaces the old
table with the new table and modifies its name. Therefore, during the process
of overwriting the table, the data in the old table can still be accessed
normally until the overwriting is completed.
### Example
@@ -138,6 +141,13 @@ PROPERTIES (
#### Overwrite Table Partition
+When using INSERT OVERWRITE to rewrite partitions, we actually encapsulate the
following three steps into a single transaction and execute it. If it fails
halfway through, the operations that have been performed will be rolled back:
+1. Assuming that partition `p1` is specified to be rewritten, first create an
empty temporary partition `pTMP` with the same structure as the target
partition to be rewritten.
+2. Write data to `pTMP`.
+3. replace `p1` with the `pTMP` atom
+
+The following is examples:
+
1. Overwrite partitions `P1` and `P2` of the `test` table using the form of
`VALUES`.
```sql
@@ -175,6 +185,58 @@ PROPERTIES (
INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label4` (c1, c2)
SELECT * from test2;
```
+
+#### Overwrite Auto Detect Partition
+
+> This feature is available since version 2.1.2.
+
+When the PARTITION clause specified by the INSERT OVERWRITE command is
`PARTITION(*)`, this overwrite will automatically detect the partition where
the data is located. Example:
+
+```sql
+mysql> create table test(
+ -> k0 int null
+ -> )
+ -> partition by range (k0)
+ -> (
+ -> PARTITION p10 values less than (10),
+ -> PARTITION p100 values less than (100),
+ -> PARTITION pMAX values less than (maxvalue)
+ -> )
+ -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ -> properties("replication_num" = "1");
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> insert into test values (1), (2), (15), (100), (200);
+Query OK, 5 rows affected (0.29 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 1 |
+| 2 |
+| 15 |
+| 100 |
+| 200 |
++------+
+5 rows in set (0.23 sec)
+
+mysql> insert overwrite table test partition(*) values (3), (1234);
+Query OK, 2 rows affected (0.24 sec)
+
+mysql> select * from test order by k0;
++------+
+| k0 |
++------+
+| 3 |
+| 15 |
+| 1234 |
++------+
+3 rows in set (0.20 sec)
+```
+
+As you can see, all data in partitions `p10` and `pMAX`, where data 3 and 1234
are located, are overwritten, while partition `p100` remains unchanged. This
operation can be interpreted as syntactic sugar for specifying a specific
partition to be overwritten by the PARTITION clause during an INSERT OVERWRITE
operation, which is implemented in the same way as [specify a partition to
overwrite](#overwrite-table-partition). The `PARTITION(*)` syntax eliminates
the need to manually fill in al [...]
+
### Keywords
- INSERT OVERWRITE, OVERWRITE
+ INSERT OVERWRITE, OVERWRITE, AUTO DETECT
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
