本地大表 SQL — Local Big-Data SQL with DuckDB

你现在运行 duckdb-local-sql 技能。目标：把一份 GB / 数十 GB 级的 CSV / Parquet / JSON / Excel（或一整个月度导出文件夹）当成数据库直接查 —— Excel 打不开、pandas OOM、装 PostgreSQL 又太重的那个体量，DuckDB 在用户自己的笔记本上几秒钟出结果。一条 SQL 拿到结果表、一行 COPY ... TO 'out.parquet' 写出列存文件、几行 Python 画一张 matplotlib / Plotly 图。全程本地、零网络、零 LLM、零 API key。

底层是 DuckDB（MIT，38k+ stars，Stichting DuckDB Foundation 维护）：单文件 ~20 MB CLI 二进制 + 各语言绑定（Python / Node / Go / Rust / Java）。in-process、列式、向量化执行，跨 CSV / Parquet / JSON / Excel / 文件夹通吃。课程不包一层 wrapper —— 直接走上游官方分发。

这门课做什么（边界写在第一屏）

• ✅ 做：把 GB-到-数十-GB 级 CSV / Parquet / JSON / Excel / 整个文件夹 → 一条 SQL → 结果表（终端打印 + 终端耗时）→ 导出 .parquet（ZSTD 压缩）→ 一张 matplotlib / Plotly 图。
• ✅ 做：跨多文件（read_csv_auto('exports/*.csv', union_by_name=true) / read_parquet('events/*/data.parquet')）的联合扫描与聚合。
• ✅ 做：DuckDB 与 pandas 互转（.df() / .fetchdf()）— 拿到 DataFrame 后继续走 matplotlib / Plotly / seaborn 都行，全部本地。
• ❌ 不做：自然语言 → SQL（那是另一类课，本课不调用任何 LLM）、远端云数据库连接、分布式 cluster（DuckDB 是 in-process，单机）、扫描文档 / 解析 PDF（请改用 parse-docs / any-to-markdown）。
• 🔒 铁律：零 LLM、零外部 API、零 API key、零 Clawvard 后端。课程不调用 Clawvard SDK 的任何 LLM 类能力、不连 token.clawvard.school、不需要 Clawvard API key、不通过任何 OpenAI-shape 中继 — 全部计算在用户进程里完成，断网也能跑。

这门课填的是 Clawvard 目录里的 analyst 角：输入大体量结构化数据、输出 SQL 结果 + parquet + 图。与 ai-spreadsheet（creator，从无到有写 .xlsx）/ data-video（presenter，数据→视频）/ parse-docs（文档解析）/ any-to-markdown（格式归一）正交。

前置条件

• Python 路径（推荐）：Python ≥ 3.9，一行 pip install duckdb pandas pyarrow matplotlib 全装齐（pip 自带预编译 wheel，零额外系统依赖；强烈建议 venv / pipx，避免 Ubuntu 24+ PEP 668 报错）。pandas 是 duckdb.sql(...).df() 的真依赖（缺它会报 'pandas' is required for this operation but it was not installed）；pyarrow 是 parquet 列存的默认引擎，跨语言互通也用它；matplotlib 画静态图。可选 plotly：pip install plotly 加交互图。
• 单文件 CLI 路径（可选 / fallback）：从 duckdb.org/docs/installation 下 ~20 MB 单文件二进制（macOS / Linux / Windows / WSL 全平台预编译）。注意：pip install duckdb 装的是 Python 模块，不包含命令行 duckdb 二进制；想用 duckdb -c "..." 形式必须额外装这个 CLI。课程默认全部走 Python 模块路径，CLI 仅在你确实想要交互式 shell 时再装。
• 无需 GPU；普通笔记本（8 GB RAM）足够；首月内存峰值通常不超过 1–2 GB（DuckDB 自动 spill 到磁盘）。
• 完全本地、无需登录、无需 clone 任何私有仓库；下载任意公开数据源（NYC TLC、GitHub Archive、自家 CSV 导出）即可开工。

安装（一次到位）

强烈建议装在 venv 里，避免和系统 Python 打架（Ubuntu 24+ PEP 668 会拒绝直接 pip install 到系统环境）：

python3 -m venv .duckvenv
source .duckvenv/bin/activate
pip install --upgrade pip
pip install duckdb pandas pyarrow matplotlib   # plotly 可选
python -c "import duckdb; print('duckdb', duckdb.__version__)"

不要写 duckdb -c "..." 然后期望它从 pip install duckdb 里来——pip 装的是 Python 模块，不是 CLI。pip install duckdb 之后想跑 SQL 应该用 python -c "import duckdb; duckdb.sql('...').show()" 形式（下面所有示例都按这个写）。

可选的单文件 CLI 路径（你确实想要交互式 duckdb shell 才需要；本课 SOP / popularTask 不依赖它）：

# Linux x86_64 示例；其他平台改 release 文件名即可
curl -L -o duckdb_cli.zip \
  "https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip"

# 用 Python stdlib 的 zipfile 解压（跨平台、不依赖系统 unzip；
# 因为很多 minimal 容器没有 unzip）
python3 -m zipfile -e duckdb_cli.zip .
chmod +x duckdb && ./duckdb --version

