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 Patch 26-27 |
Desktop Platform |
Windows Server 2019 |
Vertica Client |
Vertica ODBC Driver 23.4.0 |
Vertica Server | Vertica Analytic Database 23.4.0 |
Vertica Server Platform | RHEL 8.7 |
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.
Additionally, review the compatibility matrix to select the appropriate version of SAP DS and SAP BO.
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 your 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
To connect SAP Data Services to Vertica, you will be
Creating a SAP Datastore
You must create a SAP datastore 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 HP Vertica.
Database Version: Select the latest version from the drop-down. Currently, SAP DS lists only Vertica versions 7.1.x and 11.x.
- 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,
-
Expand Datastore.
-
Double-click Tables.
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 and drop the dataflow in the designer pane.
- 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. Right-click batch job and select Execute to execute the job.
Known Limitations
Read Mode
- For CHAR and VARCHAR data types, up to 1024 characters are displayed.
- LONG VARCHAR data type is not displayed.
- DECIMAL data type reads 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 reads precision up to 15 digits and scale up to 6 digits above which the value is rounded off.
- For TIME, TIMETZ, and TIMESTAMPTZ data types, milliseconds are not displayed. For TIMETZ and TIMESTAMPTZ, timezone offset is not displayed.
- BINARY, VARBINARY, and LONG VARBINARY data types are not displayed.
-
UUID datatype is not displayed.
Write Mode
- For CHAR and VARCHAR data types, up to 32232 characters are loaded.
- For VARBINARY and LONG VARBINARY data types, up to 2048 characters are loaded.
- DECIMAL data type writes positive precision up to 41 digits and negative precision up to 40 digits.
- For TIME, TIMETZ, AND TIMESTAMPTZ data types, milliseconds are not loaded. For TIMETZ data type, timezone offset is not loaded. For TIMESTAMPTZ, minimum date displayed is 01-01-0100.
-
UUID data type is not loaded.
To load the UUID data type, you need to use the generic connector. To do this, select Project > New > Datastore. In the Create New Datastore window, provide the details and in the Database Type, select ODBC.