DuckDB v1.5.4 隐藏宝藏：MERGE INTO 改进、ATTACH 新选项与终端体验升级

-- 复杂的 MERGE INTO 场景：从多个源表合并数据

-- 目标表：客户主数据
CREATE TABLE customers_master AS
SELECT * FROM (VALUES
    (1, 'Alice', '[email protected]'),
    (2, 'Bob', '[email protected]'),
    (3, 'Charlie', '[email protected]')
) AS t(id, name, email);

-- 源表：来自不同渠道的新客户
CREATE TEMP TABLE channel_web AS VALUES
    (2, 'Bob Updated', '[email protected]'),
    (4, 'David', '[email protected]');

CREATE TEMP TABLE channel_mobile AS VALUES
    (5, 'Eve', '[email protected]'),
    (2, 'Bob Mobile', '[email protected]');

-- 合并所有渠道的数据到目标表
MERGE INTO customers_master cm
USING (
    SELECT id, name, email FROM channel_web
    UNION ALL
    SELECT id, name, email FROM channel_mobile
) AS src
ON cm.id = src.id
WHEN MATCHED THEN
    UPDATE SET name = src.name, email = src.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email) VALUES (src.id, src.name, src.email);

-- 验证结果
SELECT * FROM customers_master ORDER BY id;

-- 附加数据库并自动重建索引
ATTACH 'orders.db' AS orders (
    vacuum_rebuild_indexes TRUE
);

-- 现在可以跨库查询
SELECT o.order_id, c.customer_name
FROM orders.orders o
JOIN main.customers c ON o.customer_id = c.id;

import duckdb

# 创建主数据库连接
con = duckdb.connect(":memory:")

# 附加各个业务数据库，同时重建索引
con.execute("ATTACH 'analytics/orders.db' AS orders (vacuum_rebuild_indexes TRUE)")
con.execute("ATTACH 'analytics/customers.db' AS customers (vacuum_rebuild_indexes TRUE)")
con.execute("ATTACH 'analytics/products.db' AS products (vacuum_rebuild_indexes TRUE)")

# 跨库联合查询
result = con.execute("""
    SELECT 
        c.region,
        COUNT(o.order_id) AS order_count,
        SUM(o.amount) AS total_revenue
    FROM customers.customers c
    JOIN orders.orders o ON c.customer_id = o.customer_id
    JOIN products.products p ON o.product_id = p.product_id
    WHERE o.order_date >= '2026-01-01'
    GROUP BY c.region
    ORDER BY total_revenue DESC
""").fetchdf()

print(result)

# 强制使用暗色模式
duckdb -dark-mode

# 强制使用亮色模式
duckdb -light-mode

# 自动检测（默认行为，现在更准确了）
duckdb

#!/bin/bash
# 自动化 ETL 脚本中使用暗色模式

duckdb -dark-mode <<EOF
INSTALL httpfs;
LOAD httpfs;

-- 从 S3 读取数据
SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet');

-- 写入结果
COPY (SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet'))
TO '/data/results.parquet' (FORMAT PARQUET);
EOF

import duckdb
import pyarrow as pa

# 使用 ADBC 接口进行高效数据传输
con = duckdb.connect(":memory:")

# 创建大型数据集
con.execute("""
    CREATE TABLE large_data AS
    SELECT 
        i AS id,
        RANDOM() AS value,
        DATE '2026-01-01' + (i % 365) AS date
    FROM range(10000000) t(i)
""")

# 通过 Arrow 格式高效导出
arrow_table = con.execute("SELECT * FROM large_data").fetch_arrow_table()
print(f"导出 {arrow_table.num_rows} 行，{arrow_table.num_columns} 列")

-- 通配符路径查询
SELECT json_keys('{
    "users": [
        {"name": "Alice", "age": 30},
        {"name": "Bob", "age": 25}
    ]
}'::JSON, '$.users[*].name') AS user_names;

-- 数组转 JSON
SELECT array_to_json([1, 2, 3, 4, 5]) AS json_array;
-- 输出: [1,2,3,4,5]

-- TopN 查询：每个类别销售额最高的前 3 个产品
WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        sales,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY sales DESC
        ) AS rn
    FROM product_sales
)
SELECT category, product_name, sales
FROM ranked_products
WHERE rn <= 3
ORDER BY category, sales DESC;

-- 安装 spatial 扩展
INSTALL spatial;
LOAD spatial;

-- 空间查询受益于改进的统计剪枝
SELECT city_name, population
FROM cities.parquet
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    geometry_column
);

# Python
pip install duckdb --upgrade

# CLI (Linux amd64)
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
sudo mv duckdb /usr/local/bin/

# Docker
docker pull duckdb/duckdb:1.5.4