On this page

Show all

Help Center/ Data Lake Insight/ Spark SQL Syntax Reference/ Tables/ Partition-related Syntax/ Deleting Partitions by Specifying Filter Criteria (Only Supported on OBS Tables)

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

Updated on 2024-07-04 GMT+08:00

View PDF

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

    
       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

**Table 1** Parameters
Parameter	Description
db_name	Database name that contains letters, digits, and underscores (_). It cannot contain only digits or start with an underscore (_).
table_name	Table name of a database that contains letters, digits, and underscores (_). It cannot contain only digits or start with an underscore (_). The matching rule is *^(?!_)(?![0-9]+$)[A-Za-z0-9_$]$. If special characters are required, use single quotation marks ('') to enclose them. This statement is used for OBS table operations.**
partition_filtercondition	Condition used to search partitions to be deleted. The format is as follows: Partition column name Operator Value to compare Example: start_date < '201911' Example 1: <partition_filtercondition1> AND\|OR <partition_filtercondition2> Example: start_date < '201911' OR start_date >= '202006' Example 2: (<partition_filtercondition1>)[,partitions (<partition_filtercondition2>), ...] Example: (start_date <> '202007'), partitions(start_date < '201912')

Example

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

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.

      
         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);

Insert partition data into the table.

You can insert the following data:

      
       
         
         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");

        

      
     

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

Delete a partition.

NOTE:

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

Parent topic: Partition-related Syntax

Previous topic: Deleting a Partition

Next topic: Altering the Partition Location of a Table (Only OBS Tables Supported)

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot