Drop all partitions from a hive table?

Matt Joiner picture Matt Joiner · Mar 19, 2013 · Viewed 23.8k times · Source

How can I drop all partitions currently loaded in a Hive table?

I can drop a single partition with alter table <table> drop partition(a=, b=...);

I can load all partitions with the recover partitions statement. But I cannot seem to drop all partitions.

I'm using the latest Hive version supported by EMR, 0.8.1.

Answer

Lorand Bendig picture Lorand Bendig · Mar 19, 2013

As of version 0.9.0 you can use comparators in the drop partition statement which may be used to drop all partitions at once.

An example, taken from the drop_partitions_filter.q testcase :

create table ptestfilter (a string, b int) partitioned by (c string, d string);
alter table ptestfilter add partition (c='US', d=1);
alter table ptestfilter add partition (c='US', d=2);
alter table ptestFilter add partition (c='Uganda', d=2);
alter table ptestfilter add partition (c='Germany', d=2);
alter table ptestfilter add partition (c='Canada', d=3);
alter table ptestfilter add partition (c='Russia', d=3);
alter table ptestfilter add partition (c='Greece', d=2);
alter table ptestfilter add partition (c='India', d=3);
alter table ptestfilter add partition (c='France', d=4);

show partitions ptestfilter;
alter table ptestfilter drop partition (c>'0', d>'0');
show partitions ptestfilter;