Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to delete/drop a partition of an external table along with data

avatar
Expert Contributor

I have an external table which is created with partitions and i would like to delete/drop few partition along with data as i no longer require it.

 

Appreciate any suggestions.

2 REPLIES 2

avatar
Super Collaborator

Did you try to drop the partition using Hive query ? It should look like this :

ALTER TABLE <table_name> DROP PARTITION (<partition_col_name>='<value>');

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DropPartitions

 

 

If it does not delete the data you will need to delete the directory of the partition (in HDFS) after deleting it using the Hive query.

avatar
Expert Contributor

You can use PURGE option to delete data file as well along with partition mentadata but it works only in INTERNAL/MANAGED tables

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec PURGE;

 

External Tables have a two step process to alterr table drop partition + removing file

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec;

hadoop fs -rm -r <partition file path>

Em Jay