Getting started with the Vertica database

This page contains information to help you get started with Vertica in Semarchy xDI.

Connect to your data

The database structure can be entirely reversed in Metadata and then used in Mappings and Processes to design and adapt the business rules to meet the user’s requirements.

You can refer to the Connect to your data page which explains the procedure.

Configure Vertica component to use Vertica loaders.

Overview

For performance purposes, some of the Vertica Templates are optimized to use the Vertica COPY statement to load data into the database.

For this, Semarchy xDI will extract source data to temporary files and then load them through the COPY statement.

To use COPY statement, Vertica needs to have access to the temporary files to load. Vertica Templates supports multiple methods to make these files accessible:

  • Load the temporary files implicitely 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, 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 optional, and should be preferred for performance purposes when loading large files.

Load the temporary files implicitely through the JDBC Driver

This method 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)

This method consists of loading the file implicitely through the JDBC Driver.

This can be done through the "Local Mode" parameter available on Vertica Load Templates.

Example:

getting started vertica load local mode

Send the temporary files through FTP

This method 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)

This method consists of sending automatically the temporary files through FTP on Vertica machine.

If you want to use this method, the first step is to prepare the FTP connection by creating an FTP Metadata.

  1. Create an FTP Metadata, fill in the target Vertica machine connection properties, and finally define inside a folder in which the temporary files will be transferred.

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

Example:

getting started vertica load ftp mode

Send the temporary files through SSH

This method is supported by the following Vertica Load Templates

  • LOAD File to Vertica (with COPY)

This method consists of sending automatically the temporary files through FTP on Vertica machine.

If you want to use this method, the first step is to prepare the SSH connection by creating an SSH Metadata.

  1. Create an SSH Metadata, fill in the target Vertica machine connection properties, and finally define inside a folder in which the temporary files will be transferred.

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

Example:

getting started vertica load ssh mode

Create your first mappings

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

The Vertica technology can be used like any other database in Semarchy xDI.

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

getting started vertica mapping example 1

Loading data from a Delimited File into Vertica

getting started vertica mapping example 2

Loading data from an XML File into Vertica

getting started vertica mapping example 3

Loading data from Vertica into an HSQL Database

getting started vertica mapping example 4

Performing Reject detection while loading a Vertica Table

getting started vertica mapping example 5

Replicating an HSQL Database into a Vertica Database

getting started vertica replicator example 1

Sample project

The Vertica component is distributed with sample projects that contain various examples and files. Use these projects to better understand how the component works, and to get a head start on implementing it in your projects.

Refer to Install components in Semarchy xDI Designer to learn about importing sample projects.