by Nadia Dobrianskaia

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:

  • Download an evaluation copy of Simba’s Hive JDBC driver. Be sure to select Java (JDBC) for the platform. Depending on your use case select either Desktop or Server from the drop-down list.

  • Extract Simba_Hive_JDBC.zip archive that you just downloaded. You will notice that this archive contains different versions of the driver. Each version supports the JDBC version indicated in the file name.

  • Install JRE or check the installed version on your Windows machine. Open command prompt and type: 
    java -version

  • Each machine where you use the Simba Hive JDBC driver must have Java Runtime Environment (JRE) installed. Choose the JDBC and JRE versions to use, based on your requirements. Refer to the following table to determine which version of JRE to install. 01Hive_JDBC_driver_versions

  • Extract the driver version that is compatible with the JRE installation. For the purpose of this example we will be using the JDBC 4.1 driver, which is inside SimbaHiveJDBC41-1.0.41.1053.zip version.

  • A license file was emailed to you when you downloaded the driver. Locate this file and save it to the same directory where you extracted the .jar files.

    • 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:

    jdbc:hive2://[Server]:10000;AuthMech=2;transportMode=sasl;UID=[uid]

    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: 

    com.simba.hive.jdbc41.HS2Driver

    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:

    1. 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. SQLWorkbenchJ Manage JDBC Driver

    2. Click the Create a New Entry icon. SQL WorkbenchJ add New Driver

    3. Name the driver “Simba Hive”. Load the .jar files by clicking the folder icon. SQL WorkbenchJ Load .Jar Files

    4. 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.SQL Workbench J Select All .Jar files

    5. 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. Hive SQL WorkbenchJ Driver of Choice Hive Only

    6. The Classname field is now populated with the driver’s class name.
      SQL Workbench J Populated Class Name

    7. In the example URL field, type the connection URL mentioned in the “Configuring the Hive JDBC Driver” section above, and then click OK. SQL Workbench J Sample URL

    8. click File > Connect Window.SQL Workbench/J Connect Window

    9. Select the Simba Hive driver from the driver list, fill in the Username and Password (if it is required), and then click OK.SQL Workbench J Driver Selected

    10. 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. SQL Workbench J Select Statement

    11. Congratulations! The results of the of your query from SQL Workbench/J to Hive using simba’s Hive JDBC driver will be displayed. SQL Workbench J - Hive Data Results


    Now it's your turn !

    Download the Hive JDBC driver evaluation