Share this article:

Vertica Integration with SyncSort DMExpress: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and SyncSort DMExpress: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
DMExpress

8.2.0

Desktop Platform

Windows Server 2012 R2

Vertica Client

Vertica ODBC 9.1.1

Vertica Server Vertica 9.1.1

Syncsort DMExpress Overview

Syncsort DMExpress (DMX) is a data integration application tool that can extract large amount of data from any data source and load it into Vertica. DMX also allows you to transform that data while loading the data into a target data warehouse.

Download and Install Syncsort DMExpress

Download and install DMExpress as follows:

  1. On the SyncSort MySupport portal, click Download product software and documents.
  2. Log in or create an account.
  3. Check the requirements.
  4. Follow the installation instructions.

For more information, see:

Install the Client Driver

Before you can connect to Vertica using DMExpress, you must install the Vertica client driver. Follow these steps:

  1. Navigate to the Client Drivers page on the Vertica website.
  2. Download the client driver that is compatible with the architecture of your operating system and Vertica server version.

    Note  

    For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Follow the installation instructions in the Vertica documentation.

  4. Create a DSN as described in Creating an ODBC Data Source Name (DSN).

DMExpress Components

When creating a connection to Vertica, you need to become familiar with the folllowing DMExpress components:

Access these components from the Start > DMExpress menu.

DMExpress Job Editor

Use the Job Editor to configure your data flow. In the Job Editor, you can add jobs and tasks to the canvas and create sequences between them. This helps you develop, manage, and oversee the data flow from your sources to your targets.

DMExpress Task Editor

Use the Task Editor to define your transformations. DMExpress allows you to perform the following set-level transformations on your data:

  • Join
  • Copy
  • Merge
  • Sort
  • Aggregation

You can also specify how to perform the following transformations on a field-level basis:

  • Date/time functions
  • String manipulations
  • Lookups
  • Mathematical operations

DMExpress Server

The DMExpress Server provides information about the server and jobs that are scheduled, executing, or complete.

Select the Status button to retrieve information about your connection.

status_button.png

In the Status window, you can:

  • View information about scheduled, executing, and completed jobs.
  • Monitor job execution status.
  • Modify DMExpress environment variables.

Connecting to Vertica Using DMExpress

Follow these steps to connect to Vertica using Syncsort DMExpress.

  1. Select Start > DMExpress > DMExpress Task Editor.

    The Task Editor appears:

    task_editor_new.png

  2. Click New to create a new task. In this example, the new task defines the Vertica data source, the Vertica target, and transformation. Continue to Define the Vertica Data Source to continue creating the task.

Define the Vertica Data Source

  1. Right-click Source under DMExpress Task and select Add Database Table to define the source Vertica database.

    task_editor_add_db_tables.png

  2. In the Source Database Table dialog box, click Add new in the upper-right corner.

     task_editor_source_db_tables.png

  3. In the Database Connection dialog box, enter the Vertica source database connection information. Select Vertica from the DBMS drop-down list.



  4. Click Verify connection.
  5. Upon a successful connection, click OK to exit the Database Connection dialog box.
  6. The Source Database Table dialog box appears and contains the information about the Vertica source database. For this example, the connection is called DatabaseConnection1.

  7. Select the source Vertica database table and columns that you want to transform and define the desired transformation.

    source_db_table.png

  8. Click OK.

    DMExpress creates and saves the transformation and closes the Source Database Table dialog box. Continue to Define the Target Database to continue creating the task.For this example, the target database is also a Vertica database.

Define the Target Database

  1. Right-click Target under DMExpress Task and select Add Database Table.

    target_table.png

  2. In the Target Database Table dialog box, click Add New.
  3. In the Database Connection dialog box, enter the target Vertica database information. Select Vertica from the DBMS drop-down list.
  4. Click Verify connection.
  5. Upon a successful connection, click OK to exit the Database Connection dialog box.
  6. The Target Database Table dialog box appears and contains the information about the Vertica target database. For this example, the connection is called DatabaseConnection2.

  7. Select the target Vertica database table and specify the desired column mappings between the source and the target.

    target_db_table.png

  8. Click OK.

    DMExpress saves the column mapping and closes the Target Database Table dialog box.

Run the Job

Now you want to extract the data from the source, transform the data, and save it in the target.

Click the Run button to execute the job using the designated source and target Vertica databases.

run.png

When you run the job, DMExpress connects to the Vertica source, extracts the data, transforms it, and then connects to the Vertica target where it saves the data.

Verify the Job Status

To check on the status of your job, click the Status button. On the Jobs tab, look under the Job Status field for the status of your job.

Possible values for the job status are:

  • Starting
  • Running
  • Completed successfully
  • Completed with exceptions
  • Ended in error
  • Lost contact
  • Unknown

Known Limitations and Workaround

Known limitations for data types using the ODBC driver with Syncsort DMExress are:

  • DMExpress does not support INTERVAL SECOND and INTERVAL MONTH.
  • For the following data types, the ODBC driver truncates the values or does not load those columns: 
    • BINARY
    • CHAR
    • VARCHAR
    • LONG VARCHAR

    To fix this issue, enable the Report Unicode columns as char setting in Windows:

    1. Select Start > Control Panel > Administrative Tools > Data Sources (ODBC).

    2. Click System DSN.

    3. Select your DSN and click Configure.

    4. Click the Client Settings tab.

    5. Select Report Unicode columns as char.

For More Information

Share this article: