Querying SAP S/4HANA Data as a Graph
Summary
In this tutorial, you will:
- Start PuppyGraph next to a small bridge that exposes SAP OData APIs as PostgreSQL tables.
- Connect it to the SAP Business Accelerator Hub sandbox, an SAP S/4HANA Cloud system with SAP's demo company.
- Model suppliers, purchase orders, materials, bills of material, sales orders and customers as a graph, and query it live over OData.
Experimental
Reading SAP through OData is experimental. See Limitations.
PuppyGraph reads SAP data through SAP's own OData APIs, so it needs no database login and copies no data. The bridge is a PostgreSQL container whose tables are SAP OData entity sets, published as puppygraph/sap-odata-bridge and built on multicorn2 and a foreign data wrapper, odata_fdw. PuppyGraph connects to it as a PostgreSQL data source. When a graph query reads a table, the bridge sends an OData request to SAP, passing the query's conditions as $filter and the columns it needs as $select. If you can reach the SAP HANA database directly, see Connecting to SAP HANA instead.
Prerequisites
Please ensure that docker compose is available. The installation can be verified by running:
See https://docs.docker.com/compose/install/ for Docker Compose installation instructions. Ports 8081, 8182 and 7687 must be free, and the machine needs internet access to Docker Hub and sandbox.api.sap.com.
You also need an SAP account (registration at https://api.sap.com is free) and a browser for the PuppyGraph Web UI.
Setup
Create a folder
Create a folder for the tutorial:
Get an SAP API key
Sign in at https://api.sap.com, open an API such as Business Partner (A2X) and choose Show API Key.
Save the key in
secrets/sap_apikey without echoing it into your shell history:
mkdir -p secrets && chmod 700 secrets
read -rs -p "SAP API key: " key && printf '%s' "$key" > secrets/sap_apikey && unset key
chmod 644 secrets/sap_apikey
The bridge reads the key as another user inside its container, hence chmod 644. The key is personal: do not share it or commit it.
Deployment
Create a file
docker-compose.yaml with the following content:
docker-compose.yaml
services:
sap-bridge:
image: puppygraph/sap-odata-bridge:0.1.2-pg16
container_name: sap-bridge
environment:
- POSTGRES_DB=sap
- POSTGRES_USER=sap
- POSTGRES_PASSWORD=sap-odata-demo
- SAP_ODATA_BASE=https://sandbox.api.sap.com/s4hanacloud/sap/opu/odata/sap
# schema=SERVICE:EntitySet,...; SAP keeps partners, purchasing, sales, products and BOMs in separate services.
- SAP_ODATA_SERVICES=bp=API_BUSINESS_PARTNER:A_Supplier,A_Customer;po=API_PURCHASEORDER_PROCESS_SRV:A_PurchaseOrder,A_PurchaseOrderItem;so=API_SALES_ORDER_SRV:A_SalesOrder,A_SalesOrderItem;product=API_PRODUCT_SRV:A_Product;bom=API_BILL_OF_MATERIAL_SRV:A_BillOfMaterial,A_BillOfMaterialItem
- SAP_ODATA_API_KEY_FILE=/run/secrets/sap_apikey
- SAP_ODATA_BATCH_CHUNK=40
# SAP's sandbox is shared: refuse unfiltered reads of entity sets larger than this.
- SAP_ODATA_MAX_UNFILTERED_ROWS=50000
volumes:
- ./secrets/sap_apikey:/run/secrets/sap_apikey:ro
puppygraph:
image: puppygraph/puppygraph:latest
container_name: puppygraph
environment:
- PUPPYGRAPH_USERNAME=puppygraph
- PUPPYGRAPH_PASSWORD=puppygraph123
# Column statistics read every column in full; through OData that is a full download of each entity set.
- STATISTICS_WARMUP_ENABLED=false
- AUTO_ANALYZE_EXTERNAL_CATALOG=false
ports:
- "127.0.0.1:8081:8081"
- "127.0.0.1:8182:8182"
- "127.0.0.1:7687:7687"
The bridge reads five SAP APIs, one PostgreSQL schema each:
| Schema | SAP API | Tables |
|---|---|---|
bp |
API_BUSINESS_PARTNER |
a_supplier, a_customer |
po |
API_PURCHASEORDER_PROCESS_SRV |
a_purchaseorder, a_purchaseorderitem |
so |
API_SALES_ORDER_SRV |
a_salesorder, a_salesorderitem |
product |
API_PRODUCT_SRV |
a_product |
bom |
API_BILL_OF_MATERIAL_SRV |
a_billofmaterial, a_billofmaterialitem |
The sandbox is shared by every SAP user, so SAP_ODATA_MAX_UNFILTERED_ROWS makes the bridge refuse to read a whole entity set of more than 50,000 rows. Queries that start from a given supplier, part or customer stay small.
PuppyGraph's automatic table statistics would read each entity set in full through OData, so the compose file turns them off with STATISTICS_WARMUP_ENABLED=false and AUTO_ANALYZE_EXTERNAL_CATALOG=false.
Default passwords
The compose file ships with default passwords for convenience and publishes its ports on 127.0.0.1 only. Change the password environment variables before running on a shared machine.
Start the stack:
On its first start the bridge reads each SAP service's metadata and creates its tables, which takes a few seconds.
Wait until the bridge accepts connections:
until docker exec sap-bridge pg_isready -q -h 127.0.0.1 -U sap -d sap 2>/dev/null; do
[ "$(docker inspect -f '{{.State.Running}}' sap-bridge)" = true ] || { echo "The bridge stopped. See: docker logs sap-bridge"; break; }
sleep 3
done
If the bridge stops, see Troubleshooting.
Modeling a Graph
The graph has 6 node types and 6 edge types:
| Edge | From | To | Meaning |
|---|---|---|---|
ISSUED_PO |
Supplier | PurchaseOrder | The supplier of a purchase order. |
CONTAINS |
PurchaseOrder | Material | A material ordered on a purchase order. |
HAS_BOM |
Material | Bom | A bill of material that builds the material. |
HAS_COMPONENT |
Bom | Material | A component in a bill of material. |
ORDERED_IN |
Material | SalesOrder | A material sold on a sales order. |
PLACED_BY |
SalesOrder | Customer | The customer who placed a sales order. |
Create a file
schema.json with the following content:
schema.json
{
"catalog": [
{
"name": "sap_sandbox",
"type": "postgresql",
"jdbc": {
"username": "sap",
"password": "sap-odata-demo",
"jdbcUri": "jdbc:postgresql://sap-bridge:5432/sap",
"driverClass": "org.postgresql.Driver"
}
}
],
"node": [
{
"label": "Supplier",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "bp",
"table": "a_supplier",
"mappedField": [
{
"sourceFieldName": "supplier",
"targetFieldName": "supplier_id"
},
{
"sourceFieldName": "suppliername",
"targetFieldName": "name"
}
]
}
},
"id": [
{
"name": "supplier_id",
"type": "String"
}
],
"attribute": [
{
"name": "supplier_id",
"type": "String"
},
{
"name": "name",
"type": "String"
}
]
},
{
"label": "Customer",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "bp",
"table": "a_customer",
"mappedField": [
{
"sourceFieldName": "customer",
"targetFieldName": "customer_id"
},
{
"sourceFieldName": "customername",
"targetFieldName": "name"
}
]
}
},
"id": [
{
"name": "customer_id",
"type": "String"
}
],
"attribute": [
{
"name": "customer_id",
"type": "String"
},
{
"name": "name",
"type": "String"
}
]
},
{
"label": "PurchaseOrder",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "po",
"table": "a_purchaseorder",
"mappedField": [
{
"sourceFieldName": "purchaseorder",
"targetFieldName": "po_number"
},
{
"sourceFieldName": "companycode",
"targetFieldName": "company_code"
},
{
"sourceFieldName": "purchaseorderdate",
"targetFieldName": "po_date"
},
{
"sourceFieldName": "documentcurrency",
"targetFieldName": "currency"
}
]
}
},
"id": [
{
"name": "po_number",
"type": "String"
}
],
"attribute": [
{
"name": "po_number",
"type": "String"
},
{
"name": "company_code",
"type": "String"
},
{
"name": "po_date",
"type": "DateTime"
},
{
"name": "currency",
"type": "String"
}
]
},
{
"label": "Material",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "product",
"table": "a_product",
"mappedField": [
{
"sourceFieldName": "product",
"targetFieldName": "material_id"
},
{
"sourceFieldName": "producttype",
"targetFieldName": "type"
},
{
"sourceFieldName": "productgroup",
"targetFieldName": "material_group"
},
{
"sourceFieldName": "baseunit",
"targetFieldName": "unit"
}
]
}
},
"id": [
{
"name": "material_id",
"type": "String"
}
],
"attribute": [
{
"name": "material_id",
"type": "String"
},
{
"name": "type",
"type": "String"
},
{
"name": "material_group",
"type": "String"
},
{
"name": "unit",
"type": "String"
}
]
},
{
"label": "Bom",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "bom",
"table": "a_billofmaterial",
"mappedField": [
{
"sourceFieldName": "billofmaterialheaderuuid",
"targetFieldName": "bom_uuid"
},
{
"sourceFieldName": "billofmaterial",
"targetFieldName": "bom"
},
{
"sourceFieldName": "billofmaterialvariant",
"targetFieldName": "variant"
},
{
"sourceFieldName": "plant",
"targetFieldName": "plant"
}
]
}
},
"id": [
{
"name": "bom_uuid",
"type": "String"
}
],
"attribute": [
{
"name": "bom_uuid",
"type": "String"
},
{
"name": "bom",
"type": "String"
},
{
"name": "variant",
"type": "String"
},
{
"name": "plant",
"type": "String"
}
]
},
{
"label": "SalesOrder",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "so",
"table": "a_salesorder",
"mappedField": [
{
"sourceFieldName": "salesorder",
"targetFieldName": "so_number"
},
{
"sourceFieldName": "salesorderdate",
"targetFieldName": "order_date"
},
{
"sourceFieldName": "totalnetamount",
"targetFieldName": "net_value"
},
{
"sourceFieldName": "transactioncurrency",
"targetFieldName": "currency"
},
{
"sourceFieldName": "overalldeliverystatus",
"targetFieldName": "delivery_status"
}
]
}
},
"id": [
{
"name": "so_number",
"type": "String"
}
],
"attribute": [
{
"name": "so_number",
"type": "String"
},
{
"name": "order_date",
"type": "DateTime"
},
{
"name": "net_value",
"type": "Decimal"
},
{
"name": "currency",
"type": "String"
},
{
"name": "delivery_status",
"type": "String"
}
]
}
],
"edge": [
{
"label": "ISSUED_PO",
"fromNodeLabel": "Supplier",
"toNodeLabel": "PurchaseOrder",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "po",
"table": "a_purchaseorder",
"mappedField": [
{
"sourceFieldName": "purchaseorder",
"targetFieldName": "po_id"
},
{
"sourceFieldName": "supplier",
"targetFieldName": "supplier_id"
},
{
"sourceFieldName": "purchaseorder",
"targetFieldName": "po_number"
}
]
}
},
"id": [
{
"name": "po_id",
"type": "String"
}
],
"fromKey": [
{
"name": "supplier_id",
"type": "String"
}
],
"toKey": [
{
"name": "po_number",
"type": "String"
}
],
"attribute": []
},
{
"label": "CONTAINS",
"fromNodeLabel": "PurchaseOrder",
"toNodeLabel": "Material",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "po",
"table": "a_purchaseorderitem",
"mappedField": [
{
"sourceFieldName": "purchaseorder",
"targetFieldName": "po_number"
},
{
"sourceFieldName": "purchaseorderitem",
"targetFieldName": "po_item"
},
{
"sourceFieldName": "purchaseorder",
"targetFieldName": "from_po"
},
{
"sourceFieldName": "material",
"targetFieldName": "material_id"
},
{
"sourceFieldName": "orderquantity",
"targetFieldName": "quantity"
},
{
"sourceFieldName": "netpriceamount",
"targetFieldName": "net_price"
}
]
}
},
"id": [
{
"name": "po_number",
"type": "String"
},
{
"name": "po_item",
"type": "String"
}
],
"fromKey": [
{
"name": "from_po",
"type": "String"
}
],
"toKey": [
{
"name": "material_id",
"type": "String"
}
],
"attribute": [
{
"name": "quantity",
"type": "Decimal"
},
{
"name": "net_price",
"type": "Decimal"
}
]
},
{
"label": "HAS_BOM",
"fromNodeLabel": "Material",
"toNodeLabel": "Bom",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "bom",
"table": "a_billofmaterial",
"mappedField": [
{
"sourceFieldName": "billofmaterialheaderuuid",
"targetFieldName": "bom_id"
},
{
"sourceFieldName": "material",
"targetFieldName": "material_id"
},
{
"sourceFieldName": "billofmaterialheaderuuid",
"targetFieldName": "bom_uuid"
}
]
}
},
"id": [
{
"name": "bom_id",
"type": "String"
}
],
"fromKey": [
{
"name": "material_id",
"type": "String"
}
],
"toKey": [
{
"name": "bom_uuid",
"type": "String"
}
],
"attribute": []
},
{
"label": "HAS_COMPONENT",
"fromNodeLabel": "Bom",
"toNodeLabel": "Material",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "bom",
"table": "a_billofmaterialitem",
"mappedField": [
{
"sourceFieldName": "billofmaterialitemuuid",
"targetFieldName": "item_uuid"
},
{
"sourceFieldName": "billofmaterialheaderuuid",
"targetFieldName": "bom_uuid"
},
{
"sourceFieldName": "billofmaterialcomponent",
"targetFieldName": "component_id"
},
{
"sourceFieldName": "billofmaterialitemquantity",
"targetFieldName": "quantity"
}
]
}
},
"id": [
{
"name": "item_uuid",
"type": "String"
}
],
"fromKey": [
{
"name": "bom_uuid",
"type": "String"
}
],
"toKey": [
{
"name": "component_id",
"type": "String"
}
],
"attribute": [
{
"name": "quantity",
"type": "Decimal"
}
]
},
{
"label": "ORDERED_IN",
"fromNodeLabel": "Material",
"toNodeLabel": "SalesOrder",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "so",
"table": "a_salesorderitem",
"mappedField": [
{
"sourceFieldName": "salesorder",
"targetFieldName": "so_number"
},
{
"sourceFieldName": "salesorderitem",
"targetFieldName": "so_item"
},
{
"sourceFieldName": "material",
"targetFieldName": "material_id"
},
{
"sourceFieldName": "salesorder",
"targetFieldName": "to_so"
},
{
"sourceFieldName": "requestedquantity",
"targetFieldName": "quantity"
}
]
}
},
"id": [
{
"name": "so_number",
"type": "String"
},
{
"name": "so_item",
"type": "String"
}
],
"fromKey": [
{
"name": "material_id",
"type": "String"
}
],
"toKey": [
{
"name": "to_so",
"type": "String"
}
],
"attribute": [
{
"name": "quantity",
"type": "Decimal"
}
]
},
{
"label": "PLACED_BY",
"fromNodeLabel": "SalesOrder",
"toNodeLabel": "Customer",
"dataSourceGroup": {
"externalDataSource": {
"enabled": true,
"catalog": "sap_sandbox",
"schema": "so",
"table": "a_salesorder",
"mappedField": [
{
"sourceFieldName": "salesorder",
"targetFieldName": "so_id"
},
{
"sourceFieldName": "salesorder",
"targetFieldName": "from_so"
},
{
"sourceFieldName": "soldtoparty",
"targetFieldName": "customer_id"
}
]
}
},
"id": [
{
"name": "so_id",
"type": "String"
}
],
"fromKey": [
{
"name": "from_so",
"type": "String"
}
],
"toKey": [
{
"name": "customer_id",
"type": "String"
}
],
"attribute": []
}
]
}
Log into the PuppyGraph Web UI at http://localhost:8081:
| Field | Value |
|---|---|
| Username | puppygraph |
| Password | puppygraph123 |
Click Graph in the sidebar, then Upload JSON on the Upload Schema card. Choose Select Schema File, select
schema.json, and click Upload.
If a graph is already loaded, the Graph page shows the graph instead, and Upload in its toolbar opens the same dialog. While PuppyGraph applies the schema, the page can show "PuppyGraph is starting up" for a few seconds.
Uploading the schema reads every table to check the edges. The bridge refuses that read on the large order tables, and the upload tolerates it.
Upload via CLI
You can also POST the schema directly:
Querying the Graph
In the PuppyGraph Web UI, click Query in the sidebar. Each query below takes a few seconds and a handful of OData requests to SAP. The sandbox is live and shared, so your numbers can differ.
The following query counts the purchase orders of the supplier Domestic US Supplier 1:
MATCH (s:Supplier {supplier_id: '17300001'})-[:ISSUED_PO]->(po:PurchaseOrder)
RETURN s.name AS supplier, count(po) AS purchase_orders
It returns 925 purchase orders.
The following query finds the bills of material that use raw material RM128, and the finished goods they build:
MATCH (part:Material {material_id: 'RM128'})<-[:HAS_COMPONENT]-(b:Bom)<-[:HAS_BOM]-(fg:Material)
RETURN count(DISTINCT b) AS boms, count(DISTINCT fg) AS finished_goods, collect(DISTINCT fg.material_id)[0..12] AS examples
It returns 36 bills of material and 9 finished goods, such as FG111_A and MZ-FG-HMF01.
The following query counts the sales orders of the customer Domestic US Customer 1:
MATCH (so:SalesOrder)-[:PLACED_BY]->(c:Customer {customer_id: '17100001'})
RETURN c.name AS customer, count(so) AS sales_orders
It returns 965 sales orders.
A query that reads a whole order table without a narrowing condition fails, because the bridge refuses the read. The error message contains refused unfiltered read. This includes the order counts on the Dashboard page.
Building the bridge image yourself (optional)
The tutorial uses the published puppygraph/sap-odata-bridge image. To build it yourself instead, for example to review or change it, create the files below in the tutorial folder. The build downloads PostgreSQL 16 and multicorn2 from their official sources and checks multicorn2's checksums. Nothing from SAP is included.
Create the bridge folders:
Create a file
bridge/Dockerfile with the following content:
bridge/Dockerfile
FROM postgres:16-bookworm
# multicorn2 3.2 (PostgreSQL licence) from the PostgreSQL yum repository's source package; its
# GitHub repository no longer exists. Both the package and the tarball inside it are checked.
ARG MULTICORN_SRPM=https://download.postgresql.org/pub/repos/yum/srpms/16/redhat/rhel-9-x86_64/multicorn2_16-3.2-10PGDG.rhel9.8.src.rpm
ARG MULTICORN_SRPM_SHA256=42c816c8b4268278c938e5c7993a929370dda2eca3cb955e776922f8415a23a3
ARG MULTICORN_TAR_SHA256=e31e8f6e25acafd0dc8a2faf90a8c1e29e114abe8383e5c1041f54ab0f9bb44f
RUN apt-get update && apt-get install -y --no-install-recommends \
build-essential ca-certificates curl cpio rpm2cpio python3 python3-dev python3-pip python3-setuptools \
postgresql-server-dev-16 \
&& mkdir -p /tmp/srpm /tmp/multicorn2 && cd /tmp/srpm \
&& curl -fsSL -o multicorn2.src.rpm "$MULTICORN_SRPM" \
&& echo "$MULTICORN_SRPM_SHA256 multicorn2.src.rpm" | sha256sum -c - \
&& rpm2cpio multicorn2.src.rpm | cpio -idm --quiet v3.2.tar.gz \
&& echo "$MULTICORN_TAR_SHA256 v3.2.tar.gz" | sha256sum -c - \
&& tar xzf v3.2.tar.gz -C /tmp/multicorn2 --strip-components=1 \
&& cd /tmp/multicorn2 && make PYTHON_OVERRIDE=python3 && make install PYTHON_OVERRIDE=python3 \
&& pip3 install --break-system-packages --no-deps /tmp/multicorn2 \
&& rm -rf /tmp/srpm /tmp/multicorn2 /var/lib/apt/lists/*
COPY odata_fdw /opt/odata_fdw/odata_fdw
RUN cp -r /opt/odata_fdw/odata_fdw "$(python3 -c 'import sysconfig; print(sysconfig.get_paths()["purelib"])')/"
COPY init/ /docker-entrypoint-initdb.d/
Create a file
bridge/init/10-odata.sh with the following content. It runs on the bridge's first start: it reads the $metadata of each SAP service and creates one PostgreSQL table per entity set.
bridge/init/10-odata.sh
#!/bin/bash
# Expose SAP OData entity sets as foreign tables. Two ways to configure it:
# SAP_ODATA_URL one service root; every entity set in its $metadata goes into schema "sap"
# SAP_ODATA_SERVICES several services under SAP_ODATA_BASE, as "schema=SERVICE:EntitySet,EntitySet;..."
# (real SAP keeps purchasing, sales, products and BOMs in separate services)
# Login: SAP_ODATA_USER/SAP_ODATA_PASSWORD (Basic), or SAP_ODATA_API_KEY_FILE (APIKey header).
set -euo pipefail
if [ -n "${SAP_ODATA_SERVICES:-}" ]; then
: "${SAP_ODATA_BASE:?SAP_ODATA_BASE must be set with SAP_ODATA_SERVICES}"
SPECS=$(tr ';' '\n' <<<"$SAP_ODATA_SERVICES")
else
: "${SAP_ODATA_URL:?SAP_ODATA_URL or SAP_ODATA_SERVICES must be set}"
SAP_ODATA_BASE=${SAP_ODATA_URL%/*}
SPECS="sap=${SAP_ODATA_URL##*/}:"
fi
wait_for() {
for _ in $(seq 60); do
python3 - "$1" <<'PY' && return 0
import base64, os, sys, urllib.request
req = urllib.request.Request(sys.argv[1] + "/$metadata")
if os.environ.get("SAP_ODATA_USER"):
token = f"{os.environ['SAP_ODATA_USER']}:{os.environ.get('SAP_ODATA_PASSWORD', '')}".encode()
req.add_header("Authorization", "Basic " + base64.b64encode(token).decode())
if os.environ.get("SAP_ODATA_API_KEY_FILE"):
req.add_header("APIKey", open(os.environ["SAP_ODATA_API_KEY_FILE"]).read().strip())
urllib.request.urlopen(req, timeout=10)
PY
sleep 1
done
echo "OData service $1 did not answer" >&2
return 1
}
psql() { command psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" "$@"; }
psql -c "CREATE EXTENSION IF NOT EXISTS multicorn"
while IFS= read -r spec; do
[ -z "$spec" ] && continue
schema=${spec%%=*}; rest=${spec#*=}; service=${rest%%:*}; sets=${rest#*:}
url="$SAP_ODATA_BASE/$service"
wait_for "$url"
psql <<SQL
CREATE SERVER odata_${schema} FOREIGN DATA WRAPPER multicorn OPTIONS (
wrapper 'odata_fdw.ODataFdw',
service_url '${url}',
batch_chunk '${SAP_ODATA_BATCH_CHUNK:-40}',
in_style '${SAP_ODATA_IN_STYLE:-or}',
max_unfiltered_rows '${SAP_ODATA_MAX_UNFILTERED_ROWS:-0}',
request_log '${SAP_ODATA_REQUEST_LOG:-}'
);
CREATE SCHEMA ${schema};
SQL
if [ -n "${SAP_ODATA_USER:-}" ]; then
# psql substitutes and quotes :'name' variables in statements it reads, but not in -c.
psql -v user="$SAP_ODATA_USER" -v password="${SAP_ODATA_PASSWORD:-}" <<SQL
ALTER SERVER odata_${schema} OPTIONS (ADD user :'user', ADD password :'password');
SQL
fi
if [ -n "${SAP_ODATA_API_KEY_FILE:-}" ]; then
psql -c "ALTER SERVER odata_${schema} OPTIONS (ADD api_key_file '${SAP_ODATA_API_KEY_FILE}')"
fi
if [ -n "$sets" ]; then
limit=" LIMIT TO (${sets,,})"
else
limit=""
fi
psql -c "IMPORT FOREIGN SCHEMA odata${limit} FROM SERVER odata_${schema} INTO ${schema}"
done <<<"$SPECS"
Make it executable:
Create a file
bridge/odata_fdw/__init__.py with the following content. This is the foreign data wrapper, licensed under Apache-2.0, that turns PostgreSQL scans into OData requests. It uses only the Python standard library and multicorn.
bridge/odata_fdw/__init__.py
# Copyright 2026 PuppyGraph
# SPDX-License-Identifier: Apache-2.0
"""Multicorn foreign data wrapper that reads SAP OData v2 entity sets at query time.
Every scan becomes live HTTP: Postgres quals the wrapper can express are sent as
$filter, the referenced columns as $select. Quals it cannot express are not sent;
Postgres re-applies every qual to the returned rows, so dropping one widens the
fetch but never changes the answer.
"""
import base64
import gzip
import json
import time
import re
import urllib.error
import urllib.parse
import urllib.request
import uuid
import xml.etree.ElementTree as ET
from datetime import date, datetime, timezone
from decimal import Decimal
from multicorn import ColumnDefinition, ForeignDataWrapper, TableDefinition
from multicorn.utils import log_to_postgres, WARNING
_COMPARISON = {"=": "eq", "<>": "ne", "!=": "ne", ">": "gt", ">=": "ge", "<": "lt", "<=": "le"}
_EDM_TO_PG = {
"Edm.String": "text",
"Edm.Int16": "smallint",
"Edm.Int32": "integer",
"Edm.Int64": "bigint",
# Used when a property declares no usable Precision and Scale: PuppyGraph cannot type a
# Decimal attribute read from an unconstrained numeric column.
"Edm.Decimal": "numeric(38,10)",
"Edm.Double": "double precision",
"Edm.Boolean": "boolean",
"Edm.DateTime": "timestamp",
}
def _pg_type(prop):
"""PostgreSQL type of an OData property; Edm.Decimal keeps the Precision and Scale SAP declares."""
edm = prop.get("Type")
if edm == "Edm.Decimal":
precision, scale = prop.get("Precision", ""), prop.get("Scale", "")
if precision.isdigit() and scale.isdigit() and 0 < int(precision) <= 1000 and int(scale) <= int(precision):
return f"numeric({int(precision)},{int(scale)})"
return _EDM_TO_PG.get(edm, "text")
_NS = {
"edmx": "http://schemas.microsoft.com/ado/2007/06/edmx",
"edm": "http://schemas.microsoft.com/ado/2008/09/edm",
"sap": "http://www.sap.com/Protocols/SAPData",
}
def _literal(value, edm=None):
# SAP Gateway v2 only accepts Edm.Guid values written as guid'...'.
if edm == "Edm.Guid" and isinstance(value, str):
return f"guid'{value}'"
# Dates go out as datetime'...' / datetimeoffset'...'; Multicorn may hand timestamps over as text.
if edm in ("Edm.DateTime", "Edm.DateTimeOffset") and isinstance(value, (str, date)):
if isinstance(value, str):
value = datetime.fromisoformat(value.replace("Z", "+00:00"))
elif not isinstance(value, datetime):
value = datetime(value.year, value.month, value.day)
if edm == "Edm.DateTime":
return f"datetime'{value.replace(tzinfo=None).isoformat()}'"
if value.tzinfo is None:
value = value.replace(tzinfo=timezone.utc)
return f"datetimeoffset'{value.astimezone(timezone.utc).replace(tzinfo=None).isoformat()}Z'"
if value is None:
return "null"
if isinstance(value, bool):
return "true" if value else "false"
if isinstance(value, (int, float, Decimal)):
return str(value)
if isinstance(value, (date, datetime)):
return "'" + value.isoformat() + "'"
return "'" + str(value).replace("'", "''") + "'"
def _pushable(value):
return value is None or isinstance(value, (str, bool, int, float, Decimal, date, datetime))
def _coerce(value, type_name):
# Multicorn hands boolean qual values over as their text form ('t' / 'f').
if type_name == "boolean" and isinstance(value, str):
return value.lower() in ("t", "true")
return value
_DATE = re.compile(r"^/Date\((-?\d+)(?:[+-]\d+)?\)/$")
def _value(v):
# Real SAP Gateway v2 sends Edm.DateTime as /Date(<ms since epoch>)/.
if isinstance(v, str):
m = _DATE.match(v)
if m:
return datetime.utcfromtimestamp(int(m.group(1)) / 1000).isoformat()
return v
def _body(resp):
# SAP's API sandbox gzips responses even when the request does not ask for it.
raw = resp.read()
return gzip.decompress(raw) if raw[:2] == b"\x1f\x8b" else raw
def _api_key(options):
path = options.get("api_key_file")
if not path:
return None
with open(path) as f:
return f.read().strip()
def _log(options, entry):
path = options.get("request_log")
if path:
with open(path, "a") as f:
f.write(json.dumps(entry) + "\n")
def _narrows(url):
# A $filter made only of "<field> ne null" clauses (what an engine sends for a join key)
# still reads the whole entity set.
flt = urllib.parse.parse_qs(urllib.parse.urlsplit(url).query).get("$filter", [""])[0]
clauses = [c.strip() for c in flt.split(" and ") if c.strip()]
return any(not re.fullmatch(r"\(*\w+ ne null\)*", c) for c in clauses)
def _basic_auth(options):
if not options.get("user"):
return None
token = f"{options['user']}:{options.get('password', '')}".encode()
return "Basic " + base64.b64encode(token).decode()
def translate_quals(quals, in_style, name=lambda c: c, type_of=lambda c: None, edm_of=lambda c: None):
"""Returns ($filter clauses, list-qual to chunk or None, quals left to Postgres)."""
clauses, key_list, skipped = [], None, []
for q in quals:
op = q.operator
t = type_of(q.field_name)
edm = edm_of(q.field_name)
if isinstance(op, tuple):
base, is_any = op
values = [_coerce(v, t) for v in (q.value or [])]
if base == "=" and is_any and values and all(_pushable(v) for v in values):
if key_list is None:
key_list = (name(q.field_name), values, edm)
else:
clauses.append(_in_clause(name(q.field_name), values, in_style, edm))
continue
skipped.append(q)
continue
if op in _COMPARISON and _pushable(q.value) and not isinstance(q.value, (list, tuple)):
if q.value is None and op not in ("=", "<>", "!="):
skipped.append(q)
continue
clauses.append(f"{name(q.field_name)} {_COMPARISON[op]} {_literal(_coerce(q.value, t), edm)}")
else:
skipped.append(q)
return clauses, key_list, skipped
def _in_clause(field, values, in_style, edm=None):
if len(values) == 1:
return f"{field} eq {_literal(values[0], edm)}"
if in_style == "in":
return f"{field} in ({','.join(_literal(v, edm) for v in values)})"
return "(" + " or ".join(f"{field} eq {_literal(v, edm)}" for v in values) + ")"
class ODataFdw(ForeignDataWrapper):
"""Table options: service_url (required), entity_set (required),
key_columns (comma-separated, used for planner hints), batch_chunk (max keys
per sub-request before an IN list is split across a $batch, default 40),
in_style ('or' for SAP Gateway v2, 'in' for v4), user/password, sap_client."""
def __init__(self, options, columns):
super().__init__(options, columns)
self.service_url = options["service_url"].rstrip("/")
self.entity_set = options["entity_set"]
self.keys = [k.strip().lower() for k in options.get("key_columns", "").split(",") if k.strip()]
self.batch_chunk = int(options.get("batch_chunk", "40"))
self.in_style = options.get("in_style", "or")
self.sap_client = options.get("sap_client")
self.auth = _basic_auth(options)
self.api_key = _api_key(options)
self.options = options
# Test guard for a shared SAP system: refuse an unfiltered read of a larger entity set.
self.max_unfiltered = int(options.get("max_unfiltered_rows", "0"))
self.odata_name = {c: (d.options or {}).get("odata_name", c) for c, d in columns.items()}
self.type_name = {c: d.type_name for c, d in columns.items()}
self.edm_type = {c: (d.options or {}).get("odata_type") for c, d in columns.items()}
def get_rel_size(self, quals, columns):
rows = 100000
names = {q.field_name for q in quals if q.operator == "=" or (isinstance(q.operator, tuple) and q.operator[0] == "=")}
if self.keys and set(self.keys) <= names:
rows = 1
elif names:
rows = 50
return rows, max(len(columns), 1) * 20
def get_path_keys(self):
return [((k,), 1 if [k] == self.keys else 50) for k in self.keys]
def explain(self, quals, columns, sortkeys=None, verbose=False, limit=None, offset=None):
return [f"OData requests: {u}" for u in self._plan_urls(quals, columns)[0]]
def execute(self, quals, columns, sortkeys=None, limit=None, offset=None):
urls, skipped = self._plan_urls(quals, columns)
if skipped:
log_to_postgres(
f"odata_fdw {self.entity_set}: quals evaluated locally, not pushed: "
+ ", ".join(f"{q.field_name} {q.operator}" for q in skipped),
WARNING,
)
if self.max_unfiltered and len(urls) == 1 and not _narrows(urls[0]):
total = int(self._raw(f"{self.service_url}/{self.entity_set}/$count").decode())
if total > self.max_unfiltered:
_log(self.options, {"entity_set": self.entity_set, "refused_full_fetch": total})
raise RuntimeError(
f"odata_fdw {self.entity_set}: refused unfiltered read of {total} rows "
f"(max_unfiltered_rows={self.max_unfiltered})")
payloads = [self._get(urls[0])] if len(urls) == 1 else self._batch(urls)
for payload in payloads:
while True:
body = payload["d"]
for row in body.get("results", [body] if "results" not in body else []):
yield {c: _value(row.get(self.odata_name.get(c, c))) for c in columns}
if not body.get("__next"):
break
payload = self._get(body["__next"])
def _plan_urls(self, quals, columns):
clauses, key_list, skipped = translate_quals(
quals, self.in_style, lambda c: self.odata_name.get(c, c), self.type_name.get, self.edm_type.get
)
select = ",".join(sorted(self.odata_name.get(c, c) for c in columns)) if columns else None
groups = [None]
if key_list is not None:
field, values, edm = key_list
values = sorted(set(values), key=str)
groups = [(field, values[i:i + self.batch_chunk], edm) for i in range(0, len(values), self.batch_chunk)]
urls = []
for g in groups:
parts = list(clauses)
if g is not None:
parts.insert(0, _in_clause(g[0], g[1], self.in_style, g[2]))
params = {"$format": "json"}
if parts:
params["$filter"] = " and ".join(parts)
if select:
params["$select"] = select
if self.sap_client:
params["sap-client"] = self.sap_client
urls.append(f"{self.entity_set}?{urllib.parse.urlencode(params, quote_via=urllib.parse.quote, safe='$,()')}")
return urls, skipped
def _headers(self):
h = {"Accept": "application/json"}
if self.auth:
h["Authorization"] = self.auth
if self.api_key:
h["APIKey"] = self.api_key
return h
def _raw(self, url, data=None, headers=None, method="GET"):
start = time.time()
req = urllib.request.Request(url, data=data, headers=headers or self._headers(), method=method)
entry = {"entity_set": self.entity_set, "method": method, "url": url[len(self.service_url):]}
try:
with urllib.request.urlopen(req, timeout=120) as resp:
ctype = resp.headers.get("Content-Type", "")
body = _body(resp)
except urllib.error.HTTPError as e:
# A failed call still went to SAP: log it, so query call counts stay complete.
failed = _body(e)
_log(self.options, dict(entry, bytes=len(failed), seconds=round(time.time() - start, 3), status=e.code))
raise
except OSError as e: # URLError, timeouts, refused connections
_log(self.options, dict(entry, bytes=0, seconds=round(time.time() - start, 3), error=str(e)))
raise
_log(self.options, dict(entry, bytes=len(body), seconds=round(time.time() - start, 3)))
self._ctype = ctype
return body
def _get(self, url):
if not url.startswith(("http://", "https://")):
url = f"{self.service_url}/{url}"
return json.loads(self._raw(url))
def _batch(self, rel_urls):
boundary = f"batch_{uuid.uuid4().hex}"
body = "".join(
f"--{boundary}\r\nContent-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\n"
f"GET {u} HTTP/1.1\r\nAccept: application/json\r\n\r\n\r\n"
for u in rel_urls
) + f"--{boundary}--\r\n"
headers = self._headers()
headers["Content-Type"] = f"multipart/mixed; boundary={boundary}"
raw = self._raw(f"{self.service_url}/$batch", data=body.encode(), headers=headers, method="POST").decode()
ctype = self._ctype
resp_boundary = re.search(r"boundary=([^;]+)", ctype).group(1).strip('"')
payloads = []
for part in raw.split(f"--{resp_boundary}"):
m = re.search(r"HTTP/1\.1 (\d{3})[^\r\n]*\r\n(?:[^\r\n]+\r\n)*\r\n(.*)", part, re.S)
if not m:
continue
if m.group(1) != "200":
raise RuntimeError(f"OData $batch sub-request failed: {m.group(1)} {m.group(2).strip()[:200]}")
payloads.append(json.loads(m.group(2).strip()))
return payloads
@classmethod
def import_schema(cls, schema, srv_options, options, restriction_type, restricts):
service_url = srv_options["service_url"].rstrip("/")
headers = {"Accept": "application/xml"}
auth = _basic_auth(srv_options)
if auth:
headers["Authorization"] = auth
key = _api_key(srv_options)
if key:
headers["APIKey"] = key
req = urllib.request.Request(f"{service_url}/$metadata", headers=headers)
with urllib.request.urlopen(req, timeout=60) as resp:
root = ET.fromstring(_body(resp))
tables = []
for s in root.iter(f"{{{_NS['edm']}}}Schema"):
types = {t.get("Name"): t for t in s.findall("edm:EntityType", _NS)}
for es in s.iter(f"{{{_NS['edm']}}}EntitySet"):
name = es.get("Name")
# Foreign tables are named in lower case, so LIMIT TO / EXCEPT lists name them that way.
if restriction_type == "limit" and name.lower() not in [r.lower() for r in restricts]:
continue
if restriction_type == "except" and name.lower() in [r.lower() for r in restricts]:
continue
et = types[es.get("EntityType").split(".")[-1]]
keys = [k.get("Name") for k in et.findall("edm:Key/edm:PropertyRef", _NS)]
cols = [
ColumnDefinition(
p.get("Name").lower(),
type_name=_pg_type(p),
options={"odata_name": p.get("Name"), "odata_type": p.get("Type")},
)
for p in et.findall("edm:Property", _NS)
]
tables.append(TableDefinition(
name.lower(),
columns=cols,
options={**options, "entity_set": name, "key_columns": ",".join(keys)},
))
return tables
Build the image under the name the compose file uses, then start the stack as in Deployment:
Limitations
- Only OData v2 services are supported.
- Only Basic authentication (
SAP_ODATA_USERandSAP_ODATA_PASSWORD) and API keys are supported. OAuth and client certificates are not. - Systems that require a CSRF token for
$batchrequests reject the bridge's batched reads. - The bridge reads each service's
$metadataonly on its first start. After a service changes, recreate the bridge withdocker compose down -vanddocker compose up -d. - Queries with several hops across large entity sets can fail or be slow, because each hop becomes OData requests.
Troubleshooting
| Symptom | Likely cause and fix |
|---|---|
The bridge stops on its first start, and docker logs sap-bridge shows HTTP Error 401: Unauthorized |
The API key is wrong. Fix secrets/sap_apikey and start again as in the next row. |
| The bridge runs but has no tables, and the schema upload fails | The bridge creates its tables only on its first start, and an earlier first start failed. Recreate it with docker compose down -v and docker compose up -d. |
A query fails with an error that contains refused unfiltered read |
The query reads a whole order table without a narrowing condition, and the bridge refuses it. Start the query from a given supplier, part or customer. |
Cleanup
Shut down and remove the containers and the bridge's data: