Using JDBC or Beeline to Connect HiveServer2

  • Post author:

Sometime we really struggle to get some simple things such as forming a JDBC URL so that we can establish a connection with different database. Today I am proving a collection of different JDBC URL so that it’s easy to refer when we need it.

Driver Configuration Options

Port: By default, Hive uses port 10000.
AuthMech: The authentication mechanism to use. Set the property to one of the following values:
 0 for No Authentication.
 1 for Kerberos.
 2 for User Name.
 3 for User Name And Password.
 6 for Hadoop Delegation Token.
KrbAuthType: This property specifies how the driver obtains the Subject for Kerberos authentication
0: The driver automatically detects which method to use for obtaining the Subject.
1: The driver checks the java.security.auth.login.config system property for a JAAS configuration. 
2: The driver checks the KRB5_CONFIG and KRB5CCNAME system environment variables for a Kerberos ticket cache.
KrbHostFQDN: The fully qualified domain name of the Hive Server 2 host. Required, if AuthMech=1.
KrbRealm: The realm of the Hive Server 2 host.
SSL: 
1: The driver connects to SSL-enabled sockets.
0: The driver does not connect to SSL-enabled sockets.
SSLTrustStore: The full path of the Java TrustStore containing the server certificate for one-way SSL authentication
SSLTrustStorePwd: The password for accessing the Java TrustStore.

Building the Connection URL

Format:
jdbc:[Subprotocol]://[Host]:[Port]/[Schema];[Property1]=[Value];Property2]=[Value];…

Following connection URL connects to a Hive server with Kerberos enabled, but without SSL enabled:

beeline -u"jdbc:hive2://node1.example.com:10000;AuthMech=1;KrbRealm=EXAMPLE.COM;
KrbHostFQDN=hs2node1.example.com;KrbServiceName=hive;KrbAuthType=2"

Following connection URL connects to a data source using kerberos authentication, with (Auto-TLS)SSL enabled:

beeline !connect "jdbc:hive2://node1.example.com:10000/default;
principal=hive/node1.example.com@example.com;ssl=true;
sslTrustStore=/opt/cloudera/security/AutoTLS/trust-store/cm-auto-global_truststore.jks;
trustStorePassword=<trustStore password>"

Common issue when we try to connect it with client: We are trying to connect to Hive from a third Party tool and it started failing after customer enabled SSL in the cluster. Some time we were getting “Invalid status 21” error too.

"Unable to establish connection: java.sql.SQLException: 
Could not open client transport with JDBC Uri:jdbc:hive2://node1.example.com:10000/default;
principal=hive/node1.example.com@example.com;ssl=true;sslTrustStore=/opt/cloudera/security/AutoTLS/trust-store/cm-auto-global_truststore.jks;
trustStorePassword=****: GSS initiate failed

Root cause of the issue was using Apache Hive jdbc Driver instead of Cloudera JDBC driver. We used the following steps to fix this issue:

  • Downloaded the latest Cloudera JDBC driver from –https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-5.html
  • jar: HiveJDBC41.jar
  • Class: com.cloudera.hive.jdbc41.HS2Driver
  • Connection String: “jdbc:hive2://node1.example.com:10000/default;;AuthMech=1;KrbRealm=example.com;KrbHostFQDN=node1.example.com;KrbServiceName=hive;SSL=1;SSLTrustStore=/opt/cloudera/security/AutoTLS/trust-store/cm-auto-global_truststore.jks;SSLTrustStorePwd=XXXX”

Now we were able to connect to Hive.

Tags: , , ,

Leave a Reply