OData V2/V4

ERPL-OData provides a universal OData reader that works seamlessly with both OData V2 and V4 services. Query any OData API like it's a native DuckDB table.

Overview

OData (Open Data Protocol) is a standard for building and consuming RESTful APIs. ERPL-OData makes OData services feel like regular databases.

What you get:

• ✅ Automatic version detection (V2 or V4)
• ✅ ATTACH services as databases
• ✅ Predicate pushdown ($filter, $select, $top, $skip)
• ✅ Expand navigation properties
• ✅ Type mapping from EDM to DuckDB
• ✅ Automatic pagination handling

---

Quick Start

-- Load the extension
LOAD erpl_odata;

-- Attach an OData service
ATTACH 'https://services.odata.org/TripPinRESTierService' 
  AS trippin (TYPE odata);

-- Query it like a normal table
SELECT UserName, FirstName, LastName 
FROM trippin.People 
WHERE FirstName = 'Russell';

That's it! The OData service is now a queryable database.

---

Two Ways to Use OData

1. ATTACH as Database (Recommended)

Attach the service once, query any entity set:

-- Attach OData V4 service
ATTACH 'https://services.odata.org/TripPinRESTierService' 
  AS trippin (TYPE odata);

-- List available tables
SHOW TABLES;

-- Query any entity
SELECT * FROM trippin.People LIMIT 10;
SELECT * FROM trippin.Airlines;
SELECT * FROM trippin.Airports;

2. Direct Read with odata_read

Query a specific entity set directly:

SELECT UserName, FirstName, LastName
FROM odata_read('https://services.odata.org/TripPinRESTierService/People')
WHERE UserName = 'russellwhyte';

tip

Use ATTACH for exploring services or querying multiple entities. Use odata_read for one-off queries or when you know exactly what you need.

---

Version Support

ERPL-OData automatically detects and handles both OData V2 and V4.

OData V4 Example

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

SELECT 
  UserName,
  FirstName,
  LastName,
  Gender
FROM trippin.People
WHERE Gender = 'Female'
LIMIT 5;

OData V2 Example

-- Northwind V2 Service
ATTACH 'https://services.odata.org/V2/Northwind/Northwind.svc' 
  AS northwind (TYPE odata);

SELECT 
  CustomerID,
  CompanyName,
  Country
FROM northwind.Customers
WHERE Country = 'Germany';

No configuration needed - ERPL-OData figures out the version automatically!

---

Predicate Pushdown

ERPL-OData translates SQL WHERE clauses into OData $filter queries, sending the filtering to the server. This dramatically improves performance.

Automatic $filter Translation

-- This SQL query...
SELECT * FROM trippin.People
WHERE FirstName = 'Russell' AND LastName = 'Whyte';

-- ...becomes this OData request:
-- GET /People?$filter=FirstName eq 'Russell' and LastName eq 'Whyte'

Supported Operators

SQL Operator	OData Translation
=	eq
!= or <>	ne
>	gt
>=	ge
<	lt
<=	le
AND	and
OR	or
NOT	not

$top and $skip (LIMIT and OFFSET)

-- Get first 10 records
SELECT * FROM trippin.People LIMIT 10;
-- Translates to: $top=10

-- Skip first 20, get next 10
SELECT * FROM trippin.People LIMIT 10 OFFSET 20;
-- Translates to: $skip=20&$top=10

$select (Column Selection)

-- Only fetch specific columns
SELECT UserName, FirstName FROM trippin.People;
-- Translates to: $select=UserName,FirstName

$orderby (Sorting)

-- Sort by last name
SELECT * FROM trippin.People ORDER BY LastName;
-- Translates to: $orderby=LastName

-- Sort descending
SELECT * FROM trippin.People ORDER BY LastName DESC;
-- Translates to: $orderby=LastName desc

---

Expand Navigation Properties

OData services often have related entities. Use expand to fetch them in one query.

Basic Expand

-- Fetch people with their trips
SELECT 
  UserName,
  FirstName,
  Trips
FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  expand := 'Trips'
);

Nested Expand

-- Expand multiple levels
SELECT UserName, Friends
FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  expand := 'Friends($expand=Trips)'
);

Expand with Filter

-- Expand with filtering
SELECT UserName, Trips
FROM odata_read(
  'https://services.odata.org/TripPinRESTierService/People',
  expand := 'Trips($filter=Name eq ''Trip to France'')'
);

---

Authentication

Using Secrets

Most production OData services require authentication:

-- Create a secret for Basic Auth
CREATE SECRET sap_odata (
  TYPE http_basic,
  username 'your_username',
  password 'your_password'
);

-- Attach with authentication (secret is auto-used)
ATTACH 'https://your-sap-server:port/sap/opu/odata/sap/SERVICE_NAME' 
  AS sap_service (TYPE odata);

SELECT * FROM sap_service.EntitySet;

Bearer Token

-- Create Bearer token secret
CREATE SECRET api_token (
  TYPE http_bearer,
  token 'your-bearer-token'
);

-- Use it automatically
ATTACH 'https://api.example.com/odata' 
  AS myservice (TYPE odata);

