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

[former_member182302]

Hello Folks,

Problem Description:

To load data into SAP HANA using a generic Stored Procedure and handling delta.I can see multiple threads to load the data into tables in HANA and to avoid usage of cursors. Hence writing my inputs on the solution of this problem using "Arrays".

Note: This blog is intended to address the above mentioned problem. I will be updating this document with additional work arounds on "real-time" issues in coming up weeks.

Scenarios:

Delta Type = FULL:

You might want to do a "Full" update . Means you want to delete the data from the target table and reload the entire data from the source.

Delta Type = DELTA:

You might want to load "Delta" depending on the primary keys of the table i.e Update if the record exists else insert the new record.

Also have a look on this blog which gives a overview on how this procedure can be called from HDBSQL

SAP HANA: Replicating Data into SAP HANA using HDBSQL

Here is the code:

CREATE PROCEDURE DATA_LOAD_USING_ARRAY(IN IN_SOURCE_SCHEMA VARCHAR(10) ,

     IN IN_SOURCE_TABLE VARCHAR(100) ,

     IN IN_TARGET_SCHEMA VARCHAR(10) ,

     IN IN_TARGET_TABLE VARCHAR(100),

     IN IN_DELTA_TYPE VARCHAR(10))

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS   i INT;

BEGIN

   DECLARE STRINGARRAY  VARCHAR(20) ARRAY;

   DECLARE TEXT VARCHAR(100000);

   DECLARE rowcount integer;

   DECLARE primary_keys VARCHAR(5000) := '' ;

   DECLARE column_list VARCHAR(30000) := '' ;

   DECLARE DELETE_QUERY VARCHAR(30000) := '' ;

   DECLARE UPSERT_QUERY VARCHAR(30000) := '' ;

   DECLARE INSERT_QUERY VARCHAR(30000) := '' ;

    /* GETTING PRIMARY KEY COLUMNS OF THE TARGET TABLE*/

   pk_tab = SELECT column_name FROM CONSTRAINTS

WHERE table_name = UPPER(:IN_TARGET_TABLE)

AND schema_name = UPPER(:IN_TARGET_SCHEMA) AND is_primary_key = 'TRUE' ;

   STRINGARRAY := ARRAY_AGG(:pk_tab.column_name);

   SELECT count(*) into rowcount from :pk_tab; -- To know the count of columns

   TEXT := :STRINGARRAY[1];

   -- To form the primary key list

   FOR i IN 1 ..:rowcount-1

   DO

   TEXT := :TEXT || ',' || :STRINGARRAY[:i+1];

   END FOR;

   primary_keys := :TEXT;

  -- To handle the column names with "Spaces" in between

   SELECT '"' ||  REPLACE (:primary_keys,',','","') || '"' INTO primary_keys FROM DUMMY;

   /* GETTING COLUMNS OF THE TARGET TABLE*/

    col_tab = SELECT column_name FROM TABLE_COLUMNS

  WHERE table_name = UPPER(:IN_TARGET_TABLE)

  AND schema_name = UPPER(:IN_TARGET_SCHEMA) ;

   STRINGARRAY := ARRAY_AGG(:col_tab.column_name);

   SELECT count(*) into rowcount from :col_tab;

   TEXT := :STRINGARRAY[1];

    -- To form the column-list

   FOR i IN 1 ..:rowcount-1

   DO

   TEXT := :TEXT || ',' || :STRINGARRAY[:i+1];

   END FOR;

   column_list := :TEXT;

 -- To handle the column names with "Spaces" in between

   SELECT '"' ||  REPLACE (:column_list,',','","') || '"' INTO column_list FROM DUMMY;

   IF IN_DELTA_TYPE = 'FULL'

   THEN

   --To delete the data from the target table

   DELETE_QUERY :=

  'DELETE FROM "'||IN_TARGET_SCHEMA||'"."'||IN_TARGET_TABLE||'"' ;

   -- To insert the new load into target table

   INSERT_QUERY :=

  'INSERT INTO "'||IN_TARGET_SCHEMA||'"."'||IN_TARGET_TABLE||'" ('||:column_list||')

   SELECT '||:column_list||' FROM "'||IN_SOURCE_SCHEMA||'"."'||IN_SOURCE_TABLE||'"';

   EXEC (:DELETE_QUERY);

   EXEC (:INSERT_QUERY);

   ELSEIF IN_DELTA_TYPE = 'DELTA'

   THEN

   UPSERT_QUERY :=

  'UPSERT '||IN_TARGET_SCHEMA||'.'||IN_TARGET_TABLE||' SELECT '||:column_list||'

  FROM '||IN_SOURCE_SCHEMA||'.'||IN_SOURCE_TABLE||'';

   EXEC (:UPSERT_QUERY);

   END IF;

END;

Sample Call Statement:

CALL DATA_LOAD_USING_ARRAY ('KRISHNA','EMPLOYEE','SYSTEM','EMPLOYEE','FULL');

Error handling and maintaining data consistency:

Please find the details in my other blog on how can you achieve consistency along with error handling:

SAP HANA: Workaround for using Commit & Rollback in exception handling block of a Stored Procedure

For Feedback on whether the procedure is success or failure and on how to control the "Commits" you can the relevant information in the above mentioned blog.

Hence i didn't add those details into this document to make it more specific to the title. ( if required based on the comments will add that too )

As mentioned at the start i will be updating the above code whenever possible.

In the mean time i would love to have your comments and if there is anyother better way of doing it.

Yours,

Krishna Tangudu