Deleting a Partition

Function

This statement is used to delete one or more partitions from a partitioned table.

Partitioned tables are classified into OBS tables and DLI tables. You can delete one or more partitions from a DLI or OBS partitioned table based on specified conditions. OBS tables also support deleting partitions by specifying filter criteria. For details, see Deleting Partitions by Specifying Filter Criteria (Only Supported on OBS Tables).

Precautions

• 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]
  PARTITION partition_spec1[,PARTITION partition_spec2,...];

Keywords

• DROP: deletes a partition.
• IF EXISTS: The partition to be deleted must exist. Otherwise, an error is reported.
• PARTITION: specifies the partition to be deleted

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 '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.
   • Example 1: deleting a partition by specifying multiple filter criteria

     In this example, the partition whose facultyNo is 20 and classNo is 103 is deleted.

     

     For details about how to delete a partition by specifying filter criteria, see Deleting Partitions by Specifying Filter Criteria (Only Supported on OBS Tables).

     The example code is as follows:

     ALTER TABLE student
     DROP IF EXISTS
     PARTITION (facultyNo=20, classNo=103);

     Use the method described in step 3 to check the partitions in the table. You can see that the partition has been deleted.

     SHOW partitions student;

   • Example 2: deleting a partition by specifying a single filter criterion

     In this example, the partitions whose facultyNo is 30 is deleted. During data insertion, there are two partitions whose facultyNo is 30.

     

     For details about how to delete a partition by specifying filter criteria, see Deleting Partitions by Specifying Filter Criteria (Only Supported on OBS Tables).

     The example code is as follows:

     ALTER TABLE student
     DROP IF EXISTS
     PARTITION (facultyNo = 30);

     Execution result:

     Table 3 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