HPE Vertica Integration with Denodo: Connection Guide

by Level 4 BD_Partner_Eng Level 4 ‎09-18-2015 07:50 AM - edited ‎11-21-2016 12:34 PM

  

Contents

About Vertica Connection Guides

Denodo Overview

In This Document

Denodo and HPE Vertica Product Versions

Install Denodo

Install the Vertica Client Drivers

      Prerequisites

      Installing the JDBC Client Driver on Windows

Connect to HPE Vertica

      Create a JDBC Data Source

      Create a Base View

      Query a Base View

Access Views from a JDBC Client

For More Information

 

PDF: HPE Vertica Integration with Denodo: 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, Hewlett Packard Enterprise may not have tested these other versions. This document provides guidance using the latest versions of Vertica and Denodo as of September, 2015.

 

Denodo Overview

The Denodo Platform is a software platform that integrates data from different types of data sources such as relational databases, Web services, XML documents, spreadsheets, and flat files. Denodo integrates unstructured and structured data in real time.

 

The Denodo Platform includes the following components:

  • The Virtual DataPort module is the core of the Denodo Platform. The Virtual DataPort executes the data federation and publishes views as SOAP (Simple Object Access Protocol) and REST (Representational State Transfer) Web services.
  • The ITPilot module extracts specific information from a website by following a navigation sequence and a pattern created graphically by the user.
  • The Aracne module is a web crawler that indexes the text in a website that you specify.
  • The Scheduler module schedules tasks to perform in the other modules.

Denodo connects to Vertica, a structured data source, and creates views on database tables. Denodo publishes these views using Virtual DataPort.

 

In This Document

The following topics show you how to:

  • Install the Denodo Platform version 5.5.
  • Install the most recent update to the Denodo Platform.
  • Create data sources that connect to Vertica.
  • Create base views on Vertica tables.
  • Query these tables from the Denodo Administration Tool and from a JDBC client.

This document assumes that the reader is familiar with both Denodo and Vertica.

 

Denodo and Vertica Product Versions

For this document, Hewlett-Packard tested connecting from Denodo to HPE Vertica with the following software versions:

Software

Version

Denodo

Denodo v5.5

Client Platform

Windows Server 2008 R2 Standard

Vertica Client

Vertica JDBC 7.1

Vertica Server

Vertica Analytic Database v7.1

 

Install Denodo

To install the Denodo Platform on Windows or Linux, follow the steps in the Denodo Platform 5.5 Installation Guide. Specifically, follow all the steps for configuring the Virtual DataPort module.

Denodo also offers a free version of Denodo Platform called Denodo Express. You can download it from the Denodo Express page. You will have to create a Community account before you can download Denodo Express.

 

Install the Vertica Client Drivers

Prerequisites

Before you can install the Vertica client drivers, your client systems must meet the following requirements:

Operating system

For the list of supported platforms, see HPE Vertica Client Drivers.

UTF-8, UTF-16 and UTF-32 support

Configure the driver to use the required encoding method. This encoding allows strings to be passed between the driver and the application without intermediate conversion.

 

Installing the JDBC Client Driver on Windows

  1. Navigate to the Vertica Client Drivers page. 
  2. Download the version (32-bit or 64-bit) of the Vertica client package that matches 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, see Client Driver and Server Version Compatibility in the Vertica documentation.

Follow the instructions at Installing the Client Drivers in the Vertica documentation.

 

Connect to Vertica

After installing Denodo, you need to create a JDBC data source. Then you can create base views that represent your Vertica tables.

 

Create a JDBC Data Source

To create a JDBC data source, follow these steps:

 

  1. Go to the directory <DENODO_HOME>\bin.
  2. If you are on Windows, run exe. If you are on Linux, run denodo_platform.sh.

The Denodo Platform Control Center appears.

 

denodo_platform_control_center.png

 

  1. Click the VDP button on the upper-left side of the window.

The Virtual DataPort window opens.

vdp.png

 

  1. To start the Virtual DataPort and its Administration Tool, click the Play buttons on the Administration Tool and Server icons.
  2. Log in to the Denodo Platform using the default credentials:
    • User name: admin
    • Password: admin
  3. After logging in, select File > New > Data source > JDBC. The Create New JDBC Data Source dialog box opens.
  4. Select the Connection tab and enter the connection details for your Vertica database.

    connection_tab_data_source_details.png            

  1. Click OK. If everything is correct, you see a list of schemas in your HPE Vertica database.

schema_list.png

 

Create a Base View

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

  1. Click + to expand the schema name you are interested in.
  2. Select the tables you want to import.
  3. At the bottom of the window, select Create selected base views.
  4. After a few seconds, the list of views appears.

list_of_views.PNG

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, right-click the base view name and click Execute. The Execute view window appears.

execute_view.png
                            

  1. If you do not want to add any conditions to this query sentence, click Execute. If you do want to add conditions to the query, click the icon next to Conditions and click Execute when you return to this window.

Access Views from a JDBC Client

For information about working with views in Denodo, see the Virtual DataPort Administration Guide in the Denodo documentation.

 

For More Information

For More Information About…

… See

Denodo

http://www.denodo.com/en

Vertica Community

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://community.dev.hpe.com/t5/Big-Data-and-Analytics/ct-p/bigdata_analytics