Deleting Partitions by Specifying Filter Criteria (Only Supported on OBS Tables)

Function

This statement is used to delete one or more partitions based on specified conditions.

Precautions

• This statement is only used for OBS tables.
• The table in which partitions are to be deleted must exist. Otherwise, an error is reported.
• The partition to be deleted must exist. Otherwise, an error is reported. To avoid this error, add IF EXISTS to this statement.

Syntax

1
2
3

ALTER TABLE [db_name.]table_name
  DROP [IF EXISTS]
  PARTITIONS partition_filtercondition;

Keywords

• DROP: deletes specified partitions.
• IF EXISTS: Partitions to be deleted must exist. Otherwise, an error is reported.
• PARTITIONS: specifies partitions meeting the conditions

Parameters

Example

To help you understand how to use this statement, this section provides an example of deleting a partition from the source data.

1. Use the DataSource syntax to create an OBS partitioned table.

   An OBS partitioned table named student is created, which contains the student ID (id), student name (name), student faculty number (facultyNo), and student class number (classNo) and uses facultyNo and classNo for partitioning.

   1 2 3 4 5 6 7 8

   create table if not exists student ( id int, name STRING, facultyNo int, classNo INT) using csv options (path 'path 'obs://bucketName/filePath'') partitioned by (faculytNo, classNo);

2. Insert partition data into the table.

   You can insert the following data:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   INSERT into student partition (facultyNo = 10, classNo = 101) values (1010101, "student01"), (1010102, "student02"); INSERT into student partition (facultyNo = 10, classNo = 102) values (1010203, "student03"), (1010204, "student04"); INSERT into student partition (facultyNo = 20, classNo = 101) values (2010105, "student05"), (2010106, "student06"); INSERT into student partition (facultyNo = 20, classNo = 102) values (2010207, "student07"), (2010208, "student08"); INSERT into student partition (facultyNo = 20, classNo = 103) values (2010309, "student09"), (2010310, "student10"); INSERT into student partition (facultyNo = 30, classNo = 101) values (3010111, "student11"), (3010112, "student12"); INSERT into student partition (facultyNo = 30, classNo = 102) values (3010213, "student13"), (3010214, "student14");

3. View the partitions.

   You can view all partitions in the table.

   The example code is as follows:

   SHOW partitions student;

   Table 2 Example table data

   facultyNo	classNo
   facultyNo=10	classNo=101
   facultyNo=10	classNo=102
   facultyNo=20	classNo=101
   facultyNo=20	classNo=102
   facultyNo=20	classNo=103
   facultyNo=30	classNo=101
   facultyNo=30	classNo=102

4. Delete a partition.
   

   This step describes how to delete a partition by specifying filter criteria. If you want to delete a partition without specifying filter criteria, see Deleting a Partition.

   This example cannot be used together with that in Deleting a Partition. Distinguish the keyword partitions in this example from the keyword partition in the example in Deleting a Partition.

   • Example 1: deleting partitions by specifying filter criteria (only supported on OBS tables), and using the AND statement to delete partitions

     Table 3 Data before execution

     facultyNo	classNo
     facultyNo=10	classNo=101
     facultyNo=10	classNo=102
     facultyNo=20	classNo=101
     facultyNo=20	classNo=102

     Run the following statements to delete the partitions whose facultyNo is 20 and classNo is 102:

     ALTER TABLE student
     DROP IF EXISTS
     PARTITIONS (facultyNo = 20 AND classNo = 102);

     You can see that the statement deletes the partitions that meet both the criteria.

     Table 4 Data after execution

     facultyNo	classNo
     facultyNo=10	classNo=101
     facultyNo=10	classNo=102
     facultyNo=20	classNo=101

   • Example 2: deleting partitions by specifying filter criteria (only supported on OBS tables), and using the OR statement to delete partitions

     Table 5 Data before execution

     facultyNo	classNo
     facultyNo=10	classNo=101
     facultyNo=10	classNo=102
     facultyNo=20	classNo=101
     facultyNo=20	classNo=102

     Run the following statements to delete the partitions whose facultyNo is 10 or classNo is 101:

     ALTER TABLE student 
     DROP IF EXISTS
     PARTITIONS (facultyNo = 10),
     PARTITIONS (classNo = 101);

     Execution result:

     Table 6 Data after execution

     facultyNo	classNo
     facultyNo=20	classNo=102

     Under the selected deletion criteria, the first record in the partition meets both facultyNo and classNo, the second record meets facultyNo, and the third record meets classNo.

     As a result, only one partition row remains after executing the partition deletion statement.

     According to method 1, the foregoing execution statement may also be written as:

     ALTER TABLE student
     DROP IF EXISTS
     PARTITIONS (facultyNo = 10 OR classNo = 101);

   • Example 3: deleting partitions by specifying filter criteria (only supported on OBS tables), and using relational operator statements to delete specified partitions

     Table 7 Data before execution

     facultyNo	classNo
     facultyNo=10	classNo=101
     facultyNo=10	classNo=102
     facultyNo=20	classNo=101
     facultyNo=20	classNo=102
     facultyNo=20	classNo=103

     Run the following statements to delete partitions whose classNo is greater than 100 and less than 102:

     ALTER TABLE student
     DROP IF EXISTS
     PARTITIONS (classNo BETWEEN 100 AND 102);

     Execution result:

     Table 8 Data before execution

     facultyNo	classNo
     facultyNo=20	classNo=103