Share this article:

Vertica Integration with SAP Data Services: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and SAP Data Services as of July, 2016.

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 that data before pushing that 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.

This document is based on the results of testing Vertica 7.2.x with SAP Data Services 4.2 SP7. SAP Data Services 4.2 SP7 includes the Vertica Specific Connector, called HP Vertica 7.1.x. The connector provides better integration with Vertica than the default Generic ODBC option that was previously available. This connector supports Vertica 7.1.x and later.  

Install SAP Data Services

Install SAP Data Services on your Windows or Linux system by following the steps from the SAP documentation.

Download and Install the Vertica Client Drivers

Before you can connect to Vertica using SAP Data Services, you must install the Vertica client package. This package includes the ODBC and the JDBC client drivers that SAP Data Services uses to connect to Vertica.

Download the Vertica Client Drivers

  1. Go to the Vertica Client Drivers.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client package you downloaded, follow the steps for installation from the Vertica documentation.

Create an ODBC Data Source Name (DSN) on Windows

You must create a DSN to use the ODBC driver to connect to Vertica from SAP Data Services. You can set up either a 64-bit DSN or a 32-bit DSN, depending on your operating system and SAP Data Services version.

Follow these steps to create a DSN on Windows:

  1. Navigate to the appropriate folder, depending on which DSN you need to install:
    • 64-bit DSN: Start > Control Panel > Administrative Tools > Data Sources (ODBC)
    • 32-bit DSN: Navigate to C:\Windows\SysWOW64 and locate the program file called odbc32.exe.

sap1.png
 

  1. The ODBC Data Source Administrator displays. In the System DSN tab, click Add.
  2. In the Create a New Data Source dialog box, select Vertica driver and click Finish. The Vertica ODBC DSN configuration dialog box appears.
  3. In the Basic Settings tab, fill in the following required fields:
    • DSN Name: The name for the DSN.
    • Server: The host name or the IP address of the Vertica server to which you want to connect.
    • Database: The name of the Vertica database.
    • User Name: The name of the user account to use when connecting to the database.

sap2.png

  1. Click OK. The Vertica ODBC DSN configuration window closes and your new DSN is listed in the ODBC Data Source Administrator window.
  2. Click OK to close the ODBC Data Source Administrator window.

For more information, see Setting Up a DSN in the Vertica documentation.

Create an ODBC Data Source Name (DSN) on Linux

  1. Install the ODBC driver manager for Linux.
  2. Enter the Data Source Name into the odbc.ini file located at <Installation_Dir>/dataservices/DataDirect/odbc as shown in the following example:
    $ [Vertica_DSN]
    Description = Vertica DatabaseDriver = <Base_Path_DriverManager>/vertica/lib64/libverticaodbc.so
    Database = <DBNAME>
    Servername = host01
    UID = dbadmin
    PWD = dbadmin
    Port = 5433
     en_GB
  1. Enter the ODBC Data Source Name into the ds odbc.ini file located at <Installation_Dir>/dataservices/bin as shown in the following example:
    [Vertica_DSN]
    Description = Vertica Database
    Driver = <Base_Path_DriverManager>/vertica/lib64/libverticaodbc.so
    Database = <DBNAME>
    Servername = host01
    UID = dbadmin
    PWD = dbadmin
    Port = 5433
    en_GB
  1. Add the vertica.ini file to /<Base_Path_DriverManager>/vertica as shown in the following example:
    [Driver]
    ODBCInstLib=<SAP_Installation_DIR>/dataservices/DataDirect/odbc/lib/libodcinst.so
    ErrorMessagePath=<Base_Path_DriverManager>/vertica/lib64
    LogPath=/tmp/temp
    LogLevel=6
    DriverManagerEncoding=UTF-8
  1. Set the LD_LIBRARY_PATH, ODBCINI, VERTICAINI variables into the al env.sh file located at <SAP_Installation_DIR>/dataservices/bin as shown in the following example:
    export 
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<SAP_Installation_DIR>/dataservices/DataDirect/odbc/lib: 
    <Base_Path_DriverManager>/vertica/lib64:<SAP_Installation_DIR>/dataservices/bin:$LD_LIBRARY_PATH
    export 
    ODBCINI=<SAP_Installation_DIR>/dataservices/DataDirect/odbc/odbc.ini
    export 
    NI=<Base_Path_DriverManager>/vertica/Vertica.ini
  1. Start SAP Data Services using the following commands:
    $ . ./al_env.sh
    $ ./svrcfg

Connect Vertica to SAP Data Services

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

Create an ODBC Data Source

You must create an ODBC Data Source in SAP to connect Vertica to SAP Data Services by following these steps:

  1. Open the SAP Data Source Designer.
  2. In the Create Datastore dialog box, select the Connection
  3. Enter the connection details for your Vertica database as shown in the following graphic.

sap3.png 

  1. Click OK.

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

Create a Base Job Using ODBC

To create a base job to load data into target tables, follow these steps:

  1. Select New > Batch Job > Add New Workflow > Add New Dataflow.
  2. Select tables from data stores to act as the source table and target table.
  3. Right-click each table to assign it as a source table or target table. Click either Make Source or Make Target.

sap4.png

  1. SAP Data Services creates a link from the source table to the target table as shown in the following graphic. Click the Execute icon to execute the job.

sap5.png 

Create a JDBC Data Source

You can create a JDBC Data Source based on your operating system:

Using the JDBC Adapter on Windows

  1. Open the Data Services Server Manager.
  2. Edit the Job Server Properties dialog box by checking the following options as shown in the following graphic:
    • Support adapter, message broker communication
    • Use SSL protocol for adapter, message broker communication

sap6.png

Using the JDBC Adapter on Linux

  1. Execute the following commands on your Linux environment from <SAP_Installation_DIR>/dataservices/bin/.
    $ . ./al_env.sh
    $ ./svrcfg
  1. The Job Server details appear as shown in the following screenshot. Configure the Adapter setting by selecting the Configure Job Server :

 sap7.png

  1. Press Enter.
  2. Select Edit a Job Entry.
  3. Press Enter.
  4. Enter your information for the Adapter.
  5. Press Enter.

SAP enables the Adapter for the Job Server.      

Create a Base Job Using ODBC

  1. Browse to the adapter instances and choose your job server.
  2. Select Adapter Configuration.
  3. From the list of Installed Adapters, select JDBCAdapter.
  4. Set the Vertica JAR file to CLASSPATH, separated by a semicolon, as shown in the following graphic.

 sap8.png

  1. Enter the following required fields:
    • JDBC driver class name
    • JDBC driver URL: Include the server name, port, and database name
    • User
    • Password

The adapter is now ready for use with SAP Data Services.

Assign an Adapter

You must choose an adapter to use to import data from Vertica into SAP Data Services.

  1. Open Data Services Designer.
  2. Click Create a New Datastore.
  3. From the drop-down menu, set the Datastore Type field to Adapter.

sap9.png

4. From the drop-down menu, set the Adapter Instance Name.

5. Click OK.

Now, you can browse the Vertica metadata and import tables from Vertica into SAP Data Services. You can also create jobs using the imported tables.

For More Information

For More Information About… See…

SAP Data Services

http://help.sap.com/bods

Vertica Community Edition

https://my.vertica.com/community/

Vertica 7.2

http://my.vertica.com/docs/7.2.x/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: