(ELT) Python & ABAP SAP HANA-BTP

The provided ABAP and Python code extracts as an example sales order data from SAP, exports it to JSON format, and loads it into SAP HANA on SAP BTP. Here's an analysis of the program:

ABAP Section:

• Data Extraction: Sales order data is retrieved from the vbak table based on a specified sales organization (vkorg = '1000').

• Serialization: The data is serialized into JSON format using the /ui2/cl_json class.

• HTTP Request: An HTTP POST request is sent to export the JSON data to SAP BTP's Object Store service.

• Error Handling: Error handling is implemented to manage errors during HTTP

Python Section:

• JSON Reading: The exported JSON file is read.

• DataFrame Creation: The JSON data is converted into a Pandas DataFrame.

• SAP HANA Connection: A connection is established to SAP HANA using HDBCLI.

• Data Loading: The DataFrame is loaded into the SAP HANA database using SQLAlchemy.

• Data Transformation: SQL transformation scripts are executed in SAP HANA to aggregate sales data by customer and categorize sales.

Transformation in SAP HANA:

• The SQL transformation aggregates sales data by customer and adds a column indicating the sales category.

Overall, the program handles the extraction, loading, trasformation (ELT) of sales order data from SAP to SAP HANA on SAP BTP. It demonstrates a robust integration between ABAP and Python for seamless data processing.

ABAP:

REPORT z_extract_sales_order_data.

*--------------------------------------------------------------------*

* Type Definition for Sales Order Structure

*--------------------------------------------------------------------*

TYPES: BEGIN OF ts_sales_order,

doc_number TYPE vbeln_va,

doc_date TYPE audat,

customer_id TYPE kunnr,

total_amount TYPE netwr,

END OF ts_sales_order.

*--------------------------------------------------------------------*

* Data Declarations

*--------------------------------------------------------------------*

DATA: it_sales_order TYPE TABLE OF ts_sales_order,

lv_json_string TYPE string,

http_client TYPE REF TO cl_http_client.

*--------------------------------------------------------------------*

* Step 1: Select Sales Order Data from VBAK

*--------------------------------------------------------------------*

SELECT vbeln AS doc_number,

audat AS doc_date,

kunnr AS customer_id,

netwr AS total_amount

FROM vbak

INTO TABLE @it_sales_order

WHERE vkorg = '1000'. " Example filter: Sales Organization

IF sy-subrc <> 0.

WRITE: / 'No records found for Sales Org 1000.'.

EXIT.

ENDIF.

*--------------------------------------------------------------------*

* Step 2: Serialize to JSON

*--------------------------------------------------------------------*

lv_json_string = /ui2/cl_json=>serialize( data = it_sales_order pretty_name = /ui2/cl_json=>pretty_mode-enabled ).

*--------------------------------------------------------------------*

* Step 3: Option A – Save JSON File Locally on SAP App Server

*--------------------------------------------------------------------*

DATA(lv_filename) = '/usr/sap/tmp/sales_order_data.json'.

OPEN DATASET lv_filename FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc = 0.

TRANSFER lv_json_string TO DATASET lv_filename.

CLOSE DATASET lv_filename.

WRITE: / 'JSON data exported to file:', lv_filename.

ELSE.

WRITE: / 'Error opening file for writing.'.

ENDIF.

*--------------------------------------------------------------------*

* Step 4: Option B – Send JSON Directly to SAP BTP Object Store

*--------------------------------------------------------------------*

CONSTANTS: lc_btp_url TYPE string VALUE 'https://<your_btp_object_store_url>/sales_order_data.json'.

TRY.

cl_http_client=>create_by_url(

EXPORTING

url = lc_btp_url

IMPORTING

client = http_client ).

http_client->request->set_method( 'POST' ).

http_client->request->set_header_field( name = 'Content-Type' value = 'application/json' ).

http_client->request->set_header_field( name = 'Authorization' value = 'Bearer <your_access_token>' ).

http_client->request->set_cdata( lv_json_string ).

http_client->send( ).

http_client->receive( ).

DATA(lv_status) = http_client->response->get_status_code( ).

IF lv_status = 200 OR lv_status = 201.

WRITE: / 'Data successfully exported to SAP BTP (Status:', lv_status, ')'.

ELSE.

WRITE: / 'Failed to export data. HTTP Status:', lv_status.

ENDIF.

http_client->close( ).

CATCH cx_root INTO DATA(lx_err).

WRITE: / 'HTTP Error:', lx_err->get_text( ).

ENDTRY.

Python:

import json

import pandas as pd

from sqlalchemy import create_engine, text

from hdbcli import dbapi

#---------------------------------------------------------------

# Step 1: Load JSON Exported from ABAP

#---------------------------------------------------------------

json_file_path = 'C:/Users/walfs/sales_order_data.json' # Adjust path as needed

with open(json_file_path, 'r', encoding='utf-8') as file:

sales_order_data = json.load(file)

# Convert JSON list to DataFrame

df_sales_order = pd.DataFrame(sales_order_data)

print(f"Loaded {len(df_sales_order)} sales order records from JSON.")

#---------------------------------------------------------------

# Step 2: Connect to SAP HANA Cloud

#---------------------------------------------------------------

hana_host = 'your_hana_host'

hana_port = '443'

hana_user = 'your_hana_user'

hana_password = 'your_hana_password'

# HDBCLI connection (optional check)

connection = dbapi.connect(

address=hana_host,

port=int(hana_port),

user=hana_user,

password=hana_password

)

print("HANA connection established via hdbcli.")

# SQLAlchemy connection (for pandas integration)

engine = create_engine(f'hana://{hana_user}:{hana_password}@{hana_host}:{hana_port}')

#---------------------------------------------------------------

# Step 3: Load DataFrame into HANA Table

#---------------------------------------------------------------

table_name = 'SALES_ORDER'

df_sales_order.to_sql(table_name, engine, if_exists='append', index=False)

print(f"Data successfully loaded into table '{table_name}' in SAP HANA.")

#---------------------------------------------------------------

# Step 4: Transform Data in HANA (Aggregation + Classification)

#---------------------------------------------------------------

with engine.begin() as conn:

# Create Aggregated Table

conn.execute(text("""

CREATE COLUMN TABLE IF NOT EXISTS AGGREGATED_SALES_DATA AS

SELECT

customer_id,

SUM(total_amount) AS total_sales

FROM SALES_ORDER

GROUP BY customer_id

"""))

# Add a new classification column

conn.execute(text("""

ALTER TABLE AGGREGATED_SALES_DATA

ADD (sales_category NVARCHAR(50))

"""))

# Update with classification

conn.execute(text("""

UPDATE AGGREGATED_SALES_DATA

SET sales_category = CASE

WHEN total_sales > 5000 THEN 'High'

WHEN total_sales BETWEEN 2000 AND 5000 THEN 'Medium'

ELSE 'Low'

END

"""))

print("Data transformation and classification completed in SAP HANA Cloud.")