19 posts tagged with "DuckDB"

Every integration tool has two speeds: the one in the benchmark, and the one you actually experience on the first day. For erpl-rev those two numbers were embarrassingly far apart. It replicates 10 million BSEG-shaped rows in about a minute, and 2.5× faster than that since August. Getting to the point where you could run that benchmark took an afternoon.

Not because anything was hard. Because it was scattered. A type-T destination here. A function group there. Eight Z_DUCKDB_* function modules. Fourteen ABAP objects. A reginfo line in a file only Basis can touch. Four documents, each correct, none of them the whole path.

This post is the whole path, and it is now short enough to time with a stopwatch.

Most SAP extracts are narrow. You want three columns out of a table with a hundred million rows, on a schedule, and you want it to finish before the batch window closes. That shape is the normal case for an incremental load — and it was the one shape sap_read_table could not read in parallel at all.

partitions fixes that by splitting the scan across rows. On 2.9 million rows it is 4.5× faster at eight workers. Getting there meant taking apart the invariant the scan had been built on, which is the part worth writing about.

In June I wrote up erpl-rev — the little registered RFC server that lets ABAP call out into DuckDB — and it replicated 10 million rows of a 400-column BSEG-shaped table in about a minute. That post did not just claim a number, it explained why the number was what it was.

Since then I put it under a profiler to find out what was still on the table. Two things came back, pulling in opposite directions. A step that genuinely earned its place in June still had about a third of the ingest path left in it. And a constraint I had treated as fixed — SAP's proprietary RFC library — turned out to be removable altogether.

Here is a problem that shows up on every hybrid-cloud project eventually. You have a container in the cloud — an AWS ECS task, a Fargate job, an analytics or AI workload — and it needs data that lives in an on-prem network: a Postgres database, or an SAP system reachable only from inside the corporate LAN.

The data is right there. The container just can't reach it. And the moment you ask, the answer from the network team is no: no inbound firewall port to the on-prem network, no public IP on the database, no exception. That's not obstruction — it's the correct default. An open port into your ERP network is exactly what you don't want.

So the usual workarounds start: a site-to-site VPN, a bastion host with SSH port-forwards, an IP allowlist someone has to maintain, a reverse proxy in a DMZ. Each one is a standing piece of infrastructure, and each one is a thing that can be misconfigured into an open door.

There's a smaller way to do this, and it doesn't open a port at all.

In Part 1 we recovered the classic RFC wire format from packet captures — the NI frame, the APPC header, the EBCDIC fossil, the self-terminating TLV fields — and validated a pure-Rust implementation byte-for-byte against SAP's own library and a live server. That protocol has been the way to talk to an ABAP system for two decades.

But it is no longer the only way. Recent NetWeaver kernels added a second transport for the same RFC protocol: wsRFC, RFC carried over a TLS WebSocket. Same payload, entirely different envelope. It is the transport SAP itself reaches for in cloud and reverse-proxy scenarios, where a raw gateway socket on port 33NN is awkward and an HTTPS port everyone already trusts is not.

erpl now speaks it too — the transport and every practical sign-on method, validated live against a real ABAP system, in pure Rust. This post lays out how wsRFC works on the wire and how a client authenticates.

Every SAP integration built in the last two decades rests on one shared library: libsapnwrfc.so, SAP's closed-source NetWeaver RFC SDK. It is the thing that actually speaks to an ABAP system. It is also a black box — a binary you download, that pulls in ICU, that you cannot read, cannot audit, and cannot ship inside your own product without a redistribution dance.

We wanted erpl — our DuckDB extension for SAP — to not need it. So we wrote a pure-Rust replacement for the RFC protocol, erpl-proto: no SDK, no ICU, no post-install download, just Rust that opens a socket and talks. To write it, we first had to learn a protocol that has no public specification.

This post is what we learned: the SAP RFC wire format, byte by byte. You can read a real record below.

If you have ever worked an SAP EDI or ALE interface, you have seen an IDoc file: a flat text file where every line is exactly 1063 characters, and the payload — the field called SDATA — is a 1000-character run with no delimiters. No commas, no tags, no header. Just a fixed-width blob whose meaning lives in a segment definition inside SAP.

That's the problem. To read what's actually in an IDoc you open SAP: transaction WE60 for the segment layout, WE02 to look at the document. Outside SAP, an IDoc file is opaque. You can't grep it in any useful way, you can't load it into a warehouse, and you certainly can't diff two of them field by field.

erpl_idoc is a DuckDB extension that fixes that. It reads IDoc files as SQL tables, decodes the SDATA blob into typed columns, writes byte-valid IDocs back out from a query, and converts flat ⇄ IDoc-XML — all in plain SQL, and all offline. No SAP connection, no RFC libraries, no middleware.

And it goes both ways. The same extension that lands an inbound file as a table also produces an outbound one: compose the records in SQL, COPY them to disk, and you get a byte-valid IDoc a SAP file-port will accept. Read and write, on one file engine.

In Part 1 we extracted a BW system's metadata into a single DuckDB file with erpl-adt catalog sync — 53 cubes, 226 queries, every characteristic and key figure, all the lineage between them. Browsable, searchable, and completely decoupled from SAP.

Now we do the interesting thing. We unplug.

No SAP credentials in this post. No VPN, no connection, no --host. Just catalog.duckdb — the file from Part 1 — and an AI agent. The goal: take one cube (0D_NW_C01, "Actual for NW Demo") and its flagship query (0D_FC_NW_C01_Q0008, "YTD Sales by Sales Org") and reverse-engineer them into runnable dbt models on DuckDB. Reconstruct the star schema, the measures, and the year-to-date logic — from metadata alone, offline.

Here is exactly how it went.

You inherit a BW system the way you inherit an old house: with the previous owner already gone and no manual in the drawer. There is a cube called 0D_NW_C01. There is a query called 0D_FC_NW_C01_Q0008. Somebody, once, knew exactly what each of them meant. That person left, and the knowledge left with them.

This is one of the quiet costs of enterprise data work: the metadata is in SAP, but it isn't easy to carry with you. To see what a cube contains you open the Data Warehousing Workbench. To read a query definition you open the Query Designer. To trace where a number comes from you open transaction after transaction, screenshotting as you go, because there is no other place to keep the answer.

I wanted that metadata in a file I could keep: cubes, queries, InfoObjects, characteristics, key figures, CDS views, and the links between them — something I could reason about without SAP in the loop.

That is what the erpl-adt catalog does. This post is Part 1: extract it, and browse it. Part 2 unplugs from SAP entirely and hands the file to an AI agent.

Picture the data team at a mid-sized manufacturer. Every night they need a fresh copy of the FI line items — BSEG and friends — in their lakehouse, where the analysts actually live: DuckDB, Parquet on object storage, the odd Iceberg table. The table is wide (hundreds of columns) and deep (tens of millions of rows), and the window is tight. The "official" answer is SLT: stand up a replication server, license it, configure the per-table settings in LTRS, babysit the queue. It works. It is also a lot of machinery — and budget — for what is, conceptually, "read this table fast and write it somewhere open."

I wanted to know how far the cheap path goes. SAP already exposes a perfectly good remote-function interface; DuckDB already ingests at memory bandwidth. What if ABAP just called out into DuckDB — no extension inside SAP, no SLT, no staging files — and we made the read fast enough to matter?

So I built it — two days, with Claude Code as the pair programmer. The result is erpl-rev, and on a throwaway SAP developer trial (single host, over loopback) it replicated 10,000,000 rows of a 400-column BSEG-shaped table in about a minute. It's an early release — a research prototype we're opening up, not an SLT replacement yet — but the numbers were enough to convince me the cheap path is real. Here is the story, the tools, and the numbers.

← Part 1: Your SAP Data Is Already Real-Time

In Part 1, a mid-size manufacturer's forecasting team got a live, validated, DuckDB-native view of their SAP order and customer data — fresh within five minutes, anomalies caught at the gate, no warehouse in the middle.

This post is Part 2. It answers the next two questions: how does the AI agent actually reach that data, and what happens when the data is wrong?

The short version: we publish the view as a REST endpoint and an MCP tool with flAPI so the agent can call it, and when the agent finds a bad number it reads the ABAP behind the view, writes a patch, and files a transport with erpl-adt.

Picture a forecasting team at an industrial-controls Mittelstand. Their job for the next quarter: launch an AI-driven inventory and demand-forecasting tool that the operations team will actually trust enough to act on. They have the model. They have the GPUs. They cannot ship.

The reason: their nightly ETL job runs at 02:00 and finishes around 04:00, by which point the model trains on data that's already half a day old. By the time the forecast reaches the procurement system at 08:00, it's reasoning about a world that existed fourteen hours ago. When the CFO asks why the model recommended ordering more of an item that just sold out at lunchtime, nobody has a good answer — because the data the model saw never knew the lunchtime existed.

The fix isn't a better ETL. The fix is removing the ETL.

Summary​

This article compares ERPL's approach to SAP integration with traditional methods, highlighting performance improvements, ease of use, and cost benefits. We'll examine real-world scenarios and provide concrete examples of how ERPL transforms SAP data integration workflows.

Summary​

Learn how to build real-time SAP dashboards using ERPL and DuckDB. This comprehensive guide covers everything from data extraction to dashboard creation, including practical examples and best practices for monitoring your SAP systems in real-time.

Summary​

Discover how ERPL transforms SAP data integration for data science workflows. This comprehensive guide covers everything from data extraction to machine learning model deployment, showing how ERPL enables data scientists to work directly with SAP data using familiar tools and frameworks.

Summary​

DuckDB is a high-performance SQL OLAP database system known for its efficiency in handling analytical queries. However, accessing and analyzing public holiday data across different countries and date ranges can be challenging. By leveraging the ERPL-Web extension, you can seamlessly fetch and process this data within DuckDB.

We demonstrate how to retrieve public holiday data for Germany, showcasing the ease and power of this approach. This example highlights how DuckDB and ERPL-Web can streamline your data workflows by connecting your SQL environment with external data sources efficiently.

Summary​

This article demonstrates how to integrate SAP BW data in Microsoft Fabric using ERPL, providing a comprehensive guide for connecting enterprise SAP systems to Microsoft's modern data platform.

Summary​

• Airbyte is one of the most popular integration solutions. However it is lacking access to enterprise data sources from SAP. We show how with our ERPL Airbyte connector one can bridge this gap.

Summary​

• The article guides on integrating Excel with Parquet files using DuckDB, highlighting the efficiency of DuckDB for large data sets and how it surpasses Excel's normal data handling limits.
• It includes step-by-step instructions for installing the DuckDB ODBC driver, configuring Excel, setting up the ODBC connection, and suggests using Power Query Desktop for data transformation.