|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
SAP DS 4.2 SP14
Windows Server 2019
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:
- Navigate to the Vertica Client Drivers page.
- Download the Vertica client package that is compatible with the Vertica server version.
- Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC only.
- Follow the installation instructions in Installing the Client Drivers and Tools on Windows.
- Create an ODBC Data Source Name (DSN) as described in Creating an ODBC Data Source Name (DSN).
Note For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.
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,
- Open the SAP Data Source Designer and select option Project > New > Datastore.
The Create New Datastore window appears.
- 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.
- 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,
This lists all tables that are available to import.
Right-click the required tables and select click Import.
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,
Create a new project. From the menu bar, select Project > New > Project.
- Enter the project name and click Create.
The project opens in the Project Area pane.
Right-click the project and select New Batch Job.
Double-click the batch job.
The job opens in the designer.
- Click the Data Flow icon on the Palette toolbar.
- Drag and drop the source and target table from the datastore onto the designer.
Right-click each table to assign it as a source table or target table. Click either Make Source or Make Target.
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.
- 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.
- 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.