On this page

Show all

Help Center/ Data Lake Insight/ SQL Syntax Reference (Kuala Lumpur Region)/ Spark SQL Syntax Reference/ Syntax for Partitioning a Table/ Deleting a Partition

Deleting a Partition

Updated on 2025-01-22 GMT+08:00

View PDF

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

    
       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

**Table 1** Parameters
Parameter	Description
db_name	Database name that contains letters, digits, and underscores (_). It cannot contain only digits and cannot start with an underscore (_).
table_name	Table name of a database that contains letters, digits, and underscores (_). It cannot contain only digits and cannot 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.
partition_specs	Partition information, in the format of "key=value", where key indicates the partition field and value indicates the partition value. In a table partitioned using multiple fields, if you specify all the fields of a partition name, only the partition is deleted; if you specify only some fields of a partition name, all matching partitions will be deleted. By default, parameters in partition_specs contain parentheses (), for example, PARTITION (facultyNo=20, classNo=103);.

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 '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.

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.

NOTE:

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.

NOTE:

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

Parent topic: Syntax for Partitioning a Table

Previous topic: Renaming a Partition (Only OBS Tables Supported)

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

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