duckdb-opendal (opendal)

A DuckDB extension that integrates Apache OpenDAL as a virtual filesystem, enabling transparent read and write access to multiple storage services through a unified SQL interface.

Query, glob, and write files (Parquet, CSV, JSON, etc.) directly on remote and local storage using standard SQL.

---

Installation

Install the signed extension from the DuckDB community repository:

INSTALL opendal FROM community;
LOAD opendal;

Requires DuckDB ≥ 1.5.5. Community binaries are per DuckDB version; older DuckDB returns HTTP 404 on INSTALL. See Compatibility.

---

Key Features

• Unified Virtual Filesystem: Serve files from multiple storage services directly within DuckDB queries — see the service guides for the ones supported today, with more services coming soon.
• Table Functions: Inspect and manage storage from SQL — list, stat, measure and copy objects, and check what a URL resolves to. See the table-function reference.
• Scoped Secrets: Configure credentials, custom endpoints, and regions with bucket/path specificity using DuckDB's native secret manager.
• Configurable Layers: Tuning of retry behavior, timeout ceilings, concurrent request limits, and data caching per secret or globally.
• Built-in Data Caching: Support for in-memory and persistent on-disk data caching (leveraging Foyer) without any manual query adjustments.
• Coexistence Layer: Dynamically delegate specific schemes (like s3://) to opendal instead of native extensions (like httpfs) using opendal_override_native_filesystems.

---

SQL Usage Examples

1. Simple Local Read & Write

Ensure the local filesystem root is configured via secrets:

-- Register a secret for local filesystem access
CREATE SECRET local_root (
    TYPE fs,
    SCOPE 'fs://',
    config MAP{'root': '.'}
);

-- Write a Parquet file to a relative path
COPY (SELECT range AS id, range * 2 AS doubled FROM range(1000))
TO 'fs:///output_data.parquet' (FORMAT parquet);

-- Read the Parquet file back
SELECT count(*), sum(doubled)
FROM read_parquet('fs:///output_data.parquet');

2. S3 / Object Storage Configuration

Configure S3 options under a scoped secret:

CREATE SECRET warehouse (
    TYPE s3,
    SCOPE 's3://warehouse-bucket',
    config MAP{
        'access_key_id': 'your-access-key',
        'secret_access_key': 'your-secret-key',
        'region': 'us-east-1',
        'endpoint': 'http://127.0.0.1:9000'
    },
    io_config MAP{
        'read.concurrent': '4',
        'read.chunk_size': '8 MiB',
        'write.concurrent': '4',
        'write.chunk_size': '8 MiB'
    },
    retry_config MAP{
        'max_times': '5',
        'jitter': 'true'
    }
);

-- Read from the S3 bucket
SELECT * FROM read_parquet('s3://warehouse-bucket/data/**/*.parquet');

3. Storage Utilities & Table Functions

-- Stat a file
SELECT * FROM opendal_stat('s3://warehouse-bucket/data/file.parquet');

-- List directory contents recursively
SELECT name, metadata.content_length
FROM opendal_ls('s3://warehouse-bucket/data/', recursive := true);

-- Calculate disk usage rollup
SELECT directory, file_count, total_size
FROM opendal_du('s3://warehouse-bucket/data/');

-- Copy files across services
SELECT * FROM opendal_copy(
    's3://warehouse-bucket/data/input.csv',
    'fs:///local_backups/backup.csv'
);

4. Native Coexistence

By default, DuckDB routes s3:// and gcs:// to its native httpfs extension. You can instruct opendal to take precedence for specific schemes using:

-- Let opendal handle s3:// and gcs:// queries
SET opendal_override_native_filesystems = 's3,gcs';

-- Queries under these schemes now run through opendal's filesystem registry
SELECT * FROM read_parquet('s3://my-bucket/dataset.parquet');

-- Revert back to httpfs handling
SET opendal_override_native_filesystems = '';

---

Compatibility

Use the latest DuckDB release to get the latest services. Community binaries are published per DuckDB version and the extension is built for the current stable release, so available services and fixes track the build for your DuckDB. On older DuckDB, INSTALL opendal FROM community returns HTTP 404 — upgrade to the latest patch, or build from source against your version and load with allow_unsigned_extensions. Check your installed version with SELECT opendal_version();.

Each release pins a DuckDB version and an Apache OpenDAL release or revision:

Extension	DuckDB	OpenDAL	Services
0.1.0	v1.5.x	3180510	fs, memory, s3

v1.5.x = any patch on the DuckDB v1.5 line. The row lists what that release shipped; for the services in your build run FROM opendal_schemes();.

See the releases for per-version notes, and MAINTAINING.md for how these versions are upgraded.

---

User guides live in docs/:

• Services — one guide per service, each with a working CREATE SECRET.
• Configuration — secrets and scopes, global defaults, concurrency, retries, timeouts.
• Caching — in-memory and on-disk read caching.
• Table functions — opendal_ls, opendal_du, opendal_copy, and friends.
• Coexistence — sharing schemes such as s3:// with httpfs.

---

Contributing

Bug reports, feature requests, and pull requests are welcome. See CONTRIBUTING.md for the developer guide (build, test, and architecture).

---

License

This project is licensed under the MIT License. See LICENSE for details.