question Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB. in Archives of Support Questions (Read Only)

Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Mon, 25 Apr 2016 16:27:26 GMT

2016-04-25 09:14:29,143 ERROR [main]: metastore.HiveMetaStore (HiveMetaStore.java:startMetaStore(6068)) - javax.jdo.JDOUserException: Exception thrown while loading remaining rows of query at org.datanucleus.api.jdo.JDOAdapter.getUserExceptionForException(JDOAdapter.java:1139) at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:296) at org.datanucleus.store.query.AbstractQueryResult.disconnect(AbstractQueryResult.java:107) at org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.disconnect(AbstractRDBMSQueryResult.java:236) at org.datanucleus.store.rdbms.query.JDOQLQuery$2.transactionPreClose(JDOQLQuery.java:755) at org.datanucleus.store.connection.AbstractManagedConnection.transactionPreClose(AbstractManagedConnection.java:94) at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionPreRollBack(ConnectionManagerImpl.java:447) at org.datanucleus.TransactionImpl.internalPreRollback(TransactionImpl.java:500) at org.datanucleus.TransactionImpl.rollback(TransactionImpl.java:427) at org.datanucleus.api.jdo.JDOTransaction.rollback(JDOTransaction.java:182) at org.apache.hadoop.hive.metastore.ObjectStore.rollbackTransaction(ObjectStore.java:505) at org.apache.hadoop.hive.metastore.ObjectStore.getMSchemaVersion(ObjectStore.java:6763) at org.apache.hadoop.hive.metastore.ObjectStore.getMetaStoreSchemaVersion(ObjectStore.java:6732) at org.apache.hadoop.hive.metastore.ObjectStore.checkSchema(ObjectStore.java:6690) at org.apache.hadoop.hive.metastore.ObjectStore.verifySchema(ObjectStore.java:6674) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498)

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Thu, 28 Apr 2016 14:10:48 GMT

While installing HDP 2.4 using Ambari, Oracle 12c is used as the external DB for Hive Metastore. The service shows green on Ambari when started but the logs <hive-metastore-error.txt> show that hive is not able to write to the external DB (Oracle 12c).

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

mlanciaux — Wed, 11 May 2016 18:35:59 GMT

Hello @Joshua Adeleke, I have seen some issues with Oracle and some of our components depending on the version. I will try to gather more information about this case.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

mlanciaux — Wed, 11 May 2016 18:46:10 GMT

Can you check you have tables created in the Oracle instance ?

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Wed, 11 May 2016 19:12:03 GMT

Hello @mlanciaux, the tables were created in the oracle instance.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

mlanciaux — Mon, 23 May 2016 17:32:18 GMT

Can you try to INSERT values in one table in Oracle (test) I guess you have GRANT since you have created tables but just to be sure.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Mon, 01 Aug 2016 14:19:55 GMT

Changing from Oracle 12c to MySQL DB got Hive working without issues.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

sbomma — Wed, 14 Sep 2016 02:13:27 GMT

Was this ERROR solved? One of the clients i am working is facing the same error. Is there a patch available for metastore to work with oracle.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Wed, 14 Sep 2016 12:35:48 GMT

@Satish Bomma The error was not resolved on Oracle 12c. The workaround was to use MySQL instead of Oracle 12c and it worked and is still working fine.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

sbomma — Wed, 14 Sep 2016 21:06:45 GMT

Thanks @Joshua Adeleke, this solution is not acceptable for my client as they want to install metastore db in Oracle. Was there any Support tickets opened for this issue?

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

joshua_adeleke — Thu, 15 Sep 2016 11:52:10 GMT

Pleasure @Satish Bomma. Unfortunately, i did not open a support ticket for this issue. Please update me on the fix if you get it resolved. Thank you. Maybe i should also add that our Oozie workflows were also very epileptic when it was on Oracle 12c and was only stable when it was changed to MySQL.

Re: Critical alerts for Hive Metastore. Hive not able to write to Oracle DB.

hrishi1dypim — Thu, 29 Sep 2016 15:25:05 GMT

@Satish Bomm,@Joshua Adeleke i am getting same problem. the surprise is i am able to create all hive tables to oracle and that too from hive/bin/schematool but when starting metastore getting this problem.using oracle 11g with java 8 and ojdbc7.jar. any solution will help. i have to setup on oracle only.