Search code examples
hiveelastic-map-reduce

Drop all partitions from a hive table?

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.

Solution

  • 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;