tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Tencent Cloud TCHouse-D
    Documentation
    Download PDF

    Temporary Partition

    Last updated: 2024-06-27 11:09:27
    Download PDF
      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.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy