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

by

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.

More Related Contents:

  1. How to set variables in HIVE scripts
  2. How to control partition size in Spark SQL
  3. How to optimize partitioning when migrating data from JDBC source?
  4. How to create n number of external tables with a single hdfs path using Hive
  5. Create Table in Hive with one file
  6. Specify minimum number of generated files from Hive insert
  7. Hive Explode / Lateral View multiple arrays
  8. Hive: Add partitions for existing folder structure
  9. java.lang.RuntimeException:Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
  10. When creating an external table in hive can I point the location to specific files in a directory?
  11. How to skip CSV header in Hive External Table?
  12. Update , SET option in Hive
  13. Partitioning in spark while reading from RDBMS via JDBC
  14. How to create SparkSession with Hive support (fails with “Hive classes are not found”)?
  15. What is the difference between partitioning and bucketing a table in Hive ?
  16. How to transpose/pivot data in hive?
  17. How to save DataFrame directly to Hive?
  18. Efficient way to divide a list into lists of n size
  19. Default Partitioning Scheme in Spark
  20. Use collect_list and collect_set in Spark SQL
  21. How to convert ISO Date to UTC date in Hive
  22. Hive unable to manually set number of reducers
  23. How to update table in Hive 0.13?
  24. Determining optimal number of Spark partitions based on workers, cores and DataFrame size
  25. How to Access Hive via Python?
  26. Overwrite only some partitions in a partitioned spark Dataset
  27. Job queue for Hive action in oozie
  28. What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask
  29. Hive loading in partitioned table
  30. What is the equivalent of Presto UNNEST function in Hive

Leave a Comment