SAP Data in Your R Analytics Workflows – ERPL - Connect DuckDB to the SAP ecosystem

Extract, transfer, and load (ETL) SAP Data in R using RFC

The ERPL DuckDB Extension for SAP revolutionizes the development of ETL applications and pipelines in R, specifically designed for SAP data.

Harnessing the power of R’s comprehensive module ecosystem, the ERPL DuckDB Extension facilitates quick deployment and smoother system integration. Our DuckDB Connector for SAP is your key to crafting high-performance applications and pipelines focused on extracting, transforming, and loading SAP data. This guide illustrates the process of connecting to SAP using the ERPL DuckDB Extension in R, coupled with DataFrames, to manage SAP data effectively and efficiently.

Our SAP Connector is engineered for advanced, optimized data processing, guaranteeing superior performance with real-time SAP data in R It adeptly navigates complex SQL queries to SAP, strategically directing supported SQL operations like filters and aggregations straight to SAP. For operations not inherently supported by SAP, such as certain SQL functions and JOIN operations, our connector leverages the embedded SQL engine to process these on the client side, ensuring comprehensive and efficient data handling

Query SAP table and join with local data

A first example demonstrates how to join two SAP tables with an external table. We’ll be using the ABAP Flight Reference Scenario, specifically joining the SFLIGHT and SPFLI tables which contain flight and flight schedule details respectively, with an external table WEATHER that holds weather information. We will extract flight information and associated temperatures at departure and arrival cities.

This example can also be found in the example (SAP Flight Demo Python) folder of our ERP extension.

Import DuckDB & load ERPL extension

This section offers a step-by-step guide on integrating DuckDB with the ERPL extension for R-based SAP data analytics. It includes instructions on importing DuckDB, configuring connections, and details on installing and loading the ERPL extension. An example script illustrates the process, emphasizing the activation of the ERPL Trampoline Extension for efficient SAP data handling. Additionally, the section links to the ERPL installation guide, providing detailed instructions for setting up the required environment.

For detailed installation instructions, please refer to the ERPL Installation Guide.

library(duckdb)

# You need to set allow_unsigned_extensions to true for Loading the ERPL extension 
config <- list()
config["allow_unsigned_extensions"] <- "true"

drv <- duckdb(config=config)
con <- dbConnect(drv)

# Install & Load ERPL extension
dbExecute(con, "INSTALL '[PATH TO ERPL EXTENSION]/erpel.duckdb_extension';")
dbExecute(con, "LOAD erpel;")
# Alternative:
# dbExecute(con, "LOAD '[PATH TO ERPL EXTENSION]/erpel.duckdb_extension';")

# Setup connection parameters
dbExecute(con, "SET sap_ashost = 'localhost';")
dbExecute(con, "SET sap_sysnr = '00';")
dbExecute(con, "SET sap_user = 'DEVELOPER';")
dbExecute(con, "SET sap_password = 'ABAPtr1909';")
dbExecute(con, "SET sap_client = '001';")
dbExecute(con, "SET sap_lang = 'EN';")

You should get the following message:

-- Loading ERPL Trampoline Extension. --
(The purpose of the extension is to extract dependencies and load the ERPL implementation)
Saving ERPL SAP dependencies to '/home/jr/.duckdb/extensions/v0.9.2/linux_amd64' and loading them ... done
ERPL extension extracted and saved to /home/jr/.duckdb/extensions/v0.9.2/linux_amd64.
ERPL implementation extension installed from /home/jr/.duckdb/extensions/v0.9.2/linux_amd64/erpl_impl.duckdb_extension.
ERPL implementation extension loaded. For instructions how to use it visit https://erpl.io

# Test your connection by pinging the server
dbGetQuery(con, "PRAGMA sap_rfc_ping;")

# Output:
#    msg
# 1 PONG

Install our RFC DuckDB Extension for Free

SET custom_extension_repository = 'http://get.erpl.io';
INSTALL erpl;

After the successfull installation all queries shown in the Python can also be executed in the R environment. Our Python example data extraction and transformation example can be found in Connecting Python with SAP