Selecting a Secret

odata_read() uses DuckDB's automatic secret lookup — the secret whose SCOPE best matches the request URL is chosen. To force a specific secret, use ATTACH:

ATTACH 'https://your-server/odata' AS my_service (TYPE odata, SECRET sap_odata);
SELECT * FROM my_service.EntitySet;

---

Type Mapping

ERPL-OData maps OData EDM types to DuckDB types automatically.

EDM Type	DuckDB Type
Edm.String	VARCHAR
Edm.Int16	SMALLINT
Edm.Int32	INTEGER
Edm.Int64	BIGINT
Edm.Decimal	DECIMAL(p,s) or DOUBLE — see below
Edm.Double	DOUBLE
Edm.Boolean	BOOLEAN
Edm.DateTime	TIMESTAMP
Edm.DateTimeOffset	TIMESTAMP WITH TIME ZONE
Edm.Guid	VARCHAR
Edm.Binary	BLOB

Complex types and collections are mapped to DuckDB STRUCT and LIST types.

Decimals: Precision and Scale

Edm.Decimal is the one type whose mapping depends on more than the type name. ERPL-OData reads the Precision and Scale facets a service declares in its $metadata:

<Property Name="GrossAmount" Type="Edm.Decimal" Precision="16" Scale="3"/>

Declared facets	DuckDB type	Why
Precision="16" Scale="3"	DECIMAL(16,3)	Both facets honoured as declared.
Scale="0"	DECIMAL(p,0)	An explicit zero scale is a real statement about the data.
No Scale, or Scale="variable"	DOUBLE	See below.
Precision absent	DECIMAL(38,s)	An absent Precision is unbounded in CSDL, so it widens to DuckDB's maximum.
Precision ≤ Scale	DECIMAL(38,s)	Facets that leave no integral digits cannot hold a value ≥ 1; the scale is kept and the precision widened.
Precision > 38	DECIMAL(38,s)	DuckDB's widest DECIMAL is 38 digits.
Scale ≥ 38	DOUBLE	No DuckDB DECIMAL leaves any integral digits at that scale.

Why a missing Scale becomes DOUBLE. CSDL says an absent Scale defaults to 0, which would make DECIMAL(p,0) conformant — and would silently round every 19.99 to 20. SAP CDS and Dataverse both publish money columns that way. Losing the cents is a worse failure than losing exactness, so ERPL-OData maps those columns to DOUBLE instead.

To see what a column actually became:

DESCRIBE SELECT * FROM my_service.MyEntitySet;

Inspecting a Service with odata_describe()

odata_describe(url) reads a service's $metadata and reports its shape without fetching any data. Point it at a service root to list entity sets and functions, or at an entity set to list its properties and navigation properties.

SELECT p.name, p.duckdb_type, p.edm_type, p.precision, p.scale
FROM odata_describe('https://services.odata.org/V4/Northwind/Northwind.svc/Products'),
     UNNEST(properties) AS t(p);

Column	Description
url, resource_type	The URL described, and service or entity_set
entity_set_name, entity_type_name	Empty for a service root
properties	List of structs: name, duckdb_type, edm_type, is_nullable, is_key, max_length, precision, scale
navigation_properties	List of structs: name, target_entity, target_entity_type (name, property_count, nav_property_count), is_collection, partner, referential_constraints
entity_sets	Service root only. List of structs: name, entity_type, url
functions	Service root only. List of structs: name, return_type, parameters

duckdb_type is the type the reader will actually bind — the same DECIMAL(p,s)/DOUBLE/VARCHAR[]/STRUCT(...) you get from odata_read() or an attached table, facets included.

precision, scale and max_length report the facets as declared, and are NULL when the service declares none. That distinction matters: Scale="0" reports 0 and yields DECIMAL(p,0), while an absent Scale reports NULL and yields DOUBLE.

It takes the same secret named parameter as odata_read():

SELECT * FROM odata_describe('https://your-server/odata/Products', secret => 'my_secret');

---

Pagination

ERPL-OData handles pagination automatically. If a service returns paginated results, the extension follows the @odata.nextLink to fetch all pages.

-- This might fetch multiple pages automatically
SELECT COUNT(*) FROM trippin.People;

Manual Pagination Control

-- Fetch specific page size
SELECT * FROM trippin.People LIMIT 100;

-- Skip to specific offset
SELECT * FROM trippin.People LIMIT 50 OFFSET 200;

---

Advanced Examples

SAP OData Services

-- Connect to SAP Gateway OData service
CREATE SECRET sap_gateway (
  TYPE http_basic,
  username 'SAP_USER',
  password 'SAP_PASS'
);

ATTACH 'https://sap-server:port/sap/opu/odata/sap/ZSERVICE_SRV' 
  AS sap (TYPE odata);

-- Query SAP data
SELECT * FROM sap.Customers WHERE Country = 'US';

Complex Filters

-- Multiple conditions
SELECT * FROM northwind.Orders
WHERE 
  ShipCountry = 'USA' 
  AND OrderDate >= '1997-01-01'
  AND Freight > 100
ORDER BY OrderDate DESC;

