Help Center/
CloudTable Service/
Developer Guide/
Doris Application Development Guide/
Doris Usage Specifications/
Doris Partition Rules
Updated on 2025-07-25 GMT+08:00
Doris Partition Rules
Partitions are used to divide data into different intervals. Logically, an original table is divided into multiple sub-tables. Data can be easily managed by partition.
- You can specify one or more columns as the partitioning columns, but they have to be KEY columns. The usage of multi-column partitions is described further below.
- Regardless of the type of the partitioning columns, double quotes are required for partition values.
- Theoretically, there is no upper limit on the number of partitions.
- If users create a table without specifying the partitions, the system will automatically generate a partition with the same name as the table. This Partition contains all data in the table and is neither visible to users nor modifiable.
- Partitions should not have overlapping ranges.
Range Partitioning
Partitioning columns are usually time columns for easy management of old and new data.
Range partitioning supports specifying only the upper bound by VALUES LESS THAN (...). The system will use the upper bound of the previous partition as the lower bound of the next partition, and generate a left-closed right-open interval.
- The following takes the VALUES [...) method as an example since it is more comprehensible. It shows how the partition ranges change as we use the VALUES LESS THAN (...) statement to add or delete partitions.
CREATE TABLE IF NOT EXISTS example_db.expamle_range_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "User ID",
`date` DATE NOT NULL COMMENT "Data import date and time",
`timestamp` DATETIME NOT NULL COMMENT "Data import timestamp",
`city` VARCHAR(20) COMMENT "City where the user locates",
`age` SMALLINT COMMENT "User age",
`sex` TINYINT COMMENT "User gender",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user",
`cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time",
)
ENGINE=OLAP
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`)
(
PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
PROPERTIES
(
"replication_num" = "3"
);
- View the partitions.
mysql> show partitions from example_db.expamle_range_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16040 | p201701 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16041 | p201702 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16042 | p201703 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [2017-03-01]; ..types: [DATE]; keys: [2017-04-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.01 sec)
- Add partition p201705.
mysql> alter table example_db.expamle_range_tbl add partition p201705 VALUES LESS THAN ("2017-06-01"); Query OK, 0 rows affected (0.02 sec)View the partitions.
mysql> show partitions from example_db.expamle_range_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16040 | p201701 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16041 | p201702 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16042 | p201703 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [2017-03-01]; ..types: [DATE]; keys: [2017-04-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16237 | p201705 | 1 | 2023-04-11 07:45:18 | NORMAL | date | [types: [DATE]; keys: [2017-04-01]; ..types: [DATE]; keys: [2017-06-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 4 rows in set (0.00 sec)
- Delete partition p201703.
mysql> alter table example_db.expamle_range_tbl drop partition p201703; Query OK, 0 rows affected (0.01 sec)
View the partitions.
mysql> show partitions from example_db.expamle_range_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16040 | p201701 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16041 | p201702 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16237 | p201705 | 1 | 2023-04-11 07:45:18 | NORMAL | date | [types: [DATE]; keys: [2017-04-01]; ..types: [DATE]; keys: [2017-06-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
Note that the partition range of p201702 and p201705 has not changed, and there is a gap between the two partitions: [2017-03-01, 2017-04-01). That means, if the imported data is within this gap range, the import would fail.
- Delete partition p201702.
mysql> alter table example_db.expamle_range_tbl drop partition p201702; Query OK, 0 rows affected (0.00 sec)
View the partitions.
mysql> show partitions from example_db.expamle_range_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16040 | p201701 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16237 | p201705 | 1 | 2023-04-11 07:45:18 | NORMAL | date | [types: [DATE]; keys: [2017-04-01]; ..types: [DATE]; keys: [2017-06-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 2 rows in set (0.00 sec)
The gap range expands to: [2017-02-01, 2017-04-01)
- Add partition p201702new VALUES LESS THAN ("2017-03-01").
mysql> alter table example_db.expamle_range_tbl add partition p201702new VALUES LESS THAN ("2017-03-01"); Query OK, 0 rows affected (0.01 sec)View the partitions.
mysql> show partitions from example_db.expamle_range_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16040 | p201701 | 1 | 2023-04-11 07:35:02 | NORMAL | date | [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16302 | p201702new | 1 | 2023-04-11 08:14:25 | NORMAL | date | [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16237 | p201705 | 1 | 2023-04-11 07:45:18 | NORMAL | date | [types: [DATE]; keys: [2017-04-01]; ..types: [DATE]; keys: [2017-06-01]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
In summary, the deletion of a partition does not change the range of the existing partitions, but might result in gaps. When a partition is added via the VALUES LESS THAN statement, the lower bound of one partition is the upper bound of its previous partition.
- Multi-column partitioning
In addition to the single-column partitioning mentioned above, range partitioning also supports multi-column partitioning. Examples are as follows:
CREATE TABLE IF NOT EXISTS example_db.expamle_range_multi_partiton_key_tbl ( `user_id` LARGEINT NOT NULL COMMENT " User ID", `date` DATE NOT NULL COMMENT "Data import date and time", `timestamp` DATETIME NOT NULL COMMENT "Data import timestamp", `city` VARCHAR(20) COMMENT "City where the user locates", `age` SMALLINT COMMENT "User age", `sex` TINYINT COMMENT "User gender", `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user", `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption", `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time" `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time" ) ENGINE=OLAP AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) PARTITION BY RANGE(`date`, `user_id`) ( PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"), PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"), PARTITION `p201703_all` VALUES LESS THAN ("2017-04-01") ) DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 PROPERTIES ( "replication_num" = "3" );In the above example, we specify date (DATE type) and user_id (INT type) as the partitioning columns, so the resulting partitions will be as follows:
mysql> show partitions from example_db.expamle_range_multi_partiton_key_tbl; +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16367 | p201701_1000 | 1 | 2023-04-11 08:28:12 | NORMAL | date, user_id | [types: [DATE, LARGEINT]; keys: [0000-01-01, -170141183460469231731687303715884105728]; ..types: [DATE, LARGEINT]; keys: [2017-02-01, 1000]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16368 | p201702_2000 | 1 | 2023-04-11 08:28:12 | NORMAL | date, user_id | [types: [DATE, LARGEINT]; keys: [2017-02-01, 1000]; ..types: [DATE, LARGEINT]; keys: [2017-03-01, 2000]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16369 | p201703_all | 1 | 2023-04-11 08:28:12 | NORMAL | date, user_id | [types: [DATE, LARGEINT]; keys: [2017-03-01, 2000]; ..types: [DATE, LARGEINT]; keys: [2017-04-01, -170141183460469231731687303715884105728]; ) | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
Note that in the last partition, the user only specifies the partition value of the date column, so the system fills in MIN_VALUE as the partition value of the user_id column by default. When data is imported, the system will compare them with the partition values in order, and put the data in their corresponding partitions.
Examples are as follows:
* Data --> Partition * 2017-01-01, 200 --> p201701_1000 * 2017-01-01, 2000 --> p201701_1000 * 2017-02-01, 100 --> p201701_1000 * 2017-02-01, 2000 --> p201702_2000 * 2017-02-15, 5000 --> p201702_2000 * 2017-03-01, 2000 --> p201703_all * 2017-03-10, 1 --> p201703_all * 2017-04-01, 1000 --> unable to import * 2017-05-01, 1000 --> unable to import
Verification method:
Insert a piece of data and check the partition to which the data is stored. Fields VisibleVersionTime and VisibleVersion are updated in the partition where the inserted data is located.
insert into example_db.expamle_range_multi_partiton_key_tbl values (200, '2017-01-01', '2017-01-01 12:00:05', 'A', 25, 1, '2017-01-01 12:00:05', 100, 30, 10); insert into example_db.expamle_range_multi_partiton_key_tbl values (2000, '2017-01-01', '2017-01-01 16:10:05', 'B', 33, 1, '2017-01-01 16:10:05', 800, 50, 1); insert into example_db.expamle_range_multi_partiton_key_tbl values (200, '2017-02-01', '2017-01-01 16:10:05', 'C', 22, 0, '2017-02-01 16:10:05', 80, 200, 1); show partitions from example_db.expamle_range_multi_partiton_key_tbl\G
List Partitioning
- The partitioning columns support the BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR data types, and the partition values are enumeration values. Partitions can be only hit if the data is one of the enumeration values in the target partition.
- List partitioning supports using VALUES IN (...) to specify the enumeration values contained in each partition.
- The following example illustrates how partitions change when adding or deleting a partition.
CREATE TABLE IF NOT EXISTS example_db.expamle_list_tbl ( `user_id` LARGEINT NOT NULL COMMENT " User ID", `date` DATE NOT NULL COMMENT "Data import date and time", `timestamp` DATETIME NOT NULL COMMENT "Data import timestamp", `city` VARCHAR(20) NOT NULL COMMENT "City where the user locates", `age` SMALLINT COMMENT "User age", `sex` TINYINT COMMENT "User gender", `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user", `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption", `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time" `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time" ) ENGINE=olap AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) PARTITION BY LIST(`city`) ( PARTITION `p_cn` VALUES IN ("A", "B", "F"), PARTITION `p_usa` VALUES IN ("G", "H"), PARTITION `p_jp` VALUES IN ("I") ) DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 PROPERTIES ( "replication_num" = "3" ); - As shown in the preceding table, when the table is created, the following three partitions are automatically created.
mysql> show partitions from example_db.expamle_list_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16764 | p_cn | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [A]; , types: [VARCHAR]; keys: [B]; , types: [VARCHAR]; keys: [F]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16765 | p_usa | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [G]; , types: [VARCHAR]; keys: [H]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16766 | p_jp | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [I]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
- Add partition p_uk VALUES IN ("L").
mysql> alter table example_db.expamle_list_tbl add partition p_uk VALUES IN ("L"); Query OK, 0 rows affected (0.01 sec)View the partitions.
mysql> show partitions from example_db.expamle_list_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16764 | p_cn | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [A]; , types: [VARCHAR]; keys: [B]; , types: [VARCHAR]; keys: [F]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16765 | p_usa | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [G]; , types: [VARCHAR]; keys: [H]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16766 | p_jp | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [I]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16961 | p_uk | 1 | 2023-04-11 09:24:39 | NORMAL | city | [types: [VARCHAR]; keys: [L]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 4 rows in set (0.00 sec)
- Delete partition p_jp.
mysql> alter table example_db.expamle_list_tbl drop partition p_jp; Query OK, 0 rows affected (0.01 sec)
View the partitions.
mysql> show partitions from example_db.expamle_list_tbl; +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 16764 | p_cn | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [A]; , types: [VARCHAR]; keys: [B]; , types: [VARCHAR]; keys: [F]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16765 | p_usa | 1 | 2023-04-11 09:21:34 | NORMAL | city | [types: [VARCHAR]; keys: [G]; , types: [VARCHAR]; keys: [H]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 16961 | p_uk | 1 | 2023-04-11 09:24:39 | NORMAL | city | [types: [VARCHAR]; keys: [L]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+--------------+-----------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
- List partitioning also supports multi-column partitioning. Examples are as follows:
CREATE TABLE IF NOT EXISTS example_db.expamle_list_multi_partiton_key_tbl ( `user_id` LARGEINT NOT NULL COMMENT " User ID", `date` DATE NOT NULL COMMENT "Data import date and time", `timestamp` DATETIME NOT NULL COMMENT "Data import timestamp", `city` VARCHAR(20) NOT NULL COMMENT "City where the user locates", `age` SMALLINT COMMENT "User age", `sex` TINYINT COMMENT "User gender", `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user", `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption", `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time" `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time" ) ENGINE=olap AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) PARTITION BY LIST(`user_id`, `city`) ( PARTITION `p1_city` VALUES IN (("1", "A"), ("1", "B")), PARTITION `p2_city` VALUES IN (("2", "A"), ("2", "B")), PARTITION `p3_city` VALUES IN (("3", "A"), ("3", "B")) ) DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 PROPERTIES ( "replication_num" = "3" );In the above example, we specify user_id (INT type) and city (VARCHAR type) as the partitioning columns, so the resulting partitions will be as follows:
mysql> show partitions from example_db.expamle_list_multi_partiton_key_tbl; +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ | 17026 | p1_city | 1 | 2023-04-11 09:31:33 | NORMAL | user_id, city | [types: [LARGEINT, VARCHAR]; keys: [1, A]; , types: [LARGEINT, VARCHAR]; keys: [1, B]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 17027 | p2_city | 1 | 2023-04-11 09:31:33 | NORMAL | user_id, city | [types: [LARGEINT, VARCHAR]; keys: [2, A]; , types: [LARGEINT, VARCHAR]; keys: [2, B]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | | 17028 | p3_city | 1 | 2023-04-11 09:31:33 | NORMAL | user_id, city | [types: [LARGEINT, VARCHAR]; keys: [3, A]; , types: [LARGEINT, VARCHAR]; keys: [3, B]; ] | user_id | 16 | 3 | HDD | 9999-12-31 15:59:59 | | NULL | 0.000 | false | tag.location.default: 3 | +-------------+---------------+----------------+---------------------+--------+---------------+------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+ 3 rows in set (0.00 sec)
- When data is imported, the system will compare them with the partition values in order, and put the data in their corresponding partitions. Examples are as follows:
* Data ---> Partition * 1, A ---> p1_city * 1, B ---> p1_city * 2, B ---> p2_city * 3, A ---> p3_city * 1, M ---> Unable to import * 4, A ---> Unable to import
Verification method:
Insert a piece of data and check the partition to which the data is stored. Fields VisibleVersionTime and VisibleVersion are updated in the partition where the inserted data is located.
INSERT INTO example_db.expamle_list_multi_partiton_key_tbl values (1, '2017-01-01', '2017-01-01 12:00:05', 'A', 25, 1, '2017-01-01 12:00:05', 100, 30, 10);
Insert a piece of data into the partition and check the corresponding fields.
mysql> SHOW partitions from example_db.expamle_list_multi_partiton_key_tbl\G *************************** 1. row *************************** PartitionId: 17026 PartitionName: p1_city VisibleVersion: 3 VisibleVersionTime: 2023-04-11 09:42:34 State: NORMAL PartitionKey: user_id, city Range: [types: [LARGEINT, VARCHAR]; keys: [1, A]; , types: [LARGEINT, VARCHAR]; keys: [1, B]; ] DistributionKey: user_id Buckets: 16 ReplicationNum: 3 StorageMedium: HDD CooldownTime: 9999-12-31 15:59:59 RemoteStoragePolicy: LastConsistencyCheckTime: NULL DataSize: 9.340 KB IsInMemory: false ReplicaAllocation: tag.location.default: 3 *************************** 2. row *************************** PartitionId: 17027 PartitionName: p2_city VisibleVersion: 1 VisibleVersionTime: 2023-04-11 09:31:33 State: NORMAL PartitionKey: user_id, city Range: [types: [LARGEINT, VARCHAR]; keys: [2, A]; , types: [LARGEINT, VARCHAR]; keys: [2, B]; ] DistributionKey: user_id Buckets: 16 ReplicationNum: 3 StorageMedium: HDD CooldownTime: 9999-12-31 15:59:59 RemoteStoragePolicy: LastConsistencyCheckTime: NULL DataSize: 0.000 IsInMemory: false ReplicaAllocation: tag.location.default: 3 *************************** 3. row *************************** PartitionId: 17028 PartitionName: p3_city VisibleVersion: 1 VisibleVersionTime: 2023-04-11 09:31:33 State: NORMAL PartitionKey: user_id, city Range: [types: [LARGEINT, VARCHAR]; keys: [3, A]; , types: [LARGEINT, VARCHAR]; keys: [3, B]; ] DistributionKey: user_id Buckets: 16 ReplicationNum: 3 StorageMedium: HDD CooldownTime: 9999-12-31 15:59:59 RemoteStoragePolicy: LastConsistencyCheckTime: NULL DataSize: 0.000 IsInMemory: false ReplicaAllocation: tag.location.default: 3 3 rows in set (0.00 sec)
Parent topic: Doris Usage Specifications
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
