|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.
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:
Anypoint Studio 6.3.0
|Vertica Client Driver||
Vertica Server version 9.0
Install the Vertica Client Driver
- Go to the Vertica Client Drivers page on Vertica.
- Download the JDBC driver.
- Follow the client installation instructions in the Vertica documentation.
Connect to Vertica
Create a Vertica connection as follows:
- Access the directory where you extracted Mulesoft, for example
<Mulesoft_Loc>/AnypointStudio. Double-click AnypointStudio.exe to launch the application:
File-> New-> Mule Projectto create a new connector project. The following appears:
- Enter Vertica_project in the Project Name field and click Finished. The new project appears in the Package Explorer pane:
- Right-click the top level Vertica Project folder and select
Build Path > Configure Build Path…. The Properties dialog appears.
- Select the Libraries tab and click Add External Jars...:
- Navigate to the directory where you downloaded JDBC and select the Vertica jar for JDBC. Click OK.
- From the bottom left panel, right-click vertica_project and select Add new connector:
- Select Generic Database Configuration as the Global Type:
- Enter the URL and Driver Class Name for Vertica and click Test Connection:
Test Connection.... Upon a successful test, the
Test connection successfulmessage 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:
- Open the vertica_project and drag and drop the Database component from the Mule palette to the vertica_project:
- Configure the database settings as shown below:
- Drag and drop another Database component from the Mule palette to the vertica_project:
- Configure the second database settings as shown below:
- Select Play > Run As > Mule Application to execute the job:
- After a successful execution, the project is successfully deployed.
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.