AUTO PARTITION

SinceVersion 2.1

The Auto Partitioning feature supports automatic detection of whether the corresponding partition exists during the data import process. If it does not exist, the partition will be created automatically and imported normally.

Usage Scenarios

The auto partition function mainly solves the problem that the user expects to partition the table based on a certain column, but the data distribution of the column is scattered or unpredictable, so it is difficult to accurately create the required partitions when building or adjusting the structure of the table, or the number of partitions is so large that it is too cumbersome to create them manually.

Take the time type partition column as an example, in the Dynamic Partition function, we support the automatic creation of new partitions to accommodate real-time data at specific time periods. For real-time user behavior logs and other scenarios, this feature basically meets the requirements. However, in more complex scenarios, such as dealing with non-real-time data, the partition column is independent of the current system time and contains a large number of discrete values. At this time to improve efficiency we want to partition the data based on this column, but the data may actually involve the partition can not be grasped in advance, or the expected number of required partitions is too large. In this case, dynamic partitioning or manually created partitions can not meet our needs, automatic partitioning function is very good to cover such needs.

Suppose our table DDL is as follows:

1. CREATE TABLE `DAILY_TRADE_VALUE`
2. (
3. `TRADE_DATE`              datev2 NOT NULL COMMENT '交易日期',
4. `TRADE_ID`                varchar(40) NOT NULL COMMENT '交易编号',
5. ......
6. )
7. UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
8. PARTITION BY RANGE(`TRADE_DATE`)
9. (
10. PARTITION p_2000 VALUES [('2000-01-01'), ('2001-01-01')),
11. PARTITION p_2001 VALUES [('2001-01-01'), ('2002-01-01')),
12. PARTITION p_2002 VALUES [('2002-01-01'), ('2003-01-01')),
13. PARTITION p_2003 VALUES [('2003-01-01'), ('2004-01-01')),
14. PARTITION p_2004 VALUES [('2004-01-01'), ('2005-01-01')),
15. PARTITION p_2005 VALUES [('2005-01-01'), ('2006-01-01')),
16. PARTITION p_2006 VALUES [('2006-01-01'), ('2007-01-01')),
17. PARTITION p_2007 VALUES [('2007-01-01'), ('2008-01-01')),
18. PARTITION p_2008 VALUES [('2008-01-01'), ('2009-01-01')),
19. PARTITION p_2009 VALUES [('2009-01-01'), ('2010-01-01')),
20. PARTITION p_2010 VALUES [('2010-01-01'), ('2011-01-01')),
21. PARTITION p_2011 VALUES [('2011-01-01'), ('2012-01-01')),
22. PARTITION p_2012 VALUES [('2012-01-01'), ('2013-01-01')),
23. PARTITION p_2013 VALUES [('2013-01-01'), ('2014-01-01')),
24. PARTITION p_2014 VALUES [('2014-01-01'), ('2015-01-01')),
25. PARTITION p_2015 VALUES [('2015-01-01'), ('2016-01-01')),
26. PARTITION p_2016 VALUES [('2016-01-01'), ('2017-01-01')),
27. PARTITION p_2017 VALUES [('2017-01-01'), ('2018-01-01')),
28. PARTITION p_2018 VALUES [('2018-01-01'), ('2019-01-01')),
29. PARTITION p_2019 VALUES [('2019-01-01'), ('2020-01-01')),
30. PARTITION p_2020 VALUES [('2020-01-01'), ('2021-01-01')),
31. PARTITION p_2021 VALUES [('2021-01-01'), ('2022-01-01'))
32. )
33. DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
34. PROPERTIES (
35. "replication_num" = "1"
36. );

The table stores a large amount of business history data, partitioned based on the date the transaction occurred. As you can see when building the table, we need to manually create the partitions in advance. If the data range of the partitioned columns changes, for example, 2022 is added to the above table, we need to create a partition by ALTER-TABLE-PARTITION to make changes to the table partition. If such partitions need to be changed, or subdivided at a finer level of granularity, it is very tedious to modify them. At this point we can rewrite the table DDL using AUTO PARTITION.

