Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Guru

Hello,

When I am trying to drop an external table in hive then I am getting foloowing error, so can someone please help me to drop thsi table.

drop table hst_staging;

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException: Exception thrown when executing query : SELECT 'org.apache.hadoop.hive.metastore.model.MStorageDescriptor' AS `NUCLEUS_TYPE`,`A0`.`INPUT_FORMAT`,`A0`.`IS_COMPRESSED`,`A0`.`IS_STOREDASSUBDIRECTORIES`,`A0`.`LOCATION`,`A0`.`NUM_BUCKETS`,`A0`.`OUTPUT_FORMAT`,`A0`.`SD_ID` FROM `SDS` `A0` WHERE `A0`.`CD_ID` = ? LIMIT 0,1

at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:677)

at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:388)

at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:225)

at org.apache.hadoop.hive.metastore.ObjectStore.listStorageDescriptorsWithCD(ObjectStore.java:3177)

at org.apache.hadoop.hive.metastore.ObjectStore.removeUnusedColumnDescriptor(ObjectStore.java:3123)

at org.apache.hadoop.hive.metastore.ObjectStore.dropPartitions(ObjectStore.java:1803)

at sun.reflect.GeneratedMethodAccessor370.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:103)

at com.sun.proxy.$Proxy8.dropPartitions(Unknown Source)

at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.dropPartitionsAndGetLocations(HiveMetaStore.java:1838)

at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_core(HiveMetaStore.java:1673)

at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_with_environment_context(HiveMetaStore.java:1859)

at sun.reflect.GeneratedMethodAccessor247.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147)

at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)

at com.sun.proxy.$Proxy12.drop_table_with_environment_context(Unknown Source)

at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.drop_table_with_environment_context(HiveMetaStoreClient.java:2190)

at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.drop_table_with_environment_context(SessionHiveMetaStoreClient.java:120)

at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.dropTable(HiveMetaStoreClient.java:1034)

at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.dropTable(HiveMetaStoreClient.java:970)

at sun.reflect.GeneratedMethodAccessor228.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:178)

at com.sun.proxy.$Proxy13.dropTable(Unknown Source)

at org.apache.hadoop.hive.ql.metadata.Hive.dropTable(Hive.java:1108)

at org.apache.hadoop.hive.ql.metadata.Hive.dropTable(Hive.java:1045)

at org.apache.hadoop.hive.ql.exec.DDLTask.dropTable(DDLTask.java:4084)

at org.apache.hadoop.hive.ql.exec.DDLTask.dropTableOrPartitions(DDLTask.java:3940)

at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:341)

at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)

at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:89)

at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1748)

at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1494)

at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1291)

at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1158)

at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1153)

at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:197)

at org.apache.hive.service.cli.operation.SQLOperation.access$300(SQLOperation.java:76)

at org.apache.hive.service.cli.operation.SQLOperation$2$1.run(SQLOperation.java:253)

at java.security.AccessController.doPrivileged(Native Method)

at javax.security.auth.Subject.doAs(Subject.java:422)

at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)

at org.apache.hive.service.cli.operation.SQLOperation$2.run(SQLOperation.java:264)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

NestedThrowablesStackTrace:

java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails ("hive"."COLUMNS_V2", CONSTRAINT "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID"))

at sun.reflect.GeneratedConstructorAccessor300.newInstance(Unknown Source)

4 REPLIES 4

Re: Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Mentor

@Saurabh

You will need to disable the Foreign key before you can delete the tables as it will have a cascading effect

"Cannot delete or update a parent row: a foreign key constraint fails"

SET FOREIGN_KEY_CHECKS=0;-- to disable them
SET FOREIGN_KEY_CHECKS=1;-- to re-enable them

Hope that helps

Highlighted

Re: Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Guru

Thanks @Geoffrey Shelton Okot You meant to say I need to disable in mysql and then drop the table ?

If yes then I tried but failing with same error:

mysql> SET FOREIGN_KEY_CHECKS=0;

Query OK, 0 rows affected (0.00 sec)

hive> drop table i0177a_cus_hdr_hst_staging;

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException: Exception thrown when executing query : SELECT 'org.apache.hadoop.hive.metastore.model.MStorageDescriptor' AS `NUCLEUS_TYPE`,`A0`.`INPUT_FORMAT`,`A0`.`IS_COMPRESSED`,`A0`.`IS_STOREDASSUBDIRECTORIES`,`A0`.`LOCATION`,`A0`.`NUM_BUCKETS`,`A0`.`OUTPUT_FORMAT`,`A0`.`SD_ID` FROM `SDS` `A0` WHERE `A0`.`CD_ID` = ? LIMIT 0,1

at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:677)

at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:388)

Re: Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Guru

Also I tried to set global and then restarted hive metastore. But same error.

mysql> SET GLOBAL FOREIGN_KEY_CHECKS=0;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

+-----------------------------+------------------------------+

| @@GLOBAL.foreign_key_checks | @@SESSION.foreign_key_checks |

+-----------------------------+------------------------------+

| 0 |1 |

+-----------------------------+------------------------------+

1 row in set (0.00 sec)

drop table i0177a_cus_hdr_hst_staging;

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException: Exception thrown when executing query : SELECT 'org.apache.hadoop.hive.metastore.model.MStorageDescriptor' AS `NUCLEUS_TYPE`,`A0`.`INPUT_FORMAT`,`A0`.`IS_COMPRESSED`,`A0`.`IS_STOREDASSUBDIRECTORIES`,`A0`.`LOCATION`,`A0`.`NUM_BUCKETS`,`A0`.`OUTPUT_FORMAT`,`A0`.`SD_ID` FROM `SDS` `A0` WHERE `A0`.`CD_ID` = ? LIMIT 0,1

at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:677)

Re: Not able to drop hive table error is org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDOException

Mentor

@Saurabh

Can you describe table i0177a_cus_hdr_hst_staging?

Can you share the DDL used to create this external table?

Under your current (possibly flawed) design, you must delete the row out of the parent table before you can delete the row in the i0177a_cus_hdr_hst_staging table that it references.

Don't have an account?
Coming from Hortonworks? Activate your account here