JDBC error with Hive ACID Enabled

[#1]

Observed in Teradata's distribution of Hortonworks, when ACID is enabled via Ambari, following error can show up.

Error: java.lang.AbstractMethodError: Method org/postgresql/jdbc3/Jdbc3ResultSet.isClosed()Z is abstract (state=,code=0)
org.apache.hive.service.cli.HiveSQLException: java.lang.AbstractMethodError: Method org/postgresql/jdbc3/Jdbc3ResultSet.isClosed()Z is abstract
	at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:258)
	at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:244)
	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:247)
	at org.apache.hive.beeline.Commands.execute(Commands.java:848)
	at org.apache.hive.beeline.Commands.sql(Commands.java:713)
	at org.apache.hive.beeline.BeeLine.dispatch(BeeLine.java:983)
	at org.apache.hive.beeline.BeeLine.initArgs(BeeLine.java:718)
	at org.apache.hive.beeline.BeeLine.begin(BeeLine.java:767)
	at org.apache.hive.beeline.BeeLine.mainWithInputRedirection(BeeLine.java:485)
	at org.apache.hive.beeline.BeeLine.main(BeeLine.java:468)
	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)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.lang.RuntimeException: java.lang.AbstractMethodError: Method org/postgresql/jdbc3/Jdbc3ResultSet.isClosed()Z is abstract
	at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:83)
	at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36)
	at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63)
	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:1709)
	at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59)
	at com.sun.proxy.$Proxy20.executeStatementAsync(Unknown Source)
	at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:276)
	at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
	at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1317)
	at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1302)
	at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
	at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
	at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
	at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
	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)
Caused by: java.lang.AbstractMethodError: Method org/postgresql/jdbc3/Jdbc3ResultSet.isClosed()Z is abstract
	at org.postgresql.jdbc3.Jdbc3ResultSet.isClosed(Jdbc3ResultSet.java)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.close(TxnHandler.java:1047)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.close(TxnHandler.java:1059)
	at org.apache.hadoop.hive.metastore.txn.TxnHandler.getOpenTxns(TxnHandler.java:279)
	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_open_txns(HiveMetaStore.java:5567)
	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)
	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107)
	at com.sun.proxy.$Proxy8.get_open_txns(Unknown Source)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getValidTxns(HiveMetaStoreClient.java:1829)
	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)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:156)
	at com.sun.proxy.$Proxy9.getValidTxns(Unknown Source)
	at org.apache.hadoop.hive.ql.lockmgr.DbTxnManager.getValidTxns(DbTxnManager.java:327)
	at org.apache.hadoop.hive.ql.Driver.recordValidTxns(Driver.java:994)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:441)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:316)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1189)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1183)
	at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
	at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
	at org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:419)
	at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:406)
	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)
	at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78)
	... 18 more
Closing: 0: jdbc:hive2://localhost:10000/default

This has nothing to do with the distribution itself, however, it is a result of the JDBC driver copied in this location:

/usr/hdp/2.x.x.x-xxx/hive/lib

Resolution

• Find the version of your metadata database, ideally it could be one of the following (existing databases)
  • MySQL
  • Oracle
  • PostgreSQL
• Find the compliant JDBC drivers
  • For MySQL (finding the version of DB) or ask your Database Administrator who manages this DB

  [root@sandbox lib]# mysql 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 41
  Server version: 5.1.73 Source distribution
  
  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective
  owners.
  
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  

  • In the above scenario, version of MySQL DB is 5.1.73. Ensure that the relevant JDBC driver is downloaded HERE
  • For PostgreSQL (finding version of DB) or ask your Database Administrator who manages this DB

  [root@sandbox lib]# su - postgres
  -bash-4.1$ psql -c "select version()"
                                                        version                                                      
  -------------------------------------------------------------------------------------------------------------------
   PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
  (1 row)
  
  
  -bash-4.1$ 
  

  • The version of PostgreSQL db here is 8.4.20. You can get the drivers for PostgreSQL JDBC HERE
• Additionally, ensure that only JDBC4 type of drivers are being used. In the error raised above JDBC3 type drivers were being used which are not as efficient as JDBC4.
• Download the relevant JDBC4 jars and replace them with the drivers in /usr/hdp/2.x.x.x.xxxx/hive/lib
• Ensure that hive.txn.manager is set to "org.apache.hadoop.hive.ql.lockmgr.DbTxnManager", then restart Ambari server and all other services which indicate that a restart is required
• Test out by running some queries via Hive View, Beeline OR Hive Shell