Merge Existing Partition in HIVE

Question

How to merge existing Partitions and make it to one Partition. For ex : I have Partitions on Year column like year=2011,year=2012,year=2013,year=2014.

My requirement is to merge partitions from 2011 to 2013 partitions. So that I can have only 2 partitions 2013 and 2014.

Please help.

Regards, Manoj

Answer 1

1) create new target table

2) Insert data into target table with dynamic partition loading:

insert overwrite table partition (partition_year)
select col1, col2 ..., case when year between 2011 and 2013 then 2013
                            when year >=2014 then 2014 
                         end  as partition_year from source_table

3) drop source_table

Answer 2

If your partitioning column year is defined as STRING, then you can just...

1. create a new partition for year=History (for instance)
2. move brutally the data files from directories such as .../year=2011/ to the new dir .../year=History/
3. drop the partitions that are now empty