Hive Partition recovery

Question

How to recover partitions in easy fashion. Here is the scenario :

1. Have 'n' partitions on existing external table 't'
2. Dropped table 't'
3. Recreated table 't' // Note : same table but with excluding some column
4. How to recover the 'n' partitions that existed for table 't' in step #1 ?

I can manually alter table to add 'n' partition by writing some script. But that's very tedious. Is there something built-in to recover these partitions ?

Carriecarrier

Answer 1

When the partitions directories still exist in the HDFS, simply run this command:

MSCK REPAIR TABLE table_name;

It adds the partitions definitions to the metastore based on what exists in the table directory.

Crept

I didnt try it with custom Serde, but i think Serdes does not affect this command, it should work fine ! – Crept

Yeah, you are correct, The Serde should not have any issues. But from what the documentation mentions is that the partitions can be recovered if only the parition format is 'yyyy-mm-dd'. Not sure how to use for custom partition format ? in mycase, I have something like yyyymmdd. – Carriecarrier

The documentation didnt put any limit for that, but by logic there is one thing to care about, is the partition format should be 'key=value', eg. date=160528 and no problem if it has hyphens or not. – Crept

Answer 2

Metadata is not saved in the trash and is removed permanently; you will not be able to restore the metadata of dropped tables, partitions, etc. Reference: http://www.cloudera.com/documentation/archive/cdh/4-x/4-7-1/CDH4-Installation-Guide/cdh4ig_hive_trash.html

Mclemore