Share this article:

Vertica Integration with SAP Data Services: 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 SAP Data Services: Latest Versions Tested

Software Version
Partner Product

SAP DS 4.2 SP14

Desktop Platform

Windows Server 2019

Vertica Client

Vertica ODBC 11.0

Vertica Server Vertica Database 11.0
Vertica Server Platform RHEL 8.3

SAP Data Services Overview

SAP Data Services is an ETL tool that uses workflows to extract data from one or more data sources. It then transforms the data before pushing this data into a target source. You can use Vertica as a source or target within the workflow. SAP Data Services is compatible with Windows and Linux operating systems. SAP Data Services uses the ODBC driver to connect to your Vertica database.

Installing SAP Data Services

To install SAP Data Services on your Windows system, follow the steps in the SAP documentation.

Installing the Vertica Client Driver

SAP DS uses the Vertica ODBC driver to connect to your Vertica database. To download and install the Vertica client package:

  1. Navigate to the Vertica Client Drivers page.
  2. Download the Vertica client package that is compatible with the Vertica server version.
  3. Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  4. Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC only.
  5. Follow the installation instructions in Installing the Client Drivers and Tools on Windows.
  6. Create an ODBC Data Source Name (DSN) as described in Creating an ODBC Data Source Name (DSN).

Connecting SAP Data Services to Vertica

You can connect use SAP Data Services to connect to Vertica using either the ODBC or the JDBC client driver.

To connect SAP Data Services to Vertica, you need to

  • Create an ODBC Data Source
  • Import Tables into the Datastore
  • Create a Base Job

Creating an ODBC Data Source

You must create an ODBC Data Source in SAP to connect Vertica to SAP Data Services. To do this,

  1. Open the SAP Data Source Designer and select option Project > New > Datastore.
    The Create New Datastore window appears.
  2. In the Create New Datastore window, enter the following details:
    • Datastore Name: Enter a name for the datasource.
    • Datastore Type: Select Database.
    • Database Type: Select ODBC.
    • Data Source Name: Select the DSN that you created.
    • User Name: Enter the user name of the Vertica database.
    • Password: Enter the password of the database.
  3. sap3.png 

  1. Click OK.
    You can view the new Datastore in the Local Object Library pane. If the Local Object Library pane is not visible, go to Tools > Object Library.

You can now browse the metadata and import tables from Vertica to SAP.

Importing Tables to Datastore

To use tables in a workflow, you first need to import the tables into the datastore. To import,

  1. Expand Datastore.

  2. Double-click Tables.

    This lists all tables that are available to import.

  3. Right-click the required tables and select click Import.

  4. Tables will be imported to the datastore and you can view these tables in Datastore.

Creating a Base Job

You need to create a base job to load data into target tables. To create a base job,

  1. Create a new project. From the menu bar, select Project > New > Project.

  2. Enter the project name and click Create.
    The project opens in the Project Area pane.
  3. Right-click the project and select New Batch Job.

  4. Double-click the batch job.
    The job opens in the designer.

  5. Click the Data Flow icon on the Palette toolbar.
  6. Drag and drop the source and target table from the datastore onto the designer.
  7. Right-click each table to assign it as a source table or target table. Click either Make Source or Make Target.

  1. Create a link from the source table to the target table as shown in the following image. Click the Execute icon to execute the job.

Known Limitations

Read Mode

  • For CHAR and VARCHAR data types, up to 1024 characters are supported.
  • LONG VARCHAR data type is not displayed.
  • DECIMAL data type supports Positive precision up to 41 digits, Negative precision up to 40, and scale up to 10 digits above which the value is truncated.
  • DOUBLE data type supports precision up to 15 digits and scale up to 10 digits above which the value is rounded off.
  • For TIME, TIMETZ, and TIMESTAMPTZ data types, milliseconds are not supported. For TIMETZ and TIMESTAMPTZ, timezone is not supported.
  • Binary, Varbinary, Longvarbinary data types are not supported.

Write Mode

  • For CHAR and VARCHAR data types, up to 32232 characters are supported.
  • For VARBINARY and LONG VARBINARY, up to 2048 characters are supported.
  • DECIMAL data type supports Positive precision up to 41 digits and Negative precision up to 40 digits.
  • For TIME, TIMETZ, AND TIMESTAMPTZ data types, milliseconds are not supported. For TIMETZ data type, timezone is not supported. For TIMESTAMPTZ, minimum date supported is 01-01-2001.

For More Information

Share this article: