Vertica Integration with Denodo: Connection Guide

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 25 May 2020
Tags: JDBC data sources

Download document

You can translate the document:

Goal

This connection guide provides basic information about setting up connections to Vertica from the Denodo Platform. In this document we will show you how to create data sources that connect to Vertica, create base views on Vertica tables, and query these tables from the Virtual DataPort Administration Tool.

Denodo and Vertica Product Versions

In this document, to test the connectivity from the Denodo Platform to Vertica the following software versions have been used:

Software

Version

Denodo Platform

Denodo Platform 8.0

Client Platform

Windows 10 Pro

Vertica Client Driver

Vertica JDBC 9.2.x

Vertica Server

Vertica Analytic Database v9.2

Download the JDBC Client Driver on Windows

The different Client Drivers can be downloaded from the Vertica website. We are going to use the JDBC interface for connecting, as the recommendation is to use JDBC whenever possible due to better performance.

Install the JDBC Client Driver in the Denodo Platform

Now that we have downloaded the driver, we need to install it. Since Denodo 8.0, you can upload the JDBC driver from your client to the Denodo Platform installation where you are connected to. This can be done via the Virtual DataPort Administration Tool by navigating to File > Extension Management > Libraries > Import.

In that window, you need to choose vertica-9 as the version and select the corresponding JDBC driver file that you have downloaded.

Note: For previous versions of the Denodo Platform, instead of uploading the JDBC driver, you need to move the driver into the correct directory which is <DENODO_HOME>\lib-external\jdbc-drivers\vertica-9 on the host machine where the Denodo Platform is installed.

Connect to Vertica

To connect to Vertica, you need to create a JDBC data source within the Denodo Platform. Then you can create the base views that will represent the Vertica tables.

Create a JDBC Data Source

  1. Open the Design Studio or the Virtual DataPort Administration Tool, log in and go to File > New > Data source > JDBC. The configuration of a new JDBC source opens.
  2. Enter the connection details for your Vertica database and click OK:
  1. Provide a name, for example ds_vertica
  2. Select Vertica 9 as Database adapter
  3. Enter the URI for the Vertica server following the predefined format and enter the login details.

If everything is correct, save the data source. You can verify this by clicking on Test Connection.

Create a Base View

To create a base view of the tables you want to query, follow these steps:

  • Switch to the tab CREATE BASE VIEW
  • Click + to expand the schema name you are interested in.
  • Select the tables you want to import.
  • Click on Create Selected

The base view is now created:

Query a Base View

To query one of the base views you just created, follow these steps:

  1. On the Elements Tree on the left-hand side of the screen, click on the three dots next to the base view name and select Execute. The Execution panel appears.

  1. If you want to add any conditions to the query sentence specify them in the bottom, otherwise simply click on Execute for retrieving the results.

References

Virtual DataPort Administration Guide: Uploading a JDBC Driver

Virtual DataPort Administration Guide: JDBC Sources

Virtual DataPort Administration Guide: Creating Base Views from a JDBC Data Source

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training