This document mainly introduces table creation and data partitioning in Doris, as well as potential problems and solutions encountered during table creation operations.
In Doris, data is logically described in the form of tables.
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 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.
Doris supports two levels of data partitioning. The first level is Partitioning, which supports Range and List partition. The second level is Bucket (also known as Tablet), which supports Hash and Random . If no partitioning is established during table creation, Doris generates a default partition that is transparent to the user. When using the default partition, only Bucket is supported.
In the 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.
Benefits of Two-Level data partitioning:
For dimensions with time or similar ordered values, such dimension columns can be used as partitioning columns. The partition granularity can be evaluated based on import frequency and partition data volume.
Historical data deletion requirements: 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.
Solving data skew issues: Each partition can specify the number of buckets independently. For example, when partitioning 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.
CREATE TABLE in Doris is a synchronous command. It returns results after the SQL execution is completed. Successful returns indicate successful table creation. For more information, please refer to CREATE TABLE, or input the HELP CREATE TABLE;
command.
This section introduces how to create tables in Doris by range partiton and hash buckets.
-- Range Partition CREATE TABLE IF NOT EXISTS example_range_tbl ( `user_id` LARGEINT NOT NULL COMMENT "User ID", `date` DATE NOT NULL COMMENT "Date when the data are imported", `timestamp` DATETIME NOT NULL COMMENT "Timestamp when the data are imported", `city` VARCHAR(20) COMMENT "User location city", `age` SMALLINT COMMENT "User age", `sex` TINYINT COMMENT "User gender", `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "User last visit time", `cost` BIGINT SUM DEFAULT "0" COMMENT "Total user consumption", `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum user dwell time", `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum user dwell time" ) ENGINE=OLAP AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) PARTITION BY RANGE(`date`) ( PARTITION `p201701` VALUES [("2017-01-01"), ("2017-02-01")), PARTITION `p201702` VALUES [("2017-02-01"), ("2017-03-01")), PARTITION `p201703` VALUES [("2017-03-01"), ("2017-04-01")) ) DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 PROPERTIES ( "replication_num" = "1" );
Here use the AGGREGATE KEY data model as an example. In the AGGREGATE KEY data model, all columns that are specified with an aggregation type (SUM, REPLACE, MAX, or MIN) are Value columns. The rest are the Key columns.
In the PROPERTIES at the end of the CREATE TABLE statement, you can find detailed information about the relevant parameters that can be set in PROPERTIES by referring to the documentation on CREATE TABLE.
The default type of ENGINE is OLAP. In Doris, only this OLAP ENGINE type is responsible for data management and storage by Doris itself. Other ENGINE types, such as mysql, broker, es, etc., are essentially just mappings to tables in other external databases or systems, allowing Doris to read this data. However, Doris itself does not create, manage, or store any tables or data for non-OLAP ENGINE types.
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 and a new schema have been created.
You can use the show create table
command to view the partition information of a table.
> show create table example_range_tbl +-------------------+---------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+---------------------------------------------------------------------------------------------------------+ | example_range_tbl | CREATE TABLE `example_range_tbl` ( | | | `user_id` largeint(40) NOT NULL COMMENT 'User ID', | | | `date` date NOT NULL COMMENT 'Date when the data are imported', | | | `timestamp` datetime NOT NULL COMMENT 'Timestamp when the data are imported', | | | `city` varchar(20) NULL COMMENT 'User location city', | | | `age` smallint(6) NULL COMMENT 'User age', | | | `sex` tinyint(4) NULL COMMENT 'User gender', | | | `last_visit_date` datetime REPLACE NULL DEFAULT "1970-01-01 00:00:00" COMMENT 'User last visit time', | | | `cost` bigint(20) SUM NULL DEFAULT "0" COMMENT 'Total user consumption', | | | `max_dwell_time` int(11) MAX NULL DEFAULT "0" COMMENT 'Maximum user dwell time', | | | `min_dwell_time` int(11) MIN NULL DEFAULT "99999" COMMENT 'Minimum user dwell time' | | | ) ENGINE=OLAP | | | AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`) | | | COMMENT 'OLAP' | | | PARTITION BY RANGE(`date`) | | | (PARTITION p201701 VALUES [('0000-01-01'), ('2017-02-01')), | | | PARTITION p201702 VALUES [('2017-02-01'), ('2017-03-01')), | | | PARTITION p201703 VALUES [('2017-03-01'), ('2017-04-01'))) | | | DISTRIBUTED BY HASH(`user_id`) BUCKETS 16 | | | PROPERTIES ( | | | "replication_allocation" = "tag.location.default: 1", | | | "is_being_synced" = "false", | | | "storage_format" = "V2", | | | "light_schema_change" = "true", | | | "disable_auto_compaction" = "false", | | | "enable_single_replica_compaction" = "false" | | | ); | +-------------------+---------------------------------------------------------------------------------------------------------+
You can use show partitions from your_table
command to view the partition information of a table.
> show partitions from example_range_tbl +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+--------------- +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+ | PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation | IsMutable | +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+--------------- +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+ | 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 | 9999-12-31 23:59:59 | | | 0.000 | false | tag.location.default: 1 | true | | 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 | 9999-12-31 23:59:59 | | | 0.000 | false | tag.location.default: 1 | true | | 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 | 9999-12-31 23:59:59 | | | 0.000 | false | tag.location.default: 1 | true | +-------------+---------------+----------------+---------------------+--------+--------------+--------------------------------------------------------------------------------+-----------------+---------+----------------+--------------- +---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+
You can add a new partition by using the alter table add partition
command.
ALTER TABLE example_range_tbl ADD PARTITION p201704 VALUES LESS THAN("2020-05-01") DISTRIBUTED BY HASH(`user_id`) BUCKETS 5;
For more partition modification operations, please refer to the SQL manual on ALTER-TABLE-PARTITION.
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.
Partition columns are usually time columns for convenient management of old and new data. Range partition supports column types such as DATE, DATETIME, TINYINT, SMALLINT, INT, BIGINT, and LARGEINT.
Partition information supports four writing methods:
PARTITION BY RANGE(col1[, col2, ...]) ( PARTITION partition_name1 VALUES [("k1-lower1", "k2-lower1", "k3-lower1",...), ("k1-upper1", "k2-upper1", "k3-upper1", ...)), PARTITION partition_name2 VALUES [("k1-lower1-2", "k2-lower1-2", ...), ("k1-upper1-2", MAXVALUE, )) )
For example:
PARTITION BY RANGE(`date`) ( PARTITION `p201701` VALUES [("2017-01-01"), ("2017-02-01")), PARTITION `p201702` VALUES [("2017-02-01"), ("2017-03-01")), PARTITION `p201703` VALUES [("2017-03-01"), ("2017-04-01")) )
PARTITION BY RANGE(col1[, col2, ...]) ( PARTITION partition_name1 VALUES LESS THAN MAXVALUE | ("value1", "value2", ...), PARTITION partition_name2 VALUES LESS THAN MAXVALUE | ("value1", "value2", ...) )
For example:
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") ) 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") PARTITION `other` VALUES LESS THAN (MAXVALUE) )
PARTITION BY RANGE(int_col) ( FROM (start_num) TO (end_num) INTERVAL interval_value ) PARTITION BY RANGE(date_col) ( FROM ("start_date") TO ("end_date") INTERVAL num YEAR | num MONTH | num WEEK | num DAY | 1 HOUR )
For example:
PARTITION BY RANGE(age) ( FROM (1) TO (100) INTERVAL 10 ) PARTITION BY RANGE(`date`) ( FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 2 YEAR )
PARTITION BY RANGE(col) ( FROM ("2000-11-14") TO ("2021-11-14") INTERVAL 1 YEAR, FROM ("2021-11-14") TO ("2022-11-14") INTERVAL 1 MONTH, FROM ("2022-11-14") TO ("2023-01-03") INTERVAL 1 WEEK, FROM ("2023-01-03") TO ("2023-01-14") INTERVAL 1 DAY, PARTITION p_20230114 VALUES [('2023-01-14'), ('2023-01-15')) )
Partition columns 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:
PARTITION BY LIST(city) ( PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"), PARTITION `p_usa` VALUES IN ("New York", "San Francisco"), PARTITION `p_jp` VALUES IN ("Tokyo") )
List partition also supports multi-column partitioning, for example:
PARTITION BY LIST(id, city) ( PARTITION p1_city VALUES IN (("1", "Beijing"), ("1", "Shanghai")), PARTITION p2_city VALUES IN (("2", "Beijing"), ("2", "Shanghai")), PARTITION p3_city VALUES IN (("3", "Beijing"), ("3", "Shanghai")) )
Dynamic partition is designed to manage partition's Time-to-Life (TTL), reducing the burden on users.
In some usage scenarios, the user will partition the table according to the day and perform routine tasks regularly every day. At this time, the user needs to manually manage the partition. Otherwise, the data load may fail because the user does not create a partition. This brings additional maintenance costs to the user.
With dynamic partitioning, users can define rules for partition creation and deletion when establishing tables. The FE initiates a background thread to handle partition creation or deletion based on these user-defined rules. Users also have the flexibility to modify these rules during runtime.
It's important to note that dynamic partitioning is exclusively supported by range partitions. Currently, the functionality enables dynamic addition and deletion of partitions.
:::tip
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.
:::
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.
CREATE TABLE tbl1 (...) PROPERTIES ( "dynamic_partition.prop1" = "value1", "dynamic_partition.prop2" = "value2", ... )
ALTER TABLE tbl1 SET ( "dynamic_partition.prop1" = "value1", "dynamic_partition.prop2" = "value2", ... )
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
.
:::tip
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:
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 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 :
["2020-06-01","2020-06-20"], ["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 :
["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
:::info Note 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.
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 setexpect_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.
p20210519 p20210520 p20210521 p20210522 p20210523
history_partition_num=5
and keep the rest attributes as in 1, then the system will automatically create the following partitions.
p20210517 p20210518 p20210519 p20210520 p20210521 p20210522 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.
p20210517 p20210518 p20210519 p20210520 p20210521 p20210522 p20210523
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.
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" );
Suppose the current date is 2020-05-29. According to the above rules, tbl1 will produce 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 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.
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.
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. The default week starts on Monday. Based on the above rules, tbl1 will produce 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 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:
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 ranges from Wednesday of the current week to Tuesday of the next week.
:::tip
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
.
:::
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.
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" );
Suppose the current date is 2020-05-29. 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, 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:
p202004: ["2020-04-28", "2020-05-28") p202005: ["2020-05-28", "2020-06-28") p202006: ["2020-06-28", "2020-07-28")
You can modify the properties of the dynamic partition with the following command
ALTER TABLE tbl1 SET ( "dynamic_partition.prop1" = "value1", ... );
The modification of certain attributes may cause conflicts. Assume that the partition granularity was 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 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.
You can further view the scheduling of dynamic partitioned tables by using the following command:
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)
FE Configuration Item
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:
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`
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 3600 (1 hour), that is, scheduling is performed every 1 hour. You can modify the parameters in fe.conf
and restart FE to take effect. You can also modify the following commands at runtime:
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 dynamic and manual partition tables to each other
For a table, dynamic and manual partitioning can be freely converted, but they cannot exist at the same time, there is and only one state.
Converting Manual Partitioning to Dynamic Partitioning
If a table is not dynamically partitioned when it is created, it can be converted to dynamic partitioning at runtime by modifying the dynamic partitioning properties with ALTER TABLE
, an example of which can be seen with HELP ALTER TABLE
.
When dynamic partitioning feature is enabled, Doris will no longer allow users to manage partitions manually, but will automatically manage partitions based on dynamic partition properties.
:::tip
If dynamic_partition.start
is set, historical partitions with a partition range before the start offset of the dynamic partition will be deleted.
:::
Converting Dynamic Partitioning to Manual Partitioning
The dynamic partitioning feature can be disabled by executing ALTER TABLE tbl_name SET ("dynamic_partition.enable" = "false")
and converting it to a manual partition table.
When dynamic partitioning feature is disabled, Doris will no longer manage partitions automatically, and users will have to create or delete partitions manually by using ALTER TABLE
.
:::tip
Doris version 2.1 starts to support automatic partitioning. To use this feature, please download Doris 2.1 and refer to the documentation for version 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.
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:
CREATE TABLE `DAILY_TRADE_VALUE` ( `TRADE_DATE` datev2 NOT NULL COMMENT '交易日期', `TRADE_ID` varchar(40) NOT NULL COMMENT '交易编号', ...... ) UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`) PARTITION BY RANGE(`TRADE_DATE`) ( PARTITION p_2000 VALUES [('2000-01-01'), ('2001-01-01')), PARTITION p_2001 VALUES [('2001-01-01'), ('2002-01-01')), PARTITION p_2002 VALUES [('2002-01-01'), ('2003-01-01')), PARTITION p_2003 VALUES [('2003-01-01'), ('2004-01-01')), PARTITION p_2004 VALUES [('2004-01-01'), ('2005-01-01')), PARTITION p_2005 VALUES [('2005-01-01'), ('2006-01-01')), PARTITION p_2006 VALUES [('2006-01-01'), ('2007-01-01')), PARTITION p_2007 VALUES [('2007-01-01'), ('2008-01-01')), PARTITION p_2008 VALUES [('2008-01-01'), ('2009-01-01')), PARTITION p_2009 VALUES [('2009-01-01'), ('2010-01-01')), PARTITION p_2010 VALUES [('2010-01-01'), ('2011-01-01')), PARTITION p_2011 VALUES [('2011-01-01'), ('2012-01-01')), PARTITION p_2012 VALUES [('2012-01-01'), ('2013-01-01')), PARTITION p_2013 VALUES [('2013-01-01'), ('2014-01-01')), PARTITION p_2014 VALUES [('2014-01-01'), ('2015-01-01')), PARTITION p_2015 VALUES [('2015-01-01'), ('2016-01-01')), PARTITION p_2016 VALUES [('2016-01-01'), ('2017-01-01')), PARTITION p_2017 VALUES [('2017-01-01'), ('2018-01-01')), PARTITION p_2018 VALUES [('2018-01-01'), ('2019-01-01')), PARTITION p_2019 VALUES [('2019-01-01'), ('2020-01-01')), PARTITION p_2020 VALUES [('2020-01-01'), ('2021-01-01')), PARTITION p_2021 VALUES [('2021-01-01'), ('2022-01-01')) ) DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" );
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.
If partitions are used, DISTRIBUTED ..
. statement describes the rules for dividing data within each partition.
If partitions are not used, it describes the rules for dividing the data across the entire table.
It is also possible to specify a bucketing method for each partition individually.
The bucket columns can be multiple columns. For the Aggregate and Unique models, they must be Key columns, while for the duplicate key data model, they can be both key and value columns. Bucket columns can be the same as or different from Partition columns.
The choice of bucket columns involves a trade-off between query throughput and query concurrency:
ADD PARTITION
, the bucket number for the new partition can be specified separately. This feature can be conveniently used to handle data reduction or expansion.Here are some examples: Assuming there are 10 BEs, each with one disk. If a table has a total size of 500MB, 4-8 tablets can be considered. For 5GB: 8-16 tablets. For 50GB: 32 tablets. For 500GB: It is recommended to partition the table, with each partition size around 50GB and 16-32 tablets per partition. For 5TB: It is recommended to partition the table, with each partition size around 50GB and 16-32 tablets per partition.
The data volume of a table can be viewed using the SHOW DATA command, and the result should be divided by the number of replicas to obtain the actual data volume of the table.
load_to_single_tablet
to true). Then, during large-volume data import, a task will only write to one tablet when writing data to the corresponding partition. This can improve the concurrency and throughput of data import, reduce the write amplification caused by data import and compaction, and ensure the stability of the cluster.Users often encounter various issues due to improper bucket settings. To address this, we provide an automated approach for setting the number of buckets, which is currently applicable only to OLAP tables.
:::tip
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.
:::
In the past, user had to set the number of buckets manually when creating table, but the automatic bucket feature is a way for Apache Doris to dynamically project the number of buckets, so that the number of buckets always stays within a suitable range and users don't have to worry about the minutiae of the number of buckets.
For the sake of clarity, this section splits the bucket into two periods, the initial bucket and the subsequent bucket; the initial and subsequent are just terms used in this article to describe the feature clearly, there is no initial or subsequent Apache Doris bucket.
As we know from the section above on creating buckets, BUCKET_DESC
is very simple, but you need to specify the number of buckets; for the automatic bucket projection feature, the syntax of BUCKET_DESC directly changes the number of buckets to Auto
and adds a new Properties configuration.
-- old version of the creation syntax for specifying the number of buckets DISTRIBUTED BY HASH(site) BUCKETS 20 -- Newer versions use the creation syntax for automatic bucket imputation DISTRIBUTED BY HASH(site) BUCKETS AUTO properties("estimate_partition_size" = "100G")
The new configuration parameter estimate_partition_size indicates the amount of data for a single partition. This parameter is optional and if not given, Doris will take the default value of estimate_partition_size to 10GB.
As you know from the above, a partitioned bucket is a Tablet at the physical level, and for best performance, it is recommended that the Tablet size be in the range of 1GB - 10GB. So how does the automatic bucketing projection ensure that the Tablet size is within this range?
To summarize, there are a few principles.
:::tip propertie estimate_partition_size not support alter :::
estimate_partition_size
by 5 (considering a data compression ratio of 5 to 1 when storing data in text format in Doris). The result obtained is(, 100MB), then take N=1 [100MB, 1GB), then take N=2 (1GB, ), then one bucket per GB
Where each BE node counts as 1, and every 50G of disk capacity counts as 1. The calculation rule for M is: M = Number of BE nodes * (Size of one disk block / 50GB) * Number of disk blocks. For example: If there are 3 BEs, and each BE has 4 disks of 500GB, then M = 3 * (500GB / 50GB) * 4 = 120.
Calculate an intermediate value x = min(M, N, 128). If x < N and x < the number of BE nodes, the final bucket is y. The number of BE nodes; otherwise, the final bucket is x.
The pseudo-code representation of the above process is as follows
int N = Compute the N value; int M = compute M value; int y = number of BE nodes; int x = min(M, N, 128); if (x < N && x < y) { return y; } return x;
With the above algorithm in mind, let's introduce some examples to better understand this part of the logic.
case1: Amount of data 100 MB, 10 BE machines, 2TB * 3 disks Amount of data N = 1 BE disks M = 10* (2TB/50GB) * 3 = 1230 x = min(M, N, 128) = 1 Final: 1 case2: Data volume 1GB, 3 BE machines, 500GB * 2 disks Amount of data N = 2 BE disks M = 3* (500GB/50GB) * 2 = 60 x = min(M, N, 128) = 2 Final: 2 case3: Data volume 100GB, 3 BE machines, 500GB * 2 disks Amount of data N = 20 BE disks M = 3* (500GB/50GB) * 2 = 60 x = min(M, N, 128) = 20 Final: 20 case4: Data volume 500GB, 3 BE machines, 1TB * 1 disk Data volume N = 100 BE disks M = 3* (1TB /50GB) * 1 = 60 x = min(M, N, 128) = 63 Final: 63 case5: Data volume 500GB, 10 BE machines, 2TB * 3 disks Amount of data N = 100 BE disks M = 10* (2TB / 50GB) * 3 = 1230 x = min(M, N, 128) = 100 Final: 100 case 6: Data volume 1TB, 10 BE machines, 2TB * 3 disks Amount of data N = 205 BE disks M = 10* (2TB / 50GB) * 3 = 1230 x = min(M, N, 128) = 128 Final: 128 case 7: Data volume 500GB, 1 BE machine, 100TB * 1 disk Amount of data N = 100 BE disk M = 1* (100TB / 50GB) * 1 = 2048 x = min(M, N, 128) = 100 Final: 100 case 8: Data volume 1TB, 200 BE machines, 4TB * 7 disks Amount of data N = 205 BE disks M = 200* (4TB / 50GB) * 7 = 114800 x = min(M, N, 128) = 128 Final: 200
The above is the calculation logic for the initial bucketing. The subsequent bucketing can be evaluated based on the amount of partition data available since there is already a certain amount of partition data. The subsequent bucket size is evaluated based on the EMA[1] (short term exponential moving average) value of up to the first 7 partitions, which is used as the estimate_partition_size. At this point there are two ways to calculate the partition buckets, assuming partitioning by days, counting forward to the first day partition size of S7, counting forward to the second day partition size of S6, and so on to S1.
S7 - S6 = delta1, S6 - S5 = delta2, ... S2 - S1 = delta6
This yields the ema(delta) value.Then, today's estimate_partition_size = S7 + ema(delta)
:::tip
According to the above algorithm, the initial number of buckets and the number of subsequent buckets can be calculated. Unlike before when only a fixed number of buckets could be specified, due to changes in business data, it is possible that the number of buckets in the previous partition is different from the number of buckets in the next partition, which is transparent to the user, and the user does not need to care about the exact number of buckets in each partition, and this automatic extrapolation will make the number of buckets more reasonable.
:::
Incomplete syntax error prompts may occur in longer table creation statements. Here are some possible syntax errors for manual troubleshooting:
Failed to create partition [xxx]. Timeout
Doris creates tables sequentially based on partition granularity. When a partition fails to create, this error may occur. Even if partitions are not used, when there is a problem with table creation, Failed to create partition
may still be reported because, as mentioned earlier, Doris creates an unmodifiable default partition for tables without specified partitions.
When encountering this error, it is usually because the BE encountered a problem when creating data tablets. You can troubleshoot by following these steps:
Failed to create partition
log entry at the corresponding timestamp. In this log entry, you may find a series of number pairs similar to {10001-10010}
. The first number in the pair represents the Backend ID, and the second number represents the Tablet ID. For example, this number pair indicates that the creation of Tablet ID 10010 on Backend ID 10001 failed.Too many open files
. The number of open file handles exceeds the Linux system limit. You need to modify the handle limit of the Linux system.tablet_create_timeout_second=xxx
and max_create_table_timeout_second=xxx
in the fe.conf file. By default, tablet_create_timeout_second
is set to 1 second, and max_create_table_timeout_second
is set to 60 seconds. The overall timeout is calculated as min(tablet_create_timeout_second * replication_num, max_create_table_timeout_second)
. For specific parameter settings, please refer to the FE Configuration section.For more detailed information on data partitioning, you can refer to the CREATE TABLE command manual or enter HELP CREATE TABLE;
in the MySQL client to get more help information.