tencent cloud

Tencent Cloud TCHouse-D

Product Introduction
Overview
Concepts
Cluster Architecture
Strengths
Scenarios
Purchase Guide
Billing Overview
Renewal Instructions
Overdue Policy
Refund Instructions
Configuration Adjustment Billing Instructions
Getting Started
Using Tencent Cloud TCHouse-D Through the Console
Using Tencent Cloud TCHouse-D Through a Client
Operation Guide
Cluster Operation
Monitoring and Alarm Configuration
Account Privilege Management
Data Management
Query Management
Modify Configurations
Node Management
Log Analysis
SQL Studio
Enabling Resource Isolation
Development Guide
Design of Data Table
Importing Data
Exporting Data
Basic Feature
Query Optimization
Ecological Expansion Feature
API Documentation
History
Introduction
API Category
Making API Requests
Cluster Operation APIs
Database and Table APIs
Cluster Information Viewing APIs
Hot-Cold Data Layering APIs
Database and Operation Audit APIs
User and Permission APIs
Resource Group Management APIs
Data Types
Error Codes
Cloud Ecosystem
Granting CAM Policies to Sub-accounts
Query Acceleration for Tencent Cloud DLC
Practical Tutorial
Basic Feature Usage
Advanced Features Usage
Resource Specification Selection and Optimization Suggestions
Naming Specifications and Limits to the Database and Data Table
Table Design and Data Import
Query Optimization
Suggested Usage to Avoid
Accessing TCHouse-D via JDBC over the Public Network
Performance Testing
TPC-H Performance Testing
SSB Performance Testing
TPC-DS Performance Testing
FAQs
Common Operational Issues
Common Errors
Contact Us
Glossary
Product Policy
Service Level Agreement
Privacy Policy
Data Processing And Security Agreement
DocumentaçãoTencent Cloud TCHouse-DDevelopment GuideBasic FeatureTemporary Partition

Temporary Partition

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 2024-06-27 11:09:27
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 statement
ALTER 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 TABLE my_table
TEMPORARY PARTITION (tp1, tp2, ...)
...
)
WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");
CREATE ROUTINE LOAD example_db.test1 ON example_tbl
COLUMNS(k1, k2, k3, v1, v2, v3 = k1 * 100),
TEMPORARY PARTITIONS(tp1, tp2, ...),
WHERE k1 > 100
PROPERTIES
(...)
FROM KAFKA
(...);
2. Query temporary partitions.
SELECT ... FROM
tbl1 TEMPORARY PARTITION(tp1, tp2, ...)
JOIN
tbl2 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.
Anterior: Dynamic PartitionPróximo: Auto Bucketing

Ajuda e Suporte

Esta página foi útil?

comentários