Skip to main content

ERPL-Web Quick Start (Cloud/Web)

In this 5-minute tutorial, you'll connect to a public OData service and query SAP Datasphere from DuckDB. By the end, you'll be reading SAP data via HTTP APIs straight into SQL.

Prerequisites
  • DuckDB installed (version 0.10.0+)
  • Network access from your machine to the OData endpoint or SAP Datasphere tenant
  • For protected services: a Datasphere OAuth2 app, an OData bearer token, or HTTP basic credentials

Step 1: Install ERPL-Web​

INSTALL 'erpl_web' FROM 'http://get.erpl.io';
LOAD 'erpl_web';
Success Check

If installation worked, you should see no errors. The extension is now loaded and ready to use.

Step 2: Query Your First OData Service​

The public TripPin reference service is a good starting point — no auth, no setup. odata_read() takes the entity-set URL and returns a regular table.

-- Read the People entity set directly
SELECT *
FROM odata_read('https://services.odata.org/TripPinRESTierService/People')
LIMIT 5;

OData's top / skip / expand / count query options are exposed as named parameters:

-- Top-5 with $expand for related Trips
SELECT *
FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  top => 5,
  expand => 'Trips'
);

What's happening?​

ERPL-Web fetches the OData $metadata once, builds a typed schema, then issues paginated requests for the entity set.

Step 3: Filter and Sort with Plain SQL​

You don't need named parameters for filtering or ordering — regular SQL WHERE and ORDER BY are pushed down to OData $filter and $orderby:

-- Filter by date range — pushed down to OData $filter
SELECT FirstName, LastName, UserName
FROM odata_read('https://services.odata.org/TripPinRESTierService/People')
WHERE FirstName = 'Russell';
-- Sort + paginate
SELECT *
FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  top => 10,
  skip => 20
)
ORDER BY LastName ASC;

Step 4: Attach a Service as a Database​

If you'll query many entity sets from the same service, attach it once. Each entity set then looks like a table:

ATTACH 'https://services.odata.org/TripPinRESTierService'
  AS trippin (TYPE odata);

SELECT FirstName, LastName FROM trippin.People LIMIT 5;
SELECT * FROM trippin.Airports LIMIT 5;

The catalog discovers entity sets from $metadata; predicate and column pushdown apply to every table.

Step 5: Connect to SAP Datasphere​

Datasphere uses OAuth2 client credentials. Create a secret once, then call the catalog functions — no per-call auth needed.

-- One-time: create the OAuth2 secret
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'mytenant',
  data_center 'eu10',
  client_id 'your-client-id',
  client_secret 'your-client-secret'
);

Discover what's available:

-- List spaces you have access to
SELECT * FROM datasphere_show_spaces();

-- List assets in a space
SELECT * FROM datasphere_show_assets('SALES_SPACE');

Then read an asset by name:

-- Read a relational view (the default consumption mode)
SELECT *
FROM datasphere_read_relational('SALES_SPACE', 'V_DEMAND_FORECAST')
WHERE month = '2026-05';

For multi-dimensional analytical models, use datasphere_read_analytical() instead.

Common OData Patterns​

-- Sales orders entity set
SELECT *
FROM odata_read('https://your-server/odata/SalesOrders', top => 5);

-- Customer master with $expand
SELECT *
FROM odata_read(
  'https://your-server/odata/Customers',
  expand => 'Addresses,Contacts',
  top => 20
);

-- Count-only query
SELECT *
FROM odata_read(
  'https://your-server/odata/Orders',
  count => true,
  top => 0
);

Filtering — let DuckDB push it down​

-- Date range — translated to OData $filter
SELECT *
FROM odata_read('https://your-server/odata/Orders')
WHERE OrderDate BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';
-- Compound predicate — also pushed down
SELECT *
FROM odata_read('https://your-server/odata/Products')
WHERE Category = 'Electronics' AND Price > 100;

Sorting and pagination​

-- Sort by date desc, server-paginated
SELECT *
FROM odata_read(
  'https://your-server/odata/Orders',
  top => 20
)
ORDER BY OrderDate DESC;

-- Skip first 100
SELECT *
FROM odata_read(
  'https://your-server/odata/Customers',
  skip => 100,
  top => 50
);

Selecting specific fields​

-- SELECT-list is pushed down as OData $select
SELECT OrderID, CustomerID, TotalAmount
FROM odata_read('https://your-server/odata/Orders', top => 10);

Troubleshooting​

Auth failures​

Most "authentication failed" errors come from a missing or mis-scoped secret. ERPL-Web looks up the secret whose SCOPE best matches the request URL.

-- Create a bearer-token secret scoped to the host
CREATE SECRET sap_api (
  TYPE http_bearer,
  token 'your-api-token',
  SCOPE 'https://api.example.com'
);

-- Then any matching call uses it automatically
SELECT * FROM odata_read('https://api.example.com/Orders', top => 5);

For Datasphere OAuth2 see Step 5 above.

Connection issues​

  • Verify the URL works in curl first.
  • Confirm the service exposes a reachable $metadata endpoint — ERPL-Web fetches it before issuing queries.
  • For self-signed TLS, configure your DuckDB extension config accordingly (see Tracing & Diagnostics).

Type or field errors​

OData fields are case-sensitive and depend on the service's $metadata. To see the exact schema, run with top => 1 first and inspect the result columns.

SELECT * FROM odata_read('https://your-server/odata/Orders', top => 1);

Next Steps​

🚀 Ready for more?​

🔧 Advanced topics​

💡 Examples​

What You've Learned​

✅ Installed ERPL-Web extension ✅ Queried a public OData service with odata_read ✅ Used WHERE and ORDER BY (with pushdown) instead of fake named params ✅ Attached an OData service as a DuckDB catalog ✅ Connected to SAP Datasphere via OAuth2 secrets

You're ready to use ERPL-Web for cloud SAP integration.

Need help? Check the troubleshooting guide or browse more examples.