OData V2/V4

ERPL-Web 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-Web 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_web;

-- 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-Web 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-Web figures out the version automatically!

---

Predicate Pushdown

ERPL-Web 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);

Explicit Secret Parameter

-- Pass secret explicitly in odata_read
SELECT * 
FROM odata_read(
  'https://your-server/odata/EntitySet',
  secret := 'sap_odata'
);

---

Type Mapping

ERPL-Web 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
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.

---

Pagination

ERPL-Web 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

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-Web 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-Web'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!