This document mainly introduces table creation and data partitioning in Doris, as well as potential problems and solutions encountered during table creation operations.

Basic concepts

In Doris, data is logically described in the form of tables.

Row & Column

A table consists of rows and columns:

  • Row: Represents a single line of user data;
  • Column: Used to describe different fields in a row of data;
  • Columns can be divided into two types: Key and Value. From a business perspective, Key and Value can correspond to dimension columns and metric columns respectively. The key columns in Apache Doris are those specified in the table creation statement, which are the columns following the keywords unique key, aggregate key, or duplicate key. The remaining columns are value columns. From the perspective of the aggregation model, rows with the same Key columns will be aggregated into a single row. The aggregation method for value columns is specified by the user during table creation. For more information on aggregation models, refer to the Doris Data Model.

Partition & Tablet

Apache Doris supports two levels of data partitioning. The first level is partition, which supports RANGE partitioning and LIST partitioning. The second level is tablet (also called bucket), which supports Hash bucket and Random bucket. If no partition is established during table creation, Apache Doris generates a default partition that is transparent to the user. When using the default partition, only bucket is supported.

In the Apache Doris storage engine, data is horizontally partitioned into several tablets. Each tablet contains several rows of data. There is no overlap between the data in different tablets, and they are stored physically independently.

Multiple tablets logically belong to different partitions. A single tablet belongs to only one partition, while a partition contains several tablets. Because tablets are stored physically independently, partitions can also be considered physically independent. The tablet is the smallest physical storage unit for operations such as data movement and replication.

Several partitions compose a table. The partition can be considered the smallest logical management unit.

The benefits of Apache Doris’s two-level data partitioning are as follows:

  • Columns with ordered values can be used as partitioning columns. The partition granularity can be evaluated based on import frequency and partition data volume.
  • If there is a need to delete historical data (such as retaining only the data for the most recent several days), composite partition can be used to achieve this goal by deleting historical partitions. Alternatively, DELETE statements can be sent within specified partitions to delete data.
  • Each partition can specify the number of buckets independently. For example, when data is partitioned by day and there are significant differences in data volume between days, the number of buckets for each partition can be specified to reasonably distribute data across different partitions. It is recommended to choose a column with high distinctiveness as the bucketing column.

Example of creating a table

CREATE TABLE in Apache Doris is a synchronous command which returns the result once the SQL is executed. Successful returns indicate successful table creation. For more information, refer to CREATE-TABLE or input the HELP CREATE TABLE command.

The following code sample introduces how to create tables in Apache Doris by RANGE partitioning and Hash buckets.

1. -- Range Partition
2. CREATE TABLE IF NOT EXISTS example_range_tbl
3. (
4. `user_id` LARGEINT NOT NULL COMMENT "User ID",
5. `date` DATE NOT NULL COMMENT "Date when the data are imported",
6. `timestamp` DATETIME NOT NULL COMMENT "Timestamp when the data are imported",
7. `city` VARCHAR(20) COMMENT "User location city",
8. `age` SMALLINT COMMENT "User age",
9. `sex` TINYINT COMMENT "User gender",
10. `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time",
11. `cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption",
12. `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum user dwell time",
13. `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum user dwell time"
14. )
15. ENGINE=OLAP
16. AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
17. PARTITION BY RANGE(`date`)
18. (
19. PARTITION `p201701` VALUES [("2017-01-01"),  ("2017-02-01")),
20. PARTITION `p201702` VALUES [("2017-02-01"), ("2017-03-01")),
21. PARTITION `p201703` VALUES [("2017-03-01"), ("2017-04-01"))
22. )
23. DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
24. PROPERTIES
25. (
26. "replication_num" = "1"
27. );

Here use Aggregate Key Model as an example. In Aggregate Key Model, all columns that are specified with an aggregation type (SUM, REPLACE, MAX, or MIN) are Value columns. The rest are the Key columns.

For more information about what fields can be set in the PROPERTIES section of CREATE TABLE, refer to CREATE-TABLE.

The default type of ENGINE is OLAP. Only OLAP is responsible for data management and storage by Apache Doris itself. Other engine types, such as MySQL, Broker and ES, are essentially just mappings to tables in other external databases or systems, allowing Apache Doris to read this data. However, Apache Doris itself does not create, manage, or store any tables or data for engine types except OLAP.

IF NOT EXISTS indicates that if the table has not been created before, it will be created. Note that this only checks if the table name exists and does not check if the schema of the new table is the same as the schema of an existing table. Therefore, if there is a table with the same name but a different schema, this command will also return successfully, but it does not mean that a new table with a new schema has been created.

