DuckDB 分区表实战：理解分区裁剪与数据管理

import duckdb

# 数据目录结构：
# /data/orders/year=2023/month=01/
# /data/orders/year=2023/month=02/
# ...

conn = duckdb.connect('orders.db')

# 创建外部表指向分区目录
conn.execute("""
    CREATE VIEW orders_view AS
    SELECT * FROM read_parquet('/data/orders/*.parquet')
""")

# DuckDB 会自动优化路径扫描
result = conn.execute("""
    SELECT month, SUM(amount) as total
    FROM orders_view
    WHERE month >= '2023-06'
    GROUP BY month
""").fetchdf()

import duckdb

conn = duckdb.connect('sales.db')

# 创建基础表
conn.execute("""
    CREATE TABLE orders (
        order_id INTEGER,
        customer_id INTEGER,
        amount DECIMAL(10,2),
        order_date DATE,
        region VARCHAR
    )
""")

# 插入数据
conn.execute("""
    INSERT INTO orders
    SELECT 
        gen,
        (random() * 10000)::INTEGER,
        ROUND(random() * 1000, 2),
        DATE '2023-01-01' + (random() * 365)::INTEGER,
        CASE random() * 4
            WHEN 0 THEN 'East'
            WHEN 1 THEN 'South'
            WHEN 2 THEN 'North'
            WHEN 3 THEN 'West'
            ELSE 'Central'
        END
    FROM generate_series(1, 1000000) AS t(gen)
""")

# 查看数据分布
print(conn.execute("SELECT count(*) FROM orders").fetchone()[0])
print(conn.execute("SELECT region, count(*) FROM orders GROUP BY region").fetchdf())

-- DuckDB 会自动将日期过滤推入 Parquet 文件扫描
-- 利用 Parquet 文件的 zone map 跳过不匹配的列块
SELECT region, SUM(amount)
FROM read_parquet('/data/sales/*.parquet')
WHERE order_date >= '2023-06-01'
GROUP BY region;

EXPLAIN
SELECT region, SUM(amount)
FROM read_parquet('/data/sales/*.parquet')
WHERE order_date >= '2023-06-01'
GROUP BY region;

# 导出为 Parquet（带分区）
conn.execute("""
    COPY (
        SELECT * FROM orders
        WHERE order_date >= '2023-01-01'
    ) TO '/data/export/orders_2023.parquet' (FORMAT PARQUET)
""")

# 合并小文件提高读取效率
conn.execute("""
    CREATE TABLE merged_orders AS
    SELECT * FROM read_parquet('/data/small_partitions/*.parquet')
""")