DuckDB: In-process OLAP SQL, Parquet and Python Reference Guide

# macOS / Linux — Homebrew
brew install duckdb

# Or grab a static binary (Linux/macOS/Windows)
curl -L https://install.duckdb.org | sh

# Python — pip
pip install "duckdb>=1.1"

# Open in-memory shell
duckdb

# Open or create a persistent DB file
duckdb sales.duckdb

# One-shot — emit CSV from a Parquet file in one line
duckdb -c "COPY (SELECT * FROM 's3://bucket/y=2026/*.parquet') TO 'out.csv'"

# Python check
python -c "import duckdb; print(duckdb.__version__)"

-- Query a remote Parquet file in place — no copy, no load.
-- DuckDB pushes filters + column projections down into the Parquet reader.
INSTALL httpfs; LOAD httpfs;

-- (Optional) S3 credentials via the typed secret API
CREATE OR REPLACE SECRET s3_demo (
    TYPE s3,
    KEY_ID    'AKIA...',
    SECRET    '****',
    REGION    'us-east-1'
);

-- Glob across a Hive-partitioned dataset; only the year=2026 files are read.
SELECT region,
       sum(amount) AS revenue,
       count(*)    AS orders
FROM read_parquet(
    's3://acme-data/orders/year=*/month=*/*.parquet',
    hive_partitioning = TRUE
)
WHERE year = 2026
  AND status = 'paid'
GROUP BY region
ORDER BY revenue DESC;

-- Materialize a slice locally for further work
CREATE TABLE orders_q1 AS
SELECT * FROM read_parquet('s3://acme-data/orders/year=2026/month=0[1-3]/*.parquet');

-- 7-day trailing revenue per user, with day-over-day delta and quartile bucket.
WITH daily AS (
    SELECT user_id,
           date_trunc('day', ts) AS d,
           sum(amount)           AS rev
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id, d
)
SELECT user_id, d, rev,
       -- trailing 7 days, including today (sliding window, rows-based)
       sum(rev) OVER w7 AS rev_7d,
       -- yesterday's revenue, for delta plots
       lag(rev, 1) OVER (PARTITION BY user_id ORDER BY d) AS rev_prev,
       -- quartile bucket within each day
       ntile(4) OVER (PARTITION BY d ORDER BY rev) AS rev_q
FROM daily
WINDOW w7 AS (
    PARTITION BY user_id ORDER BY d
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
QUALIFY rev_q = 4                                 -- keep top-quartile rows only
ORDER BY d DESC, rev_7d DESC;

import duckdb
import pandas as pd

# In-memory connection (or duckdb.connect("file.duckdb") for persistence)
con = duckdb.connect()

# Pandas → DuckDB: query a DataFrame by its Python variable name
sales = pd.DataFrame({"region": ["EU", "US", "EU"], "amount": [10, 20, 5]})
top = con.sql("""
    SELECT region, sum(amount) AS total
    FROM sales
    GROUP BY region
    ORDER BY total DESC
""")
print(top.df())                          # Result -> DataFrame
print(top.arrow())                       # Result -> pyarrow.Table (zero-copy)

# Parameterized — bind values, never f-string
row = con.execute(
    "SELECT total FROM (SELECT region, sum(amount) AS total FROM sales GROUP BY region) "
    "WHERE region = ?", ["EU"]
).fetchone()
print(row)                               # (15,)

# Register a fresh DataFrame under a chosen name
con.register("orders_df", pd.read_csv("orders.csv"))
con.sql("SELECT count(*) FROM orders_df").show()

# Export query result straight to Parquet — no intermediate file
con.sql("SELECT * FROM sales WHERE amount > 5").write_parquet("hot.parquet")

# End-to-end: read remote Parquet, aggregate, write Parquet out.
# Replaces a small PySpark/Pandas script — runs single-process, multi-core.
import duckdb

con = duckdb.connect()                                  # in-memory, multi-threaded
con.execute("INSTALL httpfs; LOAD httpfs;")
con.execute("PRAGMA threads = 8")
con.execute("PRAGMA memory_limit = '4GB'")

# Hive-partitioned dataset on S3 → ranked top regions per month, in one query
result = con.sql("""
    WITH paid AS (
        SELECT region, year, month, amount
        FROM read_parquet(
            's3://acme-data/orders/year=*/month=*/*.parquet',
            hive_partitioning = TRUE
        )
        WHERE status = 'paid'
    )
    SELECT year, month, region, revenue
    FROM (
        SELECT year, month, region,
               sum(amount)                                            AS revenue,
               row_number() OVER (PARTITION BY year, month
                                  ORDER BY sum(amount) DESC)          AS rk
        FROM paid
        GROUP BY year, month, region
    )
    WHERE rk <= 5
    ORDER BY year DESC, month DESC, rk
""")

result.write_parquet("top_regions_by_month.parquet")    # columnar, compressed
print(result.df().head())                               # peek as a DataFrame