Using loops in stored procedures for HANA ETL processes

[justin_molenaur2]

Purpose: Demonstrate how to use a looping technique to execute stored procedures in smaller chunks. This assumes no external ETL tools are available and stored procedures must be used.

Motivation: Avoid hitting Out of Memory (OOM) errors during processing or make the ETL type operations faster in some cases. I have seen a few SCN posts where people are hitting these OOM errors, and of course have hit them myself so I have figured out how to mitigate this risk. Additionally, I have observed that processing many small data sets vs. one very large one can achieve faster execution times.

Real World Scenario: Client needed to build a denormalized dimension that ended up being quite large - say 30 million records. to build this dimension, 5 seperate fairly large tables needed to be joined together from a central "driver" table. These were mostly many to many joins. Attempting to perform the logic in a stored procedure to build the dimension in one shot was consistently failing due to Out of Memory (OOM) errors.

Real World Solution: Process the required data in smaller chunks by calling the same procedure multiple times and looping through at some logical partitioning criteria. Essentially, performing the same logic for every <region, plant, or company code> in your data or driving table. In the example shown below, I have chosen the field LOOPING_DRIVER_TABLE.DATE, but of course this can be whatever makes sense for your requirement.

Solution details: I can't take full credit for this as jchesch helped me with some basics, so thanks to him for being generous with his knowledge, this is why SCN community is awesome!

To illustrate a generic approach, the following components are required. Again, this is just an example, your mileage may vary according to your requirements. This assumes a full drop and reload of the target table for simplicity - you might find a delta loading approach may make more sense.

1. Table with list of values, or the actual transaction/driver table

2. Table that contains the data to be joined (lookup)

4. Target table with transaction/driver data plus the lookup attribute

5. Procedure for performing join/lookup and insert with attribute for looping

6. Wrapper procedure for looping

7. Read/Write procedures must be enabled (check configuration/indexserver/repository/sqlscript_mode = 'unsecure' to enable read/write procedures)

8. User SYS_REPO must have CREATE/DELETE/INSERT/SELECT object privileges to <YOURSCHEMA> grantable

9. Replace <YOURSCHEMA> and <YOURPACKAGE> placeholders with your own schema and package names

1. Table with list of values, or the actual transaction/driver table

DROP TABLE "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE";

create column table "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE"

    ( "DATE" NVARCHAR (8) not null,

     "CUSTOMER" NVARCHAR (10) DEFAULT '' not null,

     "MATERIAL" NVARCHAR (18) DEFAULT '' not null,

     "SALES_QTY" DECIMAL (15,2) DEFAULT 0 not null,

     "SALES_VALUE" DECIMAL (15,2) DEFAULT 0 not null);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140101', '0000112345', '12345678', 10, 100);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140102', '0000112346', '12345678', 20, 190);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140103', '0000112347', '12345678', 11, 180);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140104', '0000112348', '12345678', 15, 175);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140105', '0000112349', '12345678', 1, 100);

INSERT INTO "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" VALUES

('20140106', '0000112351', '12345678', 4, 89);

2. Table that contains the data to be joined (lookup)

DROP TABLE "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE";

create column table "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE"

    ("CUSTOMER" NVARCHAR (10) DEFAULT '' not null,

     "CUSTOMER_SPECIALTY" NVARCHAR (2) DEFAULT '' not null);

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112345', 'LB');

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112346', 'AB');

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112347', 'HS');

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112348', 'DM');

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112349', 'AX');

INSERT INTO "<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" VALUES

('0000112351', 'ZT');

4. Target table with transaction/driver data plus the lookup attribute

DROP TABLE "<YOURSCHEMA>"."LOOPING_TARGET_TABLE";

create column table "<YOURSCHEMA>"."LOOPING_TARGET_TABLE"

    ( "DATE" NVARCHAR (8) not null,

     "CUSTOMER" NVARCHAR (10) DEFAULT '' not null,

     "MATERIAL" NVARCHAR (18) DEFAULT '' not null,

     "SALES_QTY" DECIMAL (15,2) DEFAULT 0 not null,

     "SALES_VALUE" DECIMAL (15,2) DEFAULT 0 not null,

     "CUSTOMER_SPECIALTY" NVARCHAR (2) DEFAULT '' not null);

5. Procedure for performing join/lookup and insert

--<YOUR_PACAKGE>.SP_WRITE_TARGET_TABLE

/********* Begin Procedure Script ************/

BEGIN

INSERT INTO "<YOURSCHEMA>"."LOOPING_TARGET_TABLE"

SELECT A."DATE", A."CUSTOMER", A."MATERIAL", A."SALES_QTY", A."SALES_VALUE", B."CUSTOMER_SPECIALTY"

FROM "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE" A

LEFT OUTER JOIN

"<YOURSCHEMA>"."LOOPING_LOOKUP_TABLE" B

ON (A."CUSTOMER" = B."CUSTOMER")

WHERE A."DATE" = :LOOP_DATE;

END;

/********* End Procedure Script ************/

--Test Procedure in isolation

CALL "_SYS_BIC"."<YOUR_PACAKGE>/SP_WRITE_TARGET_TABLE" ('20140101');

SELECT * FROM "<YOURSCHEMA>"."LOOPING_TARGET_TABLE";

DATE;CUSTOMER;MATERIAL;SALES_QTY;SALES_VALUE;CUSTOMER_SPECIALTY

20140101;0000112345;12345678;10;100;LB

6. Wrapper procedure for looping

--<YOUR_PACAKGE>.SP_LOOP_TEST

/********* Begin Procedure Script ************/

-- scalar variables

i INTEGER;

lv_row_count INTEGER;

lv_current_date NVARCHAR(10);

BEGIN

DELETE FROM "<YOURSCHEMA>"."LOOPING_TARGET_TABLE";

-- Find logical partition looping values

date_list = SELECT DISTINCT "DATE" FROM "<YOURSCHEMA>"."LOOPING_DRIVER_TABLE";

-- store dataset size in row_count variable

SELECT COUNT(*)

INTO lv_row_count

FROM :date_list;

FOR i IN 0 .. :lv_row_count-1 DO

  

    SELECT "DATE"

    INTO lv_current_date

    FROM :date_list

    LIMIT 1 OFFSET :i; -- notice OFFSET indexes at 0, not 1

    CALL "_SYS_BIC"."<YOUR_PACAKGE>/SP_WRITE_TARGET_TABLE" (lv_current_date);

END FOR;

--Manually initiate delta merge process

MERGE DELTA OF "<YOURSCHEMA>"."LOOPING_TARGET_TABLE";

END;

/********* End Procedure Script ************/

--Test full procedure and check results

CALL "_SYS_BIC"."<YOUR_PACAKGE>/SP_LOOP_TEST";

SELECT * FROM "<YOURSCHEMA>"."LOOPING_TARGET_TABLE";

DATE;CUSTOMER;MATERIAL;SALES_QTY;SALES_VALUE;CUSTOMER_SPECIALTY

20140101;0000112345;12345678;10;100;LB

20140102;0000112346;12345678;20;190;AB

20140103;0000112347;12345678;11;180;HS

20140104;0000112348;12345678;15;175;DM

20140105;0000112349;12345678;1;100;AX

20140106;0000112351;12345678;4;89;ZT

Happy HANA!

Justin