Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…


Stambia Data Integration allows to work with Vertica Databases, offering the possibility to produce fully customized Integration Processes.

In this article, you'll learn to reverse your first Metadata, optionally configure it to use FTP or SSH to transfer temporary files, and to produce Mappings or Processes.

The idea in our examples will be to load data from the demo HSQL Database, and from delimited files, to a Vertica Database.

Refer to the presentation article for the download section and the list of supported features.

 

Installation

The installation procedure is the following:

  1. Download the Generic and Vertica Templates and import them in your workspace
  2. Install the Vertica JDBC library (vertica-jdbc-xxx.jar) in the stambiaRuntime/lib/jdbc/ folder of the Runtime (S17, S18, S19) or in the module subfolder (S20).
  3. Restart the Designer and Runtime after having installed the libraries

 

Metadata Configuration

Vertica Database Metadata

The first step is to create the Vertica Database Metadata.

For this, simply create a new Metadata for Vertica as you would usually do for any other database:

creation wizard

Here is an example of configuration:

server wizard

 

That's all, you can now connect and reverse your schemas and tables as usual.

If the wizard informs you that the JDBC driver class cannot be loaded, you must click on the "Add/Edit Drivers", find the Vertica Driver entry in the new window displayed and configure it as explained in this article.

The Vertica Driver Class is "com.vertica.jdbc.Driver".

 

FTP and SSH Metadata

Some of the Vertica Templates are optimized to use the Vertica loaders to produce better performances while loading data into Vertica.

For this data may be extracted to temporary files before being loaded through Vertica's COPY statement.

Vertica therefor needs to have access to the files to be able to load them.

Several solutions are supported by the Templates:

  • Loading the local files directly through the JDBC driver
  • Automatically send the generated temporary files through FTP on the target Vertica machine before loading it
  • Automatically send the generated temporary files through SSH on the target Vertica machine before loading it

The first method is the most portable and easy to use one as it does not require to have any FTP or SSH servers on the target Vertica Machine.

Sending the temporary files through FTP or SSH is so optional, and should be used when loading big files for instance for performances purposes, as the files to be loaded will be directly accessible by Vertica.

 

Loading the local files through the JDBC Driver

The first method can be used simply with the dedicated "Local Mode" parameter available on Vertica Load Templates.

For instance, on the "Load Rdbms to Vertica (with COPY)" Template:

localMode

Loading through the JDBC driver is supported by the following Vertica Load Templates

  • LOAD File to Vertica (with COPY)
  • LOAD Rdbms to Vertica (with COPY)
  • LOAD XML to Vertica (with COPY)

 

Automatically send the file through FTP

Another possibility is as explained to automatically send the temporary files through FTP before processing them.

The first thing to do is to prepare the FTP connection by creating an FTP Metadata.

For this simply create an FTP Metadata, fill in the target Vertica Machine connection properties, and finally define in a folder in which the files will be transferred.

When this is done, drag and drop the FTP folder on the Vertica Metadata and rename it to 'TARGET_FTP'.

target ftp

Automatic FTP transfer is supported by the following Vertica Load Templates

  • LOAD File to Vertica (with COPY)
  • LOAD Rdbms to Vertica (with COPY)
  • LOAD XML to Vertica (with COPY)

 

Automatically send the file through SSH

The last possibility is, similarly to the FTP method, to automatically send the temporary files through SSH before processing them.

The first thing to do is to prepare the SSH connection by creating an SSH Metadata.

For this simply create an SSH Metadata, fill in the target Vertica Machine connection properties, and finally define in a folder in which the files will be transferred.

When this is done, drag and drop the SSH folder on the Vertica Metadata and rename it to 'TARGET_SSH'.

target ssh

Automatic SSH transfer is supported by the following Vertica Load Templates

  • LOAD File to Vertica (with COPY)

 

Creating your first Mappings

Your Metadata being ready and your tables reversed, you can now start creating your first Mappings.

The Vertica technology in Stambia is not different than any other database you could usually use.

Drag and drop your sources and targets, map the columns as usual, and configure the templates accordingly to your requirements.

 

Loading data from an HSQL Database into Vertica

mapping hsql to vertica

 

Loading data from a Delimited File into Vertica

mapping file to vertica

 

Loading data from an XML File into Vertica

mapping xml to vertica

 

Loading data from Vertica into an HSQL Database

mapping vertica to hsql

 

Performing Reject detection while loading a Vertica Table

mapping rejects

 

Replicating an HSQL Database into a Vertica Database

process replicator

 

Demonstration Project

The Vertica demonstration project that you can find on the download page contains examples to illustrate the features.

Do not hesitate to have a look at this project to find samples and examples.

 

 

Articles

Suggest a new Article!