Replicating data into HANA using ABAP – ADBC (Native SQL)
This is the second part of a 3-part blog – covering my experience of connecting a SAP ABAP 751 Developer Edition server to HANA Express and then, replicating data over to HANA through ABAP and finally, running a quick performance test between the two system using ABAP CDS vs HANA CDS.
After connecting the SAP AS ABAP 751 Developer Edition to a HANA Express DB as secondary DB, I need a way to replicate data over to HANA so that I’ll have some data to “play” with.
Looking around in SCN for some ideas, I found a blog post from Graham Robinson that perfectly describes what I wanted to do and although the actual program is no longer available on the blog post, Graham describes the steps perfectly and I was able to re-create a version of the program that works for my scenario.
Another reference that I used to write the program below is the mapping of HANA data type from ABAP DDIC by Tobias Koebler – https://blogs.sap.com/2013/05/14/how-slt-is-mapping-data-types/. To make it work in my program, I copied the first table in the blog and loaded the matching data type into table TVARVC manually.
As a pre-requisite step to executing the program, I manually created the schema ‘NPLDATA’ which I will use to store all the replicated tables from ABAP.
To make things simple, when executing the program, all I have to do is enter the table that I want to replicate over to HANA and the program will then first – DROP the table (if it exists), CREATE the table with all the matching field names, SELECT the data in ABAP and then INSERT the records into HANA.
** I also included a “Test Mode” that will bring over only 10 records so that I can make sure that it works for the table before triggering the full blown replication.
Here’s a screenshot of my attempt to replicate over the SBOOK table which I had over 1.3M records. This took slightly over 2hrs to complete. I ran this replication in background mode.
Finally, after about 2 and 1/2hrs, I had replicated most of the Flight Model tables over to my HANA Express DB and now I am ready to take a spin on the HANA DB! SBOOK took the longest since it had the most number of records.
Here’s the program in its entirety below.
It’s definitely far from perfect but I was able to use it to load most of the tables that I wanted over.
*&---------------------------------------------------------------------*
*& Program ZREPLICATE_NPL_TO_HXE
*&---------------------------------------------------------------------*
*& This program is used to replicate table records from ABAP (NPL) over
*& to a HANA database (HXE)
*& The versions used to code this program and test were:
*& - SAP NW AS ABAP 751 SP02 Developer Edition (NPL)
*& - SAP HANA Express 2.0 (HXE)
*&---------------------------------------------------------------------*
PROGRAM zreplicate_npl_to_hxe.
*&---------------------------------------------------------------------*
* CLASS DEFINITION
*&---------------------------------------------------------------------*
CLASS lcl_local DEFINITION.
PUBLIC SECTION.
METHODS:
constructor IMPORTING im_dbcon TYPE dbcon-con_name
im_table TYPE tabname16
im_schema type char10
im_batch TYPE i
im_test TYPE c,
get_ddic_map,
drop_table,
create_table,
select_and_load_data.
PRIVATE SECTION.
DATA:
gv_table TYPE tabname16,
gv_batch TYPE i,
gv_test_mode TYPE c,
gv_schema TYPE char10,
gr_struct_descr TYPE REF TO cl_abap_structdescr,
gt_table_fields TYPE ddfields,
gv_message TYPE string,
gv_sql_stmt TYPE string,
gv_sql TYPE string,
gv_pkey TYPE string,
gv_value TYPE string,
gv_values TYPE string,
gv_num_recs TYPE i,
gv_processed TYPE i,
gv_mod TYPE i,
gv_tabix TYPE sy-tabix,
go_sql_statement TYPE REF TO cl_sql_statement,
go_exception TYPE REF TO cx_sql_exception,
gw_fcat TYPE lvc_s_fcat,
gt_fcat TYPE lvc_t_fcat,
gd_table TYPE REF TO data,
gd_line TYPE REF TO data,
gt_stringtab TYPE stringtab,
gt_ddic_to_hana TYPE STANDARD TABLE OF tvarvc.
ENDCLASS.
*&---------------------------------------------------------------------*
* SELECTION SCREEN
*&---------------------------------------------------------------------*
PARAMETERS: p_table TYPE tabname16 OBLIGATORY.
PARAMETERS: p_dbcon TYPE dbcon-con_name DEFAULT 'HANA_HXE' OBLIGATORY.
PARAMETERS: p_schema TYPE char10 DEFAULT 'NPLDATA' OBLIGATORY.
PARAMETERS: p_batch TYPE i DEFAULT 1000 OBLIGATORY.
PARAMETERS: p_test AS CHECKBOX DEFAULT 'X'.
*&---------------------------------------------------------------------*
* AT SELECTION-SCREEN
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN.
* Validate that the entered connection is a connection to HANA
SELECT SINGLE con_name INTO @DATA(gv_con_name) FROM dbcon
WHERE con_name = @p_dbcon
AND dbms = 'HDB'. " HANA
IF sy-subrc NE 0.
MESSAGE 'Invalid HANA DB connection'(001) TYPE 'E'.
ENDIF.
*&---------------------------------------------------------------------*
* START OF SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
DATA(lo_class) = NEW lcl_local( im_dbcon = p_dbcon im_table = p_table
im_schema = p_schema im_batch = p_batch
im_test = p_test ).
lo_class->get_ddic_map( ).
lo_class->drop_table( ).
lo_class->create_table( ).
lo_class->select_and_load_data( ).
*&---------------------------------------------------------------------*
* CLASS IMPLEMENTATION
*&---------------------------------------------------------------------*
CLASS lcl_local IMPLEMENTATION.
METHOD constructor.
gv_table = im_table.
gv_schema = im_schema.
gv_batch = im_batch.
gv_test_mode = im_test.
TRY.
go_sql_statement = NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection( im_dbcon ) ).
CATCH cx_sql_exception INTO go_exception.
gv_message = go_exception->get_text( ).
ENDTRY.
* Get structure of ABAP table
gr_struct_descr ?= cl_abap_structdescr=>describe_by_name( im_table ).
gt_table_fields = gr_struct_descr->get_ddic_field_list( ).
ENDMETHOD.
METHOD get_ddic_map.
SELECT low, high INTO CORRESPONDING FIELDS OF TABLE @gt_ddic_to_hana FROM tvarvc
WHERE name = 'DDIC_TO_HANA'.
ENDMETHOD.
METHOD drop_table.
CLEAR: gv_sql_stmt, gv_message.
gv_sql_stmt = |DROP TABLE "{ gv_schema }"."{ gv_table }"|.
TRY.
go_sql_statement->execute_ddl( gv_sql_stmt ).
CATCH cx_sql_exception INTO go_exception.
gv_message = go_exception->get_text( ).
ENDTRY.
ENDMETHOD.
METHOD create_table.
CLEAR: gv_sql, gv_sql_stmt, gv_message.
LOOP AT gt_table_fields REFERENCE INTO DATA(gr_table_fields).
DATA(gw_ddic_to_hana) = gt_ddic_to_hana[ low = gr_table_fields->datatype ].
CHECK sy-subrc = 0.
gv_sql = gv_sql &&
|"{ gr_table_fields->fieldname }" { gw_ddic_to_hana-high }|.
CASE gw_ddic_to_hana-high.
WHEN 'NVARCHAR' OR 'FLOAT'.
gv_sql = gv_sql && |({ gr_table_fields->leng })|.
WHEN 'TINYINT'.
WHEN 'DECIMAL'.
gv_sql = gv_sql && |({ gr_table_fields->leng },{ gr_table_fields->decimals })|.
ENDCASE.
gv_sql = gv_sql && ','.
IF gr_table_fields->keyflag EQ 'X'.
IF gv_pkey IS NOT INITIAL.
gv_pkey = gv_pkey && ','.
ENDIF.
gv_pkey = gv_pkey && |"{ gr_table_fields->fieldname }"|.
ENDIF.
ENDLOOP.
gv_sql_stmt =
|CREATE COLUMN TABLE "{ gv_schema }"."{ gv_table }" | &&
|( { gv_sql } PRIMARY KEY ({ gv_pkey }))|.
TRY.
go_sql_statement->execute_ddl( gv_sql_stmt ).
CATCH cx_sql_exception INTO go_exception.
gv_message = go_exception->get_text( ).
ENDTRY.
ENDMETHOD.
METHOD select_and_load_data.
FIELD-SYMBOLS:
<dyn_table> TYPE STANDARD TABLE,
<dyn_wa> TYPE any,
<dyn_field> TYPE any.
DATA(gt_components) = gr_struct_descr->components[].
LOOP AT gt_components REFERENCE INTO DATA(gr_components).
CLEAR gw_fcat.
gw_fcat-fieldname = gr_components->name .
CASE gr_components->type_kind.
WHEN 'C'.
gw_fcat-datatype = 'CHAR'.
WHEN 'N'.
gw_fcat-datatype = 'NUMC'.
WHEN 'D'.
gw_fcat-datatype = 'DATE'.
WHEN 'P'.
gw_fcat-datatype = 'PACK'.
WHEN OTHERS.
gw_fcat-datatype = gr_components->type_kind.
ENDCASE.
gw_fcat-inttype = gr_components->type_kind.
gw_fcat-intlen = gr_components->length.
gw_fcat-decimals = gr_components->decimals.
APPEND gw_fcat TO gt_fcat.
ENDLOOP.
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = gt_fcat
i_length_in_byte = 'X'
IMPORTING
ep_table = gd_table.
ASSIGN gd_table->* TO <dyn_table>.
CREATE DATA gd_line LIKE LINE OF <dyn_table>.
ASSIGN gd_line->* TO <dyn_wa>.
gv_message = |Selecting data from table { gv_table }|.
CALL FUNCTION 'PROGRESS_INDICATOR'
EXPORTING
i_text = gv_message
i_output_immediately = 'X'.
IF gv_test_mode IS NOT INITIAL.
SELECT * INTO TABLE <dyn_table> FROM (gv_table) UP TO 10 ROWS.
ELSE.
SELECT * INTO TABLE <dyn_table> FROM (gv_table).
ENDIF.
IF <dyn_table> IS NOT INITIAL.
gv_num_recs = lines( <dyn_table> ).
gv_processed = 0.
REFRESH: gt_stringtab.
LOOP AT <dyn_table> ASSIGNING <dyn_wa>.
gv_tabix = sy-tabix.
CLEAR: gv_sql, gv_values.
LOOP AT gt_table_fields REFERENCE INTO DATA(gr_table_fields).
ASSIGN COMPONENT gr_table_fields->fieldname OF STRUCTURE <dyn_wa> TO <dyn_field>.
DATA(gw_ddic_to_hana) = gt_ddic_to_hana[ low = gr_table_fields->datatype ].
CHECK sy-subrc = 0.
IF gv_values IS NOT INITIAL.
gv_values = gv_values && ','.
ENDIF.
CASE gw_ddic_to_hana-high.
WHEN 'NVARCHAR'.
gv_value = <dyn_field>.
REPLACE ALL OCCURRENCES OF `'` IN gv_value WITH `''`.
gv_values = gv_values && |'{ gv_value }'|.
WHEN 'DECIMAL' OR 'INTEGER' OR 'TINYINT' OR 'FLOAT' OR 'SMALLINT'.
IF <dyn_field> IS NOT INITIAL.
gv_values = gv_values && |{ <dyn_field> }|.
ELSE.
gv_values = gv_values && |NULL|.
ENDIF.
WHEN OTHERS.
gv_values = gv_values && |{ <dyn_field> }|.
ENDCASE.
ENDLOOP.
gv_sql = |INSERT INTO "{ gv_schema }"."{ gv_table }" VALUES ({ gv_values })|.
APPEND gv_sql TO gt_stringtab.
DELETE <dyn_table> INDEX gv_tabix.
ENDLOOP.
UNASSIGN <dyn_table>. " We no longer need this
CLEAR gv_processed.
LOOP AT gt_stringtab REFERENCE INTO DATA(gr_stringtab).
gv_tabix = sy-tabix.
ADD 1 TO gv_processed.
TRY.
go_sql_statement->execute_update( gr_stringtab->* ).
CATCH cx_sql_exception INTO go_exception.
gv_message = go_exception->get_text( ).
ENDTRY.
DELETE gt_stringtab INDEX gv_tabix.
* Perform a COMMIT WORK when we hit the "batch" number
gv_mod = gv_processed MOD gv_batch.
IF gv_mod IS INITIAL.
COMMIT WORK AND WAIT.
ENDIF.
* Show progress on screen for every 1000 records
gv_mod = gv_processed MOD 1000.
IF gv_mod IS INITIAL.
IF sy-batch IS INITIAL.
gv_message = |Processed "{ gv_processed }" records out of "{ gv_num_recs }".|.
CALL FUNCTION 'PROGRESS_INDICATOR'
EXPORTING
i_text = gv_message
i_processed = gv_processed
i_total = gv_num_recs
i_output_immediately = 'X'.
ENDIF.
ENDIF.
ENDLOOP.
gv_message = |Processed a total of { gv_processed } records into { gv_schema }.{ gv_table }.|.
MESSAGE gv_message TYPE 'S'.
ELSE.
gv_message = |No data selected|.
MESSAGE gv_message TYPE 'S'.
ENDIF.
ENDMETHOD.
ENDCLASS.
=========================================================================
Part 1: Connecting AS ABAP 751 Developer Edition with HANA Express 2.0 as Secondary DB
Part 2: Replicating data into HANA using ABAP – ADBC (Native SQL) (This blog)
Part 3: Unfair speed test – ABAP CDS on ASE vs HANA CDS on HANA (DUH!)
Great Post!
One question: Tried to run your program but I got a runtime error, since I don't have any selection variables in TVARVC. Can you show what you saved there?
Thanks!
Hi Marco,
Sure - the list was pretty long and that's why I didn't include it in the blog but I did make a reference to where this list is - just go to this blog https://blogs.sap.com/2013/05/14/how-slt-is-mapping-data-types/ and look at the first table in the blog post - and then run transaction STVARV and create a selection options with the name 'DDIC_TO_HANA' and copy the values from the table into the LOW and HIGH field respectively. You should be all set then.
*** For the variable with different field lengths, I just defaulted them to the second 'larger' match. eg: RAW = BLOB, SRST = BLOB, VARC = BLOB, LCHR = BLOB
Let me know if you have any further questions!
Here's a screenshot for your reference
Hi Alban,
thank you for your very interesting blog. I managed to connect SAP Hana Express Edition 2.0 to SAP NW 7.52 SP01 Developer Edition. Both are running in a VM using openSUSE Leap 15.
I copied your coding and the programm seems to run fine but no data is transfered from the SAP NW DB to the HANA DB. I checked in the Debugger and it seems, that the report does not find the HanaDB Schema "NPLDATA".
I think I did not get the step "manual creation of DB Schema" right. I created the HANA DB Schema NPLDATA in the Hana DB using eclipse frontend tools (i.e. SQL Editor)
CREATE SCHEMA NPLDATA OWNED BY SYSTEM;
GRANT SELECT ON SCHEMA NPLDATA to _SYS_REPO WITH GRANT OPTION;
Dbconnection uses the SYSTEM User.
Do I have to create a new user on the Hana DB ?
If I start the report with Input Schema = SYSTEM no SQL Exception is raised, but i does not work nonetheless.
Sorry, if this is a dumb question....
Best wishes,
Axel
Hi Axel,
Glad you found the blog post interesting and I'm sorry to hear that you're having issues trying to follow it.
Anyway, from your explanation, it looks like you have already created the NPLDATA schema with the user SYSTEM directly in Eclipse and the DB Connection from the ABAP server uses the same SYSTEM user - I don't see anything wrong with it.
Here's something you can try - Can you try to execute this SQL directly in Eclipse and see if you're able to create a new table in the NPLDATA schema? If this works, then maybe its an issue with the secondary table connection from ABAP -> HANA - if it doesn't, then we might be missing some authorization on the SYSTEM user with this newly created Schema.
Hi Alban,
thank you for your quick reply.
I entered the "CREATE TABLE...." command and could successfully create a table.
Furthermore I copied the contents of the variable "gv_sql_stmt" to the sql-editor.
For table sflight this gives:
CREATE COLUMN TABLE "NPLDATA"."SFLIGHT" ( "MANDT" NVARCHAR(000003),"CARRID" NVARCHAR(000003),"CONNID" NVARCHAR(000004),"FLDATE" NVARCHAR(000008),"PRICE" DECIMAL(000015,000002),"CURRENCY" NVARCHAR(000005),"PLANETYPE" NVARCHAR(000010),"SEATSMAX" INTEGER,"SEATSOCC" INTEGER,"PAYMENTSUM" DECIMAL(000017,000002),"SEATSMAX_B" INTEGER,"SEATSOCC_B" INTEGER,"SEATSMAX_F" INTEGER,"SEATSOCC_F" INTEGER, PRIMARY KEY ("MANDT","CARRID","CONNID","FLDATE"));
This command was processed without errors as well.
Any ideas what could be the problem here ?
Thank you in advance,
Axel
Hi Alban,
I think I found the error. It seems, I used the wrong port for SQL when entering the system in DBCO (39015 instead of 39013). Look fine now !
Thanks again an best wishes,
Axel
Awesome! Glad you were able to figure it out! Cheers!
Hi Alban
I found errors when executing sql command but when I copy to execute in Eclipse, It can be excuted.
I got these errors.
drop = invalid table name: SFLIGHT: line 1 col 22 (at pos 21)
create = invalid schema name: NPLDATA: line 1 col 21 (at pos 20)
insert = invalid schema name: NPLDATA: line 1 col 13 (at pos 12)
I connect to port 39015 that is SQL port of HXE 7.52
Please help.
Have you created the SCHEMA "NPLDATA" in HANA DB?
Also, if you have the newer ABAP AS 752 SP04, you can now use SLT to replicate data over automatically.
https://blogs.sap.com/2019/12/20/setting-up-ltr-from-abap-7.52-sp04-dev-edition-to-hana-express-2.0-sps04/
Hi Alban
Yes, I create the schema in HDB in Eclipse and I use ABAP AS 752 SP04.
But now I found it.
It because I select multiple containers and System database.
It should select Single Container instead. It works.
Thank You to quick reply.
Best regard.
Hi folks
Really great stuff
Has anybody tried this with the current developer release SPO4 yet - I think also you can improve the performance no end if you run the AS ABAP and HANA express systems on any "Server" Linux distro that works e.g CENTOS 8 rather than a Virtual Machine -- and then access the SAP system from a Windows GUI either on a Windows VM or from say a Windows laptop on your LAN (these days decent broadband not a problem) . Simply ensure openssh and Samba are working on the linux server and connectivity should be 100% OK. Removing the Linux server GUI -- i.e as a mean server edition rather than the overhead of VMWare player / Workstation buys a lot of performance back --at least it does for the AS ABAP edition --Love to try the HANA expredd edition
Thanks again for a fascinating blog
Cheers JH
Hi Jim Hawthorne, glad you've enjoyed the blog post. Yes - I've posted another post where with the current ABAP 752 SP04 developer edition, you can now use SLT to automatically transfer data from ABAP into HANA.
https://blogs.sap.com/2019/12/20/setting-up-ltr-from-abap-7.52-sp04-dev-edition-to-hana-express-2.0-sps04/
On your comment on not running the HANA Express in a VMWare scenario - there's also another interesting blog from Thomas Jung that explores that. It is most certainly an interesting read as well although I've not had the chance to try it out personally.
https://blogs.sap.com/2020/09/30/installing-sap-hana-express-edition-into-wsl2-windows-subsystem-for-linux/