DuckDB 分区表实战：6 个场景让查询提速 10 倍

import duckdb

# 连接数据库（文件形式存储）
conn = duckdb.connect('sales.db')

# 1. 创建分区表（指定分区字段）
conn.execute("""
    CREATE TABLE orders (
        order_id INTEGER,
        customer_id INTEGER,
        amount DECIMAL(10,2),
        order_date DATE
    ) PARTITION_BY (year(order_date), month(order_date))
""")

# 2. 写入数据（自动按分区存储）
conn.execute("""
    COPY orders FROM 'orders_2023.csv' 
    (FORMAT CSV, HEADER true, AUTO_DETECT true)
""")

# 3. 查看分区结构
partitions = conn.execute("SELECT * FROM duckdb_partitions()").fetchdf()
print(partitions[['database_name', 'schema_name', 'table_name', 'partition_expression']])

import duckdb
import time

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

# 普通查询（可能触发全表扫描）
start = time.time()
result = conn.execute("""
    SELECT sum(amount) 
    FROM orders 
    WHERE order_date BETWEEN '2023-11-01' AND '2023-11-30'
""").fetchone()
print(f"普通查询耗时: {time.time() - start:.2f}秒")

# 使用分区裁剪的查询（正确姿势）
start = time.time()
result = conn.execute("""
    SELECT sum(amount) 
    FROM orders 
    WHERE year(order_date) = 2023 AND month(order_date) = 11
""").fetchone()
print(f"分区裁剪查询耗时: {time.time() - start:.2f}秒")
print(f"11月销售额: {result[0]}")

# 查看执行计划，确认分区裁剪生效
plan = conn.execute("EXPLAIN SELECT sum(amount) FROM orders WHERE year(order_date) = 2023 AND month(order_date) = 11").fetchdf()
print(plan)

import duckdb
import pandas as pd
from datetime import datetime, timedelta

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

# 创建按天分区的表
conn.execute("""
    CREATE TABLE sensor_data (
        device_id INTEGER,
        temperature FLOAT,
        humidity FLOAT,
        event_time TIMESTAMP
    ) PARTITION_BY (date(event_time))
""")

# 模拟实时数据流写入
for i in range(10):
    now = datetime.now() + timedelta(days=i)
    df = pd.DataFrame({
        'device_id': [1, 2, 3],
        'temperature': [20.5 + i, 21.0 + i, 19.8 + i],
        'humidity': [45.0, 50.0, 55.0],
        'event_time': [now, now, now]
    })
    
    # 动态写入，自动创建新分区
    conn.execute("INSERT INTO sensor_data SELECT * FROM df")

# 查看自动创建的分区
partitions = conn.execute("""
    SELECT DISTINCT partition_id 
    FROM duckdb_partitions() 
    WHERE table_name = 'sensor_data'
""").fetchdf()
print("已创建的分区:", partitions)

import duckdb

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

# 创建用户表（按地区分区）
conn.execute("""
    CREATE TABLE users (
        user_id INTEGER,
        user_name VARCHAR,
        region VARCHAR
    ) PARTITION_BY (region)
""")

conn.execute("""
    INSERT INTO users VALUES 
    (1, 'Alice', '华东'),
    (2, 'Bob', '华北'),
    (3, 'Charlie', '华南')
""")

# 订单表也按 region 分区
conn.execute("""
    CREATE TABLE orders_partitioned (
        order_id INTEGER,
        user_id INTEGER,
        amount DECIMAL(10,2),
        region VARCHAR
    ) PARTITION_BY (region)
""")

# 查询时，DuckDB 自动进行分区裁剪 + 本地 JOIN
result = conn.execute("""
    SELECT o.order_id, u.user_name, o.amount
    FROM orders_partitioned o
    JOIN users u ON o.user_id = u.user_id
    WHERE o.region = '华东'
""").fetchdf()
print(result)

import duckdb

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

# 查看当前所有分区
print("删除前分区:")
print(conn.execute("""
    SELECT * FROM duckdb_partitions() 
    WHERE table_name = 'sensor_data'
""").fetchdf())

# 方式1：直接删除整个分区（最快）
conn.execute("ALTER TABLE sensor_data DROP PARTITION (date '2024-01-01')")

# 方式2：合并小分区（将 2024年1月 所有天合并）
conn.execute("""
    ALTER TABLE sensor_data 
    MERGE PARTITIONS 
    WHERE date(event_time) BETWEEN '2024-01-01' AND '2024-01-31'
""")

print("删除后分区:")
print(conn.execute("""
    SELECT * FROM duckdb_partitions() 
    WHERE table_name = 'sensor_data'
""").fetchdf())

import duckdb

conn = duckdb.connect()

# 创建外部表（指向分区目录）
conn.execute("""
    CREATE OR REPLACE TABLE external_sales AS 
    SELECT * FROM read_parquet(
        'data/*/*/*.parquet',
        hive_partitioning = true,
        union_by_name = true
    )
""")

# 或者直接查询（不建表）
result = conn.execute("""
    SELECT year, month, sum(amount) as total
    FROM read_parquet(
        'data/*/*/*.parquet',
        hive_partitioning = true,
        union_by_name = true
    )
    WHERE year = '2024' AND month = '01'
    GROUP BY year, month
""").fetchdf()
print(result)

# 查看自动发现的虚拟分区列
schema = conn.execute("""
    DESCRIBE SELECT * FROM read_parquet(
        'data/*/*/*.parquet',
        hive_partitioning = true,
        union_by_name = true
    )
""").fetchdf()
print(schema[['column_name', 'column_type']])