Share this article:

Vertica Integration With Mulesoft Anypoint: 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, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Anypoint as of March, 2018.

Anypoint Overview

MuleSoft's Anypoint Platform is a hybrid integration platform that enables you to easily build and rapidly scale an application network of apps, data, and devices. Some benefits to using Anypoint are:

  • Rapid development
  • Connective to all systems
  • Visibility and control

For more information see the MuleSoft website.

Vertica and Anypoint Software Versions

This document provides guidance using the software listed as follows:

Software Version
Anypoint Client

Anypoint Studio 6.3.0

Anypoint Platform Windows
Vertica Client Driver

Vertica 9.0

Vertica Server

Vertica Server version 9.0

Install Anypoint

Download Anypoint 6.3.0 from the MuleSoft website. Follow the instructions to install Anypoint on your system.

Install the Vertica Client Driver

  1. Go to the Vertica Client Drivers page on MyVertica.
  2. Download the JDBC driver.
  3. Follow the client installation instructions in the Vertica documentation.

Connect to Vertica

Create a Vertica connection as follows:

  1. Access the directory where you extracted Mulesoft, for example <Mulesoft_Loc>/AnypointStudio. Double-click AnypointStudio.exe to launch the application:

  2. Select File-> New-> Mule Project to create a new connector project. The following appears:

  3. Enter Vertica_project in the Project Name field and click Finished. The new project appears in the Package Explorer pane:

  4. Right-click the top level Vertica Project folder and select Build Path > Configure Build Path…. The Properties dialog appears.
  5. Select the Libraries tab and click Add External Jars...:


  6. Navigate to the directory where you downloaded JDBC and select the Vertica jar for JDBC. Click OK.
  7. From the bottom left panel, right-click vertica_project and select Add new connector:


  8. Select Generic Database Configuration as the Global Type:


  9. Enter the URL and Driver Class Name for Vertica and click Test Connection:


  10. Click Test Connection.... Upon a successful test, the Test connection successful message appears as shown above.

Create Data Integration

After creating a connection to Vertica, you need to create an integration to load data into Vertica, as follows:

  1. Open the vertica_project and drag and drop the Database component from the Mule palette to the vertica_project:


  2. Configure the database settings as shown below:

  3. Drag and drop another Database component from the Mule palette to the vertica_project:


  4. Configure the second database settings as shown below:


  5. Select Play > Run As > Mule Application to execute the job:

  6. After a successful execution, the project is successfully deployed.

Known Limitations

Before using Anypoint with Vertica, be aware of the following known limitations:

  • Min and Max values of Decimal data types can not be loaded to a target table.
  • Test-cases for Interval Second and Interval month failed with the following error:

    java.sql.SQLFeatureNotSupportedException: [Vertica][JDBC](10220) Driver not capable

    This failure occurred because the data could not be loaded to the target table.
  • Milliseconds are rounded off up to 3 places for Time data types.
  • Test-cases for TimeTZ and TimestampTZ data types failed. TimestampTZ failed because data could not be loaded into the target table. TimeTZ failed because the wrong data was loaded into the target table.

 

Share this article: