Hive Connection Help

RazorSQL can connect to Apache Hive databases via JDBC on Windows, Mac, and Linux operating systems. On Windows systems, it can also connect via ODBC.

Connecting via JDBC

There are many different types of Hive setups such as ones from HortonWorks, Cloudera, Amazon EMR, and the base Apache Hive setup. Each of these setups can have different dependencies. There are also many different versions of Hive and the drivers necessary for connecting to Hive are not always backwards and forwards compatible. The driver provided by the RazorSQL auto download process tries to stay up to date to support more recent versions of Hive. However, it may not be backwards compatible with older versions.

The RazorSQL auto-install process installs the Apache JDBC driver for connecting to Hive. Another driver option for connecting to Hive is the Simba Hive JDBC driver. Click the appropriate link below to see information on connecting to Hive with either of these drivers.

Apache Hive JDBC Driver (Used in RazorSQL Auto-Install process)

Simba Hive JDBC Driver

Other Hive JDBC Connection Options

Apache Hive JDBC Driver

The Auto-Install button on the RazorSQL Add Connection Profile screen downloads and installs the Apache Hive JDBC Driver. To connect to Hive via this driver using the Auto-Install option, select the Connections -> Add Connection Profile menu option. Then, select Hive as the database type. On the next screen, there are several Connection Type options. The first option has an "Auto Download" capability to download and install a Hive JDBC driver. The driver file that RazorSQL downloads is called the "Hive JDBC Uber Jar" and includes all the dependencies necessary for connecting to Hive. The driver is available via the following website for manual download. Older versions of the jar are available if the latest version of the jar is not compatible with your Hive version:

https://github.com/timveil/hive-jdbc-uber-jar/releases

When connecting to Hive via the Apache Hive JDBC driver, the following information is needed. Listed below are the values to use when using the Apache Hive JDBC driver. The connections use the HiveServer2 interface. HiveServer2 needs to be running on the Hive server to allow clients like JDBC and ODBC clients to be able to execute queries against the Hive database.

Driver Class: org.apache.hive.jdbc.HiveDriver

JDBC URL Format: jdbc:hive2://<host name>:<port>/<database name>

Sample JDBC URL: jdbc:hive2://192.168.1.159:10000/sample

Simba Hive JDBC Driver

Another JDBC driver option for connecting to Hive is the Simba Technologies Hive JDBC driver. The following steps can be used to connect to Hive with this driver:

1. The Simba driver has many jar files that are needed. The following is a list of the jar files required. These jar files need to be obtained before attempting the connection:

commons-codec1.3.jar
commons-logging-1.1.1.jar
hive_metastore.jar
hive_service.jar
HiveJDBC4.jar
httpclient-4.1.3.jar
httpcore-4.1.3.jar
libfb303-0.9.0.jar
libthrift-0.9.0.jar
log4j-1.2.14.jar
ql.jar
slf4j-api-1.5.11.jar
slf4j-log4j12-1.5.11.jar
TCLIServiceClient.jar
zookeeper-3.4.6.jar

2. Go to the Connections -> Add Connection Profile menu option. Select "Hive" as the database type and click Continue. On the next screen, select the second connection type option for "JDBC". Next to the "Driver Location" field, select the "Browse" button. Each of the jar files listed above would need to be selected.

3. In the Driver Class field, enter the following: com.simba.hive.jdbc4.HS2Driver

4. The JDBC URL format is the following. Replace the host and port with your specific values. The default port is 10000.

JDBC URL: jdbc:hive2://host:port

A database name can also be included on the URL: jdbc:hive2://host:port/database_name

Properties can be included on the URL by separating them with semi-colons. The properties are in name=value format:

jdbc:hive2://localhost:10000/default;AuthMech=3;

Other Hive JDBC Drivers

Users can manually download Hive JDBC drivers in the event that the driver provided by RazorSQL is not compatible with their Hive version. Versions of the standalone Hive JDBC driver for various Hive versions can be found on the following page:

Hive JDBC Drivers

In addition to the standalone jar that can be found on the site above, other jar files may be necessary to connect to Hive such as the jar file containing Hadoop libraries, and if specific security authorization is set up for the Hive server, security related jar files. The Hadoop common jar can be downloaded from the following site:

Hadoop Common Jars

Connecting via ODBC

To connect to Hive via ODBC, a Hive ODBC driver needs to be downloaded and installed. There are several vendors that offer Hive ODBC drivers, and most of the drivers are free. After the driver is downloaded and installed, and an ODBC data source is configured on the Windows machine to connect to the Hive server, users can connect to Hive by adding a new connection in RazorSQL (Connections -> Add Connection Profile), selecting Hive as the database type, selecting ODBC as the connection type, and selecting or typing in the name of the ODBC data source that was created.