SAP Datasphere

ERPL-OData is the first native DuckDB client for SAP Datasphere. Discover, explore, and query your Datasphere data directly from SQL with full OAuth2 security.

Overview

SAP Datasphere (formerly Data Warehouse Cloud) is SAP's cloud data platform. ERPL-OData integrates with both the DWAAS Core APIs and the Catalog OData service to provide:

What you get:

✅ OAuth2 authentication (authorization code & client credentials)
✅ Discover spaces and assets automatically
✅ Rich metadata for relational and analytical datasets
✅ Query relational data with full SQL pushdown
✅ Query analytical data with metrics and dimensions
✅ Secure secret management
✅ Automatic token refresh

Prerequisites

Before you start, you'll need:

SAP Datasphere Tenant - Active Datasphere instance
OAuth2 App Registration - Client ID and secret (or use pre-delivered credentials)
Permissions - Access to spaces and views you want to query
Network Access - Ability to reach your Datasphere tenant

tip

For quick testing, ERPL-OData can use Datasphere's pre-delivered OAuth2 client. You only need your tenant name and data center!

Quick Start

Step 1: Create OAuth2 Secret

-- Load the extension
LOAD erpl_odata;

-- Create OAuth2 secret (minimal configuration)
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'your-tenant',
  data_center 'eu10',
  scope 'default'
);

This opens your browser for authentication. After you log in, the tokens are stored securely.

Step 2: Discover Your Spaces

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

Example Output:

┌──────────────┐
│     name     │
├──────────────┤
│ SALES        │
│ FINANCE      │
│ SUPPLY_CHAIN │
└──────────────┘

Step 3: Discover Assets in a Space

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

Example Output:

┌─────────────────────┬──────────────┬────────────────────┐
│        name         │ object_type  │  technical_name    │
├─────────────────────┼──────────────┼────────────────────┤
│ Sales Analytics     │ View         │ SALES_ANALYTICS_V  │
│ Customer Master     │ View         │ CUSTOMER_MASTER_V  │
│ Revenue by Region   │ View         │ REVENUE_REGION_V   │
└─────────────────────┴──────────────┴────────────────────┘

Step 4: Query Your Data

-- Read relational data
SELECT * 
FROM datasphere_read_relational('SALES', 'CUSTOMER_MASTER_V')
LIMIT 10;

Done! You're now querying SAP Datasphere from DuckDB.

OAuth2 Authentication

ERPL-OData supports multiple OAuth2 flows for different use cases.

Authorization Code Flow (Interactive)

Best for interactive use - opens browser for login:

CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'mytenant',
  data_center 'eu10',
  scope 'default',
  REDIRECT_URI 'http://localhost:65000'  -- Default
);

What happens:

A local server starts on port 65000
Browser opens to Datasphere login
You authenticate with your credentials
Tokens are saved in the secret
Tokens auto-refresh when expired

Client Credentials Flow (Service Accounts)

Best for automation and scripts - no browser needed:

CREATE SECRET datasphere_svc (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'mytenant',
  data_center 'eu10',
  client_id 'your-client-id',
  client_secret 'your-client-secret',
  GRANT_TYPE 'client_credentials',
  scope 'default'
);

warning

Keep client_secret secure! Never commit it to version control. Use environment variables or secure vaults.

Using Pre-Delivered Client

If you don't have custom OAuth2 app, use Datasphere's built-in client:

CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'mytenant',
  data_center 'eu10'
  -- No client_id or client_secret needed!
);

Data Centers

Specify your Datasphere data center:

Data Center	Location
`eu10`	Europe (Frankfurt)
`us10`	US East
`ap10`	Asia Pacific (Sydney)
`jp10`	Japan (Tokyo)

Discovery Functions

ERPL-OData provides comprehensive discovery to explore your Datasphere environment.

datasphere_show_spaces

List all accessible spaces.

Signature:

datasphere_show_spaces([secret VARCHAR])

Returns: Table with column:

name (VARCHAR): Space ID

Example:

