Vertica Integration with Lavastorm: Connection Guide

Applies to Vertica 7.1.x and earlier 

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 Verticabut they may not have been tested. This document provides guidance using the latest versions of Vertica and Lavastorm as of September, 2015.

Lavastorm Overview

Lavastorm is a data management and analytics product that enables companies to analyze, optimize, and transform performance into business improvements. Lavastorm works with Vertica to transform and process data for analytics. This document describes:

  • Setting up your client environment for connection to Vertica from Lavastorm
  • The steps required to connect to Vertica from Lavastorm

We have tested Vertica 7.1.x with Lavastorm 6.1 on a Windows platform.

Download and Install Lavastorm

Lavastorm is a Java-based application available for Windows, Solaris Sparc, and Linux operating systems. While you can use either the ODBC or the JDBC driver to connect to Vertica, use the JDBC Driver. The ODBC driver may result in unexpected behavior. The Lavastorm Analytics Engine (LAE) 6.1 Windows installer installs:

  • Business Rule Editor (BRE) 6.1
  • LAE Server 6.1 for Windows

Install the Java Development Kit

Before installing Lavastorm, you must download and install the most recent version of Java Development Kit (JDK). Follow the instructions for downloading and installing from the Oracle website.

Install Lavastorm

Currently, Lavastorm is available as a 32-bit application. Download the version of Lavastorm that corresponds to your operating system. Follow the instructions for downloading and installing the JDK from the Lavastorm website.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using Lavastorm, you must download and install the Vertica client package. This package includes the Vertica JDBC and ODBC drivers that Lavastorm uses to connect to Vertica. Use the JDBC driver. Using the ODBC driver may result in unexpected behavior.  

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers page.
  2. Download the 32-bit version 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 about client and server compatibility see the Vertica documentation.

Install Vertica Client Drivers  

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Place the Client.jar File in an External Library Product Directory

For the Lavastorm application to access Vertica, you need to place the .jar file you downloaded with the client package into the product directory for external libraries:

  1. Copy the Vertica jar file from the Vertica driver location.

For example, for a 32-bit Vertica driver package installed on a Windows machine, the directory is: C:\Program Files (x86)\Vertica Systems\JDBC\vertica-jdbc-XXX-0.jar. The XXX represents the version of your Vertica database.

  1. Paste the file into the Lavastorm Analytics Engine directory that contains the external Java libraries.

For example, on a Windows machine and a Lavastorm 32-bit application, the directory is: C:\Program Files (x86)\Lavastorm\LAE6.1\lib\java\ext.

Note If you upgrade your Vertica client, you must repeat the steps preceding to replace the .jar file.

Connect Lavastorm to Vertica

You can connect to Vertica as a source or as a target using either the ODBC driver or the JDBC driver. When you connect as Vertica a source, you read data out of your Vertica database. When you connect Vertica as a target, you load data back in to your Vertica database.

Connect Vertica as a Source Using the JDBC Driver

  1. Launch the Lavastorm Business Rules Editor.
  2. From the palette of components on the bottom of the screen, select Acquisition.
  3. Drag and drop a JDBC Query component onto the Graph Canvas.
  4. Double-click the JDBC Query component to open the Node Editor.
  5. Enter the connection information in the Connection The following fields are required:
    • DbUser: Database user
    • DbPassword: Database password
    • DbUrl Format: jdbc:vertica://<server address>:<port number>/<database name>  

  Example: jdbc:vertica://192.168.1.174:5433/Vertica

    • DbDriver: com.vertica.jdbc.Driver
  1. In the SQL tab, enter a query to execute on the Vertica database. The query in the following example returns three columns from the Boolean table in the VERT_DATATYPE_v1_0_5

 LSFinalPic1.png

  1. Close the Node Editor.
  2. To execute the query, select the query component.
  3. From the main toolbar, click the green Play (Run) button.

Connect Vertica as a Target Using the JDBC Driver

  1. Launch the Lavastorm Business Rules Editor.
  2. From the palette of components on the bottom of the screen, select Publishing.
  3. Drag and drop a JDBC Store component onto the Graph Canvas.
  4. Double-click the JDBC Store component to open the Node Editor.
  5. Enter the output information for the target table in the Output tab. The following fields are required:
    • DbTable: Target table
    • CatalogName: Database name
    • Schema name: Target schema
    • DbUser: Database user
    • DbPassword: Database password
    • DbUrl Format: jdbc:vertica://<server>:<port>/<database>

  Example: jdbc:vertica://192.168.1.174:5433/Vertica

    • DbDriver: com.vertica.jdbc.Driver
  1. Close the Node Editor.
  2. Since data was loaded back into your Vertica database, you can execute the JDBC store component without a SQL statement. To execute this component, select the component and click the green Play (Run) button from the main toolbar.

Data Type Limitations

The following is a list of known limitations for data types using the JDBC driver.

  • Lavastorm interprets, displays, and loads all NULL BOOLEAN values as FALSE.
  • Long strings may appear blank. Lavastorm does not display the long string value but retrieves the value correctly. To view the value, copy the empty cell and paste it into any text editor.
  • Lavastorm does not support milliseconds.
  • Lavastorm does not support TIMEZONE offset.
  • Lavastorm interprets, displays, and loads all NULL numeric values as ZERO.
  • Store any decimal values as a FLOAT data type to avoid potential value differences between Lavastorm and Vertica.

For More Information

For More Information About… …See

Lavastorm

http://www.lavastorm.com/

Vertica Community Edition

https://vertica.com/community/

Vertica Documentation

https://vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://vertica.com/big-data-analytics-community-content/