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

Screen Shot 2014-01-27 at 3.51.43 PM.png

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 🙂



To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Suseelan Hari

    Hi Krishna,

    Thanks for sharing information about SAP HANA. Also you have shared some web links related to SAP HANA. I will go through all the web link and update you. Keep it up. 🙂

    Regards,

    Hari Suseelan

    (0) 
  2. Rakesh Kandekar

    I have followed all the steps required for loading into HANA using hdbsql.

    I am able to laod the data too into HANA without any issue. But when the data in .csv file is invalid, all the load is failing and its not even creating error file on HANA. How would i know what went wrong?

    Find below the steps i performed

    1. created a table in our Hana server

    2. uploaded data file into Server ( .csv file)

    3.created a text file with import command and placed the file in the same path as the data file in the Server (.ctl file)

    4. Execute command “Import from ‘/folder1/folder2/file.ctl’ ” from the hana sql editor

    Heres how my control file look.

    IMPORT DATA

    INTO TABLE TESTIMPORTTABLE

    FROM ‘HanaImport.csv’

    RECORD DELIMITED BY ‘\n’

    FIELD DELIMITED BY ‘,’

    OPTIONALLY ENCLOSED BY ‘””‘

    ERROR LOG ‘ErrorLog.err’

    One more thing i need to know, is there a way to convert date format in control file before loading in HANA. (If its possible can any one be able to provide a same control file)

    As in my .csv file the date format i have is mm-dd-yyyy and it seems HANA only accepts YYYYMMDD.

    (0) 
    1. Krishna Tangudu Post author

      Hi Rakesh,

      You will need to give the required permissions on the folder (which you have selected for ERRORS) for the error file to be created

      And i don think ( not sure ) that it is possible to convert the date format using control file.

      Regards,

      Krishna Tangudu

      (0) 
      1. Rakesh Kandekar

        Thanks Krishna, that was really the access issue.

        Now my one more QQ is. While we export the data from HANA iam using .txt file which looks like below.

        \c -n 10.253.234.24 -i 00 -u HANAUSER -p HANAPASSWORD;

        \o C:\HDB\HanaTestExport.txt;

        select EMPID,FIRSTNAME FROM TABLE;

        \o

        In this way i need to store the HANA user and PASSWORD in a file, which i dont want.

        Is there a way i can pass the username and password at run time in command or .bat file so that no one else can see the hana user and password.

        Or any other way to export or import to and from HANA DB without saving the password in a file?

        (0) 

Leave a Reply