This is an automated email from the ASF dual-hosted git repository.
luzhijing pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new f907490c2589 [Doc](partition) Refine some auto partition behaviours
(#488)
f907490c2589 is described below
commit f907490c25898b3bed98a89a24a0650b51d42d17
Author: zclllyybb <[email protected]>
AuthorDate: Fri Mar 29 22:41:14 2024 +0800
[Doc](partition) Refine some auto partition behaviours (#488)
---
docs/advanced/partition/auto-partition.md | 37 ++++++--------------
.../current/advanced/partition/auto-partition.md | 37 ++++++--------------
.../advanced/partition/auto-partition.md | 39 ++++++----------------
.../advanced/partition/auto-partition.md | 39 ++++++----------------
4 files changed, 40 insertions(+), 112 deletions(-)
diff --git a/docs/advanced/partition/auto-partition.md
b/docs/advanced/partition/auto-partition.md
index e9b5e3d1e446..6805ace754fa 100644
--- a/docs/advanced/partition/auto-partition.md
+++ b/docs/advanced/partition/auto-partition.md
@@ -88,7 +88,7 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
1. AUTO RANGE PARTITION:
```sql
- AUTO PARTITION BY RANGE FUNC_CALL_EXPR
+ AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
(
)
```
@@ -97,6 +97,8 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
```
+Note: In version 2.1.0, `FUNC_CALL_EXPR` does not need to be surrounded by
parentheses.
+
2. AUTO LIST PARTITION:
```sql
@@ -114,7 +116,7 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
`TIME_STAMP` datev2 NOT NULL COMMENT 'Date of collection'
) ENGINE=OLAP
DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month')
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
(
)
DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
@@ -183,9 +185,7 @@ mysql> select * from auto_null_list partition(pX);
1 row in set (0.20 sec)
```
-2. For AUTO RANGE PARTITION, the storage of NULL values requires the manual
creation of specific partitions.
-
-In Doris' RANGE PARTITION, we do not use a separate NULL PARTITION to contain
NULL values, but rather group them in the **minium LESS THAN partition**. For
logical clarity, the AUTO RANGE PARTITION does not automatically create such a
partition when it encounters a NULL value. If necessary, you can create such a
partition yourself to contain NULL values.
+2. For AUTO RANGE PARTITION, **NULLABLE columns are not supported as partition
columns**.
```sql
mysql> CREATE TABLE `range_table_nullable` (
@@ -194,31 +194,14 @@ mysql> CREATE TABLE `range_table_nullable` (
-> `k3` DATETIMEV2(6)
-> ) ENGINE=OLAP
-> DUPLICATE KEY(`k1`)
- -> AUTO PARTITION BY RANGE date_trunc(`k2`, 'day')
+ -> AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
-> (
-> )
-> DISTRIBUTED BY HASH(`k1`) BUCKETS 16
-> PROPERTIES (
-> "replication_allocation" = "tag.location.default: 1"
-> );
-Query OK, 0 rows affected (0.09 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-ERROR 1105 (HY000): errCode = 2, detailMessage = [CANCELLED]TStatus: errCode =
2, detailMessage = Can't create partition for NULL Range
-
-mysql> alter table range_table_nullable add partition pX VALUES LESS THAN
("1970-01-01");
-Query OK, 0 rows affected (0.11 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-Query OK, 1 row affected (0.18 sec)
-
-mysql> select * from range_table_nullable;
-+------+------+------+
-| k1 | k2 | k3 |
-+------+------+------+
-| 0 | NULL | NULL |
-+------+------+------+
-1 row in set (0.18 sec)
+ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
```
## Sample Scenarios
@@ -233,7 +216,7 @@ CREATE TABLE `DAILY_TRADE_VALUE`
......
)
UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
-AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')
+AUTO PARTITION BY RANGE (date_trunc(`TRADE_DATE`, 'year'))
(
)
DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
@@ -276,7 +259,7 @@ CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
-AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year') ())
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
@@ -308,7 +291,7 @@ mysql > CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
- AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+ AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year') ())
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/advanced/partition/auto-partition.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/advanced/partition/auto-partition.md
index 15e9f2a57f5a..f0d2d212390f 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/advanced/partition/auto-partition.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/advanced/partition/auto-partition.md
@@ -88,7 +88,7 @@ PROPERTIES (
1. AUTO RANGE PARTITION:
```sql
- AUTO PARTITION BY RANGE FUNC_CALL_EXPR
+ AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
(
)
```
@@ -97,6 +97,8 @@ PROPERTIES (
FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
```
+注意：在 2.1.0 版本，`FUNC_CALL_EXPR` 外围不需要被括号包围。
+
2. AUTO LIST PARTITION:
```sql
@@ -114,7 +116,7 @@ PROPERTIES (
`TIME_STAMP` datev2 NOT NULL COMMENT '采集日期'
) ENGINE=OLAP
DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month')
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
(
)
DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
@@ -183,9 +185,7 @@ mysql> select * from auto_null_list partition(pX);
1 row in set (0.20 sec)
```
-2. 对于 AUTO RANGE PARTITION，NULL 值的存储需要手动创建特定的分区。
-
-在 Doris 的 RANGE PARTITION 中，我们并不使用单独的 NULL PARTITION 来包含 NULL 值，而是将其归属于**最小的
LESS THAN 分区内**。为保持逻辑清晰，AUTO RANGE PARTITION 在遇到 NULL
值时不会自动创建这样的分区。如有需要，可以自行创建这样的分区，以包含 NULL 值。
+2. 对于 AUTO RANGE PARTITION，**不支持 NULLABLE 列作为分区列**。
```sql
mysql> CREATE TABLE `range_table_nullable` (
@@ -194,31 +194,14 @@ mysql> CREATE TABLE `range_table_nullable` (
-> `k3` DATETIMEV2(6)
-> ) ENGINE=OLAP
-> DUPLICATE KEY(`k1`)
- -> AUTO PARTITION BY RANGE date_trunc(`k2`, 'day')
+ -> AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
-> (
-> )
-> DISTRIBUTED BY HASH(`k1`) BUCKETS 16
-> PROPERTIES (
-> "replication_allocation" = "tag.location.default: 1"
-> );
-Query OK, 0 rows affected (0.09 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-ERROR 1105 (HY000): errCode = 2, detailMessage = [CANCELLED]TStatus: errCode =
2, detailMessage = Can't create partition for NULL Range
-
-mysql> alter table range_table_nullable add partition pX VALUES LESS THAN
("1970-01-01");
-Query OK, 0 rows affected (0.11 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-Query OK, 1 row affected (0.18 sec)
-
-mysql> select * from range_table_nullable;
-+------+------+------+
-| k1 | k2 | k3 |
-+------+------+------+
-| 0 | NULL | NULL |
-+------+------+------+
-1 row in set (0.18 sec)
+ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
```
## 场景示例
@@ -233,7 +216,7 @@ CREATE TABLE `DAILY_TRADE_VALUE`
......
)
UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
-AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')
+AUTO PARTITION BY RANGE (date_trunc(`TRADE_DATE`, 'year'))
(
)
DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
@@ -276,7 +259,7 @@ CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
-AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year')) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
@@ -308,7 +291,7 @@ mysql > CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
- AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+ AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year')) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/advanced/partition/auto-partition.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/advanced/partition/auto-partition.md
index dd2832a2b8c1..f0d2d212390f 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/advanced/partition/auto-partition.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/advanced/partition/auto-partition.md
@@ -88,7 +88,7 @@ PROPERTIES (
1. AUTO RANGE PARTITION:
```sql
- AUTO PARTITION BY RANGE FUNC_CALL_EXPR
+ AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
(
)
```
@@ -97,6 +97,8 @@ PROPERTIES (
FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
```
+注意：在 2.1.0 版本，`FUNC_CALL_EXPR` 外围不需要被括号包围。
+
2. AUTO LIST PARTITION:
```sql
@@ -114,7 +116,7 @@ PROPERTIES (
`TIME_STAMP` datev2 NOT NULL COMMENT '采集日期'
) ENGINE=OLAP
DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month')
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
(
)
DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
@@ -148,8 +150,6 @@ PROPERTIES (
### NULL 值分区
-> 从 2.1.2 开始，Doris 支持以下功能。
-
当开启 session variable `allow_partition_column_nullable` 后，LIST 和 RANGE 分区都支持
NULL 列作为分区列。当分区列实际遇到 NULL 值的插入时：
1. 对于 AUTO LIST PARTITION，会自动创建对应的 NULL 值分区：
@@ -185,9 +185,7 @@ mysql> select * from auto_null_list partition(pX);
1 row in set (0.20 sec)
```
-2. 对于 AUTO RANGE PARTITION，NULL 值的存储需要手动创建特定的分区。
-
-在 Doris 的 RANGE PARTITION 中，我们并不使用单独的 NULL PARTITION 来包含 NULL 值，而是将其归属于**最小的
LESS THAN 分区内**。为保持逻辑清晰，AUTO RANGE PARTITION 在遇到 NULL
值时不会自动创建这样的分区。如有需要，可以自行创建这样的分区，以包含 NULL 值。
+2. 对于 AUTO RANGE PARTITION，**不支持 NULLABLE 列作为分区列**。
```sql
mysql> CREATE TABLE `range_table_nullable` (
@@ -196,31 +194,14 @@ mysql> CREATE TABLE `range_table_nullable` (
-> `k3` DATETIMEV2(6)
-> ) ENGINE=OLAP
-> DUPLICATE KEY(`k1`)
- -> AUTO PARTITION BY RANGE date_trunc(`k2`, 'day')
+ -> AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
-> (
-> )
-> DISTRIBUTED BY HASH(`k1`) BUCKETS 16
-> PROPERTIES (
-> "replication_allocation" = "tag.location.default: 1"
-> );
-Query OK, 0 rows affected (0.09 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-ERROR 1105 (HY000): errCode = 2, detailMessage = [CANCELLED]TStatus: errCode =
2, detailMessage = Can't create partition for NULL Range
-
-mysql> alter table range_table_nullable add partition pX VALUES LESS THAN
("1970-01-01");
-Query OK, 0 rows affected (0.11 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-Query OK, 1 row affected (0.18 sec)
-
-mysql> select * from range_table_nullable;
-+------+------+------+
-| k1 | k2 | k3 |
-+------+------+------+
-| 0 | NULL | NULL |
-+------+------+------+
-1 row in set (0.18 sec)
+ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
```
## 场景示例
@@ -235,7 +216,7 @@ CREATE TABLE `DAILY_TRADE_VALUE`
......
)
UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
-AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')
+AUTO PARTITION BY RANGE (date_trunc(`TRADE_DATE`, 'year'))
(
)
DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
@@ -278,7 +259,7 @@ CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
-AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year')) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
@@ -310,7 +291,7 @@ mysql > CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
- AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+ AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year')) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
diff --git a/versioned_docs/version-2.1/advanced/partition/auto-partition.md
b/versioned_docs/version-2.1/advanced/partition/auto-partition.md
index 839914dd89d8..6805ace754fa 100644
--- a/versioned_docs/version-2.1/advanced/partition/auto-partition.md
+++ b/versioned_docs/version-2.1/advanced/partition/auto-partition.md
@@ -88,7 +88,7 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
1. AUTO RANGE PARTITION:
```sql
- AUTO PARTITION BY RANGE FUNC_CALL_EXPR
+ AUTO PARTITION BY RANGE (FUNC_CALL_EXPR)
(
)
```
@@ -97,6 +97,8 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' )
```
+Note: In version 2.1.0, `FUNC_CALL_EXPR` does not need to be surrounded by
parentheses.
+
2. AUTO LIST PARTITION:
```sql
@@ -114,7 +116,7 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
`TIME_STAMP` datev2 NOT NULL COMMENT 'Date of collection'
) ENGINE=OLAP
DUPLICATE KEY(`TIME_STAMP`)
- AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month')
+ AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month'))
(
)
DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
@@ -148,8 +150,6 @@ When building a table, use the following syntax to populate
[CREATE-TABLE](../..
### NULL-valued partition
-> Starting from 2.1.2, Doris supports the following features.
-
Both LIST and RANGE partitions support NULL columns as partition columns when
the session variable `allow_partition_column_nullable` is turned on. When a
partition column actually encounters an insert with a NULL value:
1. For an AUTO LIST PARTITION, the corresponding NULL-valued partition is
automatically created:
@@ -185,9 +185,7 @@ mysql> select * from auto_null_list partition(pX);
1 row in set (0.20 sec)
```
-2. For AUTO RANGE PARTITION, the storage of NULL values requires the manual
creation of specific partitions.
-
-In Doris' RANGE PARTITION, we do not use a separate NULL PARTITION to contain
NULL values, but rather group them in the **minium LESS THAN partition**. For
logical clarity, the AUTO RANGE PARTITION does not automatically create such a
partition when it encounters a NULL value. If necessary, you can create such a
partition yourself to contain NULL values.
+2. For AUTO RANGE PARTITION, **NULLABLE columns are not supported as partition
columns**.
```sql
mysql> CREATE TABLE `range_table_nullable` (
@@ -196,31 +194,14 @@ mysql> CREATE TABLE `range_table_nullable` (
-> `k3` DATETIMEV2(6)
-> ) ENGINE=OLAP
-> DUPLICATE KEY(`k1`)
- -> AUTO PARTITION BY RANGE date_trunc(`k2`, 'day')
+ -> AUTO PARTITION BY RANGE (date_trunc(`k2`, 'day'))
-> (
-> )
-> DISTRIBUTED BY HASH(`k1`) BUCKETS 16
-> PROPERTIES (
-> "replication_allocation" = "tag.location.default: 1"
-> );
-Query OK, 0 rows affected (0.09 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-ERROR 1105 (HY000): errCode = 2, detailMessage = [CANCELLED]TStatus: errCode =
2, detailMessage = Can't create partition for NULL Range
-
-mysql> alter table range_table_nullable add partition pX VALUES LESS THAN
("1970-01-01");
-Query OK, 0 rows affected (0.11 sec)
-
-mysql> insert into range_table_nullable values (0, null, null);
-Query OK, 1 row affected (0.18 sec)
-
-mysql> select * from range_table_nullable;
-+------+------+------+
-| k1 | k2 | k3 |
-+------+------+------+
-| 0 | NULL | NULL |
-+------+------+------+
-1 row in set (0.18 sec)
+ERROR 1105 (HY000): errCode = 2, detailMessage = AUTO RANGE PARTITION doesn't
support NULL column
```
## Sample Scenarios
@@ -235,7 +216,7 @@ CREATE TABLE `DAILY_TRADE_VALUE`
......
)
UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
-AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')
+AUTO PARTITION BY RANGE (date_trunc(`TRADE_DATE`, 'year'))
(
)
DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
@@ -278,7 +259,7 @@ CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
-AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year') ())
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
@@ -310,7 +291,7 @@ mysql > CREATE TABLE tbl3
k1 DATETIME NOT NULL,
col1 int
)
- AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
+ AUTO PARTITION BY RANGE (date_trunc(`k1`, 'year') ())
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
