ERPL-Web Examples

Real-world examples and patterns using ERPL-Web for API integration, OData services, SAP Datasphere, and delta replication.

---

HTTP Examples

Simple API Call

-- Get your public IP address
SELECT content::JSON->>'ip' AS my_ip
FROM http_get('https://api.ipify.org?format=json');

API with Authentication

-- Create secret for GitHub API
CREATE SECRET github_api (
  TYPE http_bearer,
  token 'ghp_your_token_here'
);

-- Get repository information
SELECT 
  content::JSON->>'name' AS repo_name,
  content::JSON->>'stargazers_count' AS stars,
  content::JSON->>'forks_count' AS forks
FROM http_get('https://api.github.com/repos/duckdb/duckdb');

POST JSON Data

-- Send webhook notification
SELECT status, content
FROM http_post(
  'https://webhook.site/your-unique-url',
  json_object(
    'event', 'data_loaded',
    'timestamp', current_timestamp,
    'records', 12345
  )::VARCHAR,
  content_type := 'application/json'
);

API Pagination

-- Fetch paginated API results
WITH page1 AS (
  SELECT content::JSON AS data
  FROM http_get('https://api.example.com/users?page=1&limit=100')
),
page2 AS (
  SELECT content::JSON AS data
  FROM http_get('https://api.example.com/users?page=2&limit=100')
)
SELECT * FROM page1
UNION ALL
SELECT * FROM page2;

ETL from API to Parquet

-- Extract weather data and save to Parquet
COPY (
  SELECT 
    content::JSON->'location'->>'name' AS city,
    content::JSON->'current'->>'temp_c' AS temperature_celsius,
    content::JSON->'current'->>'condition'->>'text' AS condition,
    current_timestamp AS extracted_at
  FROM http_get('https://api.weatherapi.com/v1/current.json?q=London&key=YOUR_KEY')
) TO 'weather_data.parquet' (FORMAT PARQUET);

---

OData Examples

Attach and Query OData Service

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

-- List available tables
SHOW TABLES;

-- Query people
SELECT UserName, FirstName, LastName, Gender
FROM trippin.People
WHERE Gender = 'Female'
LIMIT 10;

-- Query with filters
SELECT * FROM trippin.Airlines
WHERE Name LIKE '%American%';

Northwind V2 Service

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

-- Query customers by country
SELECT CustomerID, CompanyName, ContactName, Country
FROM northwind.Customers
WHERE Country IN ('Germany', 'France', 'UK')
ORDER BY Country, CompanyName;

-- Join customers and orders
SELECT 
  c.CompanyName,
  COUNT(o.OrderID) as order_count,
  SUM(o.Freight) as total_freight
FROM northwind.Customers c
LEFT JOIN northwind.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CompanyName
ORDER BY order_count DESC
LIMIT 10;

Direct OData Read

-- Read specific entity set without ATTACH
SELECT 
  UserName,
  FirstName,
  LastName,
  FavoriteFeature
FROM odata_read('https://services.odata.org/TripPinRESTierService/People')
WHERE FavoriteFeature IS NOT NULL
LIMIT 5;

Export OData to CSV

-- Extract OData data to CSV
COPY (
  SELECT * FROM northwind.Products
  WHERE UnitsInStock < 20
) TO 'low_stock_products.csv' (HEADER, DELIMITER ',');

---

SAP Datasphere Examples

Setup and Discovery

-- Create OAuth2 secret
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-corp',
  DATA_CENTER 'eu10'
);

-- List all spaces
SELECT * FROM datasphere_show_spaces();

-- List assets in SALES space
SELECT name, object_type, technical_name
FROM datasphere_show_assets('SALES')
ORDER BY name;

Query Relational Data

-- Read customer master data
SELECT 
  customer_id,
  customer_name,
  country,
  revenue
FROM datasphere_read_relational('SALES', 'CUSTOMER_MASTER_V')
WHERE country = 'United States'
ORDER BY revenue DESC
LIMIT 100;

Query Analytical Data

-- Read sales analytics with specific metrics and dimensions
SELECT 
  region,
  product_category,
  SUM(total_revenue) as revenue,
  SUM(total_cost) as cost,
  SUM(total_revenue - total_cost) as profit
FROM datasphere_read_analytical(
  'SALES',
  'REVENUE_ANALYTICS_V',
  metrics := ['TotalRevenue', 'TotalCost'],
  dimensions := ['Region', 'ProductCategory']
)
GROUP BY region, product_category
ORDER BY profit DESC;

Parameterized Views

-- Query with input parameters
SELECT *
FROM datasphere_read_relational(
  'SALES',
  'MONTHLY_SALES_V',
  params := {
    'P_YEAR': '2024',
    'P_MONTH': '03',
    'P_REGION': 'EMEA'
  }
);

Multi-Tenant Access

-- Create secrets for multiple tenants
CREATE SECRET datasphere_prod (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-prod',
  DATA_CENTER 'eu10'
);

CREATE SECRET datasphere_dev (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-dev',
  DATA_CENTER 'eu10'
);

-- Query production tenant
SELECT * FROM datasphere_show_assets(secret := 'datasphere_prod');

-- Query development tenant
SELECT * FROM datasphere_show_assets(secret := 'datasphere_dev');

---

ODP via OData Examples

Complete Delta Replication Workflow

-- 1. Setup authentication
CREATE SECRET sap_system (
  TYPE http_basic,
  username 'SAP_USER',
  password 'SAP_PASSWORD'
);

-- 2. Discover available ODP services
SELECT 
  service_name,
  entity_set_name,
  description
FROM odp_odata_show('https://sap-server:8000', secret='sap_system')
WHERE entity_set_name LIKE '%SALES%';

-- 3. Create target table
CREATE TABLE sales_facts (
  sales_order VARCHAR,
  customer_id VARCHAR,
  product_id VARCHAR,
  quantity INTEGER,
  revenue DECIMAL(15,2),
  order_date DATE,
  RECORD_MODE VARCHAR
);

-- 4. Initial load (creates subscription)
INSERT INTO sales_facts
SELECT * FROM odp_odata_read(
  'https://sap-server:8000/sap/opu/odata/sap/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
  secret='sap_system'
);

-- 5. Check subscription status
SELECT * FROM odp_odata_list_subscriptions();

-- 6. Delta update (run periodically)
WITH delta AS (
  SELECT * FROM odp_odata_read(
    'https://sap-server:8000/sap/opu/odata/sap/Z_ODP_BW_1_SRV/FactsOf0D_NW_C01',
    secret='sap_system'
  )
)
-- Process inserts
INSERT INTO sales_facts
SELECT * FROM delta WHERE RECORD_MODE = 'N';

-- Update existing records
UPDATE sales_facts t
SET 
  quantity = d.quantity,
  revenue = d.revenue
FROM delta d
WHERE t.sales_order = d.sales_order AND d.RECORD_MODE = '';

-- Delete removed records
DELETE FROM sales_facts
WHERE sales_order IN (
  SELECT sales_order FROM delta WHERE RECORD_MODE = 'D'
);

Monitor Extraction Performance

-- View extraction statistics
SELECT 
  subscription_id,
  entity_set_name,
  request_timestamp,
  request_type,
  records_received,
  execution_time_ms / 1000.0 AS execution_time_seconds
FROM erpl_web.odp_subscription_audit
ORDER BY request_timestamp DESC
LIMIT 20;

-- Average performance by subscription
SELECT 
  subscription_id,
  entity_set_name,
  COUNT(*) as extraction_count,
  AVG(records_received) as avg_records,
  AVG(execution_time_ms / 1000.0) as avg_time_seconds
FROM erpl_web.odp_subscription_audit
WHERE request_type = 'DELTA'
GROUP BY subscription_id, entity_set_name;

Export to Data Lake

-- Extract SAP data to Parquet with partitioning
COPY (
  SELECT 
    *,
    DATE_TRUNC('day', order_date) as partition_date
  FROM odp_odata_read(
    'https://sap/odata/SALES_DATA',
    secret='sap_system'
  )
) TO 'datalake/sales_data.parquet' (
  FORMAT PARQUET,
  PARTITION_BY (partition_date),
  COMPRESSION 'ZSTD'
);

---

Integration Patterns

Federated Analytics

-- Combine Datasphere, OData, and HTTP sources
WITH 
  datasphere_sales AS (
    SELECT customer_id, SUM(revenue) as ds_revenue
    FROM datasphere_read_relational('SALES', 'TRANSACTIONS')
    GROUP BY customer_id
  ),
  crm_data AS (
    SELECT 
      content::JSON->>'id' AS customer_id,
      content::JSON->>'score' AS crm_score
    FROM http_get('https://crm-api.example.com/customers')
  ),
  odata_customers AS (
    SELECT CustomerID, CompanyName
    FROM northwind.Customers
  )
SELECT 
  oc.CompanyName,
  ds.ds_revenue,
  cd.crm_score
FROM odata_customers oc
LEFT JOIN datasphere_sales ds ON oc.CustomerID = ds.customer_id
LEFT JOIN crm_data cd ON oc.CustomerID = cd.customer_id
ORDER BY ds.ds_revenue DESC
LIMIT 50;

Incremental Data Sync

-- Sync API data incrementally
CREATE TABLE api_cache (
  id VARCHAR PRIMARY KEY,
  data JSON,
  last_updated TIMESTAMP
);

-- Initial load
INSERT INTO api_cache
SELECT 
  content::JSON->>'id' AS id,
  content::JSON AS data,
  CURRENT_TIMESTAMP
FROM http_get('https://api.example.com/data');

-- Incremental update (only new records)
INSERT INTO api_cache
SELECT 
  content::JSON->>'id' AS id,
  content::JSON AS data,
  CURRENT_TIMESTAMP
FROM http_get('https://api.example.com/data?since=' || (
  SELECT MAX(last_updated) FROM api_cache
)::VARCHAR)
ON CONFLICT (id) DO UPDATE SET
  data = EXCLUDED.data,
  last_updated = EXCLUDED.last_updated;

API Gateway Pattern

-- Create views that wrap external APIs
CREATE VIEW github_trending AS
SELECT 
  content::JSON->'items'[1]->>'name' AS repo_name,
  content::JSON->'items'[1]->>'stargazers_count' AS stars,
  content::JSON->'items'[1]->>'html_url' AS url
FROM http_get('https://api.github.com/search/repositories?q=stars:>10000&sort=stars');

-- Query the view like a table
SELECT * FROM github_trending;

---

Troubleshooting Examples

Debug with Tracing

-- Enable detailed tracing
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';
SET erpl_trace_output = 'both';
SET erpl_trace_file_path = './debug.log';

-- Run problematic query
SELECT * FROM datasphere_show_spaces();

-- Check trace file for details

Test Authentication

-- Test HTTP auth
SELECT status, content
FROM http_get(
  'https://api.example.com/test',
  auth := 'user:pass',
  auth_type := 'BASIC'
);

-- Expected: status = 200
-- If 401: Wrong credentials
-- If 403: Insufficient permissions

Verify OData Metadata

-- Attach service
ATTACH 'https://your-odata-service.com/odata' AS test (TYPE odata);

-- List tables to verify metadata loaded
SHOW TABLES;

-- Query first few rows
SELECT * FROM test.EntitySet LIMIT 5;

---

Next Steps

• Read detailed documentation:

  • HTTP Functions
  • OData Guide
  • Datasphere Guide
  • ODP via OData

• Check Functions Reference for complete API

• See Real-World Use Cases for more patterns