SAP Integration in Motherduck – ERPL - Connect DuckDB to the SAP ecosystem

Note

Motherduck needs currently DuckDB version 0.9.2.

What is Motherduck?

MotherDuck enhances DuckDB, an efficient in-memory analytical database, with additional tools and extensions. It offers a streamlined approach to data analysis and processing, simplifying the management and deployment of DuckDB instances. Suitable for development and production, it provides a robust solution for efficient data analytics capabilities without the complexity of larger database systems.

Approach 1: Direct Upload to Motherduck

Start DuckDB & install and load the ERPL extension

The following examples are executed in the CLI, but are easily replicatable in any other language that supports DuckDB. Let’s start. First, we need to start DuckDB from CLI using the --unsigned flag.

duckdb --unsigned

Install and load the RFC extension and set the SAP login parameters:

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

SET sap_ashost = 'localhost';
SET sap_sysnr = '00';
SET sap_user = 'DEVELOPER';
SET sap_password = 'ABAPtr1909';
SET sap_client = '001';
SET sap_lang = 'EN';

As we want to replicate the SAP data to Motherduck, a connection needs to be established using our Motherduck token. So, let’s load the Motherduck extension, set the Token, and establish the connection. Look at the Motherduck documentation on how to do it the other way around (first connecting to Motheduck and afterwards to your local database).

LOAD motherduck;
SET motherduck_token='<TOKEN>';
PRAGMA MD_CONNECT;

Look at the Motherduck help pages on how to get the service token for your account.

Create Database in Motherduck

Now, let’s create and activate the database for the SAP data in Motherduck.

CREATE DATABASE SAP;
USE 'SAP';

Query your SAP Database using RFC and Copy Data to Motherduck

Create the query for data you want to replicate to Motherduck. In this example we will use RFC for extracting data to DuckDB. Have a look at ODP or BW help pages on how to create ODP and BICS queries.

CREATE OR REPLACE TABLE sflight AS (SELECT * FROM sap_rfc_invoke('BAPI_FLIGHT_GETLIST', path='/FLIGHT_LIST'))

Analyse Data Extract in Motherduck

Switch to the Webinterface of Motherduck and start analysing your SAP data extract using SQL.

Motherduck Webinterface

Approach 2: Query SAP Data to Local Database, do your Transformations, and push Database to Motherduck

duckdb sap_data.duckdb --unsigned

Install and load the RFC extension and set the SAP login parameters:

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

SET sap_ashost = 'localhost';
SET sap_sysnr = '00';
SET sap_user = 'DEVELOPER';
SET sap_password = 'ABAPtr1909';
SET sap_client = '001';
SET sap_lang = 'EN';

Query your SAP Database using RFC, Copy Data to Your Local Database, and Perform Transformations

CREATE TABLE sflight AS (
SELECT 
  * 
FROM sap_rfc_invoke('BAPI_FLIGHT_GETLIST', path='/FLIGHT_LIST')
);

Perform your transformations.

CREATE OR REPLACE TABLE sflight_transformed AS (SELECT * FROM sflight);

Push your local Database to Motherduck

You have to first load the Motherduck extension (see the instructions from the Motherduck documentation).

LOAD motherduck;

Afterwards, you are able to copy your database to Motherduck.

CREATE DATABASE sap_data FROM CURRENT_DATABASE();