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

TYPES: BEGIN OF ts_sales_order,

doc_number TYPE vbeln_vl,

doc_date TYPE audat,

customer_id TYPE kunnr,

total_amount TYPE netwr,

END OF ts_sales_order.

DATA: it_sales_order TYPE TABLE OF ts_sales_order,

wa_sales_order TYPE ts_sales_order.

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: Sales organization filter

" Export `it_sales_order` to a staging area or directly to SAP BTP"

" Include necessary classes

CLASS cl_sxml_string_writer DEFINITION LOAD.

CLASS cl_http_client DEFINITION LOAD.

CLASS /ui2/cl_json DEFINITION LOAD.

" Assume `it_sales_order` is already filled with data "

" Serialize it_sales_order to JSON"

DATA(json_string) = /ui2/cl_json=>serialize( data = it_sales_order ).

" Define endpoint for the BTP Object Store service"

CONSTANTS: object_store_url TYPE string VALUE 'https://<your_object_store_service_url>/path/to/your/container'.

" Define HTTP Client

DATA(http_client) TYPE REF TO cl_http_client.

" Create HTTP Client instance

cl_http_client=>create_by_url(

EXPORTING

url = object_store_url

IMPORTING

client = http_client

EXCEPTIONS

argument_not_found = 1

plugin_not_active = 2

internal_error = 3

OTHERS = 4 ).

IF sy-subrc <> 0.

" Handle error

WRITE: / 'Error creating HTTP client.'.

EXIT.

ENDIF.

" Set request method and headers for authentication and content-type

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

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

" Include authentication header, e.g., OAuth token, Basic Auth

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

" Set the JSON payload

http_client->request->set_data( json_string ).

" Send the request

http_client->send( ).

http_client->receive( ).

" Check response status

IF http_client->response->get_status_code( ) = 200 OR http_client->response->get_status_code( ) = 201.

WRITE: / 'Data successfully exported to SAP BTP'.

ELSE.

WRITE: / 'Failed to export data. Status Code:', http_client->response->get_status_code( ).

ENDIF.

" Assuming it_sales_order is already filled with the sales order data

DATA: lv_json_string TYPE string.

DATA(lo_json_writer) = cl_sxml_string_writer=>create( type = if_sxml=>co_xt_json ).

CALL TRANSFORMATION id SOURCE data = it_sales_order RESULT XML lo_json_writer.

lv_json_string = lo_json_writer->get_output( ).

" Now, lv_json_string contains the JSON representation of it_sales_order

" This JSON string can be saved to a file or passed to a Python script for further processing

" write lv_json_string to file

OPEN DATASET '/path/to/your/file.json' FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc = 0.

TRANSFER lv_json_string TO '/path/to/your/file.json'.

CLOSE DATASET '/path/to/your/file.json'.

ELSE.

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

ENDIF.

Python:

#Load libraries

import json

import pandas as pd

from sqlalchemy import create_engine

from hdbcli import dbapi

# Path to the JSON file exported from ABAP

json_file_path = 'path_to_your_json_file.json'

# Read the JSON file

with open(json_file_path, 'r') as file:

sales_order_data = json.load(file)

# Convert the JSON data to a DataFrame

df_sales_order = pd.DataFrame(sales_order_data)

# SAP HANA connection details

hana_host = 'your_hana_host'

hana_port = 'your_hana_port'

hana_user = 'your_hana_user'

hana_password = 'your_hana_password'

# Create SAP HANA connection

connection = dbapi.connect(

address=hana_host,

port=hana_port,

user=hana_user,

password=hana_password

)

# Assuming you have a table named "SALES_ORDER" in your SAP HANA schema

table_name = 'SALES_ORDER'

# Using SQLAlchemy to load data to SAP HANA

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

# Load DataFrame to SAP HANA

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

print('Data successfully loaded to SAP HANA in SAP BTP.')

from sqlalchemy import text

# SQL to perform transformation in SAP HANA

sql_transform = """

#-Example: Aggregate sales data by customer

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;

-- Additional transformation: Add a column indicating sales category

ALTER TABLE AGGREGATED_SALES_DATA ADD (sales_category NVARCHAR(50));

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;

"""

# Execute the transformation

with engine.connect() as connection:

connection.execute(text(sql_transform))

print("Data transformation in SAP HANA completed.")

Wrapper Code to Run Python in ABAP Program

REPORT z_extract_sales_order_data.

" Your ABAP code goes here..."

" Embedding Python script within ABAP program"

DATA(py_script) TYPE string.

py_script = `

# Python script goes here...

`;

" Execute Python script"

CALL METHOD cl_python_script=>execute

EXPORTING

script = py_script

RECEIVING

result = lv_result

EXCEPTIONS

execution_failed = 1

others = 2.

IF sy-subrc <> 0.

" Handle Python execution error

WRITE: / 'Python execution failed.'.

ENDIF.