View partitions

View the partiton information of a table by running the show create table command.

1. > show create table  example_range_tbl
2. +-------------------+---------------------------------------------------------------------------------------------------------+
3. | Table             | Create Table                                                                                            |
4. +-------------------+---------------------------------------------------------------------------------------------------------+
5. | example_range_tbl | CREATE TABLE `example_range_tbl` (                                                                      |
6. |                   |   `user_id` largeint(40) NOT NULL COMMENT 'User ID',                                                     |
7. |                   |   `date` date NOT NULL COMMENT 'Date when the data are imported',                                                      |
8. |                   |   `timestamp` datetime NOT NULL COMMENT 'Timestamp when the data are imported',                                             |
9. |                   |   `city` varchar(20) NULL COMMENT 'User location city',                                                       |
10. |                   |   `age` smallint(6) NULL COMMENT 'User age',                                                            |
11. |                   |   `sex` tinyint(4) NULL COMMENT 'User gender',                                                             |
12. |                   |   `last_visit_date` datetime REPLACE NULL DEFAULT "1970-01-01 00:00:00" COMMENT 'User last visit time', |
13. |                   |   `cost` bigint(20) SUM NULL DEFAULT "0" COMMENT 'Total user consumption',                                          |
14. |                   |   `max_dwell_time` int(11) MAX NULL DEFAULT "0" COMMENT 'Maximum user dwell time',                             |
15. |                   |   `min_dwell_time` int(11) MIN NULL DEFAULT "99999" COMMENT 'Minimum user dwell time'                          |
16. |                   | ) ENGINE=OLAP                                                                                           |
17. |                   | AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)                                     |
18. |                   | COMMENT 'OLAP'                                                                                          |
19. |                   | PARTITION BY RANGE(`date`)                                                                              |
20. |                   | (PARTITION p201701 VALUES [('0000-01-01'), ('2017-02-01')),                                             |
21. |                   | PARTITION p201702 VALUES [('2017-02-01'), ('2017-03-01')),                                              |
22. |                   | PARTITION p201703 VALUES [('2017-03-01'), ('2017-04-01')))                                              |
23. |                   | DISTRIBUTED BY HASH(`user_id`) BUCKETS 16                                                               |
24. |                   | PROPERTIES (                                                                                            |
25. |                   | "replication_allocation" = "tag.location.default: 1",                                                   |
26. |                   | "is_being_synced" = "false",                                                                            |
27. |                   | "storage_format" = "V2",                                                                                |
28. |                   | "light_schema_change" = "true",                                                                         |
29. |                   | "disable_auto_compaction" = "false",                                                                    |
30. |                   | "enable_single_replica_compaction" = "false"                                                            |
31. |                   | );                                                                                                      |
32. +-------------------+---------------------------------------------------------------------------------------------------------+

Or run the show partitions from your_table command.

1. > show partitions from example_range_tbl
2. +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------
3. +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
4. | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime  | State  | PartitionKey | Range                                                                          | DistributionKey | Buckets | ReplicationNum | StorageMedium
5. | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation       | IsMutable |
6. +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------
7. +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
8. | 28731       | p201701       | 1              | 2024-01-25 10:50:51 | NORMAL | date         | [types: [DATEV2]; keys: [0000-01-01]; ..types: [DATEV2]; keys: [2017-02-01]; ) | user_id         | 16      | 1              | HDD
9. | 9999-12-31 23:59:59 |                     |                    | 0.000    | false      | tag.location.default: 1 | true      |
10. | 28732       | p201702       | 1              | 2024-01-25 10:50:51 | NORMAL | date         | [types: [DATEV2]; keys: [2017-02-01]; ..types: [DATEV2]; keys: [2017-03-01]; ) | user_id         | 16      | 1              | HDD
11. | 9999-12-31 23:59:59 |                     |                    | 0.000    | false      | tag.location.default: 1 | true      |
12. | 28733       | p201703       | 1              | 2024-01-25 10:50:51 | NORMAL | date         | [types: [DATEV2]; keys: [2017-03-01]; ..types: [DATEV2]; keys: [2017-04-01]; ) | user_id         | 16      | 1              | HDD
13. | 9999-12-31 23:59:59 |                     |                    | 0.000    | false      | tag.location.default: 1 | true      |
14. +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------
15. +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+

Alter partitions

You can add a new partition by running the alter table add partition command.

1. ALTER TABLE example_range_tbl ADD  PARTITION p201704 VALUES LESS THAN("2020-05-01") DISTRIBUTED BY HASH(`user_id`) BUCKETS 5;

For more information about how to alter partitions, refer to ALTER-TABLE-PARTITION.

Manual partitioning

Partition columns

  • Partition columns can be specified as one or multiple columns, and the partition columns must be KEY columns. The usage of multi-column partitioning will be introduced later in the summary section of multi-column partitioning.
  • When allowPartitionColumnNullable is set to true, Range partition supports the use of NULL partition columns. List Partition does not support NULL partition columns at all times.
  • Regardless of the type of partition column, double quotes are required when writing partition values.
  • There is theoretically no upper limit on the number of partitions.
  • When creating a table without partitioning, the system will automatically generate a full-range partition with the same name as the table name. This partition is not visible to users and cannot be deleted or modified.
  • Overlapping ranges are not allowed when creating partitions.

RANGE partitioning

Partition columns are usually time columns for convenient management of old and new data. RANGE partitioning supports column types such as DATE, DATETIME, TINYINT, SMALLINT, INT, BIGINT, and LARGEINT.

Partition information supports the following four writing methods:

  • FIXED RANGE: This method defines the partition as a left-closed, right-open interval.
1. PARTITION BY RANGE(col1[, col2, ...])
2. (
3. PARTITION partition_name1 VALUES [("k1-lower1", "k2-lower1", "k3-lower1",...), ("k1-upper1", "k2-upper1", "k3-upper1", ...)),
4. PARTITION partition_name2 VALUES [("k1-lower1-2", "k2-lower1-2", ...), ("k1-upper1-2", MAXVALUE, ))
5. )

For example:

1. PARTITION BY RANGE(`date`)
2. (
3. PARTITION `p201701` VALUES [("2017-01-01"),  ("2017-02-01")),
4. PARTITION `p201702` VALUES [("2017-02-01"), ("2017-03-01")),
5. PARTITION `p201703` VALUES [("2017-03-01"), ("2017-04-01"))
6. )
  • LESS THAN: This method only defines the upper bound of the partition. The lower bound is determined by the upper bound of the previous partition.
1. PARTITION BY RANGE(col1[, col2, ...])
2. (
3. PARTITION partition_name1 VALUES LESS THAN MAXVALUE | ("value1", "value2", ...),
4. PARTITION partition_name2 VALUES LESS THAN MAXVALUE | ("value1", "value2", ...)
5. )

For example:

1. PARTITION BY RANGE(`date`)
2. (
3. PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
4. PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
5. PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
6. )

8. PARTITION BY RANGE(`date`)
9. (
10. PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
11. PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
12. PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
13. PARTITION `other` VALUES LESS THAN (MAXVALUE)
14. )
  • BATCH RANGE: This method batch creates partitions based on ranges of number or time, defining the partitions as left-closed, right-open intervals and setting the step size.
1. PARTITION BY RANGE(int_col)
2. (
3. FROM (start_num) TO (end_num) INTERVAL interval_value
4. )

6. PARTITION BY RANGE(date_col)
7. (
8. FROM ("start_date") TO ("end_date") INTERVAL num YEAR | num MONTH | num WEEK | num DAY ｜ 1 HOUR
9. )

For example:

1. PARTITION BY RANGE(age)
2. (
3. FROM (1) TO (100) INTERVAL 10
4. )

6. PARTITION BY RANGE(`date`)
7. (
8. FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 2 YEAR
9. )
  • MULTI RANGE: This method batch creates partitions based on range partitioning, defining the partitions as left-closed, right-open intervals. For example:
1. PARTITION BY RANGE(col)
2. (
3. FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 1 YEAR,
4. FROM ("2021-11-14") TO ("2022-11-14") INTERVAL 1 MONTH,
5. FROM ("2022-11-14") TO ("2023-01-03") INTERVAL 1 WEEK,
6. FROM ("2023-01-03") TO ("2023-01-14") INTERVAL 1 DAY,
7. PARTITION p_20230114 VALUES [('2023-01-14'), ('2023-01-15'))
8. )

LIST partitioning

Partition columns based on LIST partitioning support data types such as BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, and VARCHAR. Partition values are enumerated values. Only when the data is one of the enumerated values of the target partition, the partition can be hit.

Partitions support specifying the enumerated values contained in each partition through VALUES IN (...).

For example:

1. PARTITION BY LIST(city)
2. (
3. PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
4. PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
5. PARTITION `p_jp` VALUES IN ("Tokyo")
6. )

LIST partitioning also supports multi-column partitioning, for example:

1. PARTITION BY LIST(id, city)
2. (
3. PARTITION p1_city VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
4. PARTITION p2_city VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
5. PARTITION p3_city VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
6. )

NULL partitioning

Partition columns based on NULL partitioning must be not null columns by default. If you need to use null columns, set the session variable allow_partition_column_nullable to true. For LIST partitioning, the NULL partitioning is supported, while for RANGE partitioning, null values will be assigned to the less than partition. The columns are as follows:

LIST partitioning

1. mysql> create table null_list(
2. -> k0 varchar null
3. -> )
4. -> partition by list (k0)
5. -> (
6. -> PARTITION pX values in ((NULL))
7. -> )
8. -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
9. -> properties("replication_num" = "1");
10. Query OK, 0 rows affected (0.11 sec)

12. mysql> insert into null_list values (null);
13. Query OK, 1 row affected (0.19 sec)

15. mysql> select * from null_list;
16. +------+
17. | k0   |
18. +------+
19. | NULL |
20. +------+
21. 1 row in set (0.18 sec)

RANGE partitioning with the less than partition

1. mysql> create table null_range(
2. -> k0 int null
3. -> )
4. -> partition by range (k0)
5. -> (
6. -> PARTITION p10 values less than (10),
7. -> PARTITION p100 values less than (100),
8. -> PARTITION pMAX values less than (maxvalue)
9. -> )
10. -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
11. -> properties("replication_num" = "1");
12. Query OK, 0 rows affected (0.12 sec)

14. mysql> insert into null_range values (null);
15. Query OK, 1 row affected (0.19 sec)

17. mysql> select * from null_range partition(p10);
18. +------+
19. | k0   |
20. +------+
21. | NULL |
22. +------+
23. 1 row in set (0.18 sec)

RANGE partitioning without the less than partition

1. mysql> create table null_range2(
2. -> k0 int null
3. -> )
4. -> partition by range (k0)
5. -> (
6. -> PARTITION p200 values [("100"), ("200"))
7. -> )
8. -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
9. -> properties("replication_num" = "1");
10. Query OK, 0 rows affected (0.13 sec)

12. mysql> insert into null_range2 values (null);
13. ERROR 5025 (HY000): Insert has filtered data in strict mode, tracking_url=......

Dynamic partitioning

Dynamic partitioning is designed to manage the lifecycle of partitions, reducing the burden on users.

Dynamic partitioning only supports RANGE partitioning based on DATE or DATETIME columns. It is applicable for cases where time data in partition columns grows synchronously with the real world. In such scenarios, data can be partitioned flexibly based on time data, and can be automatically stored with the cold-hot tiering strategy or recycled according to settings.

For partitioning method that can be more widely applicable, see Auto partitioning.

Data Partitioning - 图1Warning

This feature will be disabled when synchronized by CCR. If this table is copied by CCR, that is, PROPERTIES contains is_being_synced = true, it will be displayed as enabled in show create table, but will not actually take effect. When is_being_synced is set to false, these features will resume working, but the is_being_synced property is for CCR peripheral modules only and should not be manually set during CCR synchronization.

How to use

The rules for dynamic partitioning can be specified when the table is created or modified at runtime.

Currently, dynamic partition rules can only be set for partition tables with single partition columns.

  • Specified when creating table
1. CREATE TABLE tbl1
2. (...)
3. PROPERTIES
4. (
5. "dynamic_partition.prop1" = "value1",
6. "dynamic_partition.prop2" = "value2",
7. ...
8. )
  • Modify at runtime
1. ALTER TABLE tbl1 SET
2. (
3. "dynamic_partition.prop1" = "value1",
4. "dynamic_partition.prop2" = "value2",
5. ...
6. )

Rule parameters

The rules of dynamic partition are prefixed with dynamic_partition.:

  • dynamic_partition.enable

Whether to enable the dynamic partition feature. Can be specified as TRUE or FALSE. If not filled, the default is TRUE. If it is FALSE, Doris will ignore the dynamic partitioning rules of the table. - dynamic_partition.time_unit(required parameters)

The unit for dynamic partition scheduling. Can be specified as HOUR,DAY, WEEK, MONTH and YEAR, means to create or delete partitions by hour, day, week, month and year, respectively.

When specified as HOUR, the suffix format of the dynamically created partition name is yyyyMMddHH, for example, 2020032501. When the time unit is HOUR, the data type of partition column cannot be DATE.

When specified as DAY, the suffix format of the dynamically created partition name is yyyyMMdd, for example, 20200325.

When specified as WEEK, the suffix format of the dynamically created partition name is yyyy_ww. That is, the week of the year of current date. For example, the suffix of the partition created for 2020-03-25 is 2020_13, indicating that it is currently the 13th week of 2020.

When specified as MONTH, the suffix format of the dynamically created partition name is yyyyMM, for example, 202003.

When specified as YEAR, the suffix format of the dynamically created partition name is yyyy, for example, 2020. - dynamic_partition.time_zone

The time zone of the dynamic partition, if not filled in, defaults to the time zone of the current machine’s system, such as Asia/Shanghai, if you want to know the supported TimeZone, you can found in Timezone. - dynamic_partition.start

The starting offset of the dynamic partition, usually a negative number. Depending on the time_unit attribute, based on the current day (week / month), the partitions with a partition range before this offset will be deleted. If not filled, the default is -2147483648, that is, the history partition will not be deleted. - dynamic_partition.end(required parameters)

The end offset of the dynamic partition, usually a positive number. According to the difference of the time_unit attribute, the partition of the corresponding range is created in advance based on the current day (week / month). - dynamic_partition.prefix(required parameters)

The dynamically created partition name prefix. - dynamic_partition.buckets

The number of buckets corresponding to the dynamically created partitions. - dynamic_partition.replication_num

The replication number of dynamic partition.If not filled in, defaults to the number of table’s replication number. - dynamic_partition.start_day_of_week

When time_unit is WEEK, this parameter is used to specify the starting point of the week. The value ranges from 1 to 7. Where 1 is Monday and 7 is Sunday. The default is 1, which means that every week starts on Monday. - dynamic_partition.start_day_of_month

When time_unit is MONTH, this parameter is used to specify the start date of each month. The value ranges from 1 to 28. 1 means the 1st of every month, and 28 means the 28th of every month. The default is 1, which means that every month starts at 1st. The 29, 30 and 31 are not supported at the moment to avoid ambiguity caused by lunar years or months. - dynamic_partition.create_history_partition

The default is false. When set to true, Doris will automatically create all partitions, as described in the creation rules below. At the same time, the parameter max_dynamic_partition_num of FE will limit the total number of partitions to avoid creating too many partitions at once. When the number of partitions expected to be created is greater than max_dynamic_partition_num, the operation will fail.

When the start attribute is not specified, this parameter has no effect. - dynamic_partition.history_partition_num

When create_history_partition is true, this parameter is used to specify the number of history partitions. The default value is -1, which means it is not set. - dynamic_partition.hot_partition_num

Specify how many of the latest partitions are hot partitions. For hot partition, the system will automatically set its storage_medium parameter to SSD, and set storage_cooldown_time.

Data Partitioning - 图2tip

If there is no SSD disk path under the storage path, configuring this parameter will cause dynamic partition creation to fail.

hot_partition_num is all partitions in the previous n days and in the future.

Let us give an example. Suppose today is 2021-05-20, partition by day, and the properties of dynamic partition are set to: hot_partition_num=2, end=3, start=-3. Then the system will automatically create the following partitions, and set the storage_medium and storage_cooldown_time properties:

1. p20210517: ["2021-05-17", "2021-05-18") storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59 2. p20210518: ["2021-05-18", "2021-05-19") storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59 3. p20210519: ["2021-05-19", "2021-05-20") storage_medium=SSD storage_cooldown_time=2021-05-21 00:00:00 4. p20210520: ["2021-05-20", "2021-05-21") storage_medium=SSD storage_cooldown_time=2021-05-22 00:00:00 5. p20210521: ["2021-05-21", "2021-05-22") storage_medium=SSD storage_cooldown_time=2021-05-23 00:00:00 6. p20210522: ["2021-05-22", "2021-05-23") storage_medium=SSD storage_cooldown_time=2021-05-24 00:00:00 7. p20210523: ["2021-05-23", "2021-05-24") storage_medium=SSD storage_cooldown_time=2021-05-25 00:00:00 - dynamic_partition.reserved_history_periods

