Drop hive partition by date range

Question

I use hive-0.10.0-cdh-4.7.0 in my environment.

I have a table named test store as sequence file and some partitions by date_dim like below:

game=Test/date_dim=2014-07-01    
game=Test/date_dim=2014-07-11    
game=Test/date_dim=2014-07-21    
game=Test/date_dim=2014-07-31

I want to drop partitions between 2014-07-21 and 2014-07-30 in SQL command:

alter table test drop partition (date_dim>='2014-07-11',date_dim<='2014-07-30')

I hope these 2 partitions be deleted:

game=Test/date_dim=2014-07-11    
game=Test/date_dim=2014-07-21

But actually, these 3 partitions be deleted:

game=Test/date_dim=2014-07-01
game=Test/date_dim=2014-07-11
game=Test/date_dim=2014-07-21

It seems hive drop partition only use the date_dim<='2014-07-30' condition.

Is there anyway to make hive drop partition as I wish?

HakkiBuyukcengiz HakkiBuyukcengiz · Accepted Answer · 2015-11-25T12:29:03

You should convert the string to the date type, for that purpose you can use unix_timestamp function:

alter table test drop partition (unix_timestamp(date_dim,'yyyy-MM-dd')>=unix_timestamp('2014-07-11','yyyy-MM-dd'),unix_timestamp(date_dim,'yyyy-MM-dd')<=unix_timestamp('2014-07-30','yyyy-MM-dd'))

Drop hive partition by date range

1 Answers