dropping hive partition dynamically

Question

I have an HIVE table with daily partitions day wise, something like below (which includes future date's partition as well)

20160901
20160902
........
........
........
20160931
20161001
20161002

I want to pass one date say for example yesterday's date 20160922 and want to drop all partitions dynamically which are >= 20160922 (though today is 20160923, but I want to drop from 20160922 date).

How can I can drop all these partitions dynamically.

Answer 1

You can not do in hive directly as it does not support dynamic sql.

There can be work around using shell script/or any script create file having drop partition script like below.

alter table partition_t drop if exists partition (y=20160922 );

alter table partition_t drop if exists partition (y=20160921 );

alter table partition_t drop if exists partition (y=20160920 ); ...

then run hive -v -f ./file.sh

alter table partition_t drop if exists partition

Answer 2

Before Inserting Data Into Table Perform the below steps.

1) Go to Hdfs Folder of that table and delete all the folders Inside Table Directory using Shell Commands. hadoop fs -rm r <>

2) Run MSCK repair Table to update the metadata about partitions.

above two steps will delete all the available partitions based on pattern.

Now Insert your new data.

Answer 3

You can drop partitions giving a range filter. For reference see that answer : https://stackoverflow.com/a/48422251/3132181 So your code could be like that:

Alter table mytable drop partition (datehour >= '20160922')