Replicating data into HANA using ABAP - ADBC (Native SQL)

[albanleong]

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!)