Skip to content

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:

docker compose version

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:

mkdir sap-odata-demo
cd sap-odata-demo

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:

docker compose up -d

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:

curl -X POST -H "content-type: application/json" \
  --data-binary @./schema.json \
  --user "puppygraph:puppygraph123" \
  http://localhost:8081/schema

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:

mkdir -p bridge/init bridge/odata_fdw

▶ 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:

chmod +x bridge/init/10-odata.sh

▶ 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:

docker build -t puppygraph/sap-odata-bridge:0.1.2-pg16 bridge

Limitations

  • Only OData v2 services are supported.
  • Only Basic authentication (SAP_ODATA_USER and SAP_ODATA_PASSWORD) and API keys are supported. OAuth and client certificates are not.
  • Systems that require a CSRF token for $batch requests reject the bridge's batched reads.
  • The bridge reads each service's $metadata only on its first start. After a service changes, recreate the bridge with docker compose down -v and docker 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:

docker compose down -v