Workbench/J is a free SQL query tool that helps developers to run SQL scripts (either interactively or as a batch) and export/import features. This tool can help troubleshoot required components for a JDBC application to connect to the data source, or it can be utilized to test JDBC drivers without requiring to write your own JDBC application.
Using this application you will be able to execute SQL-92 queries against Hive and report your Hive-based data in a relational view.
In this blog we show you how to connect SQL Workbench/J to your Hive-based data using Simba’s Hive JDBC Driver.
Hive JDBC Driver Installation Steps:
Configuring the Hive JDBC Driver:
To connect to a Hive server, you must configure the Simba Hive JDBC Driver to use the authentication mechanism that matches the access requirements of the server and provides the necessary credentials. To determine the authentication settings that your Hive server requires, check the server configuration and then refer to the corresponding Hive JDBC documentation.
For this example, we are going to connect to Hive Server 2 using “User Name Authentication”.
For our use case, our connection URL will be as follows:
Note that the AuthMech=2 and transportMode=sasl settings are used for “User Name Authentication”.
Also, before connecting to the data store, you need to know the name of the class to use with your application. Choose the appropriate class name listed here. For this example, the Class Name is:
Where HS2 represents Hive Server 2, and jdbc41 represents the fact that our application is using JDBC API version 4.1
Running the Hive JDBC Driver with your SQL Workbench/J:
- SQL Workbench/J does not come with the Hive JDBC Driver, so you must add the driver to the application. Start by clicking File > Manage Drivers.
- Navigate to the folder containing your extracted Hive driver files (the same folder with the license file), Select all the .jar files in the folder and click OK.
- You will be asked to select the driver that you want to use. HS2Driver supports connections to Hive Server 2, and HS1Driver supports connections to Hive Server 1. Please check with your DB administrator to determine which server type you are using. For the purpose of this example, our Hive configuration is using Hive Server 2.
- In the example URL field, type the connection URL mentioned in the “Configuring the Hive JDBC Driver” section above, and then click OK.
- Select the Simba Hive driver from the driver list, fill in the Username and Password (if it is required), and then click OK.
- You are now able to run SQL-92 queries on your Hive datasource. Type your query into the window and then click the Run icon.
- Congratulations! The results of the of your query from SQL Workbench/J to Hive using simba’s Hive JDBC driver will be displayed.