This is an automated email from the ASF dual-hosted git repository.
zclll 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 bb81b5d1997 [DDL] fix wrong create table describe about auto partition
(#2698)
bb81b5d1997 is described below
commit bb81b5d1997ba306a01d096c2b211010471cf67e
Author: zclllyybb <[email protected]>
AuthorDate: Mon Aug 4 18:39:23 2025 +0800
[DDL] fix wrong create table describe about auto partition (#2698)
## Versions
- [x] dev
- [x] 3.0
- [x] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [ ] Checked by AI
- [ ] Test Cases Built
---
.../table-and-view/table/CREATE-TABLE.md | 38 ++--
.../data-partitioning/auto-partitioning.md | 215 +++++++++++----------
.../table-and-view/table/CREATE-TABLE.md | 35 ++--
.../data-partitioning/auto-partitioning.md | 169 ++++++++--------
.../table-and-view/table/CREATE-TABLE.md | 35 ++--
.../data-partitioning/auto-partitioning.md | 173 +++++++++--------
.../table-and-view/table/CREATE-TABLE.md | 35 ++--
.../data-partitioning/auto-partitioning.md | 172 +++++++++--------
.../table-and-view/table/CREATE-TABLE.md | 37 ++--
.../data-partitioning/auto-partitioning.md | 211 ++++++++++----------
.../table-and-view/table/CREATE-TABLE.md | 38 ++--
.../data-partitioning/auto-partitioning.md | 212 ++++++++++----------
12 files changed, 686 insertions(+), 684 deletions(-)
diff --git
a/docs/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/docs/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index 4c7f53fddd4..3014a45197c 100644
--- a/docs/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++ b/docs/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -5,7 +5,6 @@
"toc_min_heading_level": 2,
"toc_max_heading_level": 4
}
-
---
## Description
@@ -66,7 +65,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- Index definition
INDEX [ IF NOT EXISTS ]
@@ -85,22 +84,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- Partition definition
<one_partition_definition>
-- Additional partition definition
[ , ... ]
- )
-```
-
-- Where <one_partition_definition>:
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -115,7 +117,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- Rollup definition
@@ -269,16 +271,10 @@ CREATE TABLE <new_table_name> LIKE <existing_table_name>
> The properties of the index. For detailed explanations, refer to the
> [Inverted Index](../../../../table-design/index/inverted-index.md) section.
-### Automatic Partitioning Related Parameters
+### Auto Partition Related Parameters
For a detailed introduction to partitioning, see the [Automatic
Partitioning](../../../../table-design/data-partitioning/auto-partitioning.md)
section.
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> The method of automatic partitioning. `<auto_partition_function>` currently
only supports `date_trunc`. `<auto_partition_arguments>` specifies the column
for automatic partitioning and the unit of `date_trunc`. For example,
`date_trunc(col_1, 'day')`.
->
-> When using automatic partitioning, the partition column must be NOT NULL.
-
### Manual Partitioning Related Parameters
For a detailed introduction to partitioning, see the "Manual Partitioning"
section.
diff --git a/docs/table-design/data-partitioning/auto-partitioning.md
b/docs/table-design/data-partitioning/auto-partitioning.md
index 6ce1389b94b..f336ea9bafc 100644
--- a/docs/table-design/data-partitioning/auto-partitioning.md
+++ b/docs/table-design/data-partitioning/auto-partitioning.md
@@ -58,120 +58,121 @@ The table stores a large amount of business history data,
partitioned based on t
## Syntax
-When creating a table, use the following syntax to populate the
`partition_info` section in the `CREATE-TABLE` statement:
+When creating a table, use the following syntax to populate the
`partitions_definition` section in the
[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
statement.
-- For RANGE Partition:
+1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-Where
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ where
-- For LIST Partition:
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+2. AUTO LIST PARTITION:
-### Sample
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
-- For Range Partition:
-
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL COMMENT '采集日期'
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
-
-- For List Partition:
-
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+### Sample
-List Auto Partition supports multiple partition columns, which are written in
the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
+1. AUTO RANGE PARTITION
+
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ (
+ )
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+2. AUTO LIST PARTITION
+
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+ List Auto Partition supports multiple partition columns, which are written
in the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
### Constraints
- In auto List Partition, the partition name length **must not exceed 50
characters**. This length is derived from the concatenation and escape of
contents of partition columns on corresponding data rows, so the actual allowed
length may be shorter.
-- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
-- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
-- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
+- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
+- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
+- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
### NULL value partition
When the session variable `allow_partition_column_nullable` is enabled:
- For Auto List Partition, the corresponding NULL value partition will be
created automatically:
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
- For Auto Range Partition, **null columns are not supported to be partition
columns**.
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## Example
@@ -221,12 +222,13 @@ It can be concluded that the partitions created by Auto
Partition share the same
## Conjunct with Dynamic Partition
Doris supports both Auto and Dynamic Partition. In this case, both functions
are in effect:
+
1. Auto Partition will automatically create partitions on demand during data
import;
2. Dynamic Partition will automatically create, recycle and dump partitions.
-There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time.
+There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time. Please note that it is uncertain whether
the partition in current period is created by Auto Partition or Dynamic
Partition. Different creation methods will lead to different naming formats for
the partitions.
-### Best Practice
+## Best Practice
In scenarios where you need to set a limit on the partition lifecycle, you can
**disable the creation of Dynamic Partition, leaving the creation of partitions
to be completed by Auto Partition**, and complete the management of the
partition lifecycle through the Dynamic Partition's function of dynamically
reclaiming partitions:
@@ -250,8 +252,16 @@ properties(
This way we have both the flexibility of Auto Partition and consistency in
partition names.
+:::note
+In some early versions prior to 2.1.7, this feature was not disabled but not
recommended.
+:::
+
## Partition Management
+:::tip
+Since 2.1.6, Doris supports the `partitions` table function and the
`auto_partition_name` function, which can be used to easily find and manage
partitions for data.
+:::
+
When Auto Partition is enabled, partition names can be mapped to partitions
using the `auto_partition_name` function.The `partitions` table function
generates detailed partition information from partition names. Let's take the
`DAILY_TRADE_VALUE` table as an example to see its current partition after we
insert data:
```sql
@@ -263,17 +273,16 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
+-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
```
-In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
+In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
-For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## Key points
-- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
-- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
-- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
+- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
+- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
+- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
- To prevent the accidental creation of too many partitions, Apache Doris
controls the maximum number of partitions an Auto Partition table can
accommodate through the `max_auto_partition_num setting` in the FE
configuration. This value can be adjusted if needed.
-- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
+- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
- When use
[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)
to load data into Auto Partition table, the behaviour is detailed in the
INSERT OVERWRITE documentation.
- If metadata operations are involved when importing and creating partitions,
the import process may fail.
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index ed07ba468a0..094fb1e207a 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -65,7 +65,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- 索引定义
INDEX [ IF NOT EXISTS ]
@@ -84,22 +84,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- 分区定义
<one_partition_definition>
-- 其他分区定义
[ , ... ]
- )
-```
-
-- 其中： <one_partition_definition>
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -114,7 +117,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- 聚合定义
@@ -274,12 +277,6 @@ CREATE TABLE <table_name> LIKE <source_table>
有关分区的详细介绍，请参阅[自动分区](../../../../table-design/data-partitioning/auto-partitioning.md)章节。
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> 自动分区方式。<auto_partition_function>
目前只支持`date_trunc`。<auto_partition_arguments> 填写自动分区的列和 date_trunc 的单位。例如
`date_trunc(col_1, 'day')`。
->
-> 当使用自动分区时，分区列必须为 NOT NULL。
-
### 手动分区相关参数
有关分区的详细介绍，请参阅“手动分区”章节。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/data-partitioning/auto-partitioning.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/data-partitioning/auto-partitioning.md
index 358d72dbddb..cc1f2a5e1fd 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/data-partitioning/auto-partitioning.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/table-design/data-partitioning/auto-partitioning.md
@@ -56,60 +56,60 @@ PROPERTIES (
## 语法
-建表时，使用以下语法填充[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)时的
`partition_info` 部分：
+建表时，使用以下语法填充[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)时的
`partitions_definition` 部分：
1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-其中
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ 其中
+
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
2. AUTO LIST PARTITION:
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
### 用法示例
1. AUTO RANGE PARTITION
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ (
+ )
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
2. AUTO LIST PARTITION
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
LIST 自动分区支持多个分区列，分区列写法同普通 LIST 分区一样： ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
@@ -126,52 +126,51 @@ PROPERTIES (
1. 对于 AUTO LIST PARTITION，可以使用 NULLABLE 列作为分区列，会正常创建对应的 NULL 值分区：
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
2. 对于 AUTO RANGE PARTITION，**不支持 NULLABLE 列作为分区列**。
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## 场景示例
@@ -221,10 +220,11 @@ show partitions from `DAILY_TRADE_VALUE`;
## 与动态分区联用
Doris 支持自动分区和动态分区同时使用。此时，二者的功能都生效：
+
1. 自动分区将会自动在数据导入过程中按需创建分区；
2. 动态分区将会自动创建、回收、转储分区。
-二者语法功能不存在冲突，同时设置对应的子句/属性即可。
+二者语法功能不存在冲突，同时设置对应的子句/属性即可。请注意，当前时间所在的分区由自动分区还是动态分区创建，是不确定的。不同创建方式会导致分区的名称格式不同。
### 最佳实践
@@ -265,7 +265,7 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
这样每个分区的 ID 和取值就可以精准地被筛选出，用于后续针对分区的具体操作（例如 `insert overwrite partition`）。
-详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## 注意事项
@@ -276,4 +276,3 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
- 向开启了 AUTO PARTITION 的表导入数据时，Coordinator 发送数据的轮询间隔与普通表有所不同。具体请见[BE
配置项](../../admin-manual/config/be-config)中的`olap_table_sink_send_interval_auto_partition_factor`。开启前移（`enable_memtable_on_sink_node
= true`）后该变量不产生影响。
-
在使用[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)插入数据时
AUTO PARTITION 表的行为详见 INSERT OVERWRITE 文档。
- 如果导入创建分区时，该表涉及其他元数据操作（如 Schema Change、Rebalance），则导入可能失败。
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index 81c06ebfb66..3ec53824c4d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -64,7 +64,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- 索引定义
INDEX [ IF NOT EXISTS ]
@@ -83,22 +83,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- 分区定义
<one_partition_definition>
-- 其他分区定义
[ , ... ]
- )
-```
-
-- 其中： <one_partition_definition>
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -113,7 +116,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- 聚合定义
@@ -270,12 +273,6 @@ CREATE TABLE <table_name> LIKE <source_table>
有关分区的详细介绍，请参阅[自动分区](../../../../table-design/data-partitioning/auto-partitioning.md)章节。
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> 自动分区方式。<auto_partition_function>
目前只支持`date_trunc`。<auto_partition_arguments> 填写自动分区的列和 date_trunc 的单位。例如
`date_trunc(col_1, 'day')`。
->
-> 当使用自动分区时，分区列必须为 NOT NULL。
-
### 手动分区相关参数
有关分区的详细介绍，请参阅“手动分区”章节。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
index 358d72dbddb..c2aed1f7395 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
@@ -56,60 +56,60 @@ PROPERTIES (
## 语法
-建表时，使用以下语法填充[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)时的
`partition_info` 部分：
+建表时，使用以下语法填充
[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
中的 `partitions_definition` 部分：
1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-其中
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ 其中
+
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
2. AUTO LIST PARTITION:
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
### 用法示例
1. AUTO RANGE PARTITION
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ (
+ )
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
2. AUTO LIST PARTITION
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
LIST 自动分区支持多个分区列，分区列写法同普通 LIST 分区一样： ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
@@ -126,52 +126,51 @@ PROPERTIES (
1. 对于 AUTO LIST PARTITION，可以使用 NULLABLE 列作为分区列，会正常创建对应的 NULL 值分区：
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
2. 对于 AUTO RANGE PARTITION，**不支持 NULLABLE 列作为分区列**。
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## 场景示例
@@ -221,10 +220,15 @@ show partitions from `DAILY_TRADE_VALUE`;
## 与动态分区联用
Doris 支持自动分区和动态分区同时使用。此时，二者的功能都生效：
+
1. 自动分区将会自动在数据导入过程中按需创建分区；
2. 动态分区将会自动创建、回收、转储分区。
-二者语法功能不存在冲突，同时设置对应的子句/属性即可。
+二者语法功能不存在冲突，同时设置对应的子句/属性即可。请注意，当前时间所在的分区由自动分区还是动态分区创建，是不确定的。不同创建方式会导致分区的名称格式不同。
+
+:::info
+该功能自 Doris 2.1.7 起支持。
+:::
### 最佳实践
@@ -265,7 +269,7 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
这样每个分区的 ID 和取值就可以精准地被筛选出，用于后续针对分区的具体操作（例如 `insert overwrite partition`）。
-详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## 注意事项
@@ -276,4 +280,3 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
- 向开启了 AUTO PARTITION 的表导入数据时，Coordinator 发送数据的轮询间隔与普通表有所不同。具体请见[BE
配置项](../../admin-manual/config/be-config)中的`olap_table_sink_send_interval_auto_partition_factor`。开启前移（`enable_memtable_on_sink_node
= true`）后该变量不产生影响。
-
在使用[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)插入数据时
AUTO PARTITION 表的行为详见 INSERT OVERWRITE 文档。
- 如果导入创建分区时，该表涉及其他元数据操作（如 Schema Change、Rebalance），则导入可能失败。
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index 80ae0592922..100e2305e7e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -65,7 +65,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- 索引定义
INDEX [ IF NOT EXISTS ]
@@ -84,22 +84,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- 分区定义
<one_partition_definition>
-- 其他分区定义
[ , ... ]
- )
-```
-
-- 其中： <one_partition_definition>
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -114,7 +117,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- 聚合定义
@@ -274,12 +277,6 @@ CREATE TABLE <table_name> LIKE <source_table>
有关分区的详细介绍，请参阅[自动分区](../../../../table-design/data-partitioning/auto-partitioning.md)章节。
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> 自动分区方式。<auto_partition_function>
目前只支持`date_trunc`。<auto_partition_arguments> 填写自动分区的列和 date_trunc 的单位。例如
`date_trunc(col_1, 'day')`。
->
-> 当使用自动分区时，分区列必须为 NOT NULL。
-
### 手动分区相关参数
有关分区的详细介绍，请参阅“手动分区”章节。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
index 358d72dbddb..8f9deea86fd 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
@@ -56,60 +56,59 @@ PROPERTIES (
## 语法
-建表时，使用以下语法填充[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)时的
`partition_info` 部分：
+建表时，使用以下语法填充
[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
中的 `partitions_definition` 部分：
1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-其中
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ 其中
+
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
2. AUTO LIST PARTITION:
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
### 用法示例
1. AUTO RANGE PARTITION
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ ()
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
2. AUTO LIST PARTITION
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
LIST 自动分区支持多个分区列，分区列写法同普通 LIST 分区一样： ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
@@ -126,52 +125,51 @@ PROPERTIES (
1. 对于 AUTO LIST PARTITION，可以使用 NULLABLE 列作为分区列，会正常创建对应的 NULL 值分区：
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
2. 对于 AUTO RANGE PARTITION，**不支持 NULLABLE 列作为分区列**。
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## 场景示例
@@ -221,10 +219,15 @@ show partitions from `DAILY_TRADE_VALUE`;
## 与动态分区联用
Doris 支持自动分区和动态分区同时使用。此时，二者的功能都生效：
+
1. 自动分区将会自动在数据导入过程中按需创建分区；
2. 动态分区将会自动创建、回收、转储分区。
-二者语法功能不存在冲突，同时设置对应的子句/属性即可。
+二者语法功能不存在冲突，同时设置对应的子句/属性即可。请注意，当前时间所在的分区由自动分区还是动态分区创建，是不确定的。不同创建方式会导致分区的名称格式不同。
+
+:::info
+该功能自 Doris 3.0.3 起支持。
+:::
### 最佳实践
@@ -265,7 +268,7 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
这样每个分区的 ID 和取值就可以精准地被筛选出，用于后续针对分区的具体操作（例如 `insert overwrite partition`）。
-详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+详细语法说明请见：[auto_partition_name
函数文档](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions
表函数文档](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## 注意事项
@@ -276,4 +279,3 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
- 向开启了 AUTO PARTITION 的表导入数据时，Coordinator 发送数据的轮询间隔与普通表有所不同。具体请见[BE
配置项](../../admin-manual/config/be-config)中的`olap_table_sink_send_interval_auto_partition_factor`。开启前移（`enable_memtable_on_sink_node
= true`）后该变量不产生影响。
-
在使用[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)插入数据时
AUTO PARTITION 表的行为详见 INSERT OVERWRITE 文档。
- 如果导入创建分区时，该表涉及其他元数据操作（如 Schema Change、Rebalance），则导入可能失败。
-
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/versioned_docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index f686a830e9a..4f6d2d9978a 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -64,7 +64,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- Index definition
INDEX [ IF NOT EXISTS ]
@@ -83,22 +83,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- Partition definition
<one_partition_definition>
-- Additional partition definition
[ , ... ]
- )
-```
-
-- Where <one_partition_definition>:
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -113,7 +116,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- Rollup definition
@@ -264,16 +267,10 @@ CREATE TABLE <new_table_name> LIKE <existing_table_name>
> The properties of the index. For detailed explanations, refer to the
> [Inverted Index](../../../../table-design/index/inverted-index.md) section.
-### Automatic Partitioning Related Parameters
+### Auto Partition Related Parameters
For a detailed introduction to partitioning, see the [Automatic
Partitioning](../../../../table-design/data-partitioning/auto-partitioning.md)
section.
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> The method of automatic partitioning. `<auto_partition_function>` currently
only supports `date_trunc`. `<auto_partition_arguments>` specifies the column
for automatic partitioning and the unit of `date_trunc`. For example,
`date_trunc(col_1, 'day')`.
->
-> When using automatic partitioning, the partition column must be NOT NULL.
-
### Manual Partitioning Related Parameters
For a detailed introduction to partitioning, see the "Manual Partitioning"
section.
diff --git
a/versioned_docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
b/versioned_docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
index 86e3ec490a2..f5d349d71ea 100644
---
a/versioned_docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
+++
b/versioned_docs/version-2.1/table-design/data-partitioning/auto-partitioning.md
@@ -58,120 +58,121 @@ The table stores a large amount of business history data,
partitioned based on t
## Syntax
-When creating a table, use the following syntax to populate the
`partition_info` section in the `CREATE-TABLE` statement:
+When creating a table, use the following syntax to populate the
`partitions_definition` section in the
[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
statement.
-- For RANGE Partition:
+1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-Where
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ where
-- For LIST Partition:
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+2. AUTO LIST PARTITION:
-### Sample
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
-- For Range Partition:
-
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL COMMENT '采集日期'
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
-
-- For List Partition:
-
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+### Sample
-List Auto Partition supports multiple partition columns, which are written in
the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
+1. AUTO RANGE PARTITION
+
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ (
+ )
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+2. AUTO LIST PARTITION
+
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+ List Auto Partition supports multiple partition columns, which are written
in the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
### Constraints
- In auto List Partition, the partition name length **must not exceed 50
characters**. This length is derived from the concatenation and escape of
contents of partition columns on corresponding data rows, so the actual allowed
length may be shorter.
-- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
-- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
-- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
+- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
+- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
+- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
### NULL value partition
When the session variable `allow_partition_column_nullable` is enabled:
- For Auto List Partition, the corresponding NULL value partition will be
created automatically:
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
- For Auto Range Partition, **null columns are not supported to be partition
columns**.
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## Example
@@ -220,11 +221,16 @@ It can be concluded that the partitions created by Auto
Partition share the same
## Conjunct with Dynamic Partition
-Since 2.1.7, Doris supports both Auto and Dynamic Partition. In this case,
both functions are in effect:
+Doris supports both Auto and Dynamic Partition. In this case, both functions
are in effect:
+
1. Auto Partition will automatically create partitions on demand during data
import;
2. Dynamic Partition will automatically create, recycle and dump partitions.
-There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time.
+There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time. Please note that it is uncertain whether
the partition in current period is created by Auto Partition or Dynamic
Partition. Different creation methods will lead to different naming formats for
the partitions.
+
+:::info
+This feature has been supported since Doris 2.1.7
+:::
## Best Practice
@@ -271,17 +277,16 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
+-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
```
-In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
+In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
-For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## Key points
-- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
-- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
-- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
+- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
+- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
+- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
- To prevent the accidental creation of too many partitions, Apache Doris
controls the maximum number of partitions an Auto Partition table can
accommodate through the `max_auto_partition_num setting` in the FE
configuration. This value can be adjusted if needed.
-- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
+- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
- When use
[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)
to load data into Auto Partition table, the behaviour is detailed in the
INSERT OVERWRITE documentation.
- If metadata operations are involved when importing and creating partitions,
the import process may fail.
-
diff --git
a/versioned_docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
b/versioned_docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
index 38d365c36db..d525d2e7cbc 100644
---
a/versioned_docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
+++
b/versioned_docs/version-3.0/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE.md
@@ -5,7 +5,6 @@
"toc_min_heading_level": 2,
"toc_max_heading_level": 4
}
-
---
## Description
@@ -66,7 +65,7 @@ columns_definition
[ , <col_name> <col_type> [ ... ] ]
```
-```sql
+```sql
indexes_definition
: -- Index definition
INDEX [ IF NOT EXISTS ]
@@ -85,22 +84,25 @@ indexes_definition
```sql
partitions_definition
- : AUTO PARTITION BY RANGE(
- <auto_partition_function>(<auto_partition_arguments>))
- ()
- | PARTITION BY <partition_type>
- (<partition_cols>)
- (
+ : AUTO PARTITION BY
RANGE(<auto_partition_function>(<auto_partition_arguments>))
+ <origin_partitions_definition>
+ | AUTO PARTITION BY LIST(<partition_cols>)
+ <origin_partitions_definition>
+ | PARTITION BY <partition_type> (<partition_cols>)
+ <origin_partitions_definition>
+```
+
+- 其中：
+
+ ```sql
+ <origin_partitions_definition>
+ : (
-- Partition definition
<one_partition_definition>
-- Additional partition definition
[ , ... ]
- )
-```
-
-- Where <one_partition_definition>:
+ )
- ```sql
<one_partition_definition>
: PARTITION [ IF NOT EXISTS ] <partition_name>
VALUES LESS THAN <partition_value_list>
@@ -115,7 +117,7 @@ partitions_definition
}
```
-```sql
+```sql
roll_up_definition
: ROLLUP (
-- Rollup definition
@@ -269,16 +271,10 @@ CREATE TABLE <new_table_name> LIKE <existing_table_name>
> The properties of the index. For detailed explanations, refer to the
> [Inverted Index](../../../../table-design/index/inverted-index.md) section.
-### Automatic Partitioning Related Parameters
+### Auto Partition Related Parameters
For a detailed introduction to partitioning, see the [Automatic
Partitioning](../../../../table-design/data-partitioning/auto-partitioning.md)
section.
-**<auto_partition_function>(<auto_partition_arguments>)**
-
-> The method of automatic partitioning. `<auto_partition_function>` currently
only supports `date_trunc`. `<auto_partition_arguments>` specifies the column
for automatic partitioning and the unit of `date_trunc`. For example,
`date_trunc(col_1, 'day')`.
->
-> When using automatic partitioning, the partition column must be NOT NULL.
-
### Manual Partitioning Related Parameters
For a detailed introduction to partitioning, see the "Manual Partitioning"
section.
diff --git
a/versioned_docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
b/versioned_docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
index 32f2f160ab9..7f041092e40 100644
---
a/versioned_docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
+++
b/versioned_docs/version-3.0/table-design/data-partitioning/auto-partitioning.md
@@ -58,120 +58,121 @@ The table stores a large amount of business history data,
partitioned based on t
## Syntax
-When creating a table, use the following syntax to populate the
`partition_info` section in the `CREATE-TABLE` statement:
+When creating a table, use the following syntax to populate the
`partitions_definition` section in the
[CREATE-TABLE](../../sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
statement.
-- For RANGE Partition:
+1. AUTO RANGE PARTITION:
-```sql
- AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
- ()
-```
+ ```sql
+ AUTO PARTITION BY RANGE(<partition_expr>)
+ <origin_partitions_definition>
+ ```
-Where
-```sql
- FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
-```
+ where
-- For LIST Partition:
+ ```sql
+ partition_expr ::= date_trunc ( <partition_column>, '<interval>' )
+ ```
-```sql
- AUTO PARTITION BY LIST(`partition_col1`[, `partition_col2`, ...])
- ()
-```
+2. AUTO LIST PARTITION:
-### Sample
+ ```sql
+ AUTO PARTITION BY LIST(`partition_col1` [, `partition_col2`, ...])
+ <origin_partitions_definition>
+ ```
-- For Range Partition:
-
-```sql
- CREATE TABLE `date_table` (
- `TIME_STAMP` datev2 NOT NULL COMMENT '采集日期'
- ) ENGINE=OLAP
- DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
- (
- )
- DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
-
-- For List Partition:
-
-```sql
- CREATE TABLE `str_table` (
- `str` varchar not null
- ) ENGINE=OLAP
- DUPLICATE KEY(`str`)
- AUTO PARTITION BY LIST (`str`)
- (
- )
- DISTRIBUTED BY HASH(`str`) BUCKETS 10
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-```
+### Sample
-List Auto Partition supports multiple partition columns, which are written in
the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
+1. AUTO RANGE PARTITION
+
+ ```sql
+ CREATE TABLE `date_table` (
+ `TIME_STAMP` datev2 NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`TIME_STAMP`)
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
+ (
+ )
+ DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+2. AUTO LIST PARTITION
+
+ ```sql
+ CREATE TABLE `str_table` (
+ `str` varchar not null
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`str`)
+ AUTO PARTITION BY LIST (`str`)
+ ()
+ DISTRIBUTED BY HASH(`str`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ ```
+
+ List Auto Partition supports multiple partition columns, which are written
in the same way as normal List Partition: ```AUTO PARTITION BY LIST (`col1`,
`col2`, ...)```
### Constraints
- In auto List Partition, the partition name length **must not exceed 50
characters**. This length is derived from the concatenation and escape of
contents of partition columns on corresponding data rows, so the actual allowed
length may be shorter.
-- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
-- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
-- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
+- In auto Range Partition, the partition function only supports `date_trunc`,
and the partition column supports only `DATE` or `DATETIME` types.
+- In auto List Partition, function calls are not supported, and the partition
column supports `BOOLEAN`, `TINYINT`, `SMALLINT`, `INT`, `BIGINT`, `LARGEINT`,
`DATE`, `DATETIME`, `CHAR`, `VARCHAR` data types, with partition values being
enumeration values.
+- In auto List Partition, for every existing value in the partition column
that does not correspond to a partition, a new independent partition will be
created.
### NULL value partition
When the session variable `allow_partition_column_nullable` is enabled:
- For Auto List Partition, the corresponding NULL value partition will be
created automatically:
-```sql
- create table auto_null_list(
- k0 varchar null
- )
- auto partition by list (k0)
- (
- )
- DISTRIBUTED BY HASH(`k0`) BUCKETS 1
- properties("replication_num" = "1");
-
-
- insert into auto_null_list values (null);
-
- select * from auto_null_list;
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-
- select * from auto_null_list partition(pX);
- +------+
- | k0 |
- +------+
- | NULL |
- +------+
-```
+
+ ```sql
+ create table auto_null_list(
+ k0 varchar null
+ )
+ auto partition by list (k0)
+ (
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ properties("replication_num" = "1");
+
+ insert into auto_null_list values (null);
+
+ select * from auto_null_list;
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+
+ select * from auto_null_list partition(pX);
+ +------+
+ | k0 |
+ +------+
+ | NULL |
+ +------+
+ ```
- For Auto Range Partition, **null columns are not supported to be partition
columns**.
-```sql
- CREATE TABLE `range_table_nullable` (
- `k1` INT,
- `k2` DATETIMEV2(3),
- `k3` DATETIMEV2(6)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
- (
- )
- DISTRIBUTED BY HASH(`k1`) BUCKETS 16
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1"
- );
-ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
-```
+
+ ```sql
+ CREATE TABLE `range_table_nullable` (
+ `k1` INT,
+ `k2` DATETIMEV2(3),
+ `k3` DATETIMEV2(6)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
+ ()
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 16
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION
doesn't support NULL column
+ ```
## Example
@@ -206,7 +207,6 @@ After inserting data and checking again, it is found that
the table has created
```sql
insert into `DAILY_TRADE_VALUE` values ('2012-12-13', 1), ('2008-02-03', 2),
('2014-11-11', 3);
-
show partitions from `DAILY_TRADE_VALUE`;
+-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State
| PartitionKey | Range
| DistributionKey | Buckets | ReplicationNum | StorageMedium |
CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize
| IsInMemory | ReplicaAllocation | IsMutable |
@@ -221,11 +221,16 @@ It can be concluded that the partitions created by Auto
Partition share the same
## Conjunct with Dynamic Partition
-Since 3.0.3, Doris supports both Auto and Dynamic Partition. In this case,
both functions are in effect:
+Doris supports both Auto and Dynamic Partition. In this case, both functions
are in effect:
+
1. Auto Partition will automatically create partitions on demand during data
import;
2. Dynamic Partition will automatically create, recycle and dump partitions.
-There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time.
+There is no conflict between the two syntaxes, just set the corresponding
clauses/attributes at the same time. Please note that it is uncertain whether
the partition in current period is created by Auto Partition or Dynamic
Partition. Different creation methods will lead to different naming formats for
the partitions.
+
+:::info
+This feature has been supported since Doris 3.0.3
+:::
## Best Practice
@@ -272,17 +277,16 @@ select * from
partitions("catalog"="internal","database"="optest","table"="DAILY
+-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
```
-In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
+In this way the IDs and values of each partition can be precisely filtered for
subsequent partition-specific operations (e.g. `insert overwrite partition`).
-For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
+For a detailed grammar description, see:
[auto_partition_name](../../sql-manual/sql-functions/scalar-functions/string-functions/auto-partition-name)，[partitions](../../sql-manual/sql-functions/table-valued-functions/partitions)。
## Key points
-- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
-- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
-- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
+- Similar to regular partitioned tables, aoto List Partition supports
multi-column partitioning with no syntax differences.
+- If partitions are created during data insertion or import processes, and the
entire import process is not completed (fails or is canceled), the created
partitions will not be automatically deleted.
+- Tables using Auto Partition only differ in the method of partition creation,
switching from manual to automatic. The original usage of the table and its
created partitions remains the same as non-Auto Partition tables or partitions.
- To prevent the accidental creation of too many partitions, Apache Doris
controls the maximum number of partitions an Auto Partition table can
accommodate through the `max_auto_partition_num setting` in the FE
configuration. This value can be adjusted if needed.
-- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
+- When importing data into a table with Auto Partition enabled, the
coordinator sends data with a polling interval different from regular tables.
Refer to `olap_table_sink_send_interval_auto_partition_factor` in [BE
Configuration](../../admin-manual/config/be-config) for details. This setting
does not have an impact after `enable_memtable_on_sink_node` is enabled.
- When use
[insert-overwrite](../../sql-manual/sql-statements/data-modification/DML/INSERT-OVERWRITE)
to load data into Auto Partition table, the behaviour is detailed in the
INSERT OVERWRITE documentation.
- If metadata operations are involved when importing and creating partitions,
the import process may fail.
-
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
