How to Connect to Hiveserver2 Using Cloudera JDBC driver

[#1]

If you need to connect HiveServer2 from Third parties like Dbbeaver OR PowerBI or any Java client, you can connect using the Cloudera JDBC driver.

The driver can be downloaded from Hive JDBC Connector 2.6.21 for Cloudera Enterprise. 

We will cover the scenario for SSL, Zookeeper,Kerberos, LDAP, and LoadBalancer to connect to HiveServer2 using the driver.    

Please note that the Beeline URL is not the same as the JDBC driver. The list of properties for the JDBC driver is listed at Cloudera JDBC Driver 2.6.21 for Apache Hive.

You need to use com.cloudera.hive.jdbc.HS2Driver class to connect to HiveServer2.

Lets assume:

•   Host: c2345.node.cloudera.com
•   Kerberos Realm: EXAMPLE.COM

Plain Connection:

 jdbc:hive2://c2345.node.cloudera.com:10000/default;LogLevel=6;LogPath=/tmp

•   where LogLevel =6 ==> is more verbose level of logging

Kerberos+SSL+binary:

jdbc:hive2://c2345.node.cloudera.com:10000/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxxxxxxxxx;LogLevel=6;LogPath=/tmp/logs;KrbRealm=EXAMPLE.COM;KrbHostFQDN=c2345.node.cloudera.com;KrbServiceName=hive;AuthMech=1

If you import root certificate of HiveServer2 to CACERTS in JDK, you do not need to specify SSLTrustStore and SSLTrustStorePwd, it takes the trustsore and password from CACERTS

jdbc:hive2://c2345.node.cloudera.com:10000/default;SSL=1;LogLevel=6;LogPath=/tmp/logs;KrbRealm=EXAMPLE.COM;KrbHostFQDN=c2345.node.cloudera.com;KrbServiceName=hive;AuthMech=1

• where AuthMEch =1 ==> uses Kerberos authentication

LDAP+SSL+binary:

jdbc:hive2://c2345.node.cloudera.com.com:10000/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=3;UID=test1;PWD=Password1

• where AuthMEch =3 ==> uses LDAP authentication
• where UID and PWD is for the user present in the LDAP.

LDAP+SSL+HTTP:

jdbc:hive2://c2345.node.cloudera.com.com:10001/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=3;UID=test1;PWD=Password1;transportMode=http;httpPath=cliservice

• The port has been changed from 10000 to 10001.transportMode and httpPath is added.

Kerberos+SSL+HTTP:

jdbc:hive2://c2345.node.cloudera.com.com:10001/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=1;KrbRealm=EXAMPLE.COM;KrbHostFQDN=c2345.node.cloudera.com;KrbServiceName=hive

• The port has been changed from 10000 to 10001.transportMode and httpPath is added.

Zookeeper+SSL+LDAP:

You can use Zookeeper to connect to HiveServer2 for high availability (HA)

jdbc:hive2://zk=c2345.node2.cloudera.com.com:2181/hiveserver2,c2345.node3.cloudera.com.com:2181/hiveserver2,c2345.node4.cloudera.com.com:2181/hiveserver2;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=3;UID=test1;PWD=Password1

Zookeeper+SSL+Kerberos:

jdbc:hive2://zk=c2345.node2.cloudera.com.com:2181/hiveserver2,c2345.node3.cloudera.com.com:2181/hiveserver2,c2345.node4.cloudera.com.com:2181/hiveserver2;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=1;KrbRealm=EXAMPLE.COM;KrbHostFQDN=_HOST;KrbServiceName=hive

• KrbHostFQDN=_HOST is used as string to connect to any hiveserver2 host. _HOST is replaced by exact hostname to which it will connect internally.

HA-Proxy+SSL+Kerberos:

Configure HA for hiveserer2 from Configuring the HiveServer load balancer. 

Connect using below URL:

jdbc:hive2://ha-proxy-host.com:11000/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=1;KrbRealm=EXAMPLE.COM;KrbHostFQDN=_HOST;KrbServiceName=hive

Where,  KrbHostFQDN=_HOST is used as  string to connect to any HiveServer2 host. _HOST is replaced by exact hostname to which it will connect internally.

HA-Proxy+SSL+LDAP:

jdbc:hive2://ha-proxy-host.com:11000/default;SSL=1;SSLTrustStore=/home/keystore-cdp/cm-auto-global_truststore.jks;SSLTrustStorePwd=xxx;LogLevel=6;LogPath=/tmp/logs;AuthMech=3;UID=test1;PWD=Password1

[#2]

very detailed and useful article. Thank you @asish!