Skip to Content

Configure Smart Data Integration with the SAP HANA Service

test
0 %
Configure Smart Data Integration with the SAP HANA Service
Details
// Explore More Tutorials

Configure Smart Data Integration with the SAP HANA Service

Requires Customer/Partner License

05/23/2019

Configure the data provisioning agent and create a remote source.

You will learn

  • How to start and register the data provisioning agent with SAP HANA as a Service in a local computer
  • How to configure the file adapter to load text files
  • How to create a remote source in SAP HANA using the Database Explorer

This tutorial cannot currently be completed with a trial account.


Step 1: Connect to the database using the Database Cockpit

Navigate into the space in which you are developing in your subaccount.

Access cockpit

Select Service Instances and click the Dashboard icon for the service of type hana-db

Access cockpit

Use your email account to login:

Access cockpit

If prompted for authorization, click Authorize. Take note of the endpoint

Access cockpit

Click the SAP HANA Cockpit icon.

Access cockpit

Use the user SYSTEM and the password you set as the database password.

Access cockpit

Open the SQL console.

Access cockpit

The Database Explorer will automatically open a SQL console connected to your database.

Access cockpit
Log on to answer question
Step 2: Download and install the Data Provisioning Agent)

Download the Data Provisioning Agent from the SAP Software Center. Choose SPS3 Patch 52 or higher.

For more information about installing the Data Provisioning Agent for productive environments check the documentation in SAP Help

Choose a directory in your file system and use the following command to extract the file into it.

You can download SAPCAR to extract your file from the SAP Support Portal,

sapcar -xvf <<DOWNLOADED FILE>>
Download SDI

The previous command has created a new directory. Navigate into it and execute hdbsetup.

Alternatively, you can use hdbinst from a command line.

Go into step 2. Choose an installation path and make a note of it, you will need it later:

Download SDI

Review the options and click Install

Download SDI

Wait until installation finishes and you get confirmation that Installation finished successfully

Log on to answer question
Step 3: Register the agent

You will use the command-line tool to configure the data provisioning agent to connect to your SAP HANA instance using a secure JDBC connection.

Open a command line or terminal with administration rights.

For Windows, search for cmd and use the context menu to choose Run as Administrator.

Run as admin

Use the following commands to set the environment variables to the installation path you specified in step 2:

  • For Windows:

    set DPA_INSTANCE="C:\usr\sap\dpa"
    
  • For Linux:

    export DPA_INSTANCE=/usr/sap/dpa
    

Switch to that directory using command cd. For example:


cd %DPA_INSTANCE%
Download SDI

Navigate into the bin directory. Execute the configuration script agentcli with parameter --configAgent.

cd bin
agentcli --configAgent
Download SDI

Start with option 2 . Start or Stop Agent and start the agent with option 1 . Start Agent.

Download SDI

Use b . Back to go back to the main menu. Continue with option 6 . SAP HANA Connection.

Download SDI

Choose option 3 for JDBC.

Download SDI

Choose true for Use Web Socket to connect

Download SDI

###Note: This option is available in the Data Provisioning Agent version 2.3.5.2 or higher.

Get the database ID from the SAP HANA Service Dashboard

Configure SDI

And concatenate /service/ and database ID to form the Web Socket URL

Configure SDI

Get the endpoint for Web Socket also from the SAP HANA Service Dashboard

Configure SDI

And use it to complete the Host and Port

Configure SDI

Choose true for Use encrypted JDBC connection

Use SYSTEM (or an administration user in a non-trial environment) and its credentials.

Download SDI

Choose an option for the HTTP proxy.

Download SDI

Enter user JDCBUSR and a password. Choose true. This user will be created for you.

Download SDI

The configuration agent will connect and create the user. Press Enter when prompted.

Download SDI

Make sure the IP address from which you are connecting the agent is whitelisted.

Enter b to go back to the main menu. Choose option 7. Agent Registration and follow the instructions to register your local computer as an agent.

Download SDI

Finally, register an adapter using option 8.

Download SDI

Use FileAdapter when prompted. Use Quit to exit the configuration agent.

Log on to answer question
Step 4: Configure an access token

From a command prompt, use the following command to set an access token.

agentcli --setSecureProperty

Enter option 8.

Set access token

Set a token and make a note of it. This example uses HANAToken

Set access token

Press Enter and quit the configuration tool.

Log on to answer question
Step 5: Prepare a file for upload

Download this sample file into the default workspace.

The default workspace is located in <<ROOT DIRECTORY>>\workspace, for example, \usr\sap\dpa\workspace .

Download file

Create a text file called salarydata.cfg with the following content:

#Configuration file for data load
CODEPAGE=UTF-8
ERROR_ON_COLUMNCOUNT=false
ESCAPE_CHAR=\
EXPONENTIAL=E
FORCE_DIRECTORY_PATTERN=C:\usr\sap\workspace
FORCE_FILENAME_PATTERN=salarydata.csv
FORMAT=CSV
LENIENT=true
LOCALE=en_US
ROW_DELIMITER=\n
SKIP_HEADER_LINES=1
COLUMN=id;INTEGER;
COLUMN=salary;INTEGER;
COLUMN=start_year;INTEGER;
COLUMN=gender;NVARCHAR(256);
COLUMN=region;NVARCHAR(256);
COLUMN=T-Level;NVARCHAR(256);

Save the configuration file in the same directory.

Log on to answer question
Step 6: Create a Remote Source

Go back to the Database Explorer. You will see the adapter under Catalog -> Data Provisioning Agents

Download SDI

You should see the agent you registered.

Right-click on Remote Sources. Choose Add Remote Source

Configure client

Choose FileAdapter

Configure client

Call the source LocalFile. The Source location should automatically choose the agent you registered on step 4.

Configure client

Set the path to the workspace directory in which you downloaded the CSV file

Configure client

Scroll down to the credentials and choose Technical User as the credentials mode and enter the access token in the AccessToken field. If you used the example token before, enter HANAToken.

Click Create

Configure client

Check the remote objects to make sure configuration has been successful and to complete the validation below.

Configure client
Do you see `salarydata` in the list of remote objects?
×

Next Steps

Prerequisites

Back to top