Skip to Content

It is great that SAP provide trial licensed versions of HANA for us to get familiar with the technology and to acquire and practise the skills we will need to use this great new technology.

Of course as a developer, after getting familiar with the tools I pretty quickly wanted to load up some ERP data into my trial HANA database to see how I could use the power of HANA on these very large datasets.

Problem. The HANA trial systems do not come with any of the tooling to support the loading of external data. Tools such at the SAP LT Replication Server (http://scn.sap.com/docs/DOC-33274) are not included in the license so options for loading large datasets are pretty limited.

Several people have offered their solutions which usually involve dumping the source dataset out into a CSV file and then loading that data into the appropriate HANA tables. This is fine for small datasets but not really suitable for the typically large ERP tables.

Recently SAP made available a trial license version of the SAP NetWeaver ABAP 7.4 on HANA. You can find out more here.  http://scn.sap.com/community/developer-center/abap

This opens up the possibility of using good old RFC to transfer a table from an ERP system into the HANA database. As an ABAPer I love this idea!

First I created a schema on the HANA database to hold the replicated ERP tables – I called it ERP-DATA.

Screen Shot 2013-08-17 at 5.32.39 PM.png

I also need to create a suitable role and assign it to the SAP<sid> user so that my ABAP code can create tables in this schema.

Next I built a simple RFC-enabled function module that will process passed native SQL statements using the ADBC class CL_SQL_STATEMENT.

The code for this function module looks like this…

FUNCTION zhana_exec_sql.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(IV_DDL_STMT) TYPE  STRING OPTIONAL
*"     VALUE(IV_DML_STMT) TYPE  STRING OPTIONAL
*"     VALUE(IT_INSERTS) TYPE  STRINGTAB OPTIONAL
*"  EXPORTING
*"     VALUE(EV_MESSAGE) TYPE  STRING
*"     VALUE(EV_ROWS) TYPE  NUM20
*"----------------------------------------------------------------------
DATA: exc TYPE REF TO cx_root,
         lr_insert TYPE REF TO string,
         lv_rows TYPE i,
         lv_count TYPE i.
IF lo_sql_statement IS NOT BOUND.
CREATE OBJECT lo_sql_statement.
ENDIF.
IF iv_ddl_stmt IS NOT INITIAL.
TRY.
         lo_sql_statement->execute_ddl( iv_ddl_stmt ).
CATCH cx_root INTO exc.
         ev_message = exc->get_text( ).
ENDTRY.
RETURN.
ENDIF.
IF iv_dml_stmt IS NOT INITIAL.
TRY.
         ev_message = |{ lo_sql_statement->execute_update( iv_dml_stmt ) } rows processed|.
CATCH cx_root INTO exc.
         ev_message = exc->get_text( ).
ENDTRY.
RETURN.
ENDIF.
LOOP AT it_inserts REFERENCE INTO lr_insert.
TRY.
         lv_rows = lo_sql_statement->execute_update( lr_insert->* ).
ADD lv_rows TO lv_count.
CATCH cx_root INTO exc.
         ev_message = exc->get_text( ).
ENDTRY.
ENDLOOP.
   ev_rows = lv_count.
   ev_message = |{ lv_count } rows inserted|.
ENDFUNCTION.

Note that the LO_SQL_STATEMENT variable is defined in the TOP include to maximise reuse.


DATA: lo_sql_statement TYPE REF TO cl_sql_statement.

This is all pretty rudimentary with minimal error handling, etc. I will pass DDL statements like DROP TABLE and CREATE TABLE in the importing variable IV_DDL_STMT.  I will batch up a series of INSERT statements and pass them in via the IT_INSERTS importing variable.

Now we move over to the ERP system where I have most of my code.

I have everything in a single class called ZCL_TABLE_REPL. You can find the complete code in the attached text file – so let me just describe the main pieces.

Firstly we use Runtime Type Services (RTTS) to get the details of the columns in the source table.


    struct_descr ?= cl_abap_structdescr=>describe_by_name( table_name ).
     table_fields = struct_descr->get_ddic_field_list( ).

 

Then we send a DROP TABLE statement to the RFC-enabled function module to ensure the table is removed before we send a CREATE TABLE statement.  

 

    lv_sql_stmt = |DROP TABLE "{ schema }"."{ table_name }"|.
CALL FUNCTION 'ZHANA_EXEC_SQL'
       DESTINATION rfc_dest
EXPORTING
         iv_ddl_stmt = lv_sql_stmt
IMPORTING
         ev_message  = lv_message.

Now we need to build the CREATE TABLE statement using the information from the data dictionary and a mapping table that was built by the class constructor. Note I have only done minimal mapping so you may well need to expand this table to support some of the less common datatypes.     

LOOP AT table_fields REFERENCE INTO lr_field.
READ TABLE type_map REFERENCE INTO lr_type_map
WITH KEY erp = lr_field->datatype.
CHECK sy-subrc = 0.
       lv_sql = lv_sql &&
         |"{ lr_field->fieldname }" { lr_type_map->hana }|.
CASE lr_type_map->hana.
WHEN 'NVARCHAR' OR 'FLOAT'.
           lv_sql = lv_sql && |({ lr_field->leng })|.
WHEN 'TINYINT'.
WHEN 'DECIMAL'.
           lv_sql = lv_sql && |({ lr_field->leng },{ lr_field->decimals })|.
ENDCASE.
       lv_sql = lv_sql && ','.
IF lr_field->keyflag EQ 'X'.
IF lv_pkey IS NOT INITIAL.
           lv_pkey = lv_pkey && ','.
ENDIF.
         lv_pkey = lv_pkey && |"{ lr_field->fieldname }"|.
ENDIF.
ENDLOOP.
     rv_sql =
       |CREATE COLUMN TABLE "{ schema }"."{ table_name }" | &&
       |( { lv_sql } PRIMARY KEY ({ lv_pkey }))|.

Then we pass the CREATE TABLE statement across to our RFC-enabled function module to execute it.

CALL FUNCTION 'ZHANA_EXEC_SQL'
       DESTINATION rfc_dest
EXPORTING
         iv_ddl_stmt = lv_sql_stmt
IMPORTING
         ev_message  = lv_message.

Now the heavy lifting begins. We again use RTTS and the mapping data to generate a series of INSERT sql statements that are batched up and passed across to our RFC-enabled function module for processing.

WHILE <table> IS NOT INITIAL.
           lv_row_count = 0.
LOOP AT <table> ASSIGNING <row>.
ADD 1 TO lv_row_count.
IF lv_row_count > insert_batch_size.
EXIT.
ENDIF.
CLEAR lv_values.
LOOP AT table_fields REFERENCE INTO lr_table_field.
ASSIGN COMPONENT lr_table_field->fieldname OF STRUCTURE <row> TO <field>.
READ TABLE type_map REFERENCE INTO lr_map
WITH KEY erp = lr_table_field->datatype.
CHECK sy-subrc = 0.
IF lv_values IS NOT INITIAL.
                 lv_values = lv_values && ','.
ENDIF.
CASE lr_map->hana.
WHEN 'NVARCHAR'.
                   lv_value = <field>.
REPLACE ALL OCCURRENCES OF `'` IN lv_value WITH `''`.
                   lv_values = lv_values && |'{ lv_value }'|.
WHEN 'DECIMAL' OR 'INTEGER' OR 'TINYINT' OR 'FLOAT'.
                   lv_values = lv_values && |{ <field> }|.
ENDCASE.
ENDLOOP.
             lv_sql = |insert into "{ schema }"."{ table_name }" values ({ lv_values })|.
APPEND lv_sql TO lt_inserts.
DELETE <table>.
ENDLOOP.
CALL FUNCTION 'ZHANA_EXEC_SQL'
             DESTINATION rfc_dest
EXPORTING
               it_inserts = lt_inserts
IMPORTING
               ev_message = lv_msg
               ev_rows    = lv_insert.
ADD lv_insert TO lv_insert_counter.
"WRITE: /, lv_insert_counter, ` records inserted`.
CLEAR lt_inserts.
ENDWHILE.

All that’s left to do is define the RFC destination for the NW7.4 on HANA system using transaction SM59 and then we are right to go.

To execute just call the method passing the table name. (Note I have defaulted parameters for schema, RFC destination and batch size.)

zcl_table_repl=>replicate_table( iv_table_name = 'DD03L' ).

I have found that batching up the insert statements into groups of 1000 is reasonably efficient. To give you some idea of throughput I replicated table DD03L which had 911,282 rows in 63 minutes. That is well over 14000 rows per minute. Both ABAP systems were running on Amazon EC2 instances and connected via a SAPRouter.

This was just an experiment so please understand…

  • This is just one way of doing this – there are many others
  • I have used minimal error handling
  • I have only mapped the most common datatypes – others are ignored
  • I have my own logging/messaging class which in this sample I have replaced with WRITE statements
  • I have no idea if the trial license conditions prevent us from doing this. You would need to check these details yourself.

Enjoy!

* The complete source for the ZCL_TABLE_REPL class is in the attached text file.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Gregory Misiorek

    Hi Graham,

    this is a great piece of ABAP code and definitely more appropriate for industrial strength tables than CSV files. however, this also assumes that you will be making a connection between amazon-hosted system and your live SAP instance which will be hosted behind the firewall.

    there may be some customers which will not be happy about that.

    thanks for sharing.

    rgds,

    greg

    (0) 
    1. Graham Robinson Post author

      Gregory Misiorek wrote:

                             

      …this also assumes that you will be making a connection between amazon-hosted system and your live SAP instance which will be hosted behind the firewall… there may be some customers which will not be happy about that.

      That is a whole other topic for another blog – but in my example the connection is initiated from the ERP system to the HANA system not the other way around.

      Cheers,

      Graham Robbo

      (0) 
  2. Jason Scott

    Great blog Graham… What you’ve essentially done is build a LT Replication Server – Lite!

    Both use RFC for comm’s on an ABAP stack. I like yours better though as it’s much simpler and you don’t need an entire project to implement it.


    You could easily make it real-time (if you ever wanted to) via numerous methods like change-pointers for example. You could also spin-up a separate AWS instance of the ABAP developer edition if you wanted to stick with the standalone HANA sp6 thats available.

    ps. Who needs SAP LT Replication Server!

    –Jason.

    (0) 

Leave a Reply