Share this article:

Vertica Integration with SQuirreL: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the software versions and hardware platforms described in the following topic.

Software Versions and Hardware Platforms

This document describes the integration of Vertica with SQuirreL SQL Client. It is based on testing with the following software/hardware combinations:

  • Vertica server 7.2-3 on CentOS 6.5
    Vertica JDBC driver 7.2-3
    SQuirreL 3.7.1 on Windows 7 Enterprise, Service Pack 1 64-bit
    Java JRE 1.8.0
  • Vertica server 7.2-3 on CentOS 6.5
    Vertica JDBC driver 7.2-3
    SQuirreL 3.7.1 on Redhat Enterprise Linux 6.5
    Java JRE 1.8.0
  • Vertica server 8.0.0 on CentOS 6.5
    Vertica JDBC driver 8.0.0
    SQuirreL 3.6.1 on Windows Server 2008 R2 Standard
    Java JRE 1.8.0

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 is an open-source product. You can use plugins to extend the functionality of SQuirreL.

For information about SQuirreL, visit the SQuirreL website. If you are new to SQuirreL, see the Introduction document that is posted there.

Download and Install SQuirreL

SQuirreL is available for download on the SQuirreL website, http://www.squirrelsql.org. Download the JAR file for the latest version of SQuirreL for your platform.

After you download the JAR, follow the instructions on the Download and Installation page to install SQuirreL.

Note The installer and SQuirreL SQL Client version 3.x require Java JRE 1.6.x or later.

Optional Plugins

The SQuirreL installer prompts you to select optional plugins. One of the plugins is for Vertica. You can choose to install the Vertica plugin to obtain additional Vertica-specific functionality in SQuirreL.

If you choose the Vertica plugin, the installer places vertica.jar in the plugins directory under install_path.

Install_Plugin.png

After you install the plugin and connect SQuirreL to Vertica, you must activate the plugin. Follow the instructions in Activating the Vertica Plugin.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using SQuirreL, you must install the Vertica client package. This package includes the JDBC client driver that SQuirreL uses to connect to Vertica.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client package you downloaded, follow the installation steps in the Vertica documentation:

Install the JDBC driver in a location that is easily accessible by SQuirreL. In our testing, we created a verticalib directory under the SQuirreL installation directory and installed the JDBC driver there.

Connect SQuirreL to Vertica

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

Create a Driver Object

  1. On the left side of the main SQuirreL window, select the Driver 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.
  4. In the Example URL field, add the JDBC URL, including the driver type, IP address, port, and database name. For example:
    jdbc:vertica://10.20.71.175:5433/VMart
  5. Go to the Extra Class Path tab, and click Add.
  6. Navigate to the location where you installed the Vertica JDBC client driver and select it.
  7. In the Class name field, enter the JDBC driver class:
    com.vertica.jdbc.Drive

    Here is an example of a completed Driver form:

    Add_Driver.png

  8. Click Ok.
  9. Check the status window at the bottom of the main screen to ensure that the driver was successfully registered. You should see a message like the following:
    Driver class com.vertica.jdbc.Driver successfully registered for driver definition: Vertica_VMart_on_175

Create an Alias Object

  1. On the left side of the main SQuirreL window, select the Alias 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.
  5. In the User Name field, enter the name of the Vertica dbadmin user.
  6. In the Password field, enter the password of the Vertica dbadmin user.
  7. If you want to log on to Vertica automatically, check the Auto Logon box.
  8. If you want to automatically connect to Vertica at startup, check the Connect at Startup box.
  9. Click the Properties button, and select the properties that you want.

     

    Note Maure sure to go back and modify the properties after you successfully connect to Vertica and review the default behavior.

Here is an example of a completed Alias form:Add_Alias.png

  1. Click the Test button to ensure that the alias is associated with the connection
  2. Select the alias and click the connection icon to connect to Vertica.

An object navigation tree displays on the left. Several tabs containing information about the database and its objects display on the right.

The navigation tree on the left 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.

This example shows the main SQuirreL navigator for a Vertica database:

Connect_Navigate.png

Activating the Vertica Plugin

If you chose to install the Vertica plugin, you must activate it to enable its functionality in SQuirreL.

To activate the Vertica plugin:

  1. From the SQuirreL 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.
  4. Return to the Plugin Summary dialog box. The Vertica plugin is now loaded, as shown in this example:

    Activate_Plugin.png

The Vertica plugin provides the following additional information in SQuirreL:

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

Here is an example of the main screen in SQuirreL when the Vertica plugin is loaded:

Plugin_Additions.png

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

Troubleshooting

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

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

  1. On the Windows menu in SQuirreL, select View SQuirreL Logs.
  2. Look for a message like this:
    Caused by: com.vertica.util.LRSException: [Vertica][VJDBC](100102) Statement "select * from (select 'VMart' 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, 0 as user_data_type from v_catalog.odbc_columns order by catalog_name, schema_name, table_name, ordinal_position) as vmd where CATALOG_NAME ilike 'VMart' escape E'\\' and SCHEMA_NAME ilike 'store' escape E'\\' and TABLE_NAME ilike 'store_orders_fact' escape E'\\' and COLUMN_NAME ilike '%' 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. 

    (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=16384

The value of Contents – Limit Rows in SQuirreL 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 under the SQuirreL File menu.

Known Limitations

DECIMAL Data Type

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

To resolve this issue:

  1. On the SQuirreL 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 default format(3.14159)

    Data_Type_Controls.png

INTERVAL Data Types

INTERVALSECONDS and INTERVALMONTH datatype values display as other in SQuirreL.

To resolve this issue:

  1. On the SQuirreL File menu, select Global Preferences.
  2. Select Data Type Controls.
  3. Check the boxes for the following:
    • SQL Other (SQL type 1111)
    • Unknown DataTypes (non-standard SQL type codes)

      Interval_Data_Type.png

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 theTroubleshooting topic) instead of the SQL Result – Limit Rows property. This issue is under investigation.

For More Information

For More Information About… … See
SQuirreL SQL Client http://www.squirrelsql.org/
Vertica Community Edition https://my.vertica.com/community/
Vertica Documentation http://my.vertica.com/docs/latest/HTML/index.htm
Big Data and Analytics Community https://my.vertica.com/big-data-analytics-community-content/

Share this article: