Share this article:

Vertica Integration with DBeaver: Tips and Techniques

About Vertica Tips and Techniques

Vertica Tips and Techniques documents provide information to assist you in using Vertica with third-party products. This document provides guidance using one version of Vertica and one version of the vendor's software. While other combinations may feature the same or similar behavior, they may not have been tested.

Vertica and DBeaver Versions Tested

This document is based on our testing of Vertica and DBeaver using the following versions:

Software Version
Partner Product

DBeaver 4.3.0

Desktop Platform

Windows Server 2012 R2

Vertica Client

Vertica JDBC driver 9.0.x

Vertica Server

Vertica Server 9.0.1-0

DBeaver Overview

DBeaver is a free and open-source SQL client tool. DBeaver runs on Windows, MacOS, and Linux platforms and supports most databases. You can write your own extensions to DBeaver.

For details about DBeaver, see the DBeaver website.

Vertica-Specific Features in DBeaver

DBeaver Version Vertica-Specific Feature
DBeaver Enterprise Manager 4.3.5

Metadata browser adds support for Vertica flex tables and functions.

DBeaver Enterprise Manager 4.3.0

New dialog box exposes Vertica JDBC properties.

DBeaver Enterprise Manager 4.2.6

Metadata browser adds support for Vertica projections, nodes, sequences, and procedures source.

Connecting Vertica and DBeaver

DBeaver uses the Vertica JDBC driver to connect to Vertica. To install the driver on Windows, follow the instructions in Installing the JDBC Client Driver for Windows in the Vertica documentation. For additional information, see Connecting to Vertica.

For step-by-step connection instructions, see Vertica Integration with DBeaver: Connection Guide.

Tips for Using DBeaver with Vertica

The following topics present information to assist you in using DBeaver with Vertica:

Editing Driver Properties

You can modify Vertica driver properties from within DBeaver.

To access the Vertica driver properties:

  1. On the main menu of DBeaver, locate the Database Navigator.
  2. Right-click the Vertica database connection and click Edit Connection.
  3. Under Connection settings, select Driver properties.

For details about JDBC connection properties in Vertica, see the Vertica documentation.

Using Native Connection Load Balancing

Native connection load balancing is a feature built into the Vertica server and client libraries. It helps to evenly distribute the resource demands of client connections across the hosts in a cluster. Connection load balancing requires configuration on the server and on the client.

Configuration on the Server

You can set load balancing to one of the following:

  • NONE: Disables load balancing. (Default)
  • ROUNDROBIN: Chooses the next host from a circular list.
  • RANDOM: Chooses the next host at random.

The following query checks if load balancing is in effect:

SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES;

The following statement sets load balancng to ROUNDROBIN:

SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');

For details about server configuration for connection load balancing, see the Vertica documentation.

Configuration on the Client

  1. In DBeaver, open the Driver properties page for your Vertica connection.
  2. Under Configuration settings, select Driver properties.
  3. Set connectionloadbalance to true.

For details about native connection load balancing, see the Vertica documentation.

Using Client-Side Logging

You can enable logging in DBeaver by setting the log path and log level in the driver properties. With logging, you can debug or trace connectivity details or issues. The log path and log level settings are defined as follows:

  • Log Path: Sets the path where the log file is written.
  • Log Level: Sets the kind of information logged by the JDBC driver. The log level values are: Debug, Error, Trace, Warning, Info, and Off.

In the following example, the log level is set to info and the path is set to C:/, causing information about load balancing for this connection to be written to a log file directly under C:\.

In the following log file, you can see the JDBC properties and verify that load balancing is enabled. You can debug client issues by examining other entries in the log file.

Using Routable Queries

The JDBC Routable Query API is ideal for high-volume, short requests that return a small number of results on a single node. The Routable Query API provides lower latency, increases throughput, and uses fewer system resources than distributed queries. However, you must segment the data so that the JDBC client can determine on which node the data resides.

For example, the following driver settings enable routable queries, setting maxpooledconnections to 200 and maxpooledconnectionspernode to 24:

You can check the logs to vertfy that routable queries were in effect and evaluate the increase in throughput:

For more information on routable queries, see JDBC Routable Query API: Best Practices.

