Tencent Cloud

Recent Pages

Dynamic Partition

Last updated: 2024-06-27 11:09:10
Dynamic partition is to implement lifetime management (TTL) for table-level partitions to ease the user workload. Presently, the features of dynamically adding and deleting partitions have been implemented.
Dynamic partition only supports Range partition.
Principles
In some use cases, users partition the table by day and carry out routine tasks at a set time every day. It requires manual management of partitions by users; otherwise, failure to create partitions may lead to data import errors, imposing additional maintenance costs on users.
Through the dynamic partition feature, users can stipulate the rules of dynamic partition at the time of table creation. The Frontend (FE) will start a backend thread to create or delete partitions according to user-specified rules. Users can also change the existing rules during runtime.
Use Method
The rules of dynamic partition can be specified during table creation or modified during runtime. Currently, dynamic partition rules can only be set for tables with a single partition column.
Specification during table creation:
CREATE TABLE tbl1
(...)
PROPERTIES
(
      "dynamic_partition.prop1" = "value1",
      "dynamic_partition.prop2" = "value2",
      ...
)
Modification during runtime:
ALTER TABLE tbl1 SET
(
      "dynamic_partition.prop1" = "value1",
      "dynamic_partition.prop2" = "value2",
      ...
)
Dynamic partition rule parameters
The rule parameters for dynamic partition all start with dynamic_partition.:
Main Parameters
dynamic_partition.enable
Whether to enable the dynamic partition feature. This can be specified to be TRUE or FALSE. If it is empty, it defaults to TRUE. If FALSE, Doris will ignore the dynamic partition rules of this table.
dynamic_partition.time_unit
The unit of dynamic partition scheduling. It can be specified to be HOUR, DAY, WEEK, and MONTH. These respectively represent partition creation or deletion by day, week, and month.
When it is set to HOUR, the suffix format for dynamically created partition names is yyyyMMddHH, such as 2020032501. The partition column data type cannot be DATE when the unit is hourly.
When it is set to DAY, the suffix format for dynamically created partition names is yyyyMMdd, such as 20200325.
When it is set to WEEK, the suffix format for dynamically created partition names is yyyy_ww, i.e., which week of the year this date falls into, such as, for 2020-03-25, the partition name suffix is 2020_13, indicating that it is currently the 13th week of 2020.
When it is set to MONTH, the suffix format of the dynamically created partition name is yyyyMM, for example 202003.
dynamic_partition.start
The start offset of the dynamic partition, a negative number. Based on the time_unit property, with today (week/month) as the base, partitions before this offset will be deleted. If it is empty, it defaults to -2147483648, i.e., historical partitions will not be deleted.
dynamic_partition.end
The end offset of the dynamic partition, a positive number. Based on the time_unit property, with today (week/month) as the base, create the corresponding range of partitions in advance.
dynamic_partition.buckets
The number of buckets corresponding to the dynamically created partition.
dynamic_partition.replication_num
The number of replicas corresponding to the dynamically created partition. If it is empty, it defaults to the number of replicas specified when creating the table.
Other Parameters
dynamic_partition.prefix
The prefix of the dynamically created partition.
dynamic_partition.time_zone
The time zone of the dynamic partition. If it is empty, it defaults to the time zone of the current machine system, for example, Asia/Shanghai. If you want to access the current supported time zone setting, you can refer to https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
dynamic_partition.start_day_of_week
When time_unit is WEEK, this parameter is used to specify the starting point of each week. The value ranges from 1 to 7, where 1 means Monday, and 7 means Sunday. It defaults to 1, which means each week starts on Monday.
dynamic_partition.start_day_of_month
When time_unit is MONTH, this parameter is used to specify the starting date of each month. The value ranges from 1 to 28 where 1 means the 1st of each month, and 28 means the 28th of each month. It defaults to 1, which means each month starts on the 1st. It is currently not supported to start on the 29th, 30th, or 31st to avoid ambiguities due to leap years or leap months.
dynamic_partition.create_history_partition
It defaults to false. When it is set to true, Doris will automatically create all partitions, and the specific creation rules are detailed below. At the same time, the FE parameter max_dynamic_partition_num will limit the total quantity of partitions to avoid creating too many partitions at once. If the expected number of partitions to be created is greater than the max_dynamic_partition_num value, the operation will be forbidden.
This parameter does not take effect when the start property is not specified.
dynamic_partition.history_partition_num
When create_history_partition is true, this parameter is used to specify the number of historical partitions to create. It defaults to -1, meaning it is not set.
dynamic_partition.hot_partition_num
Specifies how many of the most recent partitions are hot partitions. For hot partitions, the system will automatically set the storage_medium parameter to SSD, and set the storage_cooldown_time.
Let's give an example. Suppose today is 2021-05-20. It is partitioned by day, and the settings for dynamic partitioning are: 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 parameters:
p20210517:["2021-05-17", "2021-05-18") storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59
p20210518:["2021-05-18", "2021-05-19") storage_medium=HDD storage_cooldown_time=9999-12-31 23:59:59
p20210519:["2021-05-19", "2021-05-20") storage_medium=SSD storage_cooldown_time=2021-05-21 00:00:00
p20210520:["2021-05-20", "2021-05-21") storage_medium=SSD storage_cooldown_time=2021-05-22 00:00:00
p20210521:["2021-05-21", "2021-05-22") storage_medium=SSD storage_cooldown_time=2021-05-23 00:00:00
p20210522:["2021-05-22", "2021-05-23") storage_medium=SSD storage_cooldown_time=2021-05-24 00:00:00
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 time range of the historical partitions to be retained. When the dynamic_partition.time_unit is set to "DAY/WEEK/MONTH", it needs to be set in the format of [yyyy-MM-dd,yyyy-MM-dd],[...,...]. When the dynamic_partition.time_unit is set to "HOUR", it needs to be set in the format of [yyyy-MM-dd HH:mm:ss,yyyy-MM-dd HH:mm:ss],[...,...]. If it is empty, it defaults to "NULL".
Let's give an example. Suppose today is 2021-09-06. It is partitioned by day, and the dynamic partition property setting is:
time_unit="DAY/WEEK/MONTH", end=3, start=-3, reserved_history_periods="[2020-06-01,2020-06-20],[2020-10-31,2020-11-15]"
Then the system will automatically retain:
["2020-06-01","2020-06-20"],
["2020-10-31","2020-11-15"]
Or the property setting for the dynamic partition is:
time_unit="HOUR", end=3, start=-3, reserved_history_periods="[2020-06-01 00:00:00,2020-06-01 03:00:00]"
Then the system will automatically retain:
["2020-06-01 00:00:00","2020-06-01 03:00:00"]
This partition for these two periods. Among them, each [..., ...] of reserved_history_periods is a pair of setting items, both of which need to be set at the same time, and the first time cannot be greater than the second time.
Creating Partition Rules for Historical Data
When create_history_partition is true, i.e., the feature of creating history partitions is enabled, Doris will decide the number of history partitions to be created based on dynamic_partition.start and dynamic_partition.history_partition_num.
Suppose the number of historical partitions to be created is expect_create_partition_num. The specific numbers vary according to different settings as follows:
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 will not create historical partitions, expect_create_partition_num = end - 0; When expect_create_partition_num is greater than max_dynamic_partition_num (default 500), the creation of too many partitions is prohibited.
Example:
1. Assume today is 2021-05-20, by daily partition. The property setting for dynamic partition is: If create_history_partition=true, end=3, start=-3, history_partition_num=1, the system will automatically create the following partitions:
p20210519
p20210520
p20210521
p20210522
p20210523
2. If history_partition_num=5, the rest properties are consistent with those in 1, the system will automatically create the following partitions:
p20210517
p20210518
p20210519
p20210520
p20210521
p20210522
p20210523
3. If history_partition_num=-1, i.e., do not set the number of history partitions, the rest properties are consistent with those in 1, the system will automatically create the following partitions:
p20210517
p20210518
p20210519
p20210520
p20210521
p20210522
p20210523
Notes
During the use of dynamic partitions, if some partitions between dynamic_partition.start and dynamic_partition.end are lost due to some unexpected circumstances, the lost partitions between the current time and dynamic_partition.end will be recreate, and the lost partitions between dynamic_partition.start and the current time will not be replenished.
Sample code
1. For table tbl1, partition column k1 is of DATE type. Create a dynamic partition rule. Partition by days, only retain the partitions of the last 7 days, and pre-create the partitions of the next 3 days.
CREATE TABLE tbl1
(
 k1 DATE,
 ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
 "dynamic_partition.enable" = "true",
 "dynamic_partition.time_unit" = "DAY",
 "dynamic_partition.start" = "-7",
 "dynamic_partition.end" = "3",
 "dynamic_partition.prefix" = "p",
 "dynamic_partition.buckets" = "32"
);
Assume the current date is 2020-05-29. Then according to the above rules, tbl1 will generate the following partitions:
p20200529: ["2020-05-29", "2020-05-30")
p20200530: ["2020-05-30", "2020-05-31")
p20200531: ["2020-05-31", "2020-06-01")
p20200601: ["2020-06-01", "2020-06-02")
On the second day, namely 2020-05-30, a new partition will be created p20200602: ["2020-06-02", "2020-06-03") 
At 2020-06-06, because dynamic_partition.start is set to 7, the partition from 7 days ago will be deleted, namely the partition p20200529 is deleted.
2. For table tbl1, partition column k1 is of DATETIME type. Create a dynamic partition rule. Partition by weeks, keeping only the partitions of the last 2 weeks, and pre-create the next 2 weeks' partitions.
CREATE TABLE tbl1
(
     k1 DATETIME,
     ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
     "dynamic_partition.enable" = "true",
     "dynamic_partition.time_unit" = "WEEK",
     "dynamic_partition.start" = "-2",
     "dynamic_partition.end" = "2",
     "dynamic_partition.prefix" = "p",
     "dynamic_partition.buckets" = "8"
);
Suppose the current date is 2020-05-29, which is the 22nd week of 2020. By default, Monday is the start of each week. Then, based on the above rules, tbl1 will generate the following partitions:
p2020_22: ["2020-05-25 00:00:00", "2020-06-01 00:00:00")
p2020_23: ["2020-06-01 00:00:00", "2020-06-08 00:00:00")
p2020_24: ["2020-06-08 00:00:00", "2020-06-15 00:00:00")
The start date of each partition is the Monday of the current week. Also, because the partition column k1 is of DATETIME type, the partition value will complete the hour, minute, and second parts to 0.
On 2020-06-15, which is the 25th week, the partition from 2 weeks ago will be deleted, namely the p2020_22 partition will be deleted.
In the above examples, suppose the user specified the start day of the week to be "dynamic_partition.start_day_of_week" = "3", namely set every Wednesday as the start day. Then the partitions are as follows:
p2020_22: ["2020-05-27 00:00:00", "2020-06-03 00:00:00")
p2020_23: ["2020-06-03 00:00:00", "2020-06-10 00:00:00")
p2020_24: ["2020-06-10 00:00:00", "2020-06-17 00:00:00")
That is, the partition range is from Wednesday of the current week to Tuesday of the next week.
Note
 2019-12-31 and 2020-01-01 are in the same week. If the start date of the partition is 2019-12-31, and the partition name is p2019_53. If the start date of the partition is 2020-01-01, the partition name is p2020_01.
3. For table tbl1, partition column k1 is of DATE type. Create a dynamic partition rule. Partition by month, do not delete historical partitions and pre-create the next 2 months' partitions. Furthermore, set the 3rd of each month as the start date.
CREATE TABLE tbl1
(
     k1 DATE,
     ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
     "dynamic_partition.enable" = "true",
     "dynamic_partition.time_unit" = "MONTH",
     "dynamic_partition.end" = "2",
     "dynamic_partition.prefix" = "p",
     "dynamic_partition.buckets" = "8",
     "dynamic_partition.start_day_of_month" = "3"
);
Assume the current date is 2020-05-29. Then based on the above rules, tbl1 will produce the following partitions:
p202005: ["2020-05-03", "2020-06-03")
p202006: ["2020-06-03", "2020-07-03")
p202007: ["2020-07-03", "2020-08-03")
Because dynamic_partition.start is not set, historical partitions will not be deleted.
Suppose today is 2020-05-20, and set the 28th of each month as the start date. The partition range is:
p202004: ["2020-04-28", "2020-05-28")
p202005: ["2020-05-28", "2020-06-28")
p202006: ["2020-06-28", "2020-07-28")
Changing Dynamic Partition Properties
The following command can be used to change the properties of dynamic partitions:
ALTER TABLE tbl1 SET
(
    "dynamic_partition.prop1" = "value1",
    ...
);
Some property modifications may conflict with each other. Suppose the previous partition granularity is DAY, and the following partitions have been created:
p20200519: ["2020-05-19", "2020-05-20")
p20200520: ["2020-05-20", "2020-05-21")
p20200521: ["2020-05-21", "2020-05-22")
If the partition granularity is now changed to MONTH, the system will try to create a partition with a range of ["2020-05-01", "2020-06-01"), but the range of this partition conflicts with an existing partition, so it cannot be created. However, a partition with a range of ["2020-06-01", "2020-07-01") can be created normally. Therefore, partitions in the time period from 2020-05-22 to 2020-05-30 need to be filled in manually.
Viewing Scheduling Situation of Dynamic Partition Tables
The following command can be used to further view the scheduling situation of all dynamic partition tables in the current database:
mysql> SHOW DYNAMIC PARTITION TABLES;
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
| TableName | Enable | TimeUnit | Start       | End  | Prefix | Buckets | StartOf   | LastUpdateTime | LastSchedulerTime   | State  | LastCreatePartitionMsg | LastDropPartitionMsg | ReservedHistoryPeriods  |
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
| 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] |
| d5        | true   | DAY      | -7          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d4        | true   | WEEK     | -3          | 3    | p      | 1       | WEDNESDAY | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    | 
| d6        | true   | MONTH    | -2147483648 | 2    | p      | 8       | 3rd       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d2        | true   | DAY      | -3          | 3    | p      | 32      | N/A       | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
| d7        | true   | MONTH    | -2147483648 | 5    | p      | 8       | 24th      | N/A            | 2020-05-25 14:29:24 | NORMAL | N/A                    | N/A                  | NULL                    |
+-----------+--------+----------+-------------+------+--------+---------+-----------+----------------+---------------------+--------+------------------------+----------------------+-------------------------+
7 rows in set (0.02 sec)
LastUpdateTime: Time of the last modification of the dynamic partition property.
LastSchedulerTime: Time of the last execution of the dynamic partition scheduling.
State: Status of the last dynamic partition scheduling execution.
LastCreatePartitionMsg: Error message from the last dynamic partition addition scheduling execution.
LastDropPartitionMsg: Error message from the last dynamic partition deletion scheduling execution.
Advanced Operations
FE configuration item
dynamic_partition_enable
Controls whether to enable the dynamic partition feature of Doris. By default, it is false, which means it is disabled. This parameter only affects the partition operation of the dynamic partition table and does not affect the regular table. It can be modified by adjusting the parameters in the fe.conf file and restarting the FE to take effect. It can also be modified by running the following command:
MySQL protocol:
ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true") 
HTTP protocol:
curl --location-trusted -u username:password -XGET http://fe_host:fe_http_port/api/_set_config?dynamic_partition_enable=true
If you want to globally disable the dynamic partition, just set this parameter to false.
dynamic_partition_check_interval_seconds
Frequency of dynamic partition thread execution, which defaults to 600 (10 minutes), meaning it is scheduled every 10 minutes. It can be adjusted by modifying the parameters in the fe.conf file and restarting the FE. It can also be modified by running the following command:
MySQL protocol:
ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds" = "7200") 
HTTP protocol:
curl --location-trusted -u username:password -XGET http://fe_host:fe_http_port/api/_set_config?dynamic_partition_check_interval_seconds=432000
Converting Between Dynamic Partition Tables and Manual Partition Tables
For a table, dynamic partition and manual partition can freely convert, but they cannot coexist. There is only one status.
Converting from Manual Partition to Dynamic Partition
If a table is created without specifying a dynamic partition, the dynamic partition properties can be changed with the ALTER TABLE command at runtime to convert it to a dynamic partition. Detailed examples can be viewed using the HELP ALTER TABLE command.
After the dynamic partition feature, is enabled Doris will no longer allow users to manually manage partitions, but will automatically manage partitions according to dynamic partition property.
Note
 If dynamic_partition.start has been set, the historical partitions that preceded the start offset of the dynamic partition will be deleted.
Converting from Dynamic Partition to Manual Partition
You can disable the dynamic partition feature and convert it into a manual partition table by executing the ALTER TABLE tbl_name SET ("dynamic_partition.enable" = "false") command.
After the dynamic partition feature is disabled, Doris will no longer automatically manage partitions. Instead, users must manually create or delete partitions using the ALTER TABLE command.
FAQs
1. After you create a dynamic partition table, the message Could not create table with dynamic partition when fe config dynamic_partition_enable is false appears.
The dynamic partition feature could not create a dynamic partition table because the overall switch for dynamic partition, the FE configuration dynamic_partition_enable is set to false.
At this point, modify the FE configuration file, add a line dynamic_partition_enable=true, and restart the FE. Alternatively, execute the command ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true") to enable the dynamic partition feature.
2. Relationship Between Bucketing and Replicas Parameters of Dynamic Partition and Corresponding Parameters of Table
2.1 The bucketing and replicas parameters of the dynamic partition are independent of the table's bucketing and replicas parameters, and need to be set separately. When a dynamic partition is created, it uses the bucketing and replicas parameters of the dynamic partition, while a manually created partition uses the bucketing and replicas parameters of the table.
2.2 Only when the dynamic partition feature is enabled without setting bucketing and replicas parameters, these two parameters will be set to the bucketing and replicas parameters of the table. After this, the two sets of parameters are independent.
3. Can partitions be manually edited after enabling the dynamic partition feature?
No. The dynamic partition feature needs to be disabled to manually edit partitions.
Contact Us
Contact our sales team or business advisors to help your business.
Technical Support
Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.
7x24 Phone Support
tencent cloud

Recent Pages

Dynamic Partition

Principles

Use Method

Dynamic partition rule parameters

Main Parameters

Other Parameters

Creating Partition Rules for Historical Data

Notes

Sample code

Changing Dynamic Partition Properties

Viewing Scheduling Situation of Dynamic Partition Tables

Advanced Operations

FE configuration item

Converting Between Dynamic Partition Tables and Manual Partition Tables

Converting from Manual Partition to Dynamic Partition

Converting from Dynamic Partition to Manual Partition

FAQs

Was this page helpful?

Was this page helpful?

tencent cloud

Sign Up

Log in

Recent Pages

Dynamic Partition

Principles

Use Method

Dynamic partition rule parameters

Main Parameters

Other Parameters

Creating Partition Rules for Historical Data

Notes

Sample code

Changing Dynamic Partition Properties

Viewing Scheduling Situation of Dynamic Partition Tables

Advanced Operations

FE configuration item

Converting Between Dynamic Partition Tables and Manual Partition Tables

Converting from Manual Partition to Dynamic Partition

Converting from Dynamic Partition to Manual Partition

FAQs

Was this page helpful?

Was this page helpful?
