|Share this article:|
Vertica Integration with DbVisualizer: 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 DbVisualizer: Latest Versions Tested
DbVisualizer Free 10.0.13 and 10.0.14
Windows 10 Enterprise
JDBC 9.1.0 and 9.1.1
Vertica Database 9.1.0 and 9.1.1
DbVisualizer is a visual SQL client that includes the following main features:
- Broad database and platform support
- A SQL command tool
- Database browsing
- Object management
- Table data management
It is available in both Free and Pro Editions. The Free Edition is a subset of the Pro Edition’s capabilities. See the DbVisualizer website for complete details and comparisons.
Before You Begin
There have been some updates in how DbVisualizer Free for Vertica is distributed. Previously, there was a DbVisualizer Free for Vertica available on the Vertica Marketplace, and DbVisualizer Free and Pro Editions available on the DbVisualizer web site.
Now you can access DbVisualizer Free for Vertica by downloading the DbVisualizer Free Edition from the DbVisualizer download page. Then downloading and installing a special license from Vertica. This license activates Vertica specific features in the DbVisualizer Free version, making it equivalent to the previously distributed DbVisualizer Free for Vertica.
Note DbVisualizer Pro Edition includes all the Vertica-specific features and uses the standard Pro license from DbVis.
Download and Install DbVisualizer
See the DbVisualizer documentation for:
- System requirements (requirements are in the left-hand margin)
- Download instructions
- Installation instructions
For our tests we downloaded the Windows .exe distribution and ran that installer. The install checks for the existence of the required Java JVM and provides the option to install it if it isn’t already present. During the install, choices for database specific drivers are provided. Make sure the Vertica driver is selected.
Download and Install the Vertica Client Drivers
No Vertica driver installation is necessary. DbVisualizer comes with a built-in Vertica JDBC driver. DbVisualizer versions prior to 10.0.14 have the Vertica 7.2 JDBC driver included. This driver is forward compatible with Vertica Server 7.2 and above. The latest DbVisualizer as of this testing, 10.0.14, includes the Vertica 9.1.1 driver which supports backward compatibility back to Vertica Server 7.2. See the Vertica Supported Platforms Guide for details.
If you need to use a newer Vertica JDBC driver, for example to get a bug fix, you can:
- Go to the Vertica Client Drivers page.
- Download the version of the Vertica client package that you want to install.
- Based on the client driver package you downloaded, follow the installation instructions in the Vertica documentation.
- After installation, rename the .jar file to
- Rename the existing vertica.jar file in the
DbVisualizer <installpath>\DbVisualizer\jdbc\verticato something like vertica_old.jar.
- Copy the new vertica.jar file to
DbVisualizer 10.0.14 uses the new backward compatible Version 9.1.1 JDBC driver. This works with Vertica servers back to 7.2 and forward with future releases. Any changes to this compatibility will be documented in the Vertica Supported Platforms Guide.
Note If you are using a Vertica JDBC driver older than 9.1.1, make sure the version of JDBC driver is less than or equal to the Vertica Server version. The pre 9.1.1 JDBC drivers are not backwards compatible. In our initial tests we followed the above instructions and replaced the 7.2 driver supplied with a 9.1.0 driver that matched the 9.1 Vertica server we were connecting to. In the second test we upgraded DbVisualizer to 10.0.14 and let it install the newer Vertica JDBC 9.1.1 driver and we connected to both the 9.1.0 and 9.1.1 databases.
Installing the DbVisualizer Free for Vertica License
Right-click here and select Save link as... to download the special license key file needed to activate the Vertica features. Install the license as follows:
dbvisfreeforvertica.licensefile to your local disk.
- Start DbVisualizer and select Help > License Key.
- Select License Key File as the License Type.
- In the License Key File field, enter the path to the newly saved
dbvisfreeforvertica.licensefile. Or click the Browse button to the right of the field to browse for the license file.
- Click Install License.
- When prompted, restart DbVisualizer .
The DbVisualizer main window should display DbVisualizer Free 10.0.x for Vertica in the window title.
Connect DbVisualizer to Vertica
To connect DbVisualizer to your Vertica database, first you need to create a Database Connection object as follows:
- From the home page, select Database > Create Database Connection. When prompted, select Use Wizard.
- On the New Connection Wizard screen enter the alias you want your connection to be named. This names appears in the Objects Tree for connection and navigation. Vertica recommends using a name that is meaningful to your users, such as dbnameonhostname (for example, VMartOnHost9).
In the Select Database Driver screen scroll the list of installed drivers (denoted by green checkmarks next to the names) and select the Vertica driver.
- On the next screen, enter the following database information:
Settings Format: Select Server Info
Database Server: Enter the IP address or hostname of the Vertica server
Database Port: 5433
Database name: Enter the name of the database running on the Vertica server
Database Userid: Enter the name of the Vertica database user you want to connect with
Database Password: Enter the password of the Vertica database user you are connecting with
Auto Commit - Select the checkbox
Save Database Password: Select Save Between Sessions
Permission Mode: Select Development
Click the Ping Server button to verify connectivity. Upon a successful connection click Finish.
The Database Connection screen appears. Here you can view database and driver information. You can also manage data type handling, as well as connection, driver, and database properties.
For more information on the various options in the Connection Wizard and the resulting Database Connection screen tabs see the DbVisualizer User Guide.
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 pass DbVisualizer hints in the SQL to change the delimiter temporarily. We used the @delimiter hint successfully. See the DbVisualizer User Guide section on Executing Complex Statements for more details.
It’s worth noting that under the Tools menu there’s a debug window which provides excellent detailed trace information for DbVisualizer and/or the JDBC driver. This could be a very useful tool in debugging any issues you may encounter, especially those related to connectivity or keep alive. For more information see the Debugging DbVisualizer documentation.
Before using DbVisualizer with Vertica, be aware of the following known limitations:
- To get the desired display for different variations of numeric data types, you may need to change the formatting in the result screen formatting dialog, or in the Menu > Tools > Tool Properties > General tab under Data Formatting.
- Time with formatting can display up to milliseconds. Adding for microseconds adds leading zeros.
- If multilingual characters from Vertica appear corrupted, it is probably because your font cannot display them correctly. Go to Tool Properties > General > Appearance > Fonts to change the font.
The following image shows some fonts that usually display multilingual characters as expected:
- We’ve seen cases where the connection status symbol displayed on the Connection alias in the Navigator window is inaccurate. An example would be if there’s a TCP or keep alive timeout which has dropped the connection, yet the Connection pane’s database alias status shows a green check mark indicating an active connection. Further, you may have to select disconnect before you can try to reconnect. It appears it can be partially disconnected and a full disconnect has to be initiated before a clean reconnect can be established.