Friday 27 October 2017

Simple sql table export in ABAP for HANA

Idea was to play with HANA and to try it’s functionality for educational purposes.

For data extraction I wrote a simple ABAP Report, which extracts selected tables with its data and prepares sql script for import.

Here is a source code (s. attached file).

Here is a screenshot of selection screen.

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live



You can define a schema name and select if a new schema must be created or existing one used.

Of course, you define table names for extraction (only structure or with data).

Additionally you can select if the files should be sent per mail or directly downloaded to selected folder on your local hard drive.

By extraction the data are splitted by 65535 entries into separate files. SQL Scripts are zipped before sending/downloading.

On the next screenshot you can see result of extraction. I selected some tables, which represent a purchasing documents in SRM Solution.

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live

after extraction…

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live

For the mass import of the sql scripts I created a simple cmd-script on windows.

Here is an example only for BBP_PDBEI and BBP_PDIGP tables.

C:
cd "C:\Program Files\sap\hdbclient\"
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_1_BBP_PDBEI.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_2_BBP_PDBEI.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_3_BBP_PDBEI.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_4_BBP_PDBEI.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_5_BBP_PDBEI.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_6_BBP_PDIGP.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_7_BBP_PDIGP.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_8_BBP_PDIGP.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_9_BBP_PDIGP.sql
hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_10_BBP_PDIGP.sql

Here I provided path to Hana DB Client intalled and to the files, which were saved in V:\temp folder.

By the import I mentioned, that only one processor core used by the sequential processing.

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live

So, I changed the script for parallel processing

C:
cd "C:\Program Files\sap\hdbclient\"
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_1_BBP_PDBEI.sql
TIMEOUT /T 2
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_2_BBP_PDBEI.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_3_BBP_PDBEI.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_4_BBP_PDBEI.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_5_BBP_PDBEI.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_6_BBP_PDIGP.sql
TIMEOUT /T 2
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_7_BBP_PDIGP.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_8_BBP_PDIGP.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_9_BBP_PDIGP.sql
START "" hdbsql.exe -i 11 -n hanadb -u SYSTEM -p ********** -I V:\temp\hana_script_10_BBP_PDIGP.sql

I gave a paar seconds timeout after each table creation. Tables must be created first, only then start parallel import of content.

Now it looks much better. All CPU cores are used, import runs more efficient…

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live

And here is a result

SAP HANA Certifications, SAP HANA Tutorials and Materials, SAP HANA Guides, SAP HANA Live

Looks great…

Problems I encountered:
  1. Import is relative slow. I supposed SQL Import will run much faster.
  2. Memory consumption of ABAP Report is very high – may be some advices from you, how to optimize it.
  3. HANA does not understand field names with “/” sign. SRM uses some field names (and even tables) as /SAPSRM/*
  4. ABAP writes negative values as ‘1-‘, HANA needs ‘-1’.
  5. How to export cluster tables? Actually I did not need it. But it would be interesting to know…

No comments:

Post a Comment