Grammar

When building a table, use the following syntax to populate CREATE-TABLE with the partition_info section:

  1. AUTO RANGE PARTITION:

1. AUTO PARTITION BY RANGE FUNC_CALL_EXPR 2. ( 3. )

where

1. FUNC_CALL_EXPR ::= date_trunc ( <partition_column>, '<interval>' ) 2. AUTO LIST PARTITION:

1. AUTO PARTITION BY LIST(`partition_col`) 2. ( 3. )

Usage example

  1. AUTO RANGE PARTITION

1. CREATE TABLE `date_table` ( 2. `TIME_STAMP` datev2 NOT NULL COMMENT 'Date of collection' 3. ) ENGINE=OLAP 4. DUPLICATE KEY(`TIME_STAMP`) 5. AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month') 6. ( 7. ) 8. DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10 9. PROPERTIES ( 10. "replication_allocation" = "tag.location.default: 1" 11. ); 2. AUTO LIST PARTITION

1. CREATE TABLE `str_table` ( 2. `str` varchar not null 3. ) ENGINE=OLAP 4. DUPLICATE KEY(`str`) 5. AUTO PARTITION BY LIST (`str`) 6. ( 7. ) 8. DISTRIBUTED BY HASH(`str`) BUCKETS 10 9. PROPERTIES ( 10. "replication_allocation" = "tag.location.default: 1" 11. );

Using constraints

  1. The partition column for AUTO PARTITION must be a NOT NULL column;
  2. In an AUTO LIST PARTITION, the length of the partition name must not exceed 50. This length is derived from the splicing and escaping of the contents of the partition columns on the corresponding rows of data, so the actual allowable length may be shorter；
  3. In AUTO RANGE PARTITION, the partition function supports only date_trunc and the partition column supports only DATE or DATETIME type;
  4. In AUTO LIST PARTITION, function calls are not supported. Partitioned columns support BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR data-types, and partitioned values are enum values;
  5. In AUTO LIST PARTITION, a separate new PARTITION is created for each fetch of a partition column for which the corresponding partition does not currently exist.

Sample Scenarios

In the example in the Usage Scenarios section, the table DDL can be rewritten after using AUTO PARTITION:

1. CREATE TABLE `DAILY_TRADE_VALUE`
2. (
3. `TRADE_DATE`              datev2 NOT NULL,
4. `TRADE_ID`                varchar(40) NOT NULL,
5. ......
6. )
7. UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)
8. AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')
9. (
10. )
11. DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10
12. PROPERTIES (
13. "replication_num" = "1"
14. );

At this point the new table does not have a default partition:

1. mysql> show partitions from `DAILY_TRADE_VALUE`;
2. Empty set (0.12 sec)

After inserting the data and then viewing it again, we could found that the table has been created with corresponding partitions:

1. mysql> insert into `DAILY_TRADE_VALUE` values ('2012-12-13', 1), ('2008-02-03', 2), ('2014-11-11', 3);
2. Query OK, 3 rows affected (0.88 sec)

4. mysql> show partitions from `DAILY_TRADE_VALUE`;
5. +-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
6. | PartitionId | PartitionName   | VisibleVersion | VisibleVersionTime  | State  | PartitionKey | Range                                                                          | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation       | IsMutable |
7. +-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
8. | 180060      | p20080101000000 | 2              | 2023-09-18 21:49:29 | NORMAL | TRADE_DATE   | [types: [DATEV2]; keys: [2008-01-01]; ..types: [DATEV2]; keys: [2009-01-01]; ) | TRADE_DATE      | 10      | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 0.000    | false      | tag.location.default: 1 | true      |
9. | 180039      | p20120101000000 | 2              | 2023-09-18 21:49:29 | NORMAL | TRADE_DATE   | [types: [DATEV2]; keys: [2012-01-01]; ..types: [DATEV2]; keys: [2013-01-01]; ) | TRADE_DATE      | 10      | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 0.000    | false      | tag.location.default: 1 | true      |
10. | 180018      | p20140101000000 | 2              | 2023-09-18 21:49:29 | NORMAL | TRADE_DATE   | [types: [DATEV2]; keys: [2014-01-01]; ..types: [DATEV2]; keys: [2015-01-01]; ) | TRADE_DATE      | 10      | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 0.000    | false      | tag.location.default: 1 | true      |
11. +-------------+-----------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
12. 3 rows in set (0.12 sec)

