NiFi SelectHiveQL fails

[#1]

I am unable to get SelectHiveQL to start

Here is the error:

2017-03-09 16:44:49,968 WARN [Timer-Driven Process Thread-6] org.apache.hive.jdbc.HiveConnection Failed to connect to xxxx.xxxx.hortonworks.com:10010
2017-03-09 16:44:49,968 ERROR [Timer-Driven Process Thread-6] o.a.nifi.dbcp.hive.HiveConnectionPool HiveConnectionPool[id=5571c713-c8d4-1145-0000-000031b86938] Error getting Hive connection
2017-03-09 16:44:49,973 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://xxxxx.xxxx.hortonworks.com:10010/default;principal=hive/_HOST@LAKE: GSS initiate failed)
        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:288) ~[nifi-hive-processors-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at sun.reflect.GeneratedMethodAccessor1869.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_77]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_77]
        at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:177) [nifi-framework-core-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at com.sun.proxy.$Proxy192.getConnection(Unknown Source) [na:na]
        at org.apache.nifi.processors.hive.SelectHiveQL.onTrigger(SelectHiveQL.java:158) [nifi-hive-processors-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.0.2.1.1.0-2.jar:1.1.0.2.1.1.0-2]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_77]
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_77]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_77]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_77]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_77]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_77]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_77]

Hive transport is set to binary

I have tried to set the jdbc connection string using in many ways..ie using ZK or direct. Right now I have it set to

jdbc:hive2://xxxx.xxxx.hortonworks.com:10010/default;principal=hive/_HOST@LAKE

I have set the kerberos principle to smanjee@FIELD which is my principle and kerberos keytab point to location of my keytab.

I have looked at all the way to set hive jdbc here

https://community.hortonworks.com/articles/4103/hiveserver2-jdbc-connection-url-examples.html

They don't work with nifi. any insights?

[#2]

Hi Sunil

The default port for thrift is 10000. Can you please check the port Number in your HDP setup?

The document you added says "10001" its an assumption.

https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2 says the following

"HIVE_SERVER2_THRIFT_PORT – Optional TCP port number to listen on, default 10000. Overrides the configuration file setting."

So please try changing the port number from 10001 to 10000 in JDBC url.

Thanks

Mahesh Nayak Kalyanpur

[#3 · Accepted solution]

Sunile and I troubleshot this issue further.

The first thing we did is we enabled sun.security.krb5.debug=true, this can be done in bootstrap.conf, see this doc.

What we found, nifi-bootstap.log, was:

INFO [NiFi logging handler] org.apache.nifi.StdOut Found unsupported keytype (18) for smanjee@FIELD

Keytype 18 is aes256-cts-hmac-sha1-96 (see this page), and was the cipher used when we created the keytab for the smanjee@FIELD user. We created a new keytab using the cipher des3-cbc-sha1, and this resolved the issue.

Note: I am not recommending that weak ciphers be used in Production environments.