Vertica Integration with SQuirreL SQL Client: 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 SQuirreL SQL Client: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
Partner Product

SQuirreL 4.6

Desktop Platform

Windows Server 2019 Standard

Vertica Client

Vertica JDBC driver 23.3

Vertica Server

Vertica Server 23.3

Server Platform RHEL 7.9

SQuirreL SQL Client Overview

SQuirreL SQL Client is a graphical Java program that allows you to browse data and schema objects and issue SQL commands in a JDBC-compliant database. SQuirreL SQL is an open-source product. You can use plugins to extend the functionality of SQuirreL SQL.

Installing SQuirreL SQL Client

To install SQuirrel SQL, go to Download and Installation on the SQuirrel SQL Client website and follow the instructions.

Note The installer and SQuirreL SQL Client version from 4.3.0 onwards require Java 11.x.

Optional Plugins

The SQuirreL SQL installer prompts you to select optional plugins. One of the plugins is for Vertica. You should install the Vertica plugin to obtain the following additional Vertica-specific information in SQuirreL SQL:

  • Additional tabs for tables, including Content(+), Projections, and Source.
  • A Details tab for projections.

When you choose the Vertica plugin, the installer places the plugin file in the plugins directory in the install_path folder. The plugin file is called vertica.jar.

Install_Plugin.png

When you choose the plugin during installation, it is automatically activated. If you later decide not to use the plugin, you can deactivate it. Follow the instructions in Activating or Deactivating the Vertica Plugin.

Installing the Vertica Client Driver

Before you can connect to Vertica using SQuirrel SQL, you must install the Vertica JDBC client driver by following these steps:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the JDBC driver package.

    Note  

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

  3. Add the JDBC Jar file to the C:\<your_path>\squirrel-sql-<version>\lib folder.

Connecting SQuirreL SQL Client to Vertica

To configure SQuirreL SQL to connect to Vertica you must create a driver object and an alias object.

Creating a Driver Object

  1. On the left pane of the main SQuirreL SQL window, select the Drivers tab.
  2. Click the + icon at the top of the pane to open the Add Driver dialog box.
  3. In the Name field, enter a name for the driver. In the examples that follow, the driver name is Vertica 23.3
  4. In the Example URL field, add the JDBC URL, including the driver type, IP address, port, and database name. For example:
    jdbc:vertica://<IP address>:5433/PartPub80DB
  5. Go to the Squirrel Java Class Path tab.
  6. Navigate to the location where you installed the Vertica JDBC client driver and select it. Click OK.

  7. Check the status window at the bottom of the main screen to ensure that the driver was successfully registered. You should see the following message:
    Driver class com.vertica.jdbc.Driver successfully registered for driver definition: Vertica 23.3

Creating an Alias Object

  1. On the left pane of the main SQuirreL SQL window, select the Aliases tab.
  2. Click the + icon at the top of the pane to open the Add Alias dialog box.
  3. In the Name field, enter a name for the alias.
  4. From the Driver drop-down list, select the Vertica driver object that you created previously. When you choose the driver, the URL field is filled with the URL from the driver definition.
  5. In the User Name field, enter the Vertica user.
  6. In the Password field, enter the Vertica user.
  7. If you want to automatically log on to Vertica, select Auto Logon.

    Note We recommend that you check the Auto Logon box so that the user ID and password are automatically populated in the following connection test. If you have to retype the user ID and password, you will not be fully testing the alias.

  8. Now you can make your final optional changes to the alias:

    • To set properties, click Properties.
    • To automatically connect to Vertica at startup, select Auto Logon.
    • To automatically connect to Vertica at SQuirreL SQL startup, select Connect at application startup.
  9. Click Test to ensure that the alias can connect to Vertica using the driver you defined.

    When you click Test, a Test dialog box opens. The fields are populated with the values you specified for the alias.

    If you selected Auto Logon, SQuirreL SQL immediately validates the connection and returns success or failure. If you did not select Auto Logon, then you must type the user name and password and then click Test.

  10. After the connection is successful, click OK to close the Test dialog box.
  11. Click OK to save the alias configuration.
  12. On the Alias tab, select the alias you created.

    • If you configured the alias with Auto Logon, SQuirreL SQL is already connected to your database. The SQuirreL SQL navigator displays the database design.
    • If you did not check the Auto Logon box, then click the connection icon above the alias list and provide your credentials to connect to Vertica.

The SQuirreL SQL navigator displays the alias name at the top level. The next level down is the database name. The levels below are the objects in the database.

When you select a table, tabs that contain information about the table definition and data appear.

Activating or Deactivating the Vertica Plugin

You can activate or deactivate the Vertica plugin at any time. If you chose the plugin during installation, it is active by default.

