Example: SQL Workbench/J

SQL Workbench/J is a cross-platform SQL tool that can be used to access database systems. In this example, we provide a high-level overview of the steps required to setup SQL Workbench to access Hive via JDBC.

Prerequisite:

Download SSL certificate

Step 1: Install SQL Workbench and Hive JDBC Driver

1. Download and install SQL Workbench. Refer to http://www.sql-workbench.net/getting-started.html.
2. Download the Hortonworks JDBC Driver for Apache Hive from https://hortonworks.com/downloads/#addons. Next, extract the driver package.

Step 2: Configure SQL Workbench with Hive JDBC Driver

1. Launch SQL Workbench.
2. The Select Connection Profile window should be open by default. If it is not, you can open it from File > Connect window.
3. Click Manage Drivers. The Manage drivers window will appear.
4. Click to create a new driver, and enter the Name: “Hortonworks Hive JDBC”.
5. Click and then browse to the Hortonworks JDBC Driver for Apache Hive package that you downloaded earlier. Next, select the JDBC Driver JAR files in the package.
6. When prompted, select the “com.simba.hive.jdbc41.HS2Driver” driver.
7. For the Sample URL, enter: jdbc:hive2://${GATEWAY_HOST}:8443/
8. After performing these steps, your configuration should look similar to:
9. Click OK to save the driver.

Step 2: Create a Connection to Hive

1. From the Select Connection Profile window, select the “Hortonworks Hive JDBC” from the Driver dropdown.
2. For URL , enter the URL to the cluster instance where gateway is installed, such as jdbc:hive2://52.52.98.57:8443/ (where 52.52.98.57 is the public hostname of your gateway node).
3. For Username and Password, enter the credentials that you created when creating your cluster.
4. Click Extended Properties and add the following properties:

   Property	Value
   ssl	1
   transportMode	http
   httpPath	Provide "${CLUSTER_NAME}/${TOPOLOGY_NAME}/hive". For example hive-test/db-proxy/hive
   sslTrustStore	Enter the path to the gateway.jks file. This file was generated when you downloaded the SSL certificate.
   trustStorePassword	Enter the GATEWAY_JKS_PASSWORD that you specified when you downloaded the SSL certificate.

   After performing these steps, your configuration should look similar to:

   

5. Click OK to save the properties.
6. Click Test to confirm a connection can be established.
7. Click OK to make the connection and start using SQL Workbench to query Hive.