DuckDB: In-Process Analytics That Replaces Spark for Single-Machine Workloads

import duckdb

# Query a single Parquet file
result = duckdb.sql("SELECT region, SUM(revenue) FROM 'sales.parquet' GROUP BY region").df()

# Query all Parquet files in a directory
result = duckdb.sql("SELECT * FROM read_parquet('data/*.parquet') WHERE date >= '2025-01-01'").df()

# Join two files  -  no loading into memory required
result = duckdb.sql("""
    SELECT o.order_id, u.email, o.total
    FROM read_parquet('orders.parquet') o
    JOIN read_csv('users.csv') u ON o.user_id = u.id
    LIMIT 1000
""").df()

import duckdb

con = duckdb.connect("analytics.duckdb")  # persistent database

# Create table from Parquet
con.execute("CREATE TABLE events AS SELECT * FROM read_parquet('events/*.parquet')")

# Query with pandas interop
df = con.execute("SELECT date_trunc('day', ts) AS day, count(*) FROM events GROUP BY 1").df()

# Register a Pandas DataFrame as a virtual table
import pandas as pd
users_df = pd.read_csv("users.csv")
con.register("users", users_df)
result = con.execute("SELECT * FROM users WHERE country = 'US'").df()

import duckdb
import polars as pl

# Read with DuckDB, convert to Polars for transformation
result = duckdb.sql("SELECT * FROM 'large.parquet' WHERE status = 'active'").pl()

# Register Polars LazyFrame in DuckDB
lf = pl.scan_parquet("data/*.parquet")
duckdb.register("my_data", lf.collect().to_arrow())
result = duckdb.sql("SELECT category, AVG(value) FROM my_data GROUP BY category").df()