A partition created by the AUTO PARTITION function has the exact same functional properties as a manually created partition.

Coupled with dynamic partitioning

AUTO PARTITION is supported on the same table as DYNAMIC PARTITION. for example:

1. CREATE TABLE tbl3
2. (
3. k1 DATETIME NOT NULL,
4. col1 int
5. )
6. AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
7. DISTRIBUTED BY HASH(k1)
8. PROPERTIES
9. (
10. "replication_num" = "1",
11. "dynamic_partition.create_history_partition"="true",
12. "dynamic_partition.enable" = "true",
13. "dynamic_partition.time_unit" = "year",
14. "dynamic_partition.start" = "-2",
15. "dynamic_partition.end" = "2",
16. "dynamic_partition.prefix" = "p",
17. "dynamic_partition.buckets" = "8"
18. );

When the two functions are used in combination, neither of their original functions is affected and they still act on the entire table. The behavior includes but is not limited to:

  1. regardless of the creation method, the expired historical partitions will be periodically cleaned up or transferred to cold storage according to the rules specified by the DYNAMIC PARTITION properties
  2. partition ranges cannot overlap or conflict. If a new partition range that needs to be created by DYNAMIC PARTITION has already been covered by an automatically or manually created partition, the partition creation will fail without affecting the business process.

The principle is that AUTO PARTITION is only a complementary means introduced to the creation of partitions, and that a partition, whether created manually, by AUTO PARTITION, or by DYNAMIC PARTITION, will be governed by DYNAMIC PARTITION functions.

Constraint

In order to simplify the behavioral pattern of combine two partition methods, when the AUTO PARTITION and DYNAMIC PARTITION are both used, the partition intervals of the two must be consistent or the table creating will fail:

1. mysql > CREATE TABLE tbl3
2. (
3. k1 DATETIME NOT NULL,
4. col1 int
5. )
6. AUTO PARTITION BY RANGE date_trunc(`k1`, 'year') ()
7. DISTRIBUTED BY HASH(k1)
8. PROPERTIES
9. (
10. "replication_num" = "1",
11. "dynamic_partition.create_history_partition"="true",
12. "dynamic_partition.enable" = "true",
13. "dynamic_partition.time_unit" = "HOUR",
14. "dynamic_partition.start" = "-2",
15. "dynamic_partition.end" = "2",
16. "dynamic_partition.prefix" = "p",
17. "dynamic_partition.buckets" = "8"
18. );
19. ERROR 1105 (HY000): errCode = 2, detailMessage = errCode = 2, detailMessage = If support auto partition and dynamic partition at same time, they must have the same interval unit.

caveat

  • Like a normal partition table, AUTO PARTITION supports multi-column partitioning, and there is no difference in syntax.
  • If a partition is created during the insertion or import of data and the entire import process does not complete (fails or is cancelled), the created partition is not automatically deleted.
  • Tables that use AUTO PARTITION only have their partitions created automatically instead of manually. The original use of the table and the partitions it creates is the same as for non-AUTO PARTITION tables or partitions.
  • To prevent accidental creation of too many partitions, we use the FE Configuration max_auto_partition_num controls the maximum number of partitions an AUTO PARTITION table can hold. This value can be adjusted if necessary
  • When importing data to a table with AUTO PARTITION enabled, the polling interval for data sent by the Coordinator is different from that of a normal table. For details, see olap_table_sink_send_interval_auto_partition_factor in BE Configuration.
  • In the case of inserting data with insert-overwrite command, if an overwrite partition is specified, the AUTO PARTITION table behaves as a normal table during this process and no new partitions will be created.