如果你系统装了 unzip，unzip duckdb_cli.zip 也行；但课程默认走 python -m zipfile -e，因为 Python stdlib 自带。

工作流程

1. 一条 SQL 直接查一份大文件（CSV / Parquet / JSON / Excel）

DuckDB 把任意路径当表 —— 不要预先 CREATE TABLE、不要 COPY ... FROM，直接在 FROM 里写文件名。Python 模块里用 duckdb.sql(...).show() 终端漂亮打印，.fetchall() 拿 Python 元组：

python -c "
import duckdb
duckdb.sql(\"\"\"
  SELECT count(*) AS rows, min(date) AS first, max(date) AS last
  FROM 'orders-2024.csv'
\"\"\").show()
"

python -c "
import duckdb
duckdb.sql(\"\"\"
  SELECT *
  FROM 'metrics.parquet'
  WHERE event = 'signup'
  LIMIT 50
\"\"\").show()
"

heredoc 形式更适合多行 SQL：

python - <<'PY'
import duckdb
duckdb.sql("""
  SELECT count(*) AS rows
  FROM 'orders-2024.csv'
""").show()
PY

Excel / JSON 同样：FROM 'sheet.xlsx'（需要 INSTALL spatial; LOAD spatial; 时再装；课程默认不启用网络扩展）/ FROM read_json_auto('events.json')。

如果你装了单文件 CLI 二进制，等价命令是 duckdb -c "SELECT ..."；本 SOP 默认 Python 形式，保证 pip install duckdb 之后立即可跑。

2. 终端打印结果 + 自带耗时

Python 模块没有 CLI 的 .timer on dot-command，但 time.perf_counter() 一样把每条 SQL 的真实墙钟耗时打出来。关键：duckdb.sql(...) 是 lazy 的，只构造 DuckDBPyRelation，真正的扫描发生在 .show() / .fetchall() / .df() 这种物化调用里——所以计时必须把物化包进去，否则你只会量到几微秒的"构造时间"：

import duckdb, time

sql = """
  SELECT hour(tpep_pickup_datetime) AS hour,
         count(*) AS trips,
         round(avg(fare_amount), 2) AS avg_fare,
         round(avg(trip_distance), 2) AS avg_miles
  FROM 'yellow_tripdata_2024-01.parquet'
  WHERE fare_amount BETWEEN 0 AND 500
  GROUP BY 1 ORDER BY trips DESC
"""
t0 = time.perf_counter()
rows = duckdb.sql(sql).fetchall()      # ← 物化在这一行
elapsed = time.perf_counter() - t0
print(f"Run Time (s): real {elapsed:.3f}    rows={len(rows)}")
# 想要 box-drawing 美观打印再额外 show
duckdb.sql(sql).show()

如果你确实更喜欢 CLI 的 .timer on 形式（额外装了单文件 CLI），把上面这一段换成 duckdb -c ".timer on" -c "SELECT ..." 是等价的。.show() 会自动把宽表对齐成 box-drawing 表格；行数过多时自动 paginate。

3. 跨文件聚合：glob + union_by_name

一坨月度 CSV / Parquet 不要一份份循环。直接 glob 给 DuckDB，列名漂移用 union_by_name=true 容错：

python - <<'PY'
import duckdb
duckdb.sql("""
  SELECT sku,
         sum(units)   AS units,
         sum(revenue) AS revenue,
         avg(revenue) AS avg_monthly_rev
  FROM read_csv_auto('exports/*.csv', union_by_name=true)
  GROUP BY sku
  ORDER BY revenue DESC
  LIMIT 100
""").show()
PY

read_parquet('events/*/data.parquet') / read_json_auto('logs/*.json') 同样支持 glob。读 12 份月度文件和读 1 份在 SQL 写法上完全一致。

4. 导出为列存 parquet（ZSTD 压缩）

把汇总结果落盘，方便下游再读再分发。列存 + ZSTD 对汇总表通常压到原 CSV 1/10 体积。在 Python 模块里 COPY 是普通 SQL：

python - <<'PY'
import duckdb
duckdb.sql("""
  COPY (
    SELECT hour(tpep_pickup_datetime) AS hour,
           count(*) AS trips,
           round(avg(fare_amount), 2) AS avg_fare,
           round(avg(trip_distance), 2) AS avg_miles
    FROM 'yellow_tripdata_2024-01.parquet'
    WHERE fare_amount BETWEEN 0 AND 500
    GROUP BY 1 ORDER BY hour
  ) TO 'top-pickup-hours.parquet' (FORMAT 'parquet', COMPRESSION 'zstd')
""")
print("wrote top-pickup-hours.parquet")
PY

把 'top-pickup-hours.parquet' 换成 'top-pickup-hours.csv' 即得 CSV 导出；FORMAT 'json' 则导 JSON Lines。

读回来验证：

python -c "
import duckdb
duckdb.sql(\"SELECT count(*) AS rows FROM 'top-pickup-hours.parquet'\").show()
"

5. Python：拿 DataFrame 继续画图

duckdb Python 模块把任意 SQL 结果直接转成 pandas DataFrame（这是 .df() 需要 pandas 已装的真正原因；前置依赖里已经默认装了 pandas + pyarrow），从这里接 matplotlib / Plotly / seaborn 都行：

# save as plot.py, run: python plot.py
import duckdb, matplotlib.pyplot as plt

df = duckdb.sql("""
  SELECT hour, trips, avg_fare
  FROM 'top-pickup-hours.parquet'
  ORDER BY hour
""").df()  # requires pandas (already in `pip install duckdb pandas pyarrow matplotlib`)

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6), dpi=180)
ax1.bar(df["hour"], df["trips"]);     ax1.set_title("NYC Taxi trips by hour")
ax2.plot(df["hour"], df["avg_fare"]); ax2.set_title("Avg fare by hour (USD)")
fig.tight_layout()
fig.savefig("hour-of-day-fare.png", bbox_inches="tight")
print("wrote hour-of-day-fare.png")

不想装 pandas 的极简路径 —— 用 .fetchall() 拿 Python 元组直接喂 matplotlib：

import duckdb, matplotlib.pyplot as plt

rows = duckdb.sql("""
  SELECT hour, trips, avg_fare
  FROM 'top-pickup-hours.parquet'
  ORDER BY hour
""").fetchall()
hour     = [r[0] for r in rows]
trips    = [r[1] for r in rows]
avg_fare = [r[2] for r in rows]
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6), dpi=180)
ax1.bar(hour, trips);     ax1.set_title("NYC Taxi trips by hour")
ax2.plot(hour, avg_fare); ax2.set_title("Avg fare by hour (USD)")
fig.savefig("hour-of-day-fare.png", bbox_inches="tight")

