DuckDB Remote Storage Integration

DuckDB provides multiple ways to access cloud storage (S3, GCS, Azure) from within the database.

HTTPFS Extension (Native)

The HTTPFS extension enables direct queries on remote files.

import duckdb from contextlib import contextmanager

@contextmanager def get_duckdb_connection(): """Context manager ensures connection cleanup.""" con = duckdb.connect() try: con.execute("INSTALL httpfs; LOAD httpfs;") yield con finally: con.close()

Configure and query

with get_duckdb_connection() as con: # S3 configuration con.execute(""" SET s3_region='us-east-1'; SET s3_access_key_id='AKIA...'; SET s3_secret_access_key='...'; -- For temporary creds: SET s3_session_token='...' -- For S3-compatible: SET s3_endpoint='http://minio:9000'; """)

# Query Parquet directly
df = con.sql("""
    SELECT category, SUM(value) as total
    FROM read_parquet('s3://bucket/data/*.parquet')
    WHERE date >= '2024-01-01'
    GROUP BY category
""").pl()

# Read from GCS (configure via environment or default credentials)
df = con.sql("SELECT * FROM read_csv('gs://bucket/data.csv')").pl()

Configuration via Environment Variables

Instead of hardcoding credentials, use environment variables:

import os os.environ['AWS_ACCESS_KEY_ID'] = 'AKIA...' os.environ['AWS_SECRET_ACCESS_KEY'] = '...' os.environ['AWS_REGION'] = 'us-east-1'

DuckDB HTTPFS reads these automatically on first use

import duckdb con = duckdb.connect() con.execute("INSTALL httpfs; LOAD httpfs;") df = con.sql("SELECT * FROM read_parquet('s3://bucket/data.parquet')").pl()

Via fsspec

Register fsspec filesystems for protocols DuckDB doesn't natively support:

import fsspec import duckdb

Register GCS (or any fsspec protocol)

duckdb.register_filesystem(fsspec.filesystem('gcs'))

Now use gcs:// URIs natively

df = duckdb.sql(""" SELECT * FROM read_parquet('gcs://bucket/data.parquet') """).pl()

Copy Operations

Copy data between DuckDB tables and cloud storage:

import duckdb

with duckdb.connect() as con: # Export table to S3 con.sql(""" COPY (SELECT * FROM my_table) TO 's3://bucket/output.parquet' (FORMAT PARQUET) """)

# Import from S3
con.sql("""
    CREATE TABLE imported AS
    SELECT * FROM read_parquet('s3://bucket/input.parquet')
""")

Delta Lake Integration

Read Delta tables from cloud storage:

import duckdb

with duckdb.connect() as con: con.execute("INSTALL delta; LOAD delta;")

# Query Delta table
df = con.sql("""
    SELECT * FROM delta_scan('s3://bucket/delta-table/')
    WHERE date >= '2024-01-01'
""").pl()

# Time travel (read specific version)
df = con.sql("""
    SELECT * FROM delta_scan('s3://bucket/delta-table/', version => 5)
""").pl()

Connection Management (FIXED)

✅ DO: Use context manager

with duckdb.connect("analytics.db") as con: con.sql("CREATE TABLE ...")

❌ DON'T: Leak connections

con = duckdb.connect("analytics.db") con.sql("...") # Never closed → leak

✅ DO: If you must, manually close

con = duckdb.connect("analytics.db") try: con.sql("...") finally: con.close()

Authentication

See @data-engineering-storage-authentication for S3, GCS, Azure patterns. DuckDB HTTPFS reads standard environment variables (AWS_ACCESS_KEY_ID , AWS_SECRET_ACCESS_KEY , AWS_REGION , etc.) automatically.

Performance Tips

• ✅ Predicate pushdown: Filter in SQL query, not after loading

• ✅ Column pruning: Only select needed columns

• ✅ Parquet format: Use Parquet (not CSV) for remote queries

• ✅ Partitioning: Store data partitioned (e.g., by date) for efficient queries

• ⚠️ Row group filtering: Parquet row groups enable scanning subsets

• ⚠️ Use EXPLAIN to verify pushdown: con.sql("EXPLAIN SELECT ...").pl()

References

• DuckDB HTTPFS Documentation

• DuckDB Delta Lake Extension

• @data-engineering-core

• DuckDB basics