Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

SelectHiveQL processor hive connection pool configuration not working

SelectHiveQL processor hive connection pool configuration not working

Contributor

Error - nifi hive connection pool Required field 'client_protocol' is unset!

ANy example configuration of this processor as i couldnt connect to cloudera hive and currently we dont have kerberos.

Unable to execute HiveQL select query select * from dimensions.dim_email limit 10 due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not establish connection to jdbc:hive2://172.29.0.243:10000: Required field 'client_protocol' is unset! Struct:TOpenSessionReq(client_protocol:null)). No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not establish connection to jdbc:hive2://172.29.0.243:10000: Required field 'client_protocol' is unset! Struct:TOpenSessionReq(client_protocol:null))

6 REPLIES 6

Re: SelectHiveQL processor hive connection pool configuration not working

What version of Hive is on your cluster? Also what version of NiFi are you using? NiFi 1.0.0 is built to work with Apache Hive 1.2.1, and HDF 2.0 is built to work with the Hive 1.2.1-based Hortonworks version of Hive. Some Cloudera versions provide Hive 1.1.0, for those the HiveQL processors will not work as-is. In general you can try to build NiFi (especially the nifi-hive-nar module) using a specific version for Hive/Hadoop as described here. However you may run into compilation errors as the NiFi code is expecting to compile against Hive 1.2.1 (earlier versions of 1.x and later versions like 2.x likely won't compile); in that case you'd have to edit the source code for the HiveQL classes.

Re: SelectHiveQL processor hive connection pool configuration not working

Contributor

@Matt Burgess currently we are using hive 0.13 version with cdh 5.3.1 and also we have nifi 0.7. Its running a hiveserver2 service. Currently we cannot change hive version on the cdh server. Is there any other quicker alternative to extract data from hive? Thank you..

Re: SelectHiveQL processor hive connection pool configuration not working

If you have the Hive command-line tools on the NiFi node(s), you can use ExecuteProcess or ExecuteStreamCommand to issue Hive CLI commands (through "hive" or "beeline", e.g.)

Re: SelectHiveQL processor hive connection pool configuration not working

I have created a reusable implementation of Matt's solution including a modified branch of NiFi 1.1.x for use with CDH 5.10, and posted an article about it here: https://community.hortonworks.com/articles/93771/connecting-nifi-to-cdh-hive.html

Highlighted

Re: SelectHiveQL processor hive connection pool configuration not working

Super Guru

Are you running Hive Server 2?

Are you running an older version of Hive? You need a newer version of Hive that supports hive2 and matches the JDBC configuration

Re: SelectHiveQL processor hive connection pool configuration not working

New Contributor

For connecting Nifi with Hive and Cloudera and Kerberos you can use JDBC. Configure a DBCPConnectionPool as follows:

Database Connection URL: <host>:10000;AuthMech=1;KrbRealm=<kerberos realm>;KrbHostFQDN=_HOST;KrbServiceName=hive

Database Driver Class Name: com.cloudera.hive.jdbc41.HS2Driver

Database Driver Location: Location Cloudera JDBC jar files

After that you can use PutSQL, GetSQL en ConvertJSONtoSQL