Joins Across Entity Sets

-- Join customers and orders
SELECT 
  c.CompanyName,
  o.OrderID,
  o.OrderDate
FROM northwind.Customers c
JOIN northwind.Orders o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'Germany'
LIMIT 10;

Export to Parquet

-- Extract OData to Parquet file
COPY (
  SELECT * FROM trippin.People
) TO 'people.parquet' (FORMAT PARQUET);

Use with DuckDB Features

-- Aggregate OData data
SELECT 
  Country,
  COUNT(*) as customer_count,
  AVG(Freight) as avg_freight
FROM northwind.Orders o
JOIN northwind.Customers c ON o.CustomerID = c.CustomerID
GROUP BY Country
ORDER BY customer_count DESC;

---

Performance Tips

1. Use Predicate Pushdown

-- Good: Filter is pushed to OData service
SELECT * FROM trippin.People WHERE Gender = 'Female';

-- Bad: Fetches all data, then filters locally
-- (Avoid if possible)

2. Select Only Needed Columns

-- Good: Only fetches 2 columns
SELECT UserName, FirstName FROM trippin.People;

-- Bad: Fetches all columns
SELECT * FROM trippin.People;

3. Use LIMIT for Exploration

-- Good: Quick data sample
SELECT * FROM trippin.People LIMIT 10;

-- Be careful with large datasets
-- SELECT * FROM large_entity_set;  -- Could take a long time

4. Cache Results for Repeated Queries

-- Cache OData results in a table
CREATE TABLE people_cache AS
SELECT * FROM trippin.People;

-- Query the cache
SELECT * FROM people_cache WHERE FirstName = 'Russell';

---

Troubleshooting

Service Not Found

Error: OData service not found

Fixes:

• Verify the service URL is correct
• Check if service requires authentication
• Ensure the service is accessible from your network

Entity Set Not Found

Error: Entity set 'XYZ' not found

Fixes:

• Use SHOW TABLES to list available entity sets
• Check capitalization (OData is case-sensitive)
• Verify you're using the correct version (V2 vs V4)

Authentication Failed

Error: 401 Unauthorized

Fixes:

• Verify credentials in your secret
• Check if credentials have expired
• Ensure user has permissions to access the service

Decimal Values Come Back NULL

erpl_odata warning: 2 values in 1 column could not be
converted and were returned as NULL (odata_read).
  column 'serviceagreementcost': 2 failures;
  first offending value: 490.0;
  first error: Could not cast value 490.000000
                                to DECIMAL(18,18)

A value did not fit the DuckDB type derived from the service's $metadata. The giveaway is a target type whose scale equals its precision, such as DECIMAL(18,18): that type has no integral digits at all, so no value of 1 or more can be stored in it. ERPL-OData now widens such declarations to DECIMAL(38,s) — see Decimals: Precision and Scale.

Fixes:

• Check the column type with DESCRIBE SELECT * FROM my_service.MyEntitySet
• Make sure you are on a build that includes the widened decimal mapping
• Pass strict_typing=true to odata_read() to fail on the first bad value instead of collecting NULLs, when you would rather the query stop than return incomplete data

Slow Queries

Solutions:

• Use WHERE clauses to push filters to server
• Select only needed columns
• Use LIMIT for large datasets
• Check OData service performance

Enable Tracing

-- Enable tracing to see what's happening
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

-- Run your query
SELECT * FROM trippin.People LIMIT 5;

-- Check trace output for details

See Tracing & Diagnostics for more details.

---

Known Limitations

• Write operations: ERPL-OData currently supports read-only operations (GET). POST/PUT/PATCH/DELETE are not yet supported.
• Function imports: Custom OData functions may have limited support.
• Batch requests: Not currently supported.

---

Examples by Use Case

Data Integration

-- Sync OData service to local DuckDB
CREATE TABLE local_customers AS
SELECT * FROM odata_read('https://api.example.com/odata/Customers');

-- Incremental updates
INSERT INTO local_customers
SELECT * FROM odata_read('https://api.example.com/odata/Customers')
WHERE ModifiedDate > (SELECT MAX(ModifiedDate) FROM local_customers);

Analytics

-- Analyze OData data with DuckDB
WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', OrderDate) as month,
    SUM(Freight) as total_freight
  FROM northwind.Orders
  GROUP BY month
)
SELECT * FROM monthly_sales
ORDER BY month;

Data Export

-- Export filtered data to CSV
COPY (
  SELECT * FROM trippin.People
  WHERE Gender = 'Female'
) TO 'female_travelers.csv' (HEADER, DELIMITER ',');

---

Next Steps

• Explore SAP Datasphere for SAP-specific OData integration
• Learn about ODP via OData for delta replication
• See Secrets Management for secure authentication
• Check Examples for more real-world patterns

---

Summary

ERPL-OData's OData integration gives you:

✅ Universal - Works with V2 and V4 automatically
✅ Powerful - Predicate pushdown and expand support
✅ Simple - ATTACH and query like normal SQL
✅ Fast - Server-side filtering and pagination

Turn any OData service into a DuckDB table in seconds!