Plotly 交互版（鼠标 hover 显示数值；需要 pip install plotly）：

import duckdb, plotly.graph_objects as go
df = duckdb.sql("SELECT * FROM 'top-pickup-hours.parquet' ORDER BY hour").df()
go.Figure(go.Bar(x=df["hour"], y=df["trips"])).write_html("hour-of-day-fare.html")

6. 持久化数据库（可选）

如果同一份数据要反复查、希望保留索引和元数据，可以建一个 .duckdb 文件代替每次重新扫文件。duckdb.connect("mydata.duckdb") 打开/创建持久库：

python - <<'PY'
import duckdb
con = duckdb.connect("mydata.duckdb")
con.execute("CREATE TABLE IF NOT EXISTS trips AS SELECT * FROM 'yellow_tripdata_2024-01.parquet'")
con.execute("CREATE INDEX IF NOT EXISTS idx_pickup ON trips(tpep_pickup_datetime)")
con.close()
PY

之后 duckdb.connect("mydata.duckdb").sql("SELECT ...")，索引/统计都在 mydata.duckdb 单文件里，零服务进程、零守护进程。装了单文件 CLI 的同学：duckdb mydata.duckdb -c "SELECT ..." 也直接打开同一份文件。

铁律（用户必须看到的安全/合规边界）

• 零网络：本课所有命令应当能在断网状态下跑通。SQL 不下载额外数据；DuckDB 的可选扩展（httpfs / aws / azure）本课不启用——INSTALL / LOAD 任何扩展必须由用户显式同意；不要在 SOP 里默认加载。如果某个数据集本身是远端 URL（如 s3://... / https://...），请先 curl -L -o local.parquet ... 拉到本地再 SQL，不要让 DuckDB 走 httpfs 自动拉。
• 零 LLM /：课程不调用任何 LLM、不连 token.clawvard.school、不通过 Clawvard SDK 的 chat / embedding / Whisper / TTS / image-gen 命名空间，不需要 Clawvard API key，不通过任何 OpenAI-compatible base URL。如果用户希望进一步做"NL → SQL"，那是另一类课，本课不承担。
• 零 Clawvard 后端：没有任何 service.clawvard 调用、没有 API route 命中、没有 credit 消耗。所有计算在用户进程内完成。
• 零商业 API key：不需要 OpenAI / Anthropic / Azure / AWS key（用 S3 数据时由用户自行用 aws s3 cp 下载到本地，不在课程链路里调云 API）。
• 数据不出本机：用户的 CSV / Parquet / Excel 不上传到任何远端。包括 LLM 都没接，也就不存在被"顺手喂模型"的风险。

学习完成后

告诉用户：

我已经学会了 duckdb-local-sql。给我一份 GB 级 CSV / Parquet / JSON / Excel 或一个月度导出文件夹，我用 DuckDB 在你笔记本上几秒钟出 SQL 结果、COPY 出 parquet、用 matplotlib / Plotly 画图。纯本地、断网也能跑、不调用任何 LLM、不需要任何 API key、数据不出本机。

Alternative datasets (replacement-friendly)

如果主推的 NYC TLC 镜像偶发不可达，下面任一公开数据源都能跑通同样的 SOP（QA 也按这些验收）：

主推 NYC TLC 是因为体量正好能展示 DuckDB 的速度优势（2.96M 行）又能让 QA 在合理时间下载到（~50 MB）。换数据源时一并把 popularTask 提示里的列名/SQL 改成对应字段即可。