Storage

DuckDB

DuckDB is a high-performance SQL database system optimized for analytical workloads. It can directly query Iceberg tables stored in your analytics buckets, making it ideal for data exploration and complex analytical queries.

Installation#

Install DuckDB and the Iceberg extension:

1
pip install duckdb duckdb-iceberg

Connecting to Analytics buckets#

Here's a complete example of connecting to your Supabase analytics bucket and querying Iceberg tables:

1
import duckdb
2
import os
3
4
# Configuration
5
PROJECT_REF = "your-project-ref"
6
WAREHOUSE = "your-analytics-bucket-name"
7
SERVICE_KEY = "your-service-key"
8
9
# S3 credentials
10
S3_ACCESS_KEY = "your-access-key"
11
S3_SECRET_KEY = "your-secret-key"
12
S3_REGION = "us-east-1"
13
14
# Construct endpoints
15
S3_ENDPOINT = f"https://{PROJECT_REF}.supabase.co/storage/v1/s3"
16
CATALOG_URI = f"https://{PROJECT_REF}.supabase.co/storage/v1/iceberg"
17
18
# Initialize DuckDB connection
19
conn = duckdb.connect(":memory:")
20
21
# Install and load the Iceberg extension
22
conn.install_extension("iceberg")
23
conn.load_extension("iceberg")
24
25
# Configure Iceberg catalog with Supabase credentials
26
conn.execute(f"""
27
    CREATE SECRET (
28
        TYPE S3,
29
        KEY_ID '{S3_ACCESS_KEY}',
30
        SECRET '{S3_SECRET_KEY}',
31
        REGION '{S3_REGION}',
32
        ENDPOINT '{S3_ENDPOINT}',
33
        URL_STYLE 'virtual'
34
    );
35
""")
36
37
# Configure the REST catalog
38
conn.execute(f"""
39
    ATTACH 'iceberg://{CATALOG_URI}' AS iceberg_catalog
40
    (
41
        TYPE ICEBERG_REST,
42
        WAREHOUSE '{WAREHOUSE}',
43
        TOKEN '{SERVICE_KEY}'
44
    );
45
""")
46
47
# Query your Iceberg tables
48
result = conn.execute("""
49
    SELECT *
50
    FROM iceberg_catalog.default.events
51
    LIMIT 10
52
""").fetchall()
53
54
for row in result:
55
    print(row)
56
57
# Complex aggregation example
58
analytics = conn.execute("""
59
    SELECT
60
        event_name,
61
        COUNT(*) as event_count,
62
        COUNT(DISTINCT user_id) as unique_users
63
    FROM iceberg_catalog.default.events
64
    GROUP BY event_name
65
    ORDER BY event_count DESC
66
""").fetchdf()
67
68
print(analytics)

Key features with DuckDB#

Efficient data exploration#

DuckDB's lazy evaluation means it only scans the data you need:

1
# This only reads the columns you select
2
events = conn.execute("""
3
    SELECT event_id, event_name, event_timestamp
4
    FROM iceberg_catalog.default.events
5
    WHERE event_timestamp > NOW() - INTERVAL '7 days'
6
""").fetchdf()

Converting to Pandas#

Convert results to Pandas DataFrames for further analysis:

1
df = conn.execute("""
2
    SELECT *
3
    FROM iceberg_catalog.default.events
4
""").fetchdf()
5
6
# Use pandas for visualization or further processing
7
print(df.describe())

Exporting results#

Save your analytical results to various formats:

1
# Export to Parquet
2
conn.execute("""
3
    COPY (
4
        SELECT * FROM iceberg_catalog.default.events
5
    ) TO 'results.parquet'
6
""")
7
8
# Export to CSV
9
conn.execute("""
10
    COPY (
11
        SELECT event_name, COUNT(*) as count
12
        FROM iceberg_catalog.default.events
13
        GROUP BY event_name
14
    ) TO 'summary.csv' (FORMAT CSV, HEADER true)
15
""")

Best practices#

  • Connection pooling - Reuse connections for multiple queries
  • Partition pruning - Filter by partition columns to improve query performance
  • Column selection - Only select columns you need to reduce I/O
  • Limit results - Use LIMIT during exploration to avoid processing large datasets

Next steps#