The range of reserved history periods. It should be in the form of [yyyy-MM-dd,yyyy-MM-dd],[...,...] while the dynamic_partition.time_unit is “DAY, WEEK, MONTH and YEAR”. And it should be in the form of [yyyy-MM-dd HH:mm:ss,yyyy-MM-dd HH:mm:ss],[...,...] while the dynamic_partition.time_unitis "HOUR". And no more spaces expected. The default value is“NULL”`, which means it is not set.

Let us give an example. Suppose today is 2021-09-06, partitioned by day, and the properties of dynamic partition are set to:

time_unit="DAY/WEEK/MONTH/YEAR", end=3, start=-3, reserved_history_periods="[2020-06-01,2020-06-20],[2020-10-31,2020-11-15]".

The system will automatically reserve following partitions in following period :

1. ["2020-06-01","2020-06-20"], 2. ["2020-10-31","2020-11-15"]

or

time_unit="HOUR", end=3, start=-3, reserved_history_periods="[2020-06-01 00:00:00,2020-06-01 03:00:00]".

The system will automatically reserve following partitions in following period :

1. ["2020-06-01 00:00:00","2020-06-01 03:00:00"]

Otherwise, every [...,...] in reserved_history_periods is a couple of properties, and they should be set at the same time. And the first date can’t be larger than the second one. - dynamic_partition.storage_medium

Data Partitioning - 图3Note

This parameteres is supported since Doris version 1.2.3

Specifies the default storage medium for the created dynamic partition. HDD is the default, SSD can be selected.

Note that when set to SSD, the hot_partition_num property will no longer take effect, all partitions will default to SSD storage media and the cooldown time will be 9999-12-31 23:59:59.

Create history partition rules

When create_history_partition is true, i.e. history partition creation is enabled, Doris determines the number of history partitions to be created based on dynamic_partition.start and dynamic_partition.history_partition_num.

Assuming the number of history partitions to be created is expect_create_partition_num, the number is as follows according to different settings.

  • create_history_partition = true

  • dynamic_partition.history_partition_num is not set, i.e. -1. expect_create_partition_num = end - start;

  • dynamic_partition.history_partition_num is set expect_create_partition_num = end - max(start, -history_partition_num);
  • create_history_partition = false

No history partition will be created, expect_create_partition_num = end - 0;

When expect_create_partition_num is greater than max_dynamic_partition_num (default 500), creating too many partitions is prohibited.

Examples:

Suppose today is 2021-05-20, partition by day, and the attributes of dynamic partition are set to create_history_partition=true, end=3, start=-3, history_partition_num=1, then the system will automatically create the following partitions.

1. p20210519
2. p20210520
3. p20210521
4. p20210522
5. p20210523

history_partition_num=5 and keep the rest attributes as in 1, then the system will automatically create the following partitions.

1. p20210517
2. p20210518
3. p20210519
4. p20210520
5. p20210521
6. p20210522
7. p20210523

history_partition_num=-1 i.e., if you do not set the number of history partitions and keep the rest of the attributes as in 1, the system will automatically create the following partitions.

1. p20210517
2. p20210518
3. p20210519
4. p20210520
5. p20210521
6. p20210522
7. p20210523

Example

  1. Table tbl1 partition column k1, type is DATE, create a dynamic partition rule. By day partition, only the partitions of the last 7 days are kept, and the partitions of the next 3 days are created in advance.

1. CREATE TABLE tbl1 2. ( 3. k1 DATE, 4. ... 5. ) 6. PARTITION BY RANGE(k1) () 7. DISTRIBUTED BY HASH(k1) 8. PROPERTIES 9. ( 10. "dynamic_partition.enable" = "true", 11. "dynamic_partition.time_unit" = "DAY", 12. "dynamic_partition.start" = "-7", 13. "dynamic_partition.end" = "3", 14. "dynamic_partition.prefix" = "p", 15. "dynamic_partition.buckets" = "32" 16. );

Suppose the current date is 2020-05-29. According to the above rules, tbl1 will produce the following partitions:

1. p20200529: ["2020-05-29", "2020-05-30") 2. p20200530: ["2020-05-30", "2020-05-31") 3. p20200531: ["2020-05-31", "2020-06-01") 4. p20200601: ["2020-06-01", "2020-06-02")

On the next day, 2020-05-30, a new partition will be created p20200602: [" 2020-06-02 "," 2020-06-03 ")

On 2020-06-06, because dynamic_partition.start is set to 7, the partition 7 days ago will be deleted, that is, the partition p20200529 will be deleted. 2. Table tbl1 partition column k1, type is DATETIME, create a dynamic partition rule. Partition by week, only keep the partition of the last 2 weeks, and create the partition of the next 2 weeks in advance.

1. CREATE TABLE tbl1 2. ( 3. k1 DATETIME, 4. ... 5. ) 6. PARTITION BY RANGE(k1) () 7. DISTRIBUTED BY HASH(k1) 8. PROPERTIES 9. ( 10. "dynamic_partition.enable" = "true", 11. "dynamic_partition.time_unit" = "WEEK", 12. "dynamic_partition.start" = "-2", 13. "dynamic_partition.end" = "2", 14. "dynamic_partition.prefix" = "p", 15. "dynamic_partition.buckets" = "8" 16. );

Suppose the current date is 2020-05-29, which is the 22nd week of 2020. The default week starts on Monday. Based on the above rules, tbl1 will produce the following partitions:

1. p2020_22: ["2020-05-25 00:00:00", "2020-06-01 00:00:00") 2. p2020_23: ["2020-06-01 00:00:00", "2020-06-08 00:00:00") 3. p2020_24: ["2020-06-08 00:00:00", "2020-06-15 00:00:00")

The start date of each partition is Monday of the week. At the same time, because the type of the partition column k1 is DATETIME, the partition value will fill the hour, minute and second fields, and all are 0.

On 2020-06-15, the 25th week, the partition 2 weeks ago will be deleted, ie p2020_22 will be deleted.

In the above example, suppose the user specified the start day of the week as "dynamic_partition.start_day_of_week" = "3", that is, set Wednesday as the start of week. The partition is as follows:

1. p2020_22: ["2020-05-27 00:00:00", "2020-06-03 00:00:00") 2. p2020_23: ["2020-06-03 00:00:00", "2020-06-10 00:00:00") 3. p2020_24: ["2020-06-10 00:00:00", "2020-06-17 00:00:00")

That is, the partition ranges from Wednesday of the current week to Tuesday of the next week.

Data Partitioning - 图4tip

2019-12-31 and 2020-01-01 are in same week, if the starting date of the partition is 2019-12-31, the partition name is p2019_53, if the starting date of the partition is 2020-01 -01, the partition name is p2020_01. 3. Table tbl1 partition column k1, type is DATE, create a dynamic partition rule. Partition by month without deleting historical partitions, and create partitions for the next 2 months in advance. At the same time, set the starting date on the 3rd of each month.

1. CREATE TABLE tbl1 2. ( 3. k1 DATE, 4. ... 5. ) 6. PARTITION BY RANGE(k1) () 7. DISTRIBUTED BY HASH(k1) 8. PROPERTIES 9. ( 10. "dynamic_partition.enable" = "true", 11. "dynamic_partition.time_unit" = "MONTH", 12. "dynamic_partition.end" = "2", 13. "dynamic_partition.prefix" = "p", 14. "dynamic_partition.buckets" = "8", 15. "dynamic_partition.start_day_of_month" = "3" 16. );

Suppose the current date is 2020-05-29. Based on the above rules, tbl1 will produce the following partitions:

1. p202005: ["2020-05-03", "2020-06-03") 2. p202006: ["2020-06-03", "2020-07-03") 3. p202007: ["2020-07-03", "2020-08-03")

Because dynamic_partition.start is not set, the historical partition will not be deleted.

Assuming that today is 2020-05-20, and set 28th as the start of each month, the partition range is:

1. p202004: ["2020-04-28", "2020-05-28") 2. p202005: ["2020-05-28", "2020-06-28") 3. p202006: ["2020-06-28", "2020-07-28")

Principle and control behavior

Apache Doris sets a fixed FE control thread that continuously checks the table based on dynamic partitioning at specific time intervals (specified by the dynamic_partition_check_interval_seconds field) to perform the necessary partition creation and deletion operations.

Specifically, when dynamic partitioning is executed, the following checks and operations are performed (refer to the start time of the partition as START and the end time as END, and omit dynamic_partition.):

  • All partitions before START are deleted.
  • If create_history_partition is false, create all partitions between the current time and END; if create_history_partition is true, not only all partitions between the current time and END are created, but also all partitions between START and current time are created. If history_partition_num is specified, the number of created partitions before current time cannot exceed the value of history_partition_num.

Note that:

  • If the partition time range intersects with the [START, END] range, it is considered to belong to the current dynamic partition time range.
  • If the newly created partition conflicts with an existing partition, the current partition is retained, and the new partition is not created. If the conflict occurs when the table is created, DDL will occur an error.

Therefore, after the automatic maintenance of the partition table, the state presented is as follows:

  • No partitions are included before the START time except for those specified in reserved_history_periods.
  • All manually created partitions after the END time are retained.
  • Apart from manually deleted or accidentally lost partitions, the table contains all partitions within a specific range:
  • If create_history_partition is true,
    • if history_partition_num is specified, the specific range is [max(START, current time) - history_partition_num * time_unit), END];
    • if history_partition_num is not specified, the specific range is [START, END].
  • If dynamic_partition.create_history_partition is false, the specific range is [current time, END], also including existing partitions in [START, current time). The entire specific range is divided into multiple partition ranges based on time_unit. If a range intersects with an existing partition X, X is preserved; otherwise, the range will be completely covered by a partition created by dynamic partition.
  • Unless the number of partitions is about to exceed max_dynamic_partition_num, the creation will fail.

Modify properties

You can modify the properties of the dynamic partitioning with the following command:

1. ALTER TABLE tbl1 SET
2. (
3. "dynamic_partition.prop1" = "value1",
4. ...
5. );

The modification of certain attributes may cause conflicts. Assume that the partition granularity was DAY and the following partitions have been created:

1. p20200519: ["2020-05-19", "2020-05-20")
2. p20200520: ["2020-05-20", "2020-05-21")
3. p20200521: ["2020-05-21", "2020-05-22")

If the partition granularity is changed to MONTH at this time, the system will try to create a partition with the range ["2020-05-01", "2020-06-01"), and this range conflicts with the existing partition. So it cannot be created. And the partition with the range ["2020-06-01", "2020-07-01") can be created normally. Therefore, the partition between 2020-05-22 and 2020-05-30 needs to be filled manually.

Check table scheduling status

You can further view the scheduling of dynamic partitioned tables by using the following command:

1. mysql> SHOW DYNAMIC PARTITION TABLES;
2. +-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
3. | TableName | Enable | TimeUnit | Start       | End  | Prefix | Buckets | StartOf   | LastUpdateTime | LastSchedulerTime   | State  | LastCreatePartitionMsg | LastDropPartitionMsg | ReservedHistoryPeriods  |
4. +-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
5. | d3        | true   | WEEK     | -3          | 3    | p      | 1       | MONDAY    | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | [2021-12-01,2021-12-31] |
6. | d5        | true   | DAY      | -7          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
7. | d4        | true   | WEEK     | -3          | 3    | p      | 1       | WEDNESDAY | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
8. | d6        | true   | MONTH    | -2147483648 | 2    | p      | 8       | 3rd       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
9. | d2        | true   | DAY      | -3          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
10. | d7        | true   | MONTH    | -2147483648 | 5    | p      | 8       | 24th      | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
11. +-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
12. 7 rows in set (0.02 sec)
  • LastUpdateTime: The last time of modifying dynamic partition properties
  • LastSchedulerTime: The last time of performing dynamic partition scheduling
  • State: The state of the last execution of dynamic partition scheduling
  • LastCreatePartitionMsg: Error message of the last time to dynamically add partition scheduling
  • LastDropPartitionMsg: Error message of the last execution of dynamic deletion partition scheduling

Advanced operations

Modify FE configuration items

  • dynamic\_partition\_enable

Whether to enable Doris’s dynamic partition feature. The default value is false, which is off. This parameter only affects the partitioning operation of dynamic partition tables, not normal tables. You can modify the parameters in fe.conf and restart FE to take effect. You can also execute the following commands at runtime to take effect:

``` 1. MySQL protocol:

  1. ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true")

  2. HTTP protocol:

  3. curl --location-trusted -u username:password -XGET http://fe_host:fe_http_port/api/_set_config?dynamic_partition_enable=true ```

To turn off dynamic partitioning globally, set this parameter to false. - dynamic\_partition\_check\_interval\_seconds

The execution frequency of dynamic partition threads defaults to 600 (10 minutes), that is, scheduling is performed every 10 minutes. You can modify the parameters in fe.conf and restart FE to take effect. You can also modify the following commands at runtime:

``` 1. MySQL protocol:

  1. ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "7200")

  2. HTTP protocol:

  3. curl --location-trusted -u username:password -XGET http://fe_host:fe_http_port/api/_set_config?dynamic_partition_check_interval_seconds=432000 ```

Switching between dynamic partitioning and manual partitioning

You can switch a table between dynamic and manual partitioning, but a table cannot be partitioned simultaneously by dynamic and manual partitioning.

By running the ALTER TABLE tbl_name SET ("dynamic_partition.enable" = "<true/false>") command, you can turn on and off dynamic partitioning.

When dynamic partitioning is turned off, Apache Doris will no longer manage partitions automatically, and users need to create or delete partitions manually by using ALTER TABLE; when dynamic partitioning is turned on, redundant partitions will be deleted according to the rules of dynamic partitioning.

Auto partitioning

Application scenario

The Auto Partitioning feature supports automatic detect

（本文超出站点单文档体积上限，已截断）