ApsaraDB for SelectDB:Dynamic partitioning

Overview

In specific scenarios, you may partition a table by day. If you manually manage partitions, data may fail to be imported to the table on the days that you do not create partitions for the table. This increases maintenance costs. To resolve this issue, you can use the dynamic partitioning feature to configure dynamic partitioning rules when you create a table. ApsaraDB for SelectDB creates or deletes partitions based on the configured rules. You can also modify the existing rules at runtime to manage partitions.

Create a dynamically partitioned table

Dynamic partitioning rules can be configured when you create a table or modify a table at runtime. Dynamic partitioning rules can be configured only for the tables that are partitioned based on a single partition key column. The following sample code provides an example on how to configure dynamic partitioning rules when you create a table:

CREATE TABLE tbl1
(...)
PROPERTIES
( 
	"dynamic_partition.prop1" = "value1",
 	"dynamic_partition.prop2" = "value2",
  ...
)

Global configuration items

The following configuration items related to dynamic partitioning apply to an ApsaraDB for SelectDB instance.

• dynamic_partition_enable

  Specifies whether to enable the dynamic partitioning feature. Default value: true. This parameter affects only the partitioning operations on dynamically partitioned tables and does not affect standard tables.

  You can run the following command at runtime to specify this parameter:

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

  If you want to globally disable the dynamic partitioning feature, set this parameter to false.

• dynamic_partition_check_interval_seconds

  The interval at which the dynamic partitioning thread is run. Unit: seconds. Default value: 600, which specifies that the dynamic partitioning thread is run every 10 minutes.

  You can run the following command at runtime to modify this parameter:

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

Table configuration items

The names of the parameters that are used for configuring dynamic partitioning rules are prefixed with dynamic_partition. The following table describes the parameters related to dynamic partitioning.

Examples on the setting of the dynamic_partition.reserved_history_periods parameter

For example, a table is partitioned by day.

• Specify the following dynamic partitioning parameters: time_unit="DAY/WEEK/MONTH", end=3, start=-3, reserved_history_periods="[2020-06-01,2020-06-20],[2020-10-31,2020-11-15]".

  The system automatically retains historical partitions in the following time range:

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

• Specify the following dynamic partitioning parameters: time_unit="HOUR", end=3, start=-3, reserved_history_periods="[2020-06-01 00:00:00,2020-06-01 03:00:00]".

  The system automatically retains historical partitions in the following time range:

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

  In the value of the reserved_history_periods parameter, each interval in the [...,...] format consists of a start time and an end time. Both the start time and end time must be specified, and the start time cannot be later than the end time.

Examples

• Configure dynamic partitioning rules for the tbl1 table whose partition key column is k1 of the DATE type. Data is partitioned by day. Only partitions within the previous seven days are retained, and partitions for the next three days are created in advance.

  CREATE TABLE tbl1
  (
  	k1 DATE,
  	k2 int
  )
  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.create_history_partition" = "true",
  	"dynamic_partition.buckets" = "32"
  );

  For example, the current date is 2020-05-29. Based on the preceding rules, the following partitions are created for the tbl1 table:

  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 next day, which is May 30, 2020, a new partition p20200602: ["2020-06-02", "2020-06-03") is created.

  On June 06, 2020, because the dynamic_partition.start parameter is set to -7, the partitions that were created seven days ago are deleted. In this case, the p20200529 partition is deleted.

• Configure dynamic partitioning rules for the tbl1 table whose partition key column is k1 of the DATETIME type. Data is partitioned by week. Only partitions within the previous two weeks are retained, and partitions for the next two weeks are created in advance.

  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.create_history_partition" = "true",
  	"dynamic_partition.buckets" = "8"
  );

  For example, the current date is 2020-05-29, which is the twenty-second week of 2020. By default, the start point of each week is Monday. Based on the preceding rules, the following partitions are created for the tbl1 table:

  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 point of each partition is Monday. Because the k1 column is of the DATETIME type, the system uses 00:00:00 to supplement hour, minute, and second in partition key values.

  On June 15, 2020, which is the twenty-fifth week of 2020, the partitions that were created two weeks ago are deleted. In this case, the p2020_22 partition is deleted.

  In the preceding example, if the dynamic_partition.start_day_of_week parameter is set to 3, which specifies that Wednesday is the start point of each week, the following partitions are created:

  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")

  The created partitions are in the time range from Wednesday of the current week to Tuesday of the next week.

  Note

  2019-12-31 and 2020-01-01 belong to the same week. If the start point of a partition is 2019-12-31, the partition is named p2019_53. If the start point of a partition is 2020-01-01, the partition is named p2020_01.

• Configure dynamic partitioning rules for the tbl1 table whose partition key column is k1 of the DATE type. Data is partitioned by month. No historical partitions are deleted, and partitions for the next two months are created in advance. In addition, the third day of each month is specified as the start point of each month.

  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.create_history_partition" = "true",
          "dynamic_partition.history_partition_num" = "6",
  	"dynamic_partition.buckets" = "8",
  	"dynamic_partition.start_day_of_month" = "3"
  );

  For example, the current date is 2020-05-29. Based on the preceding rules, the following partitions are created for the tbl1 table:

  p202005: ["2020-05-03", "2020-06-03")
  p202006: ["2020-06-03", "2020-07-03")
  p202007: ["2020-07-03", "2020-08-03")

  Because the dynamic_partition.start parameter is left empty, no historical partitions are deleted.

  For example, if the current date is 2020-05-20 and the start point is the twenty-eighth day of each month, the following partitions are created:

  p202004: ["2020-04-28", "2020-05-28")
  p202005: ["2020-05-28", "2020-06-28")
  p202006: ["2020-06-28", "2020-07-28")

Modify dynamic partitioning parameters

Execute the following statement at runtime to modify dynamic partitioning parameters:

ALTER TABLE tbl1 SET( "dynamic_partition.prop1" = "value1", ...);

If you modify specific dynamic partitioning parameters, conflicts may occur. For example, data is partitioned by day and the following partitions are created:

p20200519: ["2020-05-19", "2020-05-20")
p20200520: ["2020-05-20", "2020-05-21")
p20200521: ["2020-05-21", "2020-05-22")

If you partition data by month, the system attempts to create a partition whose range is ["2020-05-01", "2020-06-01"). However, the range of this partition conflicts with the ranges of existing partitions. Therefore, the partition cannot be created. A partition whose range is ["2020-06-01", "2020-07-01") can be created. In this case, you must create partitions for the time range from 2020-05-22 to 2020-05-30.

Delete the partitions in dynamically partitioned tables

Before you delete a partition in a dynamically partitioned table, you must execute the following statement to disable the dynamic partitioning feature:

ALTER TABLE tbl1 SET ("dynamic_partition.enable" = "false");

Then, execute the following statement to delete a specific partition:

ALTER TABLE tbl1 DROP PARTITION p20200519;

Generally, after you delete a partition in a dynamically partitioned table, you need to re-enable the dynamic partitioning feature so that ApsaraDB for SelectDB can continue to automatically manage partitions during subsequent business operations.

ALTER TABLE tbl1 SET ("dynamic_partition.enable" = "true");

Query the scheduling status of dynamically partitioned tables

Execute the following statement to query the scheduling status of all dynamically partitioned 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: the time when the dynamic partitioning parameters were last modified.

• LastSchedulerTime: the time when dynamic partition scheduling was last performed.

• State: the state of the last dynamic partition scheduling.

• LastCreatePartitionMsg: the error message that was returned when partitions were last dynamically created.

• LastDropPartitionMsg: the error message that was returned when partitions were last dynamically deleted.

Advanced operations

A dynamically partitioned table can be converted to a manually partitioned table. However, a table can only be dynamically partitioned or manually partitioned.

Convert a manually partitioned table to a dynamically partitioned table

If you do not configure dynamic partitioning rules for a table when you create the table, you can modify dynamic partitioning parameters by executing the ALTER TABLE statement at runtime to configure dynamic partitioning rules for the table.

After the dynamic partitioning feature is enabled, you can no longer manually manage partitions. Instead, the system automatically manages partitions based on the dynamic partitioning parameters that you specify.

Convert a dynamically partitioned table to a manually partitioned table

You can execute the ALTER TABLE tbl_name SET ("dynamic_partition.enable" = "false") statement to disable the dynamic partitioning feature and convert a dynamically partitioned table to a manually partitioned table.

After the dynamic partitioning feature is disabled, partitions are no longer automatically managed. You must execute the ALTER TABLE statement to create or delete partitions.

FAQ

• Q: What do I do if the "Could not create table with dynamic partition when fe config dynamic_partition_enable is false" error is reported when I create a dynamically partitioned table?

  A: You cannot create a dynamically partitioned table because the global parameter dynamic_partition_enable of the dynamic partitioning feature is set to false. Run the ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true") command to set this global parameter to true.

• Q: How do I specify the number of buckets for a dynamically created partition?

  A: Dynamically created partitions are automatically created by the internal scheduling process of the system. When a partition is automatically created for a table, the system uses the settings of dynamic partitioning parameters whose names are prefixed with dynamic_partition instead of the default parameter settings of the table. The applied dynamic partitioning parameters also specify the number of buckets in the partition. The following sample code provides an example:

  CREATE TABLE tbl1 (
  `k1` int,
  `k2` date
  )
  PARTITION BY RANGE(k2)()
  DISTRIBUTED BY HASH(k1) BUCKETS 3
  PROPERTIES
  (
  	"dynamic_partition.enable" = "true",
  	"dynamic_partition.time_unit" = "DAY",
  	"dynamic_partition.start" = "-3",
  	"dynamic_partition.end" = "3",
  	"dynamic_partition.prefix" = "p",
  	"dynamic_partition.create_history_partition" = "true",
  	"dynamic_partition.buckets" = "32"
  );

  In the preceding example, no initial partition is created because the PARTITION BY clause does not contain partition definitions. The following parameters are specified: DISTRIBUTED BY HASH(k1) BUCKETS 3 and "dynamic_partition.buckets" = "32".

  The former parameter setting is the default setting of the table, and the latter parameter setting is exclusive to dynamic partitioning. When the system automatically creates a partition, the number of buckets is set to 32 instead of 3. When you manually create a partition by executing the ALTER TABLE tbl1 ADD PARTITION statement, the number of buckets is set to 3. Dynamically created partitions use a separate set of parameter settings. The default parameter settings of the table are used only if no exclusive parameter settings are configured for dynamic partitioning.

  CREATE TABLE tbl3 (
  `k1` int,
  `k2` date
  )
  PARTITION BY RANGE(k2)(
  	PARTITION p1 VALUES LESS THAN ("2019-10-10")
  )
  DISTRIBUTED BY HASH(k1) BUCKETS 3
  PROPERTIES
  (
  	"dynamic_partition.enable" = "true",
  	"dynamic_partition.time_unit" = "DAY",
  	"dynamic_partition.start" = "-3",
  	"dynamic_partition.end" = "3",
  	"dynamic_partition.prefix" = "p",
          "dynamic_partition.create_history_partition" = "true",
  	"dynamic_partition.buckets" = "32"
  );

  In this example, the p1 partition is manually created. This partition uses the default parameter settings of the table and the number of buckets is set to 3. However, the partitions dynamically created by the system still use the parameter settings exclusive to dynamic partitioning and the number of buckets is set to 32.