SAP ERP Connection

Extension Credit

This guide uses the erpl extension by the DuckDB community. Thanks to the contributors who made SAP ERP integration possible through RFC connections!

Connect flAPI to SAP ERP systems (ECC, S/4HANA) using DuckDB's ERPL extension. Extract data from tables, run function modules, and execute ABAP queries directly.

Prerequisites

SAP NetWeaver RFC SDK installed
SAP user with appropriate authorizations
Network access to SAP system

Quick Start

The ERPL extension is installed via its custom repository and authenticates with a DuckDB sap_rfc secret:

# flapi.yaml
connections:
  sap-erp:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';

      CREATE OR REPLACE PERSISTENT SECRET sap_erp (
        TYPE sap_rfc,
        ASHOST '${SAP_ASHOST}',
        SYSNR  '${SAP_SYSNR}',
        CLIENT '${SAP_CLIENT}',
        USER   '${SAP_USER}',
        PASSWD '${SAP_PASSWORD}',
        LANG   'EN'
      );

The secret name (sap_erp) is referenced implicitly by the ERPL extension functions like sap_read_table().

Connection Parameters

Parameter	Description	Required	Example
`ASHOST`	SAP application server hostname	Yes	`sap.company.com`
`SYSNR`	System number	Yes	`'00'`
`CLIENT`	Client number	Yes	`'100'`
`USER`	SAP username	Yes	`${SAP_USER}`
`PASSWD`	SAP password	Yes	`${SAP_PASSWORD}`
`LANG`	Language (EN, DE, etc.)	No	`EN`

Reading SAP Tables

Basic Table Read

# sqls/materials.yaml
url-path: /materials/
method: GET

request:
  - field-name: plant
    field-in: query
    description: Plant code
    required: false
    validators:
      - type: string
        regex: '^[A-Z0-9]{1,4}$'

template-source: materials.sql
connection:
  - sap-erp

-- sqls/materials.sql
SELECT
  MATNR AS material_number,
  WERKS AS plant,
  LGORT AS storage_location,
  LABST AS stock_quantity
FROM sap_read_table('MARD')
WHERE 1=1
{{#params.plant}}
  AND WERKS = '{{{ params.plant }}}'
{{/params.plant}}

Joining SAP Tables

-- sqls/sales-orders.sql
SELECT
  h.VBELN AS sales_order,
  h.ERDAT AS creation_date,
  h.KUNNR AS customer_number,
  c.NAME1 AS customer_name,
  i.MATNR AS material,
  i.KWMENG AS quantity,
  i.NETWR AS net_value
FROM sap_read_table('VBAK') h
LEFT JOIN sap_read_table('KNA1') c
  ON h.KUNNR = c.KUNNR
LEFT JOIN sap_read_table('VBAP') i
  ON h.VBELN = i.VBELN
WHERE h.ERDAT >= '20240101'
ORDER BY h.ERDAT DESC

Common SAP Tables

Materials Management (MM)

MARA - Material master (general data)
MAKT - Material descriptions
MARD - Storage location data
MARC - Plant data for materials

Sales & Distribution (SD)

VBAK - Sales document header
VBAP - Sales document items
KNA1 - Customer master (general)
KNVV - Customer master (sales)

Finance (FI)

BKPF - Accounting document header
BSEG - Accounting document items
SKA1 - G/L account master

Purchasing (MM-PUR)

EKKO - Purchase order header
EKPO - Purchase order items
LFA1 - Vendor master

Caching SAP Data

SAP tables are large and slow. Always use caching:

# sqls/materials.yaml
url-path: /materials/
method: GET

cache:
  enabled: true
  table: sap_materials_cache
  schedule: 6h
  template-file: materials_cache.sql

template-source: materials.sql
connection:
  - sap-erp

-- sqls/materials_cache.sql
-- Load all active materials into the DuckLake cache
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT
  m.MATNR AS material_number,
  t.MAKTX AS material_description,
  m.MTART AS material_type,
  m.MATKL AS material_group,
  m.MEINS AS base_unit,
  s.WERKS AS plant,
  s.LABST AS stock_quantity
FROM sap_read_table('MARA') m
JOIN sap_read_table('MAKT') t
  ON m.MATNR = t.MATNR
  AND t.SPRAS = 'E'
JOIN sap_read_table('MARD') s
  ON m.MATNR = s.MATNR
WHERE m.LVORM IS NULL

Performance comparison:

Method	Latency	Cost
Direct SAP query	5-30 seconds	High RFC load
flAPI cache	1-5ms	Minimal (scheduled refresh)

Security Best Practices

DO

# Use environment variables in the SECRET
connections:
  sap-erp:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';
      CREATE OR REPLACE PERSISTENT SECRET sap_erp (
        TYPE sap_rfc,
        ASHOST '${SAP_ASHOST}',
        SYSNR  '${SAP_SYSNR}',
        CLIENT '${SAP_CLIENT}',
        USER   '${SAP_USER}',
        PASSWD '${SAP_PASSWORD}',
        LANG   'EN'
      );

Restrict the endpoint with authentication

# sqls/materials.yaml
auth:
  enabled: true
  type: bearer
  jwt-secret: '${SAP_JWT_SECRET}'

flAPI endpoint auth uses a single type string. Supported values: basic, jwt, bearer, oidc.

DON'T

# Never hardcode credentials inside the secret
init: |
  CREATE OR REPLACE PERSISTENT SECRET sap_erp (
    TYPE sap_rfc,
    USER   'JOHNDOE',          -- BAD
    PASSWD 'Password123'       -- BAD
  );

Environment Setup

# .env (never commit)
export SAP_USER="RFC_API_USER"
export SAP_PASSWORD="secure-password"
export SAP_ASHOST="sap-prod.company.com"
export SAP_SYSNR="00"
export SAP_CLIENT="100"

# flapi.yaml
template:
  path: './sqls'
  environment-whitelist:
    - '^SAP_.*'

Complete Example

# ═══════════════════════════════════════════════════════════════
# flapi.yaml
# ═══════════════════════════════════════════════════════════════
project-name: sap-inventory-api

template:
  path: './sqls'
  environment-whitelist:
    - '^SAP_.*'

connections:
  sap-prod:
    init: |
      INSTALL 'erpl' FROM 'http://get.erpl.io';
      LOAD 'erpl';

      CREATE OR REPLACE PERSISTENT SECRET sap_prod (
        TYPE sap_rfc,
        ASHOST '${SAP_ASHOST}',
        SYSNR  '${SAP_SYSNR}',
        CLIENT '${SAP_CLIENT}',
        USER   '${SAP_USER}',
        PASSWD '${SAP_PASSWORD}',
        LANG   'EN'
      );

# ═══════════════════════════════════════════════════════════════
# sqls/inventory.yaml
# ═══════════════════════════════════════════════════════════════
url-path: /inventory/
method: GET

auth:
  enabled: true
  type: bearer
  jwt-secret: '${INVENTORY_JWT_SECRET}'

cache:
  enabled: true
  table: sap_inventory
  schedule: 1h
  template-file: inventory_cache.sql

request:
  - field-name: plant
    field-in: query
    description: Plant code (e.g., 1000)
    required: false
    validators:
      - type: string
        regex: '^[A-Z0-9]{1,4}$'
  - field-name: material_type
    field-in: query
    description: Material type (e.g., FERT, HALB)
    required: false
    validators:
      - type: enum
        allowedValues: [FERT, HALB, ROH, HAWA]

template-source: inventory.sql
connection:
  - sap-prod

-- sqls/inventory_cache.sql
-- Materialize inventory data
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT
  m.MATNR  AS material,
  t.MAKTX  AS description,
  m.MTART  AS material_type,
  s.WERKS  AS plant,
  s.LGORT  AS storage_location,
  s.LABST  AS unrestricted_stock,
  s.INSME  AS quality_inspection_stock,
  s.SPEME  AS blocked_stock,
  p.STPRS  AS standard_price,
  p.PEINH  AS price_unit
FROM sap_read_table('MARA') m
JOIN sap_read_table('MAKT') t
  ON m.MATNR = t.MATNR AND t.SPRAS = 'E'
JOIN sap_read_table('MARD') s
  ON m.MATNR = s.MATNR
JOIN sap_read_table('MBEW') p
  ON m.MATNR = p.MATNR AND s.WERKS = p.BWKEY
WHERE m.LVORM IS NULL

-- sqls/inventory.sql
-- Fast queries against the cache
SELECT *
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
WHERE 1=1
{{#params.plant}}
  AND plant = '{{{ params.plant }}}'
{{/params.plant}}
{{#params.material_type}}
  AND material_type = '{{{ params.material_type }}}'
{{/params.material_type}}
ORDER BY unrestricted_stock DESC

Troubleshooting

Connection Failed

Error: RFC_ERROR_COMMUNICATION - Connection to SAP system failed

Solutions:

Verify ASHOST and SYSNR are correct
Check network connectivity: ping sap.company.com
Ensure SAP NetWeaver RFC SDK is installed
Verify firewall allows RFC connections (port 33XX)

Authorization Errors

Error: RFC_ERROR_AUTHORITY - User lacks authorization

Solutions:

Ensure user has RFC authorizations
Check transaction authorizations (S_RFC, S_TABU_NAM)
Contact SAP Basis team for access

Slow Queries

SAP queries taking > 10 seconds:

Solutions:

Enable caching (6h-24h refresh)
Use indexes on SAP tables
Limit date ranges
Avoid SELECT *
Don't query SAP directly for high-frequency APIs

Next Steps

SAP BW Connection: Connect to SAP BW/4HANA
Caching Setup: Optimize performance
Authentication: Secure your API

Prerequisites​

Quick Start​

Connection Parameters​

Reading SAP Tables​

Basic Table Read​

Joining SAP Tables​

Common SAP Tables​

Materials Management (MM)​

Sales & Distribution (SD)​

Finance (FI)​

Purchasing (MM-PUR)​

Caching SAP Data​

Security Best Practices​

DO​

Restrict the endpoint with authentication​

DON'T​

Environment Setup​

Complete Example​

Troubleshooting​

Connection Failed​

Authorization Errors​

Slow Queries​

Next Steps​

Prerequisites

Quick Start

Connection Parameters

Reading SAP Tables

Basic Table Read

Joining SAP Tables

Common SAP Tables

Materials Management (MM)

Sales & Distribution (SD)

Finance (FI)

Purchasing (MM-PUR)

Caching SAP Data

Security Best Practices

DO

Restrict the endpoint with authentication

DON'T

Environment Setup

Complete Example

Troubleshooting

Connection Failed

Authorization Errors

Slow Queries

Next Steps