Drop all partitions from a hive table?

Question

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 1

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;

Answer 2

Hive allows you to use comparison operators (e.g. >, <, =, <> ) when selecting partitions. For example, the following should drop all partitions in the table.

ALTER TABLE table_name DROP PARTITION (partition_name > '0');

Answer 3

create a new table t2 from existing table t1 like below.

 create table t2 as
    select * from t1;

drop old table t1

drop table t1;

now check if you have partitions on new table.

show partitions t2;

Answer 4

Create table using data from original table:

CREATE TABLE t2 AS
SELECT column_name_1, ..., column_name_N FROM t1;

Only case is that it should be done in non-strict mode:

set hive.mapred.mode=nonstrict;

I hope it helps. GL!

Answer 5

truncate table table_name; 

will delete all the partitions. This is useful especially if you want to drop partitioned table.