Activate the Vertica Plugin

  1. From the SQuirreL SQL menu bar, select Plugins > Summary to open the Plugin Summary dialog box.
  2. Locate the Vertica plugin, check the Load on Startup box, and click OK.
  3. Restart SQuirreL SQL.
  4. Return to the Plugin Summary dialog box. The Vertica plugin is now loaded, as shown in this example:

Deactivating the Vertica Plugin

To deactivate the Vertica plugin, clear the Vertica Plugin check box.

For more information, see the Vertica Plugin help topic in SQuirreL SQL. Select Help on the SQuirreL SQL main menu to find the topic.

Troubleshooting

Using Content or Content(+) Tabs on Large Tables

Issue: When you try to use the Content or Content(+) tabs on a large table, SQuirreL SQL does not appear to respond and does not return results.

Solution: When you encounter this issue, check the SQuirreL SQL logs as follows:

  1. On the Windows menu in SQuirreL SQL, select View SQuirreL Logs.
  2. Look for a message like this:
    2023-09-12 15:41:35:187 [pool-1-thread-1] ERROR  net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab  - <messageIsNull>
    java.sql.SQLNonTransientConnectionException: [Vertica][VJDBC](100102) Statement "select * from (select 'PartPub80DB' as catalog_name, schema_name, table_name, column_name, case when sql_datetime_sub = 10 then 1111 else data_type end as data_type, data_type_name, column_size, buffer_length, decimal_digits, num_prec_radix, nullable, remarks, column_default as column_def, sql_type_id as sql_data_type, sql_datetime_sub, char_octet_length, ordinal_position, is_nullable, case is_identity when 't' then 'YES' when 'f' then 'NO' else null end as is_autoincrement, case is_identity when 't' then 'YES' when 'f' then 'NO' else null end as is_generatedcolumn, 0 as user_data_type from v_catalog.jdbc_columns order by catalog_name, schema_name, table_name, ordinal_position) as vmd where TABLE_NAME ilike 'Customer_Dimension' escape E'\\' and SCHEMA_NAME ilike 'CLICKSTREAM' escape E'\\' and COLUMN_NAME ilike '%' escape E'\\' and CATALOG_NAME ilike 'PartPub80DB' escape E'\\' " cannot execute because the driver has not finished reading the current open ResultSet. The driver cannot finish reading the current ResultSet because its buffer (8192 bytes) is full. The current ResultSet must be fully iterated through or closed before another statement can execute.
    

    The key string to look for is:

    The driver cannot finish reading the current ResultSet because its buffer (8192 bytes) is full. 

    Note The number of bytes may differ if you have modified ResultBufferSize.

Explanation: We have seen this error in cases where the client is issuing a new query on the connection while iterating through a result set from a previous query. Vertica only supports execution of a single query at a time on a single connection. Concurrent queries on a single connection only work when the driver's internal result buffer, which is controlled by ResultBufferSize, is not full. If you have too many results, or the results are too large, an error occurs.

Solution: To avoid this error, add the ResultBufferSize property to the JDBC connection string in your SQuirreL Alias. Set the value high enough to fit all results. The default value of ResultBufferSize is 8192 (8k, the limit noted in the error).

jdbc:vertica://hostname:5433/dbname?ResultBufferSize=32000

The value of Contents – Limit Rows in SQuirreL SQL directly affects the number of rows returned and thus the required buffer size. If you increase the value of Contents – Limit Rows, you may have to increase the value of ResultBufferSize as well.

The Contents - Limit Rows option is available on the Object Tree tab. To navigate to the Object Tree tab, select New Session Properties in the SQuirreL File menu.

Creating Vertica Functions in the SQL Commander Window

Creating a Vertica function in the SQL Commander window may result in an error such as

[CREATE - 0 rows, 0.001 secs] [Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "EOL"

This is because CREATE FUNCTION has multiple statement end characters as part of the CREATE command syntax. You need to change the Statement End character in Session Properties to something like the pipe symbol (|) temporarily. After you execute the SQL CREATE function, set the delimiter back to the semicolon (;).

Known Limitations

DECIMAL, DOUBLE Data Type

Some DECIMAL values in Vertica appear rounded off to 5 decimal places in SQuirreL SQL.

To resolve this issue:

  1. On the SQuirreL SQL File menu, select Global Preferences.
  2. Select Data Type Controls.
  3. Set FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL (SQL Types 6,7,8,2,3) to Use user defined format (1,000,000), (3.141592653589793) with: maximum number of decimal digits of :

Content(+) Tab

The Content(+) tab, added by the Vertica plugin, improves the efficiency of SQL queries and adds a LIMIT argument. Apparently, the Content(+) tab is getting the LIMIT value from the Content – Limit Objects property (noted in the Troubleshooting topic) instead of the SQL Result – Limit Rows property. This issue is under investigation.

For More Information