SAP HANA: Replicating Data into SAP HANA using HDBSQL

[former_member182302]

Hi Folks,

This document is intended to focus on how we can replicate strategies to load data into SAP HANA (with out using SLT or BODS).

Problem Description:

1) To load data into SAP HANA ( Full or Delta)

2) On Load failure, entire load has to be rejected

3) Error message to be notified to the support team

We know that we can use either SLT or SAP BODS for loading data into SAP HANA. Depending on the type of requirement and other external factors we can choose either one of the two.

There are some discussions and polls as well to understand what is the best way to replicate data into SAP HANA. ( which encouraged me to write this document)

What is the Replication Technique used in your project to get the data into SAP HANA database? Where you can see people who voted liked SLT the most  ( Possibly due to the "Real-time" replication that it supports ) .

Small-scale replication into HANA

Interesting Blog links and videos on the similar topic:

Best Practices for SAP HANA Data Loads

HANA Recommends Project: Bulk loading data usin... | SAP HANA

HANA Recommends Project:  Bulk loading dat... | SAP HANA

HANA Recommends Project: Bulk load data using C... | SAP HANA

Scheduling a job in SAP HANA using  HDBSQL and windows task scheduler

Backup and Recovery: Scheduling Scripts - by the SAP HANA Academy

So we have different options to load and practices to follow to get the optimal performance of loading into SAP HANA now let us check on how we can use HDBSQL for loading.

Find the steps below:

1) Create a HDBUSERSTORE ( for secure logging)

2) Create a .ksh to sftp the flat file to SAP HANA Database server

3) Create a .ksh to use HDBUSERSTORE to login to SAP HANA

4) Call the Stored Procedure to load data into SAP HANA.

1) Steps to Create a HDBUSERSTORE:

• Login to the File System and to the user (which will call the procedure )

          SU - <UserName>          password: *****

• Set the user store Key:

           "/usr/sap/hdbclient/hdbuserstore" SET <USERSTORENAME> <HOSTNAME:PORT>  <USERNAME> <PASSWORD>

• Check connection:

          "/usr/sap/hdbclient/hdbsql" -U <USERSTORENAME>

2) Create a phyton or shell script to use HDBUSERSTORE to login to SAP HANA:

As mentioned in the videos above you can use shell script to frame the import statements and to call the generic procedure.
You will need to login to the database using HDBUSERSTORE and then frame your import & Call statements. PFB the sample code here:

   SQLQUERY=`echo "CALL KRISHNA.DATA_LOAD_USING_ARRAY"`

    /usr/sap/hdbclient/hdbsql -U HANAUSER -z <<EOF 1>${loading_call_out} 2>${loading_call_err}

  ${SQLQUERY}

       \q

   quit

EOF

Note: As am not an Unix guy, Just sharing the sample code which will login to SAP HANA db and fire the queries. You may want to involve an Unix expert and write the shell script according to your requirements.

3) Generic Procedure to load data into SAP HANA :

Please find the sample generic procedure which will help to load data into SAP HANA below:

SAP HANA: Generic Procedure using Arrays to Load delta data into Tables

This procedure will help you to load data from the Staging table to Target table in SAP HANA. You can add the error handling and other checks as mentioned in the document as per your customer requirements.

Hope you enjoyed reading this blog. Awaiting your valuable feedback on this.

Yours,

Krishna Tangudu :smile: