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:  TYPE STANDARD TABLE,  TYPE any,  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 . CREATE DATA gd_line LIKE LINE OF . ASSIGN gd_line->* TO . 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  FROM (gv_table) UP TO 10 ROWS. ELSE. SELECT * INTO TABLE  FROM (gv_table). ENDIF. IF  IS NOT INITIAL. gv_num_recs = lines(  ). gv_processed = 0. REFRESH: gt_stringtab. LOOP AT  ASSIGNING . 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  TO . 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 = . 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  IS NOT INITIAL. gv_values = gv_values && |{  }|. ELSE. gv_values = gv_values && |NULL|. ENDIF. WHEN OTHERS. gv_values = gv_values && |{  }|. ENDCASE. ENDLOOP. gv_sql = |INSERT INTO "{ gv_schema }"."{ gv_table }" VALUES ({ gv_values })|. APPEND gv_sql TO gt_stringtab. DELETE  INDEX gv_tabix. ENDLOOP. UNASSIGN . " 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!)