Reply
Explorer
Posts: 11
Registered: ‎12-21-2016

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

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.

Posts: 177
Topics: 8
Kudos: 27
Solutions: 19
Registered: ‎07-16-2015

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

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.

Highlighted
Expert Contributor
Posts: 126
Registered: ‎11-01-2013

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

[ Edited ]

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
Announcements