Unable to drop Hive table due to corrupt partition.

[#1]

When running a Hive CTAS query that was using wrong serde (accidently) the query was killed in the middle which caused a few partitions to get created but the partition looks corrupted.. Notice the non-ascii character in the partition name.

/apps/hive/warehouse/mydb.db/mytbl
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=ϧ
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=ϧ/equip_nbr_l1=__HIVE_DEFAULT_PARTITION__
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=ϧ/equip_nbr_l1=__HIVE_DEFAULT_PARTITION__/000004_0
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=?
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=?/equip_nbr_l1=__HIVE_DEFAULT_PARTITION__
/apps/hive/warehouse/mydb.db/mytbl/created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=?/equip_nbr_l1=__HIVE_DEFAULT_PARTITION__/000083_0

When running a DROP table statement is run, following exception appears in the metastore.log

2015-10-13 17:55:50,660 ERROR [pool-3-thread-35]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:invoke(151)) - Error happens in method drop_table_with_environment_context: MetaException(message:Timeout when executing method: drop_table_with_environment_context)
	at org.apache.hadoop.hive.metastore.Deadline.newMetaException(Deadline.java:187)
	at org.apache.hadoop.hive.metastore.Deadline.check(Deadline.java:177)
	at org.apache.hadoop.hive.metastore.Deadline.checkTimeout(Deadline.java:160)
	at org.apache.hadoop.hive.metastore.ObjectStore.convertToParts(ObjectStore.java:1820)
	at org.apache.hadoop.hive.metastore.ObjectStore.convertToParts(ObjectStore.java:1807)
	at org.apache.hadoop.hive.metastore.ObjectStore.access$200(ObjectStore.java:160)
	at org.apache.hadoop.hive.metastore.ObjectStore$2.getJdoResult(ObjectStore.java:1734)
	at org.apache.hadoop.hive.metastore.ObjectStore$2.getJdoResult(ObjectStore.java:1725)
	at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2391)
	at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsInternal(ObjectStore.java:1725)
	at org.apache.hadoop.hive.metastore.ObjectStore.getPartitions(ObjectStore.java:1719)
	at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
	at com.sun.proxy.$Proxy0.getPartitions(Unknown Source)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.dropPartitionsAndGetLocations(HiveMetaStore.java:1693)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_core(HiveMetaStore.java:1532)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_with_environment_context(HiveMetaStore.java:1737)
	at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy5.drop_table_with_environment_context(Unknown Source)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$drop_table_with_environment_context.getResult(ThriftHiveMetastore.java:9256)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$drop_table_with_environment_context.getResult(ThriftHiveMetastore.java:9240)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
	at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.hadoop.hive.metastore.DeadlineException: Timeout when executing method: drop_table_with_environment_context
	at org.apache.hadoop.hive.metastore.Deadline.check(Deadline.java:174)
	... 35 more

[#2]

Here is what I see in the `PARTITIONS` table in metastore (special

(404,1444064420,0,'created_yr=__HIVE_DEFAULT_PARTITION__/created_mo=__HIVE_DEFAULT_PARTITION__/equip_init_f1=?/equip_nbr_l1=__HIVE_DEFAULT_PARTITION__',452,48,NULL)

[#3]

Also, Since the DROP table command just hangs, what will be a good work around to completely removing this table and partitions?

[#4 · Accepted solution]

Try creating a temporary database and move the table 'as is' into the new database.

CREATE DATABASE if not exists Junk;

USE targetDB;

ALTER TABLE MyCorruptTable RENAME TO Junk.MyMovedCorruptTable;

DROP DATABASE JUNK Cascade;

[#5]

Thanks David, will try that and let you know how it goes.

[#6]

Thanks David, for some reason the 3rd statement to alter the table to rename it fails with following error message -

metastore.RetryingHMSHandler (RetryingHMSHandler.java:invoke(159)) - InvalidOperationException(message:Unable to change partition or table. Database th_ti_hit_src does not exist Check metastore logs for detailed stack.junk_th)

  at org.apache.hadoop.hive.metastore.HiveAlterHandler.alterTable(HiveAlterHandler.java:253)

Looks like we will have to clean the table up manually from metastore and then delete the partitions manually.

[#7]

@bsaini@hortonworks.com Can you verify this ? th_ti_hit_src does not exist

[#8]

That database exists. User is able to use that database and also manually verified that in the metastore.

[#9]

Is the DROP TABLE command hanging or is just outright fails? If its hanging it would be good to see the stack dump from the cli to determine where it hangs. The other option is to manually delete the HDFS directory (recursive remove) of the offending table followed by a drop table if exists <tablename>.

[#10]

I have similar issue with one of my table which has about 7000 partitions and they are in the form as follows:

id_copy=íí?@%00%00%1CS

id_copy=ñ%10o%14%00%00%1CS

id_copy=ñ%17ç%0C%00%00%1CS

I have tried the above suggestions above , none had resolved the issue. If you have any other suggestion , it would be appreciated.

,

I have a table with partitions as below in total 7000 and I am not able to neither drop the table nor the database.

any suggestion is really appreciated.

id_copy=íí?@%00%00%1CS

id_copy=ñ%10o%14%00%00%1CS

id_copy=ñ%17ç%0C%00%00%1CS