How to update partition metadata in Hive , when partition data is manualy deleted from HDFS

hive partitioning
vinu.m.19 picture vinu.m.19 · Jan 14, 2014 · Viewed 38.5k times · Source

What is the way to automatically update the metadata of Hive partitioned tables?

If new partition data's were added to HDFS (without alter table add partition command execution) . then we can sync up the metadata by executing the command 'msck repair'.

What to be done if a lot of partitioned data were deleted from HDFS (without the execution of alter table drop partition commad execution).

What is the way to syncup the Hive metatdata?

Answer

cheseaux picture cheseaux · Jul 11, 2017

EDIT : Starting with Hive 3.0.0 MSCK can now discover new partitions or remove missing partitions (or both) using the following syntax :

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS]

This was implemented in HIVE-17824

As correctly stated by HakkiBuyukcengiz, MSCK REPAIR doesn't remove partitions if the corresponding folder on HDFS was manually deleted, it only adds partitions if new folders are created.

Extract from offical documentation :

In other words, it will add any partitions that exist on HDFS but not in metastore to the metastore.

This is what I usually do in the presence of external tables if multiple partitions folders are manually deleted on HDFS and I want to quickly refresh the partitions :

  • Drop the table (DROP TABLE table_name) (dropping an external table does not delete the underlying partition files)
  • Recreate the table (CREATE EXTERNAL TABLE table_name ...)
  • Repair it (MSCK REPAIR TABLE table_name)

Depending on the number of partitions this can take a long time. The other solution is to use ALTER TABLE DROP PARTITION (...) for each deleted partition folder but this can be tedious if multiple partitions were deleted.

Related questions

Dropping multiple partitions in Impala/Hive

1- I'm trying to delete multiple partitions at once, but struggling to do it with either Impala or Hive. I tried the following query, with and without ': ALTER TABLE cz_prd_corrti_st.s1mme_transstats_info DROP IF …

Read More
how to add columns to existing hive partitioned table?

alter table abc add columns (stats1 map<string,string>, stats2 map<string,string>) i have altered my table with above query. But after while checking the data i got NULL's for the both extra columns. I'm …

Read More
Spark SQL saveAsTable is not compatible with Hive when partition is specified

Kind of edge case, when saving parquet table in Spark SQL with partition, #schema definitioin final StructType schema = DataTypes.createStructType(Arrays.asList( DataTypes.createStructField("time", DataTypes.StringType, true), DataTypes.createStructField("accountId", DataTypes.StringType, true), ... DataFrame df = hiveContext.read().schema(schema).…

Read More