Skip to Content

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

8 Comments
You must be Logged on to comment or reply to a post.
  • 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!

  • 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.

      CREATE TABLE NPLDATA.DEMO_TBL ( 
              ID INTEGER, 
              FIRSTNAME VARCHAR(40), 
              LASTNAME VARCHAR(40),
              EMAIL VARCHAR(120),
              PRIMARY KEY (ID) 
      ); 
  • 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