Reply
Explorer
Posts: 14
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.

Highlighted
Posts: 177
Topics: 8
Kudos: 28
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.

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