Introduction
SAP ERP is renowned for its comprehensive suite of Business Applicatin Programming Interface (BAPIs), which serve as the cornerstone for interacting with SAP systems externally. These interfaces are meticulously designed to streamline and enhance the integration process, facilitating seamless communication between SAP ERP and other systems. It is of course possible to use custom BAPIs, the so called Z function modules.
In this guide, we’ll embark on a detailed journey to explore the integration capabilities between DuckDB and SAP ERP using BAPIs. Specifically, we will cover:
- Identifying and exploring available BAPI functions within SAP ERP.
- Retrieving parameters, along with essential metadata, to understand function specifications more thoroughly.
- Executing BAPIs directly from DuckDB, followed by a step-by-step guide on processing the fetched results effectively.
This tutorial aims to equip you with the necessary skills to leverage DuckDB for efficient data integration with SAP systems, thereby enhancing your data management and analysis capabilities.
Get yourself prepared
To effectively utilize the BAPI component, you must have access to specific authority objects, namely RFC (Remote Function Call), within the SAP system. It’s crucial to ensure you possess the requisite permissions to execute the BAPI functions you intend to use. This access is foundational to the successful integration and operation of BAPI calls from DuckDB.
First and foremost you need to install DuckDB and the ERPL extension. DuckDB as various different installation options, please follow the instructions on the DuckDB website to get yourself prepared. For our scenario down below we recommend to install the Python or R bindings as well. You can check if this worked well with the following commands:
import duckdb
cursor = duckdb.connect()
print(cursor.execute('SELECT 42').fetchall())library("DBI")
con = dbConnect(duckdb::duckdb(), ":memory:")
dbWriteTable(con, "iris", iris)
dbGetQuery(con, 'SELECT "Species", MIN("Sepal.Width") FROM iris GROUP BY "Species"')For the ERPL extension please follow the instructions in the installation section of our documentation to get yourself prepared.
List all available RFC functions
A crucial step in leveraging BAPIs is to identify the available functions within the SAP system. This information is essential for understanding the scope of functions you can access and utilize for data integration. In a typical SAP system there are tens of thousands of BAPIs available, so it most of the time it makes sense to restrict the search eather by function name or group name.:
SELECT * FROM sap_rfc_show_function(FUNCNAME='BAPI_FLIGHT*') ORDER BY 1This query will return a list of all BAPI functions whose name starts with BAPI_FLIGHT. You can replace the FUNCNAME parameter with any other search term to filter the results accordingly. The star operator * is a wildcard and matches any number of characters. Supplying no search string is equvivalent to *.
┌───────────────────────────────┬────────────────────┬─────────┬─────────┬─────────────────────────────┐
│ FUNCNAME │ GROUPNAME │ APPL │ HOST │ STEXT │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├───────────────────────────────┼────────────────────┼─────────┼─────────┼─────────────────────────────┤
│ BAPI_FLIGHT_CHECKAVAILIBILITY │ SAPBC_BAPI_SFLIGHT │ │ │ Check flight availability │
│ BAPI_FLIGHT_GETDETAIL │ SAPBC_BAPI_SFLIGHT │ │ │ Find details about a flight │
│ BAPI_FLIGHT_GETLIST │ SAPBC_BAPI_SFLIGHT │ │ │ Find list of flights │
│ BAPI_FLIGHT_SAVEREPLICA │ SAPBC_BAPI_SFLIGHT │ │ │ Save replicated flight data │
└───────────────────────────────┴────────────────────┴─────────┴─────────┴─────────────────────────────┘Alternatively you can also search for functions by their group name:
SELECT * FROM sap_rfc_show_function(GROUPNAME='SAPBC*') ORDER BY 1┌─────────────────────────┬───────────────────────────┬─────────┬─────────┬────────────────────────────────────────────┐
│ FUNCNAME │ GROUPNAME │ APPL │ HOST │ STEXT │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────────────────┼───────────────────────────┼─────────┼─────────┼────────────────────────────────────────────┤
│ BAPI_FLBOOKING_CANCEL │ SAPBC_BAPI_SBOOK │ │ │ Cancel Flight Booking │
│ BAPI_FLBOOKING_CONFIRM │ SAPBC_BAPI_SBOOK │ │ │ Confirm Flight Booking │
│ BAPI_FLBOOKING_CREATE… │ SAPBC_BAPI_SBOOK │ │ │ │
│ BAPI_FLBOOKING_CREATE… │ SAPBC_BAPI_SBOOK │ │ │ Create Flight Booking │
│ BAPI_FLBOOKING_GETLIST │ SAPBC_BAPI_SBOOK │ │ │ Determine list of flight bookings │
│ BAPI_FLBOOKING_SENDRE… │ SAPBC_BAPI_SBOOK │ │ │ │
│ BAPI_FLCONN_GETDETAIL │ SAPBC_BAPI_SFLCONN │ │ │ Find details about a flight connection │
│ BAPI_FLCONN_GETLIST │ SAPBC_BAPI_SFLCONN │ │ │ Find list of flight connections │
│ BAPI_FLCUST_CHANGE │ SAPBC_BAPI_SCUSTOMER │ │ │ Change Flight Customer │
│ BAPI_FLCUST_CHANGEPAS… │ SAPBC_BAPI_SCUSTOMER │ │ │ Change Internet password │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ BAPI_SCUSTOMER_CHANGE… │ SAPBC_BOR_SCUSTOMER │ S │ │ Change flight customer's internet password │
│ BAPI_SCUSTOMER_CHECKP… │ SAPBC_BOR_SCUSTOMER │ S │ │ Check flight customer's internet password │
│ BAPI_SCUSTOMER_CREATE… │ SAPBC_BOR_SCUSTOMER │ S │ │ Create flight customer │
│ BAPI_SFLIGHT_GETDETAIL │ SAPBC_BOR_SFLIGHT │ S │ │ Flight details │
│ BAPI_SFLIGHT_GETLIST │ SAPBC_BOR_SFLIGHT │ S │ │ List of flights │
│ SAPBC_GLOBAL_AGENCY_C… │ SAPBC_GLOBAL_TRAVELAGENCY │ S │ │ │
│ SAPBC_GLOBAL_BOOK │ SAPBC_GLOBAL_FIS │ S │ │ │
│ SAPBC_GLOBAL_CANCEL │ SAPBC_GLOBAL_FIS │ S │ │ │
│ SAPBC_GLOBAL_CONNECTI… │ SAPBC_GLOBAL_FIS │ S │ │ │
│ SAPBC_GLOBAL_CUSTOMER… │ SAPBC_GLOBAL_CUSTOMER │ S │ │ │
├─────────────────────────┴───────────────────────────┴─────────┴─────────┴────────────────────────────────────────────┤
│ 35 rows (20 shown) 5 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Describe a BAPI / RFC function
Once you’ve identified the BAPI function you wish to use, the next step is to retrieve detailed information about the function’s parameters and metadata. This information is crucial for understanding how to actually call the function and process the results effectively.:
SELECT * FROM sap_rfc_describe_function('BAPI_FLIGHT_GETLIST')This returns a detailed description of the function similar the function builder SE80 in SAP GUI would do:
Meaning that the describe function has the following result columns:
name: The name of the function module. This is typically the same as the function name you supplied to thesap_rfc_describe_functionfunction.text: The description of the function module. This is short text inSE80.function_group: The function group the function module belongs to. This is function group inSE80. (To get more information about the function group, you can use thesap_rfc_show_groupsfunction.)remote_callable: Whether the function module can be called remotely. This is the processing type remote enabled module inSE80.import: A struct containing the import parameters of the function module.export: A struct containing the export parameters of the function module.changing: A struct containing the changing parameters of the function module.tables: A struct containing the table parameters of the function module.source: The source code of the function module.
To get the import parameters of the function, use the following query:
SELECT unnest(import, max_depth := 2) FROM sap_rfc_describe_function('BAPI_FLIGHT_GETLIST')This results in the following table, where expecially the duckdb_type column is interesting, for prepeparing parameters for following sap_rfc_invoke calls:
┌──────────────────┬────────────────────────────────┬──────────────┬───────────────────────────────────────────────────┬────────────┬────────┬──────────┬───────────────┬──────────┐
│ name │ text │ abap_type │ duckdb_type │ direction │ length │ decimals │ default_value │ optional │
│ varchar │ varchar │ varchar │ varchar │ varchar │ int32 │ int32 │ varchar │ boolean │
├──────────────────┼────────────────────────────────┼──────────────┼───────────────────────────────────────────────────┼────────────┼────────┼──────────┼───────────────┼──────────┤
│ AIRLINE │ Select airline │ RFCTYPE_CHAR │ VARCHAR │ RFC_IMPORT │ 3 │ 0 │ │ true │
│ DESTINATION_FROM │ Select departure city │ BAPISFLDST │ STRUCT(AIRPORTID VARCHAR, CITY VARCHAR, COUNTR … │ RFC_IMPORT │ 28 │ 0 │ │ true │
│ DESTINATION_TO │ Select destination │ BAPISFLDST │ STRUCT(AIRPORTID VARCHAR, CITY VARCHAR, COUNTR … │ RFC_IMPORT │ 28 │ 0 │ │ true │
│ MAX_ROWS │ Maximum Number of Lines of H… │ RFCTYPE_INT │ BIGINT │ RFC_IMPORT │ 4 │ 0 │ │ true │
└──────────────────┴────────────────────────────────┴──────────────┴───────────────────────────────────────────────────┴────────────┴────────┴──────────┴───────────────┴──────────┘To show e.g. the tables parameters use the following DuckDB query:
SELECT unnest(tables, recursive := true) FROM sap_rfc_describe_function('BAPI_FLIGHT_GETLIST')This results in the following table, where expecially the duckdb_type column is interesting, for prepeparing parameters for following sap_rfc_invoke calls:
┌───────────────┬────────────────────────────────┬────────────┬────────────────────────────────────────────────────────┬────────────┬────────┬──────────┬───────────────┬──────────┐
│ name │ text │ abap_type │ duckdb_type │ direction │ length │ decimals │ default_value │ optional │
│ varchar │ varchar │ varchar │ varchar │ varchar │ int32 │ int32 │ varchar │ boolean │
├───────────────┼────────────────────────────────┼────────────┼────────────────────────────────────────────────────────┼────────────┼────────┼──────────┼───────────────┼──────────┤
│ DATE_RANGE │ Selection range for flight d… │ BAPISFLDRA │ STRUCT(SIGN VARCHAR, "OPTION" VARCHAR, LOW DATE, HIG… │ RFC_TABLES │ 19 │ 0 │ │ true │
│ EXTENSION_IN │ Import customer enhancements │ BAPIPAREX │ STRUCT(STRUCTURE VARCHAR, VALUEPART1 VARCHAR, VALUEP… │ RFC_TABLES │ 990 │ 0 │ │ true │
│ EXTENSION_OUT │ Export customer enhancements │ BAPIPAREX │ STRUCT(STRUCTURE VARCHAR, VALUEPART1 VARCHAR, VALUEP… │ RFC_TABLES │ 990 │ 0 │ │ true │
│ FLIGHT_LIST │ List of flights │ BAPISFLDAT │ STRUCT(AIRLINEID VARCHAR, AIRLINE VARCHAR, CONNECTID… │ RFC_TABLES │ 121 │ 0 │ │ true │
│ RETURN │ Return Messages │ BAPIRET2 │ STRUCT("TYPE" VARCHAR, ID VARCHAR, NUMBER VARCHAR, M… │ RFC_TABLES │ 548 │ 0 │ │ true │
└───────────────┴────────────────────────────────┴────────────┴────────────────────────────────────────────────────────┴────────────┴────────┴──────────┴───────────────┴──────────┘Call a BAPI / RFC function
Once you’ve identified the function you wish to call and understood its parameters, you can proceed to execute the function directly from DuckDB. This step involves invoking the sap_rfc_invoke function, passing the function name and the required parameters as arguments.
We saw in the list of return tables of the BAPI_FLIGHT_GETLIST function that the FLIGHT_LIST table is the one we are interested in. To call the function and fetch the result table, use the following query:
SELECT * FROM sap_rfc_invoke('BAPI_FLIGHT_GETLIST', path='/FLIGHT_LIST')The path parameter is optional and can be used to specify the path to the table you are interested in. If you omit the path parameter, the function will return all tables in the result.
┌───────────┬───────────────────┬───────────┬────────────┬───┬────────────┬───────────────┬─────────┬──────────┐
│ AIRLINEID │ AIRLINE │ CONNECTID │ FLIGHTDATE │ … │ ARRDATE │ PRICE │ CURR │ CURR_ISO │
│ varchar │ varchar │ varchar │ date │ │ date │ decimal(23,4) │ varchar │ varchar │
├───────────┼───────────────────┼───────────┼────────────┼───┼────────────┼───────────────┼─────────┼──────────┤
│ LH │ Lufthansa │ 0400 │ 2016-11-18 │ … │ 2016-11-18 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-02-06 │ … │ 2017-02-06 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-04-27 │ … │ 2017-04-27 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-04-28 │ … │ 2017-04-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-05-28 │ … │ 2017-05-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-07-16 │ … │ 2017-07-16 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-10-04 │ … │ 2017-10-04 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-12-23 │ … │ 2017-12-23 │ 666.0000 │ EUR │ EUR │
│ AA │ American Airlines │ 0017 │ 2016-11-15 │ … │ 2016-11-15 │ 422.9400 │ USD │ USD │
│ AA │ American Airlines │ 0017 │ 2017-02-03 │ … │ 2017-02-03 │ 422.9400 │ USD │ USD │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ JL │ Japan Airlines │ 0407 │ 2017-04-26 │ … │ 2017-04-26 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0407 │ 2017-07-15 │ … │ 2017-07-15 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0407 │ 2017-10-03 │ … │ 2017-10-03 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0407 │ 2017-12-22 │ … │ 2017-12-22 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2016-11-18 │ … │ 2016-11-19 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2017-02-06 │ … │ 2017-02-07 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2017-04-27 │ … │ 2017-04-28 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2017-07-16 │ … │ 2017-07-17 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2017-10-04 │ … │ 2017-10-05 │ 106136.0000 │ JPY │ JPY │
│ JL │ Japan Airlines │ 0408 │ 2017-12-23 │ … │ 2017-12-24 │ 106136.0000 │ JPY │ JPY │
├───────────┴───────────────────┴───────────┴────────────┴───┴────────────┴───────────────┴─────────┴──────────┤
│ 94 rows (20 shown) 14 columns (8 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘We can now restrict to AIRLINEID = 'LH' and the departing airport DESTINATION_FROM to FRA (we have to fill the BAPISFLDSTstructure) we can use the following query:
SELECT * FROM
sap_rfc_invoke('BAPI_FLIGHT_GETLIST',
{'AIRLINE': 'LH', 'DESTINATION_FROM': {'AIRPORTID': 'FRA'}},
path='/FLIGHT_LIST')Leading to a filtered result:
┌───────────┬───────────┬───────────┬────────────┬───────────┬───┬────────────┬───────────────┬─────────┬──────────┐
│ AIRLINEID │ AIRLINE │ CONNECTID │ FLIGHTDATE │ AIRPORTFR │ … │ ARRDATE │ PRICE │ CURR │ CURR_ISO │
│ varchar │ varchar │ varchar │ date │ varchar │ │ date │ decimal(23,4) │ varchar │ varchar │
├───────────┼───────────┼───────────┼────────────┼───────────┼───┼────────────┼───────────────┼─────────┼──────────┤
│ LH │ Lufthansa │ 0400 │ 2016-11-18 │ FRA │ … │ 2016-11-18 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-02-06 │ FRA │ … │ 2017-02-06 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-04-27 │ FRA │ … │ 2017-04-27 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-04-28 │ FRA │ … │ 2017-04-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-05-28 │ FRA │ … │ 2017-05-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-07-16 │ FRA │ … │ 2017-07-16 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-10-04 │ FRA │ … │ 2017-10-04 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0400 │ 2017-12-23 │ FRA │ … │ 2017-12-23 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 2402 │ 2016-11-18 │ FRA │ … │ 2016-11-18 │ 242.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 2402 │ 2017-02-06 │ FRA │ … │ 2017-02-06 │ 242.0000 │ EUR │ EUR │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ LH │ Lufthansa │ 2402 │ 2017-10-04 │ FRA │ … │ 2017-10-04 │ 242.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 2402 │ 2017-12-23 │ FRA │ … │ 2017-12-23 │ 242.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2016-11-13 │ FRA │ … │ 2016-11-13 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-02-01 │ FRA │ … │ 2017-02-01 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-04-22 │ FRA │ … │ 2017-04-22 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-04-28 │ FRA │ … │ 2017-04-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-05-28 │ FRA │ … │ 2017-05-28 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-07-11 │ FRA │ … │ 2017-07-11 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-09-29 │ FRA │ … │ 2017-09-29 │ 666.0000 │ EUR │ EUR │
│ LH │ Lufthansa │ 0402 │ 2017-12-18 │ FRA │ … │ 2017-12-18 │ 666.0000 │ EUR │ EUR │
├───────────┴───────────┴───────────┴────────────┴───────────┴───┴────────────┴───────────────┴─────────┴──────────┤
│ 24 rows (20 shown) 14 columns (9 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘For details on the individual functions have a look at the RFC Reference.