Tried couple of more connection options, but always got an error while setting up Hive Connection Pool. What’s strange is DBCP Connection Pool works fine with the same Hive JDBC connection string.
Now I am writing a custom “PutSQL” like processor that uses standard DBCP Controller service and allows to run DDL commands on Hive (since standard PutSQL does not allow DDL statements - only insert and update works). Basically, I’ll be writing a custom PutHiveQL that can work on standard DBCP. Regards, Manish From: Manish Gupta 8 [mailto:[email protected]] Sent: Friday, September 30, 2016 3:09 AM To: [email protected] Subject: RE: PutHiveQL and Hive Connection Pool with HDInsight Tried with different combinations, but couldn’t succeed. It’s a HDI 3.4 Cluster with default hive settings. Some of the errors I received: 1. PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/default;hive.server2.transport.mode=http;hive.server2.thrift.http.path=/: java.net.SocketException: Connection reset); rolling back session: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/default;hive.server2.transport.mode=http;hive.server2.thrift.http.path=/: java.net.SocketException: Connection reset). 2. failed to process session due to java.lang.NoSuchFieldError: INSTANCE: java.lang.NoSuchFieldError: INSTANCE 3. PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http;hive.server2.thrift.http.path=/: Invalid status 72); rolling back session: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http;hive.server2.thrift.http.path=/: Invalid status 72) 4. PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http;httpPath=/: Invalid status 72); rolling back session: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http;httpPath=/: Invalid status 72) 5. PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http: Invalid status 72); rolling back session: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?transportMode=http: Invalid status 72) Regards, Manish From: Manish Gupta 8 [mailto:[email protected]] Sent: Friday, September 30, 2016 12:44 AM To: [email protected]<mailto:[email protected]> Subject: RE: PutHiveQL and Hive Connection Pool with HDInsight Thank you Matt. I did tried with hive.server2.transport.mode=http like this jdbc:hive2:// somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=/hive2<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=/hive2>. But, I was getting java.lang.NoSuchFieldError: INSTANCE: java.lang.NoSuchFieldError: INSTANCE. I will try again with transportMode=http and/or httpPath=cliservice. But, as per hive’s documentation, right syntax should be hive.server2.transport.mode.(https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2) Regards, Manish From: Matt Burgess [mailto:[email protected]] Sent: Thursday, September 29, 2016 7:28 PM To: [email protected]<mailto:[email protected]> Subject: Re: PutHiveQL and Hive Connection Pool with HDInsight Manish, According to [1], status 72 means a bad URL, perhaps you need a transportMode and/or httpPath parameter in the URL (as described in the post)? Regards, Matt [1] https://community.hortonworks.com/questions/23864/hive-http-transport-mode-problem.html On Thu, Sep 29, 2016 at 9:06 AM, Manish Gupta 8 <[email protected]<mailto:[email protected]>> wrote: Hi, I am not able to use PutHiveQL when accessing Hive on HDInsight. I am using NiFi 0.7. • Tried specifying the URL in couple of different ways. If I follow Azure Documentation (https://azure.microsoft.com/en-in/documentation/articles/hdinsight-connect-hive-jdbc-driver/) and specify the URL as jdbc:hive2:// somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=/hive2<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=/hive2>, then I get a “failed to process session due to java.lang.NoSuchFieldError: INSTANCE: java.lang.NoSuchFieldError: INSTANCE”. • I tried using hive-jdbc jars from my cluster (dropping them into lib), but then NiFi didn’t start (some javax.xml.parsers conflicts). • When I use “jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname<http://somehdiclustername.azurehdinsight.net:443/somedbname>”, then I get following error. Is this issue because of https://issues.apache.org/jira/browse/NIFI-2575 or my connection settings are incorrect? Any workaround? /Any reference settings/example for HDI? All I need to do is call an Alter Table Add Partition command in Hive from NiFi (once a day). Should I use HWI/Custom processor? 2016-09-29 08:18:48,194 INFO [StandardProcessScheduler Thread-1] o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] to run with 1 threads 2016-09-29 08:18:48,194 INFO [Timer-Driven Process Thread-6] o.a.nifi.dbcp.hive.HiveConnectionPool HiveConnectionPool[id=4d7f766a-1177-4f1d-a376-6ba5b84bf856] Simple Authentication 2016-09-29 08:18:48,262 INFO [Timer-Driven Process Thread-6] org.apache.hive.jdbc.Utils Supplied authorities: somehdiclustername.azurehdinsight.net:443<http://somehdiclustername.azurehdinsight.net:443> 2016-09-29 08:18:48,263 INFO [Timer-Driven Process Thread-6] org.apache.hive.jdbc.Utils Resolved authority: somehdiclustername.azurehdinsight.net:443<http://somehdiclustername.azurehdinsight.net:443> 2016-09-29 08:18:48,468 INFO [Timer-Driven Process Thread-6] org.apache.hive.jdbc.HiveConnection Transport Used for JDBC connection: null 2016-09-29 08:18:48,468 ERROR [Timer-Driven Process Thread-6] o.a.nifi.dbcp.hive.HiveConnectionPool HiveConnectionPool[id=4d7f766a-1177-4f1d-a376-6ba5b84bf856] Error getting Hive connection 2016-09-29 08:18:48,484 ERROR [Timer-Driven Process Thread-6] o.a.nifi.dbcp.hive.HiveConnectionPool org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72) at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[commons-dbcp-1.4.jar:1.4] at org.apache.nifi.dbcp.hive.HiveConnectionPool.getConnection(HiveConnectionPool.java:289) ~[nifi-hive-processors-0.7.0.jar:0.7.0] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_102] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_102] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_102] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_102] at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:166) [nifi-framework-core-0.7.0.jar:0.7.0] at com.sun.proxy.$Proxy89.getConnection(Unknown Source) [na:na] at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:152) [nifi-hive-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-0.7.0.jar:0.7.0] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_102] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_102] Caused by: java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72 at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:207) ~[hive-jdbc-2.0.0.jar:2.0.0] at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:152) ~[hive-jdbc-2.0.0.jar:2.0.0] at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:107) ~[hive-jdbc-2.0.0.jar:2.0.0] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545) ~[commons-dbcp-1.4.jar:1.4] ... 22 common frames omitted Caused by: org.apache.thrift.transport.TTransportException: Invalid status 72 at org.apache.thrift.transport.TSaslTransport.sendAndThrowMessage(TSaslTransport.java:232) ~[libthrift-0.9.3.jar:0.9.3] at org.apache.thrift.transport.TSaslTransport.receiveSaslMessage(TSaslTransport.java:184) ~[libthrift-0.9.3.jar:0.9.3] at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:307) ~[libthrift-0.9.3.jar:0.9.3] at org.apache.thrift.transport.TSaslClientTransport.open(TSaslClientTransport.java:37) ~[libthrift-0.9.3.jar:0.9.3] at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:181) ~[hive-jdbc-2.0.0.jar:2.0.0] ... 28 common frames omitted 2016-09-29 08:18:48,484 ERROR [Timer-Driven Process Thread-6] o.apache.nifi.processors.hive.PutHiveQL PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] PutHiveQL[id=05505d0c-eee1-48bc-8a99-b53302118933] failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72); rolling back session: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72) 2016-09-29 08:18:48,499 ERROR [Timer-Driven Process Thread-6] o.apache.nifi.processors.hive.PutHiveQL org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72) at org.apache.nifi.dbcp.hive.HiveConnectionPool.getConnection(HiveConnectionPool.java:293) ~[na:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_102] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_102] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_102] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_102] at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:166) ~[nifi-framework-core-0.7.0.jar:0.7.0] at com.sun.proxy.$Proxy89.getConnection(Unknown Source) ~[na:na] at org.apache.nifi.processors.hive.PutHiveQL.onTrigger(PutHiveQL.java:152) ~[na:na] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) ~[nifi-api-0.7.0.jar:0.7.0] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_102] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_102] Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72) at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[na:na] at org.apache.nifi.dbcp.hive.HiveConnectionPool.getConnection(HiveConnectionPool.java:289) ~[na:na] ... 19 common frames omitted Caused by: java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true<http://somehdiclustername.azurehdinsight.net:443/somedbname;ssl=true>: Invalid status 72 at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:207) ~[na:na] at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:152) ~[na:na] at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:107) ~[na:na] at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) ~[na:na] at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556) ~[na:na] at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545) ~[na:na] ... 22 common frames omitted Caused by: org.apache.thrift.transport.TTransportException: Invalid status 72 at org.apache.thrift.transport.TSaslTransport.sendAndThrowMessage(TSaslTransport.java:232) ~[na:na] at org.apache.thrift.transport.TSaslTransport.receiveSaslMessage(TSaslTransport.java:184) ~[na:na] at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:307) ~[na:na] at org.apache.thrift.transport.TSaslClientTransport.open(TSaslClientTransport.java:37) ~[na:na] at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:181) ~[na:na] ... 28 common frames omitted Thanks, Manish
