DuckDB 嵌入式分析数据库¶

# Python安装（最常用）
pip install duckdb  # 就这一行，不需要装服务器

# CLI安装（命令行交互）
# macOS
brew install duckdb
# Ubuntu
wget https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
sudo mv duckdb /usr/local/bin/

# 验证
python -c "import duckdb; print(duckdb.__version__)"
duckdb --version  # CLI版本

import duckdb  # 导入DuckDB

# 创建内存数据库（最简单）
con = duckdb.connect()  # 内存模式，关闭即丢失

# 创建持久化数据库
con = duckdb.connect("my_analysis.db")  # 数据保存到文件

# 直接运行SQL
result = con.sql("SELECT 42 AS answer")  # 执行SQL
print(result)  # 显示结果

# 关闭连接
con.close()

import duckdb

# 直接查询CSV文件，不需要先导入！
result = duckdb.sql("""
    SELECT
        city,
        COUNT(*) AS user_count,
        AVG(age) AS avg_age
    FROM 'users.csv'          -- 直接写文件路径当表名
    GROUP BY city
    ORDER BY user_count DESC
""")
print(result)

# 查询Parquet文件
duckdb.sql("SELECT * FROM 'data/*.parquet'")  # 支持通配符，读取所有parquet文件

# 查询JSON文件
duckdb.sql("SELECT * FROM read_json_auto('events.json')")

# 查询Excel文件
duckdb.sql("""
    INSTALL spatial;  -- 安装扩展
    LOAD spatial;
    SELECT * FROM st_read('data.xlsx')
""")

# 查询远程文件（HTTP/S3）
duckdb.sql("""
    SELECT * FROM 'https://example.com/data.csv'
    LIMIT 10
""")

# 查询S3上的Parquet
duckdb.sql("""
    SELECT * FROM read_parquet('s3://bucket/path/data.parquet')
""")

import duckdb
import pandas as pd
import polars as pl

# pandas DataFrame直接当SQL表用
pandas_df = pd.DataFrame({
    "name": ["Alice", "Bob", "Charlie"],
    "score": [95, 87, 92],
})

# 直接在SQL中引用pandas变量名！
result = duckdb.sql("""
    SELECT name, score
    FROM pandas_df              -- 直接用Python变量名
    WHERE score > 90
    ORDER BY score DESC
""")

# 结果转pandas
pandas_result = result.df()  # 转pandas DataFrame

# 结果转Polars
polars_result = result.pl()  # 转Polars DataFrame

# Polars DataFrame也能直接查询
polars_df = pl.DataFrame({"x": [1, 2, 3], "y": [4, 5, 6]})
duckdb.sql("SELECT * FROM polars_df WHERE x > 1")

import duckdb

con = duckdb.connect("warehouse.db")

# 从CSV创建表
con.sql("""
    CREATE TABLE users AS
    SELECT * FROM read_csv_auto('users.csv')  -- 自动推断类型
""")

# 从Parquet创建表
con.sql("""
    CREATE TABLE events AS
    SELECT * FROM 'events.parquet'
""")

# 手动建表
con.sql("""
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,   -- 主键
        name VARCHAR NOT NULL,    -- 产品名
        price DECIMAL(10, 2),     -- 价格，10位数字2位小数
        category VARCHAR,         -- 分类
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 创建时间
    )
""")

# 插入数据
con.sql("""
    INSERT INTO products VALUES
    (1, 'Python入门', 59.90, '图书', CURRENT_TIMESTAMP),
    (2, '机械键盘', 299.00, '外设', CURRENT_TIMESTAMP)
""")

duckdb.sql("""
    SELECT
        name,
        department,
        salary,
        -- 部门内薪资排名
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
        -- 部门平均薪资
        AVG(salary) OVER (PARTITION BY department) AS dept_avg,
        -- 与上一个人的薪资差
        salary - LAG(salary) OVER (ORDER BY salary) AS salary_diff
    FROM 'employees.csv'
""")

# 行转列
duckdb.sql("""
    PIVOT (SELECT city, quarter, sales FROM sales_data)
    ON quarter
    USING SUM(sales)  -- 每个季度的销售额变成单独的列
""")

# 列转行
duckdb.sql("""
    UNPIVOT sales_wide
    ON q1, q2, q3, q4   -- 这些列转成行
    INTO NAME quarter VALUE sales  -- 新列名
""")

# 导出为Parquet
duckdb.sql("""
    COPY (SELECT * FROM users WHERE active = true)
    TO 'active_users.parquet' (FORMAT PARQUET)
""")

# 导出为CSV
duckdb.sql("""
    COPY users TO 'users_export.csv' (HEADER, DELIMITER ',')
""")

# 分区导出
duckdb.sql("""
    COPY orders TO 'output' (FORMAT PARQUET, PARTITION_BY (year, month))
""")

# 安装并使用扩展
duckdb.sql("INSTALL httpfs;")   # HTTP/S3文件系统
duckdb.sql("LOAD httpfs;")

duckdb.sql("INSTALL json;")     # JSON处理
duckdb.sql("INSTALL spatial;")  # 空间数据
duckdb.sql("INSTALL iceberg;")  # Apache Iceberg
duckdb.sql("INSTALL fts;")      # 全文搜索

# 使用全文搜索
duckdb.sql("""
    INSTALL fts; LOAD fts;
    -- 创建全文索引
    PRAGMA create_fts_index('docs', 'id', 'content');
    -- 全文搜索
    SELECT * FROM fts_main_docs.search_and_rank('机器学习');
""")

# 启动CLI
duckdb my_data.db  # 打开/创建数据库

# 常用命令
.tables              # 列出所有表
.schema users        # 查看表结构
.mode markdown       # 输出格式改为Markdown表格
.timer on            # 显示查询耗时
.maxrows 20          # 最多显示20行
.import data.csv tbl # 导入CSV到表
.quit                # 退出

-- ===== 文件查询 =====
SELECT * FROM 'file.csv';                    -- 查CSV
SELECT * FROM 'file.parquet';                -- 查Parquet
SELECT * FROM read_json_auto('file.json');   -- 查JSON
SELECT * FROM 'dir/*.parquet';               -- 通配符查多文件

-- ===== 常用函数 =====
-- 聚合：COUNT, SUM, AVG, MIN, MAX, MEDIAN, MODE
-- 字符串：LOWER, UPPER, TRIM, CONCAT, LENGTH, REGEXP_MATCHES
-- 日期：CURRENT_DATE, DATE_PART, DATE_TRUNC, AGE
-- 条件：CASE WHEN, COALESCE, NULLIF, IIF
-- 列表：LIST, UNNEST, LIST_AGGREGATE

-- ===== 导出 =====
COPY tbl TO 'out.parquet' (FORMAT PARQUET);
COPY tbl TO 'out.csv' (HEADER, DELIMITER ',');

-- ===== 配置 =====
SET threads = 4;           -- 线程数
SET memory_limit = '4GB';  -- 内存限制

-- ===== Python速查 =====
-- duckdb.sql("SQL")         执行SQL
-- result.df()               转pandas
-- result.pl()               转Polars
-- result.fetchall()         获取所有行
-- result.show()             打印结果