DuckDB v1.5.4 (Variegata) 关键新特性与生产环境最佳实践

-- 启动 DuckDB CLI 并强制使用暗色模式
duckdb -dark-mode

-- 或者显式指定亮色模式
duckdb -light-mode

-- 在暗色终端中自动检测
duckdb

-- 创建包含 VARIANT 类型的表
CREATE TABLE events AS
SELECT 
    event_id,
    user_data::VARCHAR AS user_json,
    PARSE_JSON(user_data) AS variant_data
FROM (VALUES
    (1, '{"name": "Alice", "age": 30, "tags": ["data", "duckdb"]}'),
    (2, '{"name": "Bob", "age": 25, "tags": ["analytics"]}'),
    (3, '{"name": "Charlie", "age": 35, "tags": ["engineering", "duckdb"]}'),
    (4, '{"name": "Diana", "age": 28, "tags": ["ml", "data"]}'),
    (5, '{"name": "Eve", "age": 32, "tags": ["devops"]}')
) AS t(event_id, user_data);

-- 使用 FILTER 条件查询 VARIANT 字段
-- v1.5.4 之前可能返回错误行，现在已修复
SELECT 
    event_id,
    variant_data:name::VARCHAR AS name,
    variant_data:age::INTEGER AS age
FROM events
WHERE variant_data:tags[*] = 'duckdb';

-- 结果:
-- ┌──────────┬─────────┬───────┐
-- │ event_id │  name   │  age  │
-- │  int32   │ varchar │ int32 │
-- ├──────────┼─────────┼───────┤
-- │        1 │ Alice   │    30 │
-- │        3 │ Charlie │    35 │
-- └──────────┴─────────┴───────┘

-- 安全地解析 JSON，忽略错误行
CREATE TABLE raw_logs AS
SELECT 
    id,
    TRY_PARSE_JSON(log_data) AS parsed
FROM log_entries;

-- 使用 json_keys 提取嵌套对象的键
SELECT 
    id,
    json_keys(parsed, '$') AS keys
FROM raw_logs
WHERE parsed IS NOT NULL;

-- 验证 JSON 键不为 NULL
SELECT json_valid('{}') AS is_valid;  -- TRUE
SELECT json_valid(NULL) IS NULL;       -- TRUE

-- 设置源表和目标表
CREATE TABLE target_products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR,
    price DECIMAL(10,2),
    updated_at TIMESTAMP
);

CREATE TABLE staging_products (
    product_id INTEGER,
    name VARCHAR,
    price DECIMAL(10,2),
    created_at TIMESTAMP
);

-- 插入测试数据
INSERT INTO staging_products VALUES
    (1, 'Laptop Pro', 1299.99, CURRENT_TIMESTAMP),
    (2, 'Wireless Mouse', 29.99, CURRENT_TIMESTAMP),
    (3, 'USB-C Hub', 49.99, CURRENT_TIMESTAMP);

-- 正确的 MERGE INTO 用法
MERGE INTO target_products t
USING staging_products s
ON t.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET 
        price = s.price,
        updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, updated_at)
    VALUES (s.product_id, s.name, s.price, CURRENT_TIMESTAMP);

-- 验证结果
SELECT * FROM target_products ORDER BY product_id;

-- 启用并查看 Parquet 扫描统计信息
INSTALL spatial;
LOAD spatial;

-- 设置显示操作符统计信息
SET enable_profiling = true;
SET profiling_mode = 'json';

-- 查询 Parquet 文件（利用统计信息剪枝）
SELECT 
    city,
    AVG(population) AS avg_population,
    COUNT(*) AS record_count
FROM read_parquet('/data/geography/*.parquet')
WHERE population > 100000
GROUP BY city
ORDER BY avg_population DESC;

-- 查看执行计划中的行组扫描信息
EXPLAIN ANALYZE
SELECT * 
FROM read_parquet('/data/geography/*.parquet')
WHERE area_sqkm > 500;

INSTALL spatial;
LOAD spatial;

-- 创建包含几何数据的表
CREATE TABLE locations (
    id INTEGER,
    name VARCHAR,
    geom GEOMETRY
);

-- 插入地理数据
INSERT INTO locations VALUES
    (1, '北京', ST_GeomFromText('POINT(116.4074 39.9042)')),
    (2, '上海', ST_GeomFromText('POINT(121.4737 31.2304)')),
    (3, '广州', ST_GeomFromText('POINT(113.2644 23.1291)'));

-- 查询特定区域的地点
SELECT name, ST_AsText(geom) 
FROM locations 
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(116.4074 39.9042)'), 500000);

-- 附加数据库时自动重建索引
ATTACH 'path/to/database.duckdb' AS remote_db (
    vacuum_rebuild_indexes true
);

-- 使用附加的数据库
SELECT * FROM remote_db.my_table LIMIT 10;

-- 复杂的窗口函数查询
WITH ranked_sales AS (
    SELECT 
        product_id,
        sale_date,
        amount,
        RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS rank,
        SUM(amount) OVER (
            PARTITION BY product_id 
            ORDER BY sale_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS moving_avg_7d
    FROM sales_data
)
SELECT * FROM ranked_sales WHERE rank <= 3;

-- 安全地读取外部 Parquet 文件
-- v1.5.4 会更好地处理异常输入
SELECT * 
FROM read_parquet('/data/untrusted/*.parquet', 
    use_vfs=true, 
    max_size=104857600);  -- 限制最大读取 100MB

# 方法 1: 通过 pip 升级
pip install --upgrade duckdb

# 方法 2: 通过 conda 升级
conda update -c conda-forge duckdb

# 方法 3: 下载最新 CLI
wget https://github.com/duckdb/duckdb/releases/download/v1.5.4/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
chmod +x duckdb
./duckdb --version  # 验证版本

import duckdb

# 检查当前版本
print(duckdb.__version__)  # 应该输出 1.5.4

# 使用新版特性
con = duckdb.connect(':memory:')

# 暗色模式下的终端体验更好
result = con.execute("""
    SELECT 
        'v1.5.4' AS version,
        'Variegata' AS codename,
        'CLI dark mode support' AS new_feature
""").fetchall()

for row in result:
    print(row)

# 示例：快速搭建一个日志分析微服务
import duckdb
from fastapi import FastAPI

app = FastAPI()

@app.get("/analyze")
def analyze_logs(file_path: str):
    con = duckdb.connect()
    result = con.execute(f"""
        SELECT 
            error_level,
            COUNT(*) as count,
            PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) as p95
        FROM read_csv_auto('{file_path}')
        GROUP BY error_level
    """).fetchdf()
    return result.to_dict()

-- 自动生成日报的查询模板
SELECT 
    DATE_TRUNC('day', created_at) AS report_date,
    COUNT(DISTINCT user_id) AS active_users,
    SUM(revenue) AS total_revenue,
    ROUND(AVG(order_value), 2) AS avg_order_value
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY report_date DESC;