duckdb-sql

DuckDB SQL analysis engine

Query CSV, Parquet, and JSON files directly with SQL, no need to import into a database. Suitable for large dataset analysis.

Use cases

• The user says "analyze this CSV file with SQL" or "how many rows does this dataset have"
• When the dataset is large (>100,000 rows) and excel-analyzer is slow to process
• The user needs complex aggregation, JOIN, window functions, and other SQL features
• The user says "compare the differences between these two CSV files"

Differences from excel-analyzer

Tool	Good at	Limitations
excel-analyzer	Small datasets, charts, formatted output	Slow for large datasets
duckdb-sql	Large datasets, complex SQL, multi-file JOIN	Does not do formatted output

Execution method

Python API

import duckdb

con = duckdb.connect()

# 直接查询 CSV
result = con.sql("SELECT * FROM 'data.csv' LIMIT 10").fetchdf()

# 聚合分析
result = con.sql("""
    SELECT category, COUNT(*) as count, AVG(price) as avg_price
    FROM 'sales.csv'
    GROUP BY category
    ORDER BY count DESC
""").fetchdf()

# 多文件 JOIN
result = con.sql("""
    SELECT o.order_id, c.name, o.total
    FROM 'orders.csv' o
    JOIN 'customers.csv' c ON o.customer_id = c.id
    WHERE o.total > 1000
""").fetchdf()

# 查询 Parquet
result = con.sql("SELECT * FROM 'data.parquet' WHERE year = 2026").fetchdf()

# 通配符查询多文件
result = con.sql("SELECT * FROM 'logs/*.csv'").fetchdf()

Command-line usage

# 安装
pip install duckdb

# 交互式
python3 -c "import duckdb; print(duckdb.sql(\"SELECT count(*) FROM 'data.csv'\").fetchone())"

Common analysis patterns

-- 数据概览
SUMMARIZE SELECT * FROM 'data.csv';

-- 去重计数
SELECT COUNT(DISTINCT user_id) FROM 'events.csv';

-- 窗口函数：排名
SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM 'employees.csv';

-- 导出结果
COPY (SELECT * FROM 'data.csv' WHERE status = 'active') TO 'output.csv';

Output specifications

• Query results are displayed in table format (first 20 rows + total row count)
• Large result sets are automatically truncated, prompting the user to add LIMIT
• Displays a progress prompt when a query takes more than 5 seconds