Disclaimer: This extension is currently in an experimental state. Feel free to try it out, but be aware that minimal testing and benchmarking were done.
With ERPL, use Enterprise Data in your Data Science and ML pipelines within minutes!
Install with two lines of code in DuckDB (unsigned option must be set):
INSTALL 'erpl' FROM 'http://get.erpl.io';
LOAD 'erpl';The primary objective of this DuckDB extension is to facilitate seamless integration with the SAP data ecosystem. Our approach prioritizes:
We focus predominantly on two main use cases:
Please be aware that DataZoo GmbH is the independent developer of this extension and does not hold any affiliation with DuckDB Labs or the DuckDB Foundation. "DuckDB" is a trademark registered by the DuckDB Foundation.
Our development journey is underway, with a functioning prototype available that facilitates:
Transparency is our ethos, and in line with this, we are planning a commercial trajectory for the extension, structured as follows:
For inquiries, potential collaborations, or if your curiosity is piqued, connect with us at https://erpl.io.
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.
To start with, we assume you have setup DuckDB and already installed the ERPL extension (see below) and have access to an SAP system having the ABAP Flight Reference Scenario data.
First we start DuckDB with the -unsigned - flag set. Then we load the extension and configure the necessary credentials to connect to the SAP system:
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';In our case we use the docker based ABAP Platform Trial. The credentials are set by default, details can be found in the documentation of the docker image.
Now we explore the schema of the three tables tables:
The actual example joins this three tables:
SELECT
f.CARRID,
f.CONNID,
f.FLDATE,
s.CITYFROM as CITY_FROM,
ROUND(w_from.TEMPERATURE, 1) as TEMP_FROM,
s.CITYTO as CITY_TO,
ROUND(w_to.TEMPERATURE, 1) as TEMP_TO,
FROM sap_read_table('SFLIGHT') AS f
JOIN sap_read_table('SPFLI') AS s
ON (f.MANDT = s.MANDT AND f.CARRID = s.CARRID AND f.CONNID = s.CONNID)
JOIN "WEATHER.csv" AS w_from
ON (f.FLDATE = w_from.FLDATE AND s.COUNTRYFR = w_from.COUNTRY AND s.CITYFROM = w_from.CITY)
JOIN "WEATHER.csv" AS w_to
ON (f.FLDATE = w_to.FLDATE AND s.COUNTRYTO = w_to.COUNTRY AND s.CITYTO = w_to.CITY)
ORDER BY 1, 2, 3
LIMIT 25This SQL query performs the following operations:
SFLIGHT using ERPL's sap_read_table, aliasing it as f.sap_read_table we join SPFLI (aliased as s) on MANDT, CARRID, and CONNID to get the flight's city of origin and destination.w_from and w_to, matching on flight date and respective cities' country and name for departure and arrival.CARRIER_ID, CONNECTION_ID, and FLIGHT_DATE.The output of this query will provide a comprehensive view of the flights, including their departure and arrival cities, and the corresponding temperatures, thus offering valuable insights for flight operations analysis.
| CARRID | CONNID | FLDATE | CITY_FROM | TEMP_FROM | CITY_TO | TEMP_TO |
|:---------|---------:|:--------------------|:------------|------------:|:--------------|----------:|
| AA | 0017 | 2016-11-15 00:00:00 | NEW YORK | 28.3 | SAN FRANCISCO | 19.8 |
| AA | 0017 | 2017-02-03 00:00:00 | NEW YORK | 18.9 | SAN FRANCISCO | 17.2 |
| AA | 0017 | 2017-04-24 00:00:00 | NEW YORK | 14.7 | SAN FRANCISCO | 16.2 |
| AA | 0017 | 2017-07-13 00:00:00 | NEW YORK | 16.8 | SAN FRANCISCO | 22.5 |
| AA | 0017 | 2017-10-01 00:00:00 | NEW YORK | 14.6 | SAN FRANCISCO | 28.3 |
| AA | 0017 | 2017-12-20 00:00:00 | NEW YORK | 13 | SAN FRANCISCO | 21.4 |
| AZ | 0555 | 2016-11-15 00:00:00 | ROME | 20.6 | FRANKFURT | 24.2 |
| AZ | 0555 | 2017-02-03 00:00:00 | ROME | 13.1 | FRANKFURT | 24.1 |
| AZ | 0555 | 2017-04-24 00:00:00 | ROME | 20.7 | FRANKFURT | 24.6 |
| AZ | 0555 | 2017-07-13 00:00:00 | ROME | 14 | FRANKFURT | 16.3 |
| AZ | 0555 | 2017-10-01 00:00:00 | ROME | 20.9 | FRANKFURT | 15.3 |
| AZ | 0555 | 2017-12-20 00:00:00 | ROME | 23.8 | FRANKFURT | 24.7 |
| AZ | 0789 | 2016-11-15 00:00:00 | TOKYO | 29.7 | ROME | 20.6 |
| AZ | 0789 | 2017-02-03 00:00:00 | TOKYO | 23.4 | ROME | 13.1 |
| AZ | 0789 | 2017-04-24 00:00:00 | TOKYO | 28.5 | ROME | 20.7 |
| AZ | 0789 | 2017-07-13 00:00:00 | TOKYO | 19.4 | ROME | 14 |
| AZ | 0789 | 2017-10-01 00:00:00 | TOKYO | 19.8 | ROME | 20.9 |
| AZ | 0789 | 2017-12-20 00:00:00 | TOKYO | 15.6 | ROME | 23.8 |
| DL | 0106 | 2016-11-13 00:00:00 | NEW YORK | 9.1 | FRANKFURT | 13.8 |
| DL | 0106 | 2017-02-01 00:00:00 | NEW YORK | 24.3 | FRANKFURT | 23.9 |
| DL | 0106 | 2017-04-22 00:00:00 | NEW YORK | 15.8 | FRANKFURT | 23.5 |
| DL | 0106 | 2017-07-11 00:00:00 | NEW YORK | 20.7 | FRANKFURT | 30.6 |
| DL | 0106 | 2017-09-29 00:00:00 | NEW YORK | 16.3 | FRANKFURT | 19.1 |
| DL | 0106 | 2017-12-18 00:00:00 | NEW YORK | 14.4 | FRANKFURT | 21.8 |
| JL | 0407 | 2016-11-17 00:00:00 | TOKYO | 20.3 | FRANKFURT | 20.4 |This example can also be found in its Python, R, or NODEJS version in the examples folder.
Building extensions for DuckDB can be challenging due to the varying C++ compiler and library ecosystem. This variability often leads to incompatibilities between locally built extensions and the centrally distributed DuckDB binary, primarily due to differences in the Application Binary Interface (ABI).
To ensure compatibility and ease of use, we follow a build process similar to that of the DuckDB team. Our advice is to start with the pre-compiled binaries available in our GitHub releases. For those interested in building the extension themselves, our development instructions provide detailed guidance.
The assets in each release follow this naming convention:
erpl-${DUCKDB_VERSION}-extension-{OS}-{ARCH}.tar.gzChoose the binary that matches your usage scenario. The table below summarizes the available binaries for various platforms and use cases:
| Usage | Operating System | Architecture |
|---|---|---|
| DuckDB CLI | Linux | amd64_gcc4 |
| Python | Linux | amd64 |
| R | Linux | amd64 |
| Julia | Linux | amd64 |
| Node.js | Linux | amd64 |
| DuckDB CLI | Windows | amd64 |
| Python | Windows | amd64 |
| R | Windows | amd64 |
| Julia | Windows | amd64 |
| Node.js | Windows | amd64 |
| DuckDB CLI | OSX | amd64 |
| Python | OSX | amd64 |
| R | OSX | amd64 |
| Julia | OSX | amd64 |
| Node.js | OSX | amd64 |
| DuckDB CLI | OSX | arm64 |
| Python | OSX | arm64 |
| R | OSX | arm64 |
| Julia | OSX | arm64 |
| Node.js | OSX | arm64 |
Installation of the ERPL extension is straightforward. Please note that this extension is independent of the DuckDB Foundation and DuckDB Labs, meaning the binaries are unsigned. Consequently, DuckDB must be initiated with the -unsigned flag. Detailed instructions on this process can be found in the DuckDB documentation.
-unsigned flag as described in the DuckDB documentation.INSTALL 'path/to/erpl.duckdb_extension';
LOAD 'erpl';Upon successful installation and loading, the extension will output the following message:
-- Loading ERPL Trampoline Extension. --
(Saves ERPL SAP dependencies to '/home/jr/.duckdb/extensions/v0.9.2/linux_amd64' and loads them)
ERPL extension saved and loaded from /home/jr/.duckdb/extensions/v0.9.2/linux_amd64/erpl_impl.duckdb_extension.
For usage instructions, visit https://erpl.ioThe ERPL extension is composed of two parts:
The erpl_init function in the trampoline extension bundles and extracts dependencies into the DuckDB extension folder. Post-installation, the directory ~/.duckdb/extensions/v0.9.2/linux_amd64 should contain the following files:
-rw-r--r-- 1 jr jr 110M 26. Nov 10:23 erpl.duckdb_extension
-rw-r--r-- 1 jr jr 34M 26. Nov 10:35 erpl_impl.duckdb_extension
-rw-r--r-- 1 jr jr 20M 26. Nov 10:35 libicudata.so.50
-rw-r--r-- 1 jr jr 12M 26. Nov 10:35 libicui18n.so.50
-rw-r--r-- 1 jr jr 8,4M 26. Nov 10:35 libicuuc.so.50
-rw-r--r-- 1 jr jr 9,5M 26. Nov 10:35 libsapnwrfc.so
-rw-r--r-- 1 jr jr 1,1M 26. Nov 10:35 libsapucum.soThis revised section aims for a clearer, more structured presentation of the installation process, ensuring users can easily understand and follow the steps.
Our extension automatically collects basic usage data to enhance its performance and gain insights into user engagement. We employ Posthog for data analysis, transmitting information securely to the European Posthog instance at https://eu.posthog.com via HTTPS.
Each transmitted request includes the following information:
Data is transmitted under these circumstances:
Users can control tracking through these settings:
Enable/Disable Tracking:
SET erpl_telemetry_enabled = TRUE; -- Enabled by default; set to FALSE to disable trackingPosthog API Key Configuration (usually unchanged):
SET erpl_telemetry_key = 'phc_XXX'; -- Pre-set to our key; customizable to your own keyThis approach ensures transparency about data collection while offering users control over their privacy settings.
The ERPL extension is licensed under the Business Source License (BSL) Version 1.1. The BSL is a source-available license that gives you the following permissions:
This summary is based on the provided license text and should be used as a guideline. For legal advice or clarification on specific points, consulting a legal professional is recommended, especially for commercial or complex use cases.