- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
In version 0.12, Doris supports the temporary partition feature. A temporary partition belongs to a certain partitioned table. Only partition tables can create temporary partitions.
Rule
The partition column of the temporary partition is consistent with the formal partition and cannot be modified.
The partition ranges of all temporary partitions within a table cannot overlap, but the range of a temporary partition can overlap with a formal partition.
The name of the temporary partition cannot be repeated with the formal partition and other temporary partitions.
Supported Operations
Temporary partitions support addition, deletion, and replacement operations.
Adding Temporary Partition
You can add a temporary partition to a table with the
ALTER TABLE ADD TEMPORARY PARTITION statement:ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");ALTER TABLE tbl2 ADD TEMPORARY PARTITION tp1 VALUES [("2020-01-01"), ("2020-02-01"));ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01")("in_memory" = "true", "replication_num" = "1")DISTRIBUTED BY HASH(k1) BUCKETS 5;ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai");ALTER TABLE tbl4 ADD TEMPORARY PARTITION tp1 VALUES IN ((1, "Beijing"), (1, "Shanghai"));ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai")("in_memory" = "true", "replication_num" = "1")DISTRIBUTED BY HASH(k1) BUCKETS 5;
Use
HELP ALTER TABLE; for more help and examples.Some explanations for the addition operation:
Adding a temporary partition is similar to adding a formal partition. The partition range of the temporary partition is independent of the formal partition.
Temporary partitions can independently specify some properties, including the number of buckets, the number of replicas, whether it is a memory table, storage medium and other information.
Deleting Temporary Partition
You can delete a table's temporary partition with the
ALTER TABLE DROP TEMPORARY PARTITION statementALTER TABLE tbl1 DROP TEMPORARY PARTITION tp1;
Use
HELP ALTER TABLE; for more help and examples.Note
Deleting temporary partition will not affect the data of the official partition.
Replacing Partition
You can replace a table's official partition with a temporary partition using the
ALTER TABLE REPLACE PARTITION statement.ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2, tp3);ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2)PROPERTIES ("strict_range" = "false","use_temp_partition_name" = "true");
Use
HELP ALTER TABLE; for more help and examples.The replace operation has two special optional parameters:
1.
strict_range
It is set to true by default. For Range partition, when this parameter is set to true, it means the total range of all official partitions to be replaced must be exactly the same as the total range of the temporary partition to replace. When it is set to false, it only needs to ensure that after replacement, the ranges between the new official partitions do not overlap.
For List partition, this parameter is always true. The enumeration values of all official partitions to be replaced must be exactly the same as the enumeration values of the temporary partition to replace. Here are some examples:Example 1
The range of partitions p1, p2, p3 to be replaced (=> union):
[10, 20), [20, 30), [40, 50) => [10, 30), [40, 50)
The range of replacement partitions tp1, tp2 (=> union):
[10, 30), [40, 45), [45, 50) => [10, 30), [40, 50)
If the range is the same, tp1 and tp2 can replace p1, p2, p3.
Example 2
The range of partition p1 to be replaced (=> union):
[10, 50) => [10, 50)
The range of replacement partitions tp1, tp2 (=> union):
[10, 30), [40, 50) => [10, 30), [40, 50)
If the range is not the same and
strict_range is true, tp1 and tp2 cannot replace p1. If it is set to false and the range of the two partitions after replacement [10, 30), [40, 50) does not overlap with other official partitions, replacement is possible.Example 3
The enumeration value of the partitions p1, p2 to be replaced (=> union):
(1, 2, 3), (4, 5, 6) => (1, 2, 3, 4, 5, 6)
The enumeration value of the replacement partitions tp1, tp2, tp3 (=> union):
(1, 2, 3), (4), (5, 6) => (1, 2, 3, 4, 5, 6)
If the sets of enumeration values are the same, tp1, tp2, tp3 can replace p1, p2.
Example 4
The enumeration value of the partitions p1, p2, p3 to be replaced (=> union):
(("1","beijing"), ("1", "shanghai")), (("2","beijing"), ("2", "shanghai")), (("3","beijing"), ("3", "shanghai")) => (("1","beijing"), ("1", "shanghai"), ("2","beijing"), ("2", "shanghai"), ("3","beijing"), ("3", "shanghai"))
The enumeration values (union) of the replacement partitions tp1, tp2:
(("1","beijing"), ("1", "shanghai")), (("2","beijing"), ("2", "shanghai"), ("3","beijing"), ("3", "shanghai")) => (("1","beijing"), ("1", "shanghai"), ("2","beijing"), ("2", "shanghai"), ("3","beijing"), ("3", "shanghai"))
If the sets of enumeration values are the same, tp1, tp2 can replace p1, p2, p3.
2.
use_temp_partition_name
It defaults to false. When this parameter is false, and the number of partitions to be replaced and the number of partitions to replace are the same, the name of the official partition remains the same after replacement. If it is true, after the replacement, the name of the official partition is the name of the replacement partition. The following are examples:Example 1
ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);
If
use_temp_partition_name defaults to false, after the replacement, the name of the partition remains p1, but the related data and properties are replaced with tp1.
If use_temp_partition_name defaults to true, after the replacement, the name of the partition is tp1. The p1 partition no longer exists.Example 2
ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1);
If
use_temp_partition_name defaults to false, but because the number of partitions to be replaced and the number of replace partitions are different, this parameter is invalid. After the replacement, the partition names are tp1, and p1 and p2 no longer exist.Note
Once the partition is successfully replaced, the replaced partition will be deleted and cannot be recovered.
Importing and Querying Temporary Partitions
Users can import data into temporary partitions and also specify temporary partitions for queries.
1. Import into a temporary partition.
Depending on the method of importing, the syntax for specifying the import into a temporary partition varies slightly. This is illustrated with examples.
INSERT INTO tbl TEMPORARY PARTITION(tp1, tp2, ...) SELECT ....
curl --location-trusted -u root: -H "label:123" -H "temporary_partitions: tp1, tp2, ..." -T testData http://host:port/api/testDb/testTbl/_stream_load
LOAD LABEL example_db.label1(DATA INFILE("hdfs://hdfs_host:hdfs_port/user/palo/data/input/file")INTO TABLEmy_tableTEMPORARY PARTITION (tp1, tp2, ...)...)WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
CREATE ROUTINE LOAD example_db.test1 ON example_tblCOLUMNS(k1, k2, k3, v1, v2, v3 = k1 * 100),TEMPORARY PARTITIONS(tp1, tp2, ...),WHERE k1 > 100PROPERTIES(...)FROM KAFKA(...);
2. Query temporary partitions.
SELECT ... FROMtbl1 TEMPORARY PARTITION(tp1, tp2, ...)JOINtbl2 TEMPORARY PARTITION(tp1, tp2, ...)ON ...WHERE ...;
Relationship with Other Operations
DROP
After using the Drop command to delete a database or table directly, you can recover the database or table through the Recover command (within a certain time), but the temporary partitions are not recovered.
After using the Alter command to delete a formal partition, you can recover the partition through the Recover command (within a certain time). The operation of the formal partition is unrelated to the temporary partition.
If you delete a temporary partition with the Alter command, you cannot recover the temporary partition through the Recover command.
TRUNCATE
After you use the Truncate command to clear a table, the temporary partition of the table will be deleted and cannot be recovered.
When you use the Truncate command to clear the formal partition, it does not affect the temporary partition.
You cannot use the Truncate command to clear the temporary partition.
ALTER
When the table has a temporary partition, you cannot use the Alter command to change the table's Schema Change, Rollup, and other Alter operations.
While the table is undergoing Alter operations, you cannot add a temporary partition to the table.
Best Practice
1. Atomic overwrite operation.
In some cases, users hope to overwrite the data of a partition. If you choose to delete first and then import, there will be a period when the data cannot be viewed. At this time, the user can create a corresponding temporary partition first, import the new data into the temporary partition, and replace the original partition atomically through the replace operation to achieve the purpose. For atomic overwrite operations of non-partitioned tables:
2. Change the number of buckets.
In some cases, the user uses an inappropriate number of buckets when creating a partition. Then the user can create a temporary partition with a corresponding partition range and specify a new number of buckets. Then use the
INSERT INTO command to import data from the formal partition into the temporary partition. Through the replace operation, the original partition is replaced atomically, achieving the desired goal.3. Merge or split partition.
In some cases, users want to change the range of a partition, for example, merge two partitions, or split a large partition into multiple smaller ones. Then the user can create a temporary partition with the corresponding range after the merge or split and then use the
INSERT INTO command to import data from the formal partition into the temporary partition. Through the replace operation, the original partition is replaced atomically, achieving the desired goal.
Yes
No
Was this page helpful?