6. Hive ODBC and JDBC Drivers

Hortonworks provides Hive JDBC and ODBC drivers that allow you to connect popular Business Intelligence (BI) tools to query, analyze and visualize data stored within the Hortonworks Data Platform. JDBC URLs have the following format:

jdbc:hive2://<host>:<port>/<dbName>;<sessionConfs>?<hiveConfs>#<hiveVars>

Table 2.5. JDBC Connection Parameters

JDBC Connection Parameter	Description
host	The cluster node hosting HiveServer2.
port	The port number to which HiveServer2 listens.
dbName	The name of the Hive database to run the query against.
sessionConfs	Optional configuration parameters for the JDBC/ODBC driver in the following format: `<key1>=<value1>;<key2>=<key2>; ...`. The configurations last for the duration of the user session.
hiveConfs	Optional configuration parameters for Hive on the server in the following format:`<key1>=<value1>;<key2>=<key2>; ...`. The configurations last for the duration of the user session.
hiveVars	Optional configuration parameters for Hive variables in the following format: `<key1>=<value1>;<key2>=<key2>; ...`. The configurations last for the duration of the user session.

The specific JDBC connection URL for a HiveServe2 client depends on several factors:

How is HiveServer2 deployed on the cluster?
What type of transport does HiveServer2 use?
Does HiveServer2 use transport layer security?
Is HiveServer2 configured to authenticate users?
The rest of this topic describes how to use session configuration variables to format the JDBC connection URLs for all of these scenarios. In addition, the topic provides links to download the Hive ODBDC driver and instructions for using it.

Some HiveServer2 clients may need to run on a host outside the Hadoop cluster. These clients require all of the following .jar files to successfully connect to the Hive JDBC driver in both HTTP and HTTPS modes:

Off-cluster Jars Without Kerberos

hive-jdbc.jar
hive-service.jar
hive-common.jar
hadoop-common.jar
libthrift-0.9.0.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
commons-logging-1.1.3.jar
commons-codec-1.4.jar
slf4j-api-1.7.5.jar

Off-cluster Jars With Kerboeros

hive-jdbc.jar
hive-service.jar
hive-common.jar
hive-shims-common.jar
hive-shims-common-secure.jar
hive-shims-0.23-*.jar
hadoop-common.jar
hadoop-auth.jar
hadoop-mapreduce-client-core.jar
libthrift-0.9.0.jar
guava-11.0.2.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
commons-logging-1.1.3.jar
commons-codec-1.4.jar
commons-collections-3.1.jar
commons-configuration-1.6.jar
commons-lang-2.4.jar
log4j-1.2.16.jar
slf4j-api-1.7.5.jar

Embedded and Remote Modes

In embedded mode, HiveServer2 runs within the Hive client rather than in a separate process. No host or port number is necessary for the JDBC connection. In remote mode, HiveServer2 runs as a separate daemon on a specified host and port, and the JDBC client and HiveServer2 interact using the Thrift protocol.

Embedded Mode

jdbc://hive2://

Remote Mode

jdbc://hive2://<host>:<port>/<dbName>;<sessionConfs>?<hiveConfs>#<hiveVars>

	Note
	The rest of the example JDBC connection URLs in this topic are valid only or HiveServer2 configured in remote mode.

TCP and HTTP Transport

The JDBC client and HiveServer2 can use either HTTP or TCP-based transport to exchanbe RPC messages. Specify the transport used by HiveServer2 with the transportMode and httpPath session configuration variables. The default transport is TCP.

Table 2.6.

transportMode Variable Value	Description
http	Connect to HiveServer2 using HTTP transport.
binary	Connect to HiveServer2 using TCP transport.

HTTP Transport

Use the following JDBC connection URL for HTTP transport:

jdbc:hive2//<host>:<port>/<dbName>;transportMode=http;httpPath=<http_endpoint>;<otherSessionConfs>?<hiveConfs>#<hiveVars>

	Note
	The JDBC driver assume a value of `cliservice` if the `httpPath` configuration variable is not specified.

TCP Transport

Use the following JDBC connection URL for TCP transport:

jdbc:hive2://<host>:<port>/<dbName>;<otherSessionConfs>?<hiveConfs>#hiveVars

There is no need to specify transportMode=binary because the default transport is TCP.

User Authentication

HiveServer2 supports Kerberos, LDAP, Pluggable Authentication Modules (PAM), and custom plugins for authenticating JDBC users connecting to HiveServer2. The format of the JDBC connection URL for authentication with Kerberos differs from the format for other authenticatin models.

Table 2.7.

User Authentication Variable	Description
`principal`	A string that uniquely identifies a Kerberos user.
`saslQop`	Quality of protection for the SASL framework. The level of quality is negotiated between the client and server duringh authentication. Used by Kerberos authentication with TCP transport.
`user`	Username for non-Kerberos authentication model.
`password`	Password for non-Kerberos authentication model.

Kerberos Authentication

Use the following JDBC connection URL to authenticate the connecting user with Kerberos:

jdbc:hive2://<host>:<port>/<dbName>;principal=<HiveServer2_kerberos_principal>;<otherSessionConfs>?<hiveConfs>#<hiveVars>

Kerberos Authentication with Sasl QOP

Use the following JDBC connection URL to authenticate the connecting user with Kerberos and Sasl QOP.

jdbc:hive2://<host>:<port>/<dbName>;principal=<HiveServer2_kerberos_principal>;saslQop=<qop_value>;<otherSessionConfs>?<hiveConfs>#<hiveVars>

Non-Kerberos Authentication

Use the following JDBC connection to authenticate the connecting user without Kerberos:

jdbc:hive2://<host>:<port>/<dbName>;user=<username>;password=<password>;<otherSessionConfs>?<hiveConfs>#<hiveVars>

Transport Layer Security

HiveServer2 supports SSL and Sasl QOP for transport layer security. The format of the JDBC connection URL for SSL differs from the format used by Sasl QOP.

jdbc:hive2://<host>:<port>/<dbName>;principal=<HiveServer2_kerberos_principal>;saslQop=auth-conf;<otherSessionConfs>?<hiveConfs>#<hiveVars>

Table 2.8.

SSL Variable	Description
ssl	Specifies whether to use SSL.
sslTrustStore	The path to the SSL TrustStore.
trustStorePassword	The password to the SSL TrustStore.

Hive ODBC Driver

Hortonworks also provides an ODBC driver as an add-on to the HDP distribution:

Download the Hortonworks Hive ODBC driver from here.
The instructions on installing and using this driver are available here.

Some HiveServer2 clients, such as Apache Knox, may need to run on a host outside the Hadoop cluster. Such clients require all of the following .jar files to successfully use the Hive JDBC driver in both HTTP and HTTPS modes:

commons-codec-1.4.jar
commons-logging-1.1.3.jar
hive-jdbc-0.13.0.jar
hive-service-0.13.0.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
libthrift-0.9.0.jar

Legal notices