Vertica Integration with SQL Workbench/J: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and SQL Workbench/J: Latest Versions Tested

Software Version
Partner Product

SQL Workbench/J Build 128
Partner Product Platform Windows Server 2019 Standard
Vertica Client

Vertica JDBC Driver 11.0.1-0
Vertica Server Vertica Analytic Database 11.0.1-0

SQL WorkBench/J Overview

SQL Workbench/J is an open source, DBMS-independent, powerful command line SQL query tool written in Java and runs on operating systems supporting Java runtime environment.

This tool can run SQL scripts and has the import/export feature to read from and write to csv, XML, and HTML.

Installing SQL Workbench/J

  1. Go to the SQL Workbench/J website.
  2. In the left pane, click Downloads.
  3. Click Generic package for all systems including all optional libraries to download the zip file.
    SQL WorkBench/J does not require an installation. When you unzip this file and double-click the executable, the IDE opens.

Installing the Vertica Client Driver

SQL Workbench/J connects to Vertica using the Vertica JDBC driver. Follow these instructions to download and install the JDBC driver:

  1. Navigate to the Client Drivers page on the Vertica website.

  2. Download the JDBC driver compatible with your Vertica Server version.

    Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Connecting SQL Workbench/J to Vertica

Follow these steps to create a connection to Vertica.

Note You can connect to Vertica using SSH. See, Connecting SQL Workbench/J to Vertica Using SSH

  1. Unzip the SQL Workbench zip file that you downloaded to a directory of your choice.
  2. Double-click the SQLWorkbench64 file if you are running a 64-bit platform else double-click SQLWorkbench.

  3. SQL WorkBench/J prompts for Java runtime when executing the first time. Click Select Java Home to navigate to the Java runtime directory.

    SQL Workbench/J requires Java 11 or later and recommends using the Java 17 version.

    The Select Connection Profile screen appears.

  4. In the left pane, click Manage Drivers, scroll down and select Vertica.

  5. Click the folder icon to navigate to the directory where you placed the Vertica jar file and click OK.
  6. Provide the connection details in the Select Connection Profile screen.
    • Name: Type the name of the connection profile.

    • Driver: Select Vertica from the drop-down list.

    • URL: Type the JDBC URL to the database. For example, jdbc:vertica://(hostname):5433/(database)

    • Username: Type the database user name.

    • Password: Type the database password.

  7. Click Test to check the connection and click OK.

Connecting SQL Workbench/J to Vertica Using SSH

  1. SQL WorkBench/J can connect to a remote database using SSH.
    1. Configure the Vertica jar file as described in Steps 1 to 5 in Connecting SQL Workbench/J to Vertica
    2. Click SSH in the Select Connection Profile and provide the configuration details:

      • SSH hostname: IP address of the remote host.

      • SSH port: SSH port. Default is 22.

      • Username: Remote host username.

      • Private key file: If the SSH host is configured to use authentication SSH key file, click the three dots to locate the SSH key file.

      • Password: Password for the remote user login or the paraphrase of the private key file.

      • Local port: Port to be used for tunnel. If no port is specified, it is automatically chosen.

      • DB hostname: IP address of the database server.

      • DB port: Database port of the Vertica server.

    3. Click Test to check the connection and click OK.
  2. The home screen appears.
    Click Tools > Show DbTree to get the details of all the schemas and write the required SQL query.

Troubleshooting

Incomplete Data Type Display

Issue: SQL workbench/J does not show the complete value for milliseconds and decimal data types with the default settings.

Resolution: Follow these steps to display the complete data set:

  1. Click Tools > Options. In the left pane, select Data formatting.

  2. Change the value of Time format to display milliseconds. Change the value of Decimal digits to display the decimal data type values beyond two digits to the right of the decimal.

Known Limitations

  • For TIME data type, milliseconds are not displayed.
  • For TIMETZ data type, milliseconds and timezone offset is not displayed.
  • For TIMESTAMPTZ data type, timezone offset is not displayed.

For More Information