Using Session Labels to Track Queries

The sessionlabel driver property lets you track the queries in the labeled session. In the following example, sessionlabel is set to testlog:

With sessionlabel set to testlog, the following query returns results like the sample output shown below:

SELECT * FROM SESSIONS WHERE client_label = 'testlog';

Using Entity Relationship Diagrams

The DBeaver UI includes an option for viewing your data as an Entity Relationship (ER) diagram. ER diagrams offer a visual representation of the business rles that govern the relationships between tables. Use ER diagrams to gain a better understanding of the structure of your data.

The following ER diagram shows the relationships between a fact table and its dimensions in the Vertica VMart database.

Using SSL Security

Vertica supports Secure Socket Layer (SSL) v3/Transport Layer Security (TLS) 1.0. SSL/TLS provides secure connections and protects the privacy and integrity of the data exchanged between Vertica and its clients.

Configure SSL in Vertica

  1. At the Linux command line, execute the following:
    $ open ssl req -x509 -nodes -days 3650 -newkey rsa:1024 -keyout server.key -out server.crt

    Specify the following:

    • Country Name: A 2-letter code. For example, GB or US.
    • State or Province Name: The full state or province name. For example, Berkshire or Massachusetts.
    • Locality Name: The city. For example, Newbury or Boston.
    • Organization Name: The company name. For example MyCompany_Ltd or Vertica
    • Organizational Unit Name: The section within the company. For example, Support_server.
    • Common Name: Your name or your server host name. For example, MyHost.
    • Email Address: For example, myhost@vertica.com
  2. Check that the server.crt and server.key files have been generated.
  3. Copy server.crt and server.key to the database catalog directory.
  4. Start vsql and execute the following command:
    SQL> SELECT set_config_parameter('EnableSSL', '1');
  5. Restart the database.
  6. Start vsql again. You should see a message like the following before the vsql prompt appears:
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256, protocol: TLSv1.2)

Enable SSL in DBeaver

  1. At the Linux command line on the server, execute the following:
    > openssl x509 -in server.crt -out server.crt.der -outform der

    This command converts the Vertica server certificate to a form that can be recognized by Java.

  2. Copy server.crt.der to the DBeaver client machine and execute the following command:
    > Keytool.exe  -keystore verticassl -keypasswd -storepass test123 -importkeystore -noprompt -alias verticasql -import -file server.crt.der

    You can find keytool.exe in C:\Program Files\Java\jdk1.8.0_71\bin

  3. Execute the following command to start DBeaver:
    > dbeaver -vmargs -Djavax.net.ssl.trustStore="C:\Program Files\Java\jdk1.8.0_71\bin\verticassl" -Djavax.net.ssl.trustStorePassword=test123Set ssl
  4. In the DBeaver driver properties, set ssl to true.

You can verify that SSL has been enabled by checking the JDBC logs. For example:

For details about SSL in Vertica, see the Vertica documentation.

Using Query Manager

DBeaver logs all queries that have been executed in the session in its Query Manager.

To open the Query Manager, select Window > Show View from the DBeaver main menu.

The information presented within the Query Manager is shown in the following example:

Note DBeaver Enterprise Edition persists all executed queries in the internal database, so execution history is available after program restart.

Exporting and Importing Tables

Dbeaver supports import and export across database connections and importing from and exporting to different file formats.

To import and export data across database connections, use this option in DBeaver:

Database= Database table(s)

Follow these steps to export data from one database connection to another:

  1. In the main menu of DBeaver, select a table from one of the available database connections.
  2. Click Export Table Data.

    In this example, online_sales.call_center_dimenion is selected for export.

  3. In the Data Transfer dialog box, select Database to indicate export to a different database, and Database table(s) to indicate that the export target is one or more tables in that database.

  4. Click Next.
  5. Click Existing table to export the current table or New table to create a new table to export.

  6. Click Next to export the data.

    Note Exporting a very large amount of data may cause a Java heap space error in DBeaver. If this error occurs, restart Dbeaver with -vmargs-Xms500m from the command promp. Depending on the export requirement, increase the memory.

For More Information

For more information about Vertica and DBeaver, see the following:

Share this article: