Use the Hive JDBC Driver
You can use popular business intelligence tools like Microsoft Excel, MicroStrategy, QlikView, and Tableau with Amazon EMR to explore and visualize your data. Many of these tools require an ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) driver. Amazon EMR supports both JDBC and ODBC connectivity.
To connect to Hive via JDBC requires you to download the JDBC driver and install a SQL client. The following example demonstrates using SQL Workbench/J to connect to Hive using JDBC.
To download JDBC drivers
Download and extract the drivers for Amazon EMR 4.x releases at the following location:
Hive 1.0 JDBC drivers (driver version 1.0.4): https://amazon-odbc-jdbc-drivers.s3.amazonaws.com/public/AmazonHiveJDBC_184.108.40.2064.zip
To install and configure SQL Workbench
Download the SQL Workbench/J client for your operating system from http://www.sql-workbench.net/downloads.html.
Go to the Installing and starting SQL Workbench/J page and follow the instructions for installing SQL Workbench/J on your system.
Linux, Unix, Mac OS X users: In a terminal session, create an SSH tunnel to the master node of your cluster using the following command. Replace
master-public-dns-namewith the public DNS name of the master node and
path-to-key-filewith the location and file name of your Amazon EC2 private key (
Hive version Command 1.0
ssh -o ServerAliveInterval=10 -i
path-to-key-file-N -L 10000:localhost:10000 hadoop@
Windows users: In a PuTTY session, create an SSH tunnel to the master node of your cluster (using local port forwarding) with the following settings. Replace
master-public-dns-namewith the public DNS name of the master node. For more information about creating an SSH tunnel to the master node, see Option 1: Set Up an SSH Tunnel to the Master Node Using Local Port Forwarding in the Amazon EMR Management Guide.
Hive version Tunnel settings 1.0 Source port: 10000 Destination:
Add the JDBC driver to SQL Workbench/J.
In the Select Connection Profile dialog box, click Manage Drivers.
Click the Create a new entry (blank page) icon.
In the Name field, type
For Library, click the Select the JAR file(s) icon.
Browse to the location containing the extracted drivers, select the following JAR files and click Open.
hive_metastore.jar hive_service.jar HiveJDBC41.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
In the Please select one driver dialog box, select the following driver and click OK.
When you return to the Manage Drivers dialog box, verify that the Classname field is populated and click OK.
When you return to the Select Connection Profile dialog box, verify that the Driver field is set to Hive JDBC and provide the JDBC connection string in the URL field.
Click OK to connect. After the connection is complete, connection details appear at the top of the SQL Workbench/J window.
For more information about using Hive and the JDBC interface, go to http://wiki.apache.org/hadoop/Hive/HiveClient and http://wiki.apache.org/hadoop/Hive/HiveJDBCInterface.