SELECT * FROM datasphere_show_spaces();

datasphere_show_assets

List assets in a space or across all spaces.

Signatures:

-- Assets in specific space
datasphere_show_assets(space_id VARCHAR [, secret VARCHAR])

-- Assets across all spaces
datasphere_show_assets([secret VARCHAR])

Returns: Table with columns:

name (VARCHAR): Asset name (label)
object_type (VARCHAR): Type (View, Table, etc.)
technical_name (VARCHAR): Technical identifier
space_name (VARCHAR): Space ID (only when querying all spaces)

Examples:

-- Assets in one space
SELECT * FROM datasphere_show_assets('SALES');

-- All accessible assets
SELECT * FROM datasphere_show_assets();

-- Filter by type
SELECT * FROM datasphere_show_assets('SALES')
WHERE object_type = 'View';

datasphere_describe_space

Get detailed space metadata.

Signature:

datasphere_describe_space(space_id VARCHAR [, secret VARCHAR])

Returns: Table with columns:

name (VARCHAR): Space ID
label (VARCHAR): Display label

Example:

SELECT * FROM datasphere_describe_space('SALES');

datasphere_describe_asset

Get comprehensive asset metadata including schema details.

Signature:

datasphere_describe_asset(
  space_id VARCHAR, 
  asset_id VARCHAR 
  [, secret VARCHAR]
)

Returns: Table with 15 columns:

Basic Metadata:

name (VARCHAR): Technical name
space_name (VARCHAR): Space ID
label (VARCHAR): Display label
asset_type (VARCHAR): Asset type

Access URLs:

asset_relational_metadata_url (VARCHAR)
asset_relational_data_url (VARCHAR)
asset_analytical_metadata_url (VARCHAR)
asset_analytical_data_url (VARCHAR)

Capabilities:

supports_analytical_queries (BOOLEAN)
has_relational_access (BOOLEAN)
has_analytical_access (BOOLEAN)

Schema Information:

relational_schema (STRUCT): Relational columns definition
analytical_schema (STRUCT): Metrics, dimensions, variables
odata_context (VARCHAR)
odata_metadata_etag (VARCHAR)

Example:

SELECT 
  name,
  label,
  supports_analytical_queries,
  relational_schema,
  analytical_schema
FROM datasphere_describe_asset('SALES', 'REVENUE_REGION_V');

Reading Data

datasphere_read_relational

Query relational (table-like) data.

Signature:

datasphere_read_relational(
  space_id VARCHAR,
  asset_id VARCHAR
  [, secret VARCHAR]
  [, top BIGINT]
  [, skip BIGINT]
  [, params MAP(VARCHAR, VARCHAR)]
)

Parameters:

space_id: Space identifier
asset_id: Asset technical name
secret: Optional secret name (uses default if not specified)
top: Limit number of rows (OData $top)
skip: Skip rows (OData $skip)
params: Input parameters for parameterized views

Example:

-- Read all data
SELECT * FROM datasphere_read_relational('SALES', 'CUSTOMER_MASTER_V');

-- With limit
SELECT * 
FROM datasphere_read_relational('SALES', 'CUSTOMER_MASTER_V', top := 100);

-- With pagination
SELECT * 
FROM datasphere_read_relational(
  'SALES', 
  'CUSTOMER_MASTER_V',
  top := 50,
  skip := 100
);

-- With parameters
SELECT * 
FROM datasphere_read_relational(
  'SALES',
  'PARAMETERIZED_VIEW',
  params := {'YEAR': '2024', 'REGION': 'EMEA'}
);

datasphere_read_analytical

Query analytical (multidimensional) data.

Signature:

datasphere_read_analytical(
  space_id VARCHAR,
  asset_id VARCHAR
  [, secret VARCHAR]
  [, top BIGINT]
  [, skip BIGINT]
  [, params MAP(VARCHAR, VARCHAR)]
  [, metrics LIST(VARCHAR)]
  [, dimensions LIST(VARCHAR)]
)

Parameters:

space_id: Space identifier
asset_id: Asset technical name
secret: Optional secret name
top: Limit rows
skip: Skip rows
params: Input parameters
metrics: List of measures to retrieve
dimensions: List of dimensions to retrieve

tip

The metrics and dimensions parameters automatically generate an optimal OData $select clause for analytical queries.

Examples:

-- Read all metrics and dimensions
SELECT * FROM datasphere_read_analytical('SALES', 'REVENUE_ANALYTICS');

-- Select specific metrics and dimensions
SELECT * 
FROM datasphere_read_analytical(
  'SALES',
  'REVENUE_ANALYTICS',
  metrics := ['TotalRevenue', 'TotalCost', 'Profit'],
  dimensions := ['Region', 'ProductCategory', 'Year']
);

-- With parameters and limit
SELECT * 
FROM datasphere_read_analytical(
  'SALES',
  'REVENUE_ANALYTICS',
  params := {'FISCAL_YEAR': '2024'},
  metrics := ['TotalRevenue'],
  dimensions := ['Quarter', 'Region'],
  top := 100
);

Complete Workflow Example

Here's a full end-to-end workflow:

-- 1. Setup
LOAD erpl_odata;

CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'acme-corp',
  data_center 'eu10'
);

-- 2. Discover spaces
SELECT * FROM datasphere_show_spaces();
-- Result: SALES, FINANCE, HR

-- 3. Explore SALES space
SELECT name, object_type, technical_name
FROM datasphere_show_assets('SALES')
ORDER BY name;

-- 4. Get metadata for specific asset
SELECT 
  label,
  supports_analytical_queries,
  has_relational_access,
  has_analytical_access
FROM datasphere_describe_asset('SALES', 'Q1_REVENUE_VIEW');

-- 5. Query relational data
CREATE TABLE local_customers AS
SELECT * 
FROM datasphere_read_relational('SALES', 'CUSTOMER_MASTER')
WHERE country_code = 'US';

-- 6. Query analytical data
CREATE TABLE revenue_by_region AS
SELECT *
FROM datasphere_read_analytical(
  'SALES',
  'REVENUE_ANALYTICS',
  metrics := ['Revenue', 'Margin'],
  dimensions := ['Region', 'Quarter']
);

-- 7. Analyze locally with DuckDB
SELECT 
  Region,
  SUM(Revenue) as total_revenue,
  AVG(Margin) as avg_margin
FROM revenue_by_region
GROUP BY Region
ORDER BY total_revenue DESC;

Advanced Features

Named Parameters for Parameterized Views

Some Datasphere views require input parameters:

SELECT *
FROM datasphere_read_relational(
  'SALES',
  'MONTHLY_SALES_VIEW',
  params := {
    'P_YEAR': '2024',
    'P_MONTH': '03',
    'P_REGION': 'EMEA'
  }
);

Snake_Case Column Names

ERPL-OData converts column names to snake_case for consistency:

-- Datasphere column: TotalRevenue
-- DuckDB column: total_revenue

SELECT total_revenue, product_category
FROM datasphere_read_analytical('SALES', 'REVENUE_VIEW');

Combine with DuckDB Features

-- Export to Parquet
COPY (
  SELECT * FROM datasphere_read_relational('SALES', 'CUSTOMERS')
) TO 'customers.parquet' (FORMAT PARQUET);

-- Join Datasphere data with local data
SELECT 
  c.customer_name,
  l.local_data
FROM datasphere_read_relational('SALES', 'CUSTOMERS') c
JOIN local_table l ON c.customer_id = l.id;

-- Window functions
SELECT 
  region,
  revenue,
  AVG(revenue) OVER (PARTITION BY region) as avg_regional_revenue
FROM datasphere_read_analytical('SALES', 'REVENUE_VIEW');

Secret Management

Using Config Files

Store credentials in a config file:

# ~/.datasphere/config.ini
[datasphere]
tenant_name=acme-corp
data_center=eu10
client_id=abc123
client_secret=secret456

CREATE SECRET datasphere_cfg (
  TYPE datasphere,
  PROVIDER config,
  CONFIG_FILE '/home/user/.datasphere/config.ini'
);

Using File Provider

Store credentials in JSON:

{
  "tenant_name": "acme-corp",
  "data_center": "eu10",
  "access_token": "...",
  "refresh_token": "..."
}

CREATE SECRET datasphere_file (
  TYPE datasphere,
  PROVIDER file,
  FILEPATH '/secure/path/datasphere-creds.json'
);

Multiple Secrets for Multiple Tenants

-- Production tenant
CREATE SECRET datasphere_prod (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'acme-prod',
  data_center 'eu10'
);

-- Development tenant
CREATE SECRET datasphere_dev (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'acme-dev',
  data_center 'eu10'
);

-- Use specific secret
SELECT * 
FROM datasphere_read_relational(
  'SALES', 
  'CUSTOMERS',
  secret := 'datasphere_prod'
);

Performance Tips

1. Use Column Selection

Select only columns you need for better performance:

-- Good: Specific columns
SELECT customer_id, customer_name, country
FROM datasphere_read_relational('SALES', 'CUSTOMERS');

-- Less efficient: All columns
SELECT * FROM datasphere_read_relational('SALES', 'CUSTOMERS');

2. Use top Parameter for Sampling

-- Quick sample for exploration
SELECT * 
FROM datasphere_read_relational('SALES', 'LARGE_VIEW', top := 100);

3. Filter in DuckDB After Loading

OData filters can be limited. Sometimes it's faster to load data and filter locally:

-- Load once, filter multiple times
CREATE TABLE customer_cache AS
SELECT * FROM datasphere_read_relational('SALES', 'CUSTOMERS');

-- Fast local filtering
SELECT * FROM customer_cache WHERE country = 'US';
SELECT * FROM customer_cache WHERE revenue > 100000;

4. For Analytical Queries, Specify Metrics/Dimensions

-- Efficient: Only requested columns are transferred
SELECT *
FROM datasphere_read_analytical(
  'SALES',
  'REVENUE_VIEW',
  metrics := ['Revenue'],
  dimensions := ['Region']
);

Troubleshooting

OAuth2 Browser Not Opening

Issue: Browser doesn't open for authentication

Solution:

-- Check redirect_uri and port
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  tenant_name 'mytenant',
  data_center 'eu10',
  REDIRECT_URI 'http://localhost:65000'  -- Try different port if blocked
);

Token Expired

Issue: 401 Unauthorized after some time

Solution: Tokens are auto-refreshed. If refresh fails, recreate the secret:

DROP SECRET datasphere;
CREATE SECRET datasphere (...);  -- Re-authenticate

Asset Not Found

Issue: 404 Not Found when querying asset

Solution:

-- Verify asset exists
SELECT * FROM datasphere_show_assets('SPACE_NAME');

-- Check exact technical_name (case-sensitive)
SELECT technical_name FROM datasphere_show_assets('SPACE_NAME')
WHERE name LIKE '%search_term%';

Permission Denied

Issue: 403 Forbidden

Solution:

Verify your user has access to the space
Check space permissions in Datasphere UI
Ensure OAuth2 scopes include necessary permissions

Enable Tracing

For detailed diagnostics:

SET erpl_trace_enabled = TRUE;
SET erpl_trace_level = 'DEBUG';

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

-- Check trace logs for details

See Tracing & Diagnostics for more information.

Integration Patterns

ETL to Data Lake

-- Extract Datasphere data to Parquet
COPY (
  SELECT * FROM datasphere_read_relational('SALES', 'TRANSACTIONS')
  WHERE transaction_date >= '2024-01-01'
) TO 's3://datalake/sales/transactions.parquet' (FORMAT PARQUET);

Federated Analytics

-- Combine Datasphere with other sources
SELECT 
  ds.customer_id,
  ds.customer_name,
  pg.order_count,
  sf.crm_score
FROM datasphere_read_relational('SALES', 'CUSTOMERS') ds
LEFT JOIN postgres_customers pg ON ds.customer_id = pg.id
LEFT JOIN snowflake_crm sf ON ds.customer_id = sf.customer_id;

Incremental Sync

-- Track last sync time
CREATE TABLE sync_metadata (
  table_name VARCHAR,
  last_sync_time TIMESTAMP
);

-- Initial load
INSERT INTO sync_metadata VALUES ('CUSTOMERS', CURRENT_TIMESTAMP);

-- Incremental updates (if Datasphere view supports it)
INSERT INTO local_customers
SELECT * 
FROM datasphere_read_relational(
  'SALES',
  'CUSTOMERS',
  params := {'LAST_MODIFIED': (
    SELECT last_sync_time FROM sync_metadata WHERE table_name = 'CUSTOMERS'
  )::VARCHAR}
);

UPDATE sync_metadata 
SET last_sync_time = CURRENT_TIMESTAMP 
WHERE table_name = 'CUSTOMERS';

Next Steps

Learn about OData integration for general OData services
Explore Secrets Management for security best practices
See Tracing & Diagnostics for debugging
Check Examples for more patterns

Summary

ERPL-OData's Datasphere integration provides:

✅ Native Integration - First DuckDB client for Datasphere
✅ Secure - Full OAuth2 with automatic token refresh
✅ Discoverable - Explore spaces and assets easily
✅ Powerful - Query relational and analytical data
✅ Flexible - Combine with all DuckDB features

Connect your SAP Datasphere to DuckDB analytics today!

Overview​

Prerequisites​

Quick Start​

Step 1: Create OAuth2 Secret​

Step 2: Discover Your Spaces​

Step 3: Discover Assets in a Space​

Step 4: Query Your Data​

OAuth2 Authentication​

Authorization Code Flow (Interactive)​

Client Credentials Flow (Service Accounts)​

Using Pre-Delivered Client​

Data Centers​

Discovery Functions​

datasphere_show_spaces​

datasphere_show_assets​

datasphere_describe_space​

datasphere_describe_asset​

Reading Data​

datasphere_read_relational​

datasphere_read_analytical​

Complete Workflow Example​

Advanced Features​

Named Parameters for Parameterized Views​

Snake_Case Column Names​

Combine with DuckDB Features​

Secret Management​

Using Config Files​

Using File Provider​

Multiple Secrets for Multiple Tenants​

Performance Tips​

1. Use Column Selection​

2. Use top Parameter for Sampling​

3. Filter in DuckDB After Loading​

4. For Analytical Queries, Specify Metrics/Dimensions​

Troubleshooting​

OAuth2 Browser Not Opening​

Token Expired​

Asset Not Found​

Permission Denied​

Enable Tracing​

Integration Patterns​

ETL to Data Lake​

Federated Analytics​

Incremental Sync​

Next Steps​

Summary​

Overview

Prerequisites

Quick Start

Step 1: Create OAuth2 Secret

Step 2: Discover Your Spaces

Step 3: Discover Assets in a Space

Step 4: Query Your Data

OAuth2 Authentication

Authorization Code Flow (Interactive)

Client Credentials Flow (Service Accounts)

Using Pre-Delivered Client

Data Centers

Discovery Functions

datasphere_show_spaces

datasphere_show_assets

datasphere_describe_space

datasphere_describe_asset

Reading Data

datasphere_read_relational

datasphere_read_analytical

Complete Workflow Example

Advanced Features

Named Parameters for Parameterized Views

Snake_Case Column Names

Combine with DuckDB Features

Secret Management

Using Config Files

Using File Provider

Multiple Secrets for Multiple Tenants

Performance Tips

1. Use Column Selection

2. Use top Parameter for Sampling

3. Filter in DuckDB After Loading

4. For Analytical Queries, Specify Metrics/Dimensions

Troubleshooting

OAuth2 Browser Not Opening

Token Expired

Asset Not Found

Permission Denied

Enable Tracing

Integration Patterns

ETL to Data Lake

Federated Analytics

Incremental Sync

Next Steps

Summary