Impala Query fails with NoSuchObjectException error

In the last few months, I have seem CDH users hitting Impala query returning NoSuchObjectException error very often. This happens when running query against a particular table with INT partition types and it failed with below message:

WARNINGS:
ImpalaRuntimeException: Error making 'alter_partitions' RPC to Hive Metastore:
CAUSED BY: InvalidOperationException: Alter partition operation failed: NoSuchObjectException(message:partition values=[2017, 6, 1, 8])

We have confirmed that the table has four partitions with Integer data type, and select individual partition works. The following scenario will trigger such error:

• Partitions with INT data type
• Partition data was inserted from Hive with zero prefixes, something like below query:

  INSERT OVERWRITE TABLE test_tbl PARTITION (year = '2017', month = '06'....) .....
  

• Partition data will be created under HDFS location like below:

  hdfs://nameservice1/user/hive/warehouse/test_tbl/year=2017/month=06/day=01/hour=08
  

• When query through Impala, since the data type is INT, Impala will convert values from “06” to 6, “01” to 1 etc, and will be looking for location :

  hdfs://nameservice1/user/hive/warehouse/test_tbl/year=2017/month=6/day=1/hour=8
  

  instead​ of:

  hdfs://nameservice1/user/hive/warehouse/test_tbl/year=2017/month=06/day=01/hour=08
  

  hence triggered NoSuchObjectException error.

To fix the issue, there are two options:

1. Convert the data type of partition columns to String, instead of Integer:

   ALTER TABLE test_tbl PARTITION COLUMN (year string);
   ALTER TABLE test_tbl PARTITION COLUMN (month string);
   ALTER TABLE test_tbl PARTITION COLUMN (day string);
   ALTER TABLE test_tbl PARTITION COLUMN (hour string);
   

2. if integer type need to be kept, then we will need to re-build the table into a new one and store them into locations without leading zeros. This can be done by running the following queries from Impala:

   CREATE TABLE new_test_tbl LIKE test_tbl;
   
   INSERT OVERWRITE TABLE new_test_tbl PARTITION (year, month, day, hour) as SELECT * FROM test_tbl;
   

   The new table will have leading zeros in partitions removed and then we can switch over to use the new table. When writing more data into the new table through Hive, please be sure to remove all leading zeros to prevent the issue from happening again.

Above steps should help resolve the issue. Hope they will help.