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