NiFi Hive Connection Pool Error.

[#1]

Hi,

I am trying to connect to create a connection Hive using different formats DB Connection URLs. All of them are giving different errors.

first DB Connection URL and Error:

jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;

error:

21:37:29 UTCERRORd3b62ee6-0157-1000-b66f-364970fcfa98 SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] Unable to execute HiveQL select query show tables 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://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain: GSS initiate failed). No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain: GSS initiate failed)

Second DB Connection URL and Error:

jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;transportMode=http;httpPath=cliservice

Error

21:47:09 UTCERRORd3b62ee6-0157-1000-b66f-364970fcfa98 SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] Unable to execute HiveQL select query show tables due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'org.apache.hive.jdbc.HiveDriver' for connect URL ' jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;transportMode=http;httpPath=cliservice'. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'org.apache.hive.jdbc.HiveDriver' for connect URL ' jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;transportMode=http;httpPath=cliservice'

Third DB Connection and Error

jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;transportMode=http;httpPath=cliservice;auth=KERBEROS

Error 21:49:56 UTCERRORd3b62ee6-0157-1000-b66f-364970fcfa98 SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] failed to process session due to java.lang.NoClassDefFoundError: Could not initialize class org.apache.http.conn.ssl.SSLConnectionSocketFactory: java.lang.NoClassDefFoundError: Could not initialize class org.apache.http.conn.ssl.SSLConnectionSocketFactory 21:49:56 UTCWARNINGd3b62ee6-0157-1000-b66f-364970fcfa98 SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98]

Processor Administratively Yielded for 1 sec due to processing failure

for all of these above i am commonly getting a warning

HiveConnectionPool[id=d3b68593-0157-1000-2894-db6013bf04f3] Configuration does not have security enabled, Keytab and Principal will be ignored

Thanks in advance,

Sai

[#2]

Hi, looks like you are accessing Hive over HTTP. There was an issue fixed here: https://issues.apache.org/jira/browse/NIFI-2828 (will be in the NiFi 1.1.0 and HDF 2.0.1).

You can also try switching to a binary Hive endpoint for now.

[#3]

What should I do to get the fix.??

Also how do I switch to binary end point.??

[#4]

Can someone help me or point me to an example on how i can get connected to kerberos enabled Hive from NiFi.?

[#5]

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

See examples here:

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

https://community.hortonworks.com/questions/456/i-have-nifi-setup-on-my-cluster-but-it-only-works.ht...

[#6]

I am trying to connect to Hive from NiFi using Kerberos. Looks like there is no way I can do that until the newer version

NiFi 1.1.0? I cannot use any other modes as our Hadoop cluster is Kerberised.

Can I go back to previous version and use dbcp connection pool.? Will it work with Kerberos.? any other options I have (from NiFi).??

[#7]

@Saikrishna Tarapareddy

Just as @Andrew Grande mentioned, there is an issue with hiveQL processors + http.

Please connect to hive using the "binary" transport mode instead of the "http" transport mode.

To change this, In hive-site.xml or in Ambari, please update the property hive.server2.transport.mode to "binary".

Then all you need to change is the JDBC URL to "jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain;"

For the common error : "Configuration does not have security enabled, Keytab and Principal will be ignored" , you have not provided the path to the KDC server in the nifi.properties.

Please set the value of nifi.kerberos.krb5.file to the path of the krb5.conf which will be having the information of the KDC server. Then restart nifi. you should be able to connect to the said hive server.

[#8]

Hi @mkalyanpur

I have verified that transport mode is set to "binary"

my DB Connection URL is

jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain

and these are all set in NiFi.config file..

nifi.kerberos.krb5.file=/etc/krb5.conf

nifi.kerberos.service.principal=nifi/Myserver@MyDomain.COM nifi.kerberos.service.keytab.location=/etc/security/keytabs/nifi.keytab

nifi.kerberos.spnego.principal=HTTP/nifi/Myserver@MyDomain.COM

nifi.kerberos.spnego.keytab.location=/etc/security/keytabs/spnego.service.keytab nifi.kerberos.spnego.authentication.expiration=12 hours

getting the error below..

19:47:43 UTC ERROR d3b68593-0157-1000-2894-db6013bf04f3

HiveConnectionPool[id=d3b68593-0157-1000-2894-db6013bf04f3] Error getting Hive connection

19:47:43 UTC ERROR d3b62ee6-0157-1000-b66f-364970fcfa98

SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] Unable to execute HiveQL select query show tables 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://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain.COM: GSS initiate failed). No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://MyServer:10000/hdf_moat;principal=hive/MyServer@MyDomain: GSS initiate failed)

[#9]

Hey

You are getting a gss initiate fail error. Which means nifi is unable to connect to hive using kerberos .

Please note that you have not provided the kerberos credentials. Please try after providing that

[#10]

@mkalyanpur

i tried that too , but getting the same error. i am still getting the warning ,even though my nifi properties file has the settings about kerberos.

I am using HIVE user for kerberos credentials , i also tried with mine. both are resulting same error.

14:26:12 UTC ERROR d3b62ee6-0157-1000-b66f-364970fcfa98

SelectHiveQL[id=d3b62ee6-0157-1000-b66f-364970fcfa98] Unable to execute HiveQL select query show tables 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://MyServer:10000/default;principal=hive/MyDomain.COM;: GSS initiate failed). No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not open client transport with JDBC Uri: jdbc:hive2://MyServer:10000/default;principal=hive/MyDomain.COM;: GSS initiate failed)

14:26:12 UTC WARNING d3b68593-0157-1000-2894-db6013bf04f3

HiveConnectionPool[id=d3b68593-0157-1000-2894-db6013bf04f3] Configuration does not have security enabled, Keytab and Principal will be ignored