内容
<p align="center">
<img src="logo.svg" alt="csvql" width="420"/>
</p>
[](https://github.com/melihbirim/csvql/actions/workflows/ci.yml)
[](LICENSE.md)
[](https://github.com/melihbirim/csvql/releases)
**The analytical CSV query engine for AI agents.**
Run SQL analytics — `GROUP BY`, aggregates, joins, time-series — on CSV files **in place**: no database, no import, no ingest. csvql ships as an [MCP](https://modelcontextprotocol.io/) server, so an LLM can query a gigabyte file for a few hundred tokens instead of pasting it (impossible) into context. A single static binary written in Zig. Your data never leaves your machine.
> A database is something you load your data *into*. csvql is a query you run on the data where it already lives.
**Read-only and on-prem by design.** csvql only runs `SELECT` — it has no `INSERT`/`UPDATE`/`DELETE`/`DROP` and physically cannot modify your data. It makes zero network calls, needs no cloud, and runs fully air-gapped. **Our next north star:** the safe way to give AI agents query access to corporate data — run csvql *next to the data* on your own servers (read-only, nothing leaves the box) instead of shipping files out to an LLM.
### Token economics: query files instead of pasting them
Pasting a 417 MB CSV into an LLM costs **230 million tokens** — it fits no context window. Over MCP, the agent queries the file in place and gets back only the answer:
| Question an agent asks | Tokens used |
| ---------------------- | ----------- |
| *"How many trips per cab type?"* | **43** |
| *"Which year was busiest?"* | **49** |
| *"Average fare by passenger count?"* | **123** |
Same answers, **~1,000–500,000× fewer tokens** — flat, regardless of file size. One command wires it into Claude: [`csvql install`](#setup). Measure it yourself: [`bench/bench_tokens.py`](bench/bench_tokens.py).
```bash
$ csvql "SELECT cab_type, COUNT(*) FROM 'trips.csv' GROUP BY cab_type"
cab_type,COUNT(*)
green,32447
yellow,967553
0.05s — no import, queried straight off the file
```
[Website](https://melihbirim.github.io/csvql/) · [Quick Start](#quick-start) · [Installation](#installation) · [Performance](#performance) · [SQL Reference](#sql-reference) · [Docs](#documentation)
---
## Quick Start
csvql auto-detects SQL or simple mode from your input:
```bash
# SQL mode
csvql "SELECT name, salary FROM 'data.csv' WHERE age > 30 ORDER BY salary DESC LIMIT 10"
# Simple mode — same query, shorter syntax
csvql data.csv "name,salary" "age>30" 10 "salary:desc"
# Just browse a file
csvql data.csv
```
### Unix Pipes
```bash
cat data.csv | csvql "SELECT name, age FROM '-' WHERE age > 25"
csvql "SELECT * FROM 'data.csv' WHERE status = 'active'" > output.csv
csvql "SELECT email FROM 'users.csv'" | wc -l
```
### Flags
| Flag | Short | Description |
| -------------------- | ----- | --------------------------------------------------- |
| `--no-header` | | Suppress header row in output |
| `--no-input-header` | | Treat the first row as data; auto-name columns `c1`..`cN` |
| `--delimiter <char>` | `-d` | Field delimiter (default `,`). Use `\t` for TSV |
| `--json` | | Output as a JSON array (`[{...}, ...]`) |
| `--jsonl` | | Output as JSONL / NDJSON (one JSON object per line) |
| `--threads <N>` | | Worker threads for parallel execution; `0` uses automatic detection |
| `--version` | `-v` | Show version |
| `--help` | `-h` | Show help |
| `--mcp` | | Start as an MCP server (stdio JSON-RPC transport) |
| `--root <dir>` | | Confine file access to a directory (repeatable via commas) |
| `--audit <file>` | | Append a JSONL audit record per query (timestamp, SQL) |
```bash
# TSV file
csvql "SELECT name, salary FROM 'data.tsv'" -d $'\t'
# Pipe into another tool that expects no header
csvql "SELECT name, age FROM 'data.csv'" --no-header | awk -F, '{print $2}'
# TSV input, no header in output
cat data.tsv | csvql "SELECT * FROM '-'" -d $'\t' --no-header
```
## Installation
### Homebrew (macOS / Linux)
```bash
brew install melihbirim/csvql/csvql
```
Or in two steps if you plan to install multiple tools from this tap:
```bash
brew tap melihbirim/csvql
brew install csvql
```
> `melihbirim/csvql` is the tap (the formula repository), and the trailing `/csvql` is the formula name inside it.
### Prebuilt Binaries
Download from [GitHub Releases](https://github.com/melihbirim/csvql/releases):
```bash
# macOS (Apple Silicon)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-macos-aarch64.tar.gz | tar xz
sudo mv csvql-macos-aarch64 /usr/local/bin/csvql
# macOS (Intel)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-macos-x86_64.tar.gz | tar xz
sudo mv csvql-macos-x86_64 /usr/local/bin/csvql
# Linux (x86_64)
curl -L https://github.com/melihbirim/csvql/releases/latest/download/csvql-linux-x86_64.tar.gz | tar xz
sudo mv csvql-linux-x86_64 /usr/local/bin/csvql
```
### Build from Source
Requires [Zig](https://ziglang.org/) 0.13.0+ (tested with 0.15.2):
```bash
git clone https://github.com/melihbirim/csvql.git
cd csvql
zig build -Doptimize=ReleaseFast
sudo cp zig-out/bin/csvql /usr/local/bin/
```
## Performance
**1M rows, 35MB CSV, Apple M2** — all tools forced to output all rows (no display tricks):
| Query | csvql | DuckDB | Speedup |
| --------------------------------- | ---------- | ------ | -------- |
| WHERE + ORDER BY LIMIT 10 | **0.020s** | 0.179s | **9x** |
| ORDER BY LIMIT 10 | **0.041s** | 0.165s | **4x** |
| ORDER BY (all 1M rows) | **0.156s** | 1.221s | **7.8x** |
| WHERE (full output) | **0.141s** | 0.739s | **5.2x** |
| Full scan (all 1M rows) | **0.196s** | 1.163s | **5.9x** |
| `COUNT(*) GROUP BY` (6 groups) | **0.060s** | 0.110s | **1.8x** |
| `SUM + AVG GROUP BY` (6 groups) | **0.070s** | 0.110s | **1.6x** |
| `SUM(CASE WHEN) GROUP BY` | **0.016s** | 0.114s | **7.1x** |
| `SELECT DISTINCT city` (8 values) | **0.060s** | 0.110s | **1.8x** |
| `SELECT COUNT(*)` scalar | **0.050s** | 0.100s | **2x** |
| `SELECT SUM(salary)` scalar | **0.050s** | 0.110s | **2.2x** |
**35x less memory** than DuckDB (1.8MB vs 63.5MB).
**5M rows, 173MB CSV, Apple M2** — output-format benchmark (full output, all rows matched, `> /dev/null`):
| Output format | csvql | DuckDB | Speedup |
| -------------------------- | ---------- | ------ | -------- |
| CSV | **0.100s** | 0.354s | **3.5x** |
| JSON array (`--json`) | **0.164s** | 0.434s | **2.6x** |
| JSONL / NDJSON (`--jsonl`) | **0.172s** | 0.422s | **2.5x** |
Outputs are semantically/byte-identical to DuckDB (verified: CSV byte-for-byte diff; JSONL byte-for-byte diff; JSON array Python-parsed row comparison).
Run the benchmark yourself: [`bench/bench_all.sh --section formats`](bench/bench_all.sh)
**5M rows, 173MB CSV, Apple M2** — LIKE operator benchmark (CSV output, `> /dev/null`):
| Pattern | Description | csvql | DuckDB | Speedup |
| ------------------------------ | ------------------------ | --------- | ------ | -------- |
| `WHERE name LIKE 'A%'` | Prefix wildcard | **0.06s** | 2.17s | **~36x** |
| `WHERE city LIKE '%on'` | Suffix wildcard | **0.06s** | 1.12s | **~19x** |
| `WHERE department LIKE '%ing'` | Suffix, high selectivity | **0.07s** | 2.54s | **~36x** |
Row counts verified identical to DuckDB.
Run the benchmark yourself: [`bench/bench_all.sh --section like`](bench/bench_all.sh)
**1M rows, 35MB CSV, Apple M2** — JOIN benchmark (hash-join, CSV output, `> /dev/null`):
| Query | csvql | DuckDB | Speedup |
| ---------------------------------------------- | ---------- | ------ | --------- |
| `JOIN departments` (1M × 6 rows) | **0.140s** | 1.492s | **10.7x** |
| `JOIN + WHERE d.region = 'West'` (1M × 6) | **0.102s** | 0.600s | **5.9x** |
| `JOIN SELECT *` (1M × 6, all cols) | **0.220s** | 4.130s | **18.8x** |
| `JOIN cities` (1M × 8 rows) | **0.146s** | 1.464s | **10.0x** |
| `JOIN bonus_50k` (1M × 50K rows, numeric key) | **0.104s** | 0.276s | **2.7x** |
Run the benchmark yourself: [`bench/bench_all.sh --section join`](bench/bench_all.sh)
**NYC Taxi benchmark — 20M rows, 8 GB CSV, Apple M-series** — the canonical [Billion-Taxi-Rides](https://github.com/pdet/taxi-benchmark) queries on [DuckDB's own dataset](https://duckdb.org/2024/10/16/driving-csv-performance-benchmarking-duckdb-with-the-nyc-taxi-dataset). Both engines query the raw uncompressed CSV **directly** (no preload into a native store), cold per run, best-of-5, warm OS cache:
| Query | csvql | DuckDB | Speedup |
| ----------------------------------------------------------- | --------- | ------ | -------- |
| Q01 `COUNT(*) GROUP BY cab_type` | **1.29s** | 3.55s | **2.8x** |
| Q02 `AVG(total_amount) GROUP BY passenger_count` | **1.41s** | 3.81s | **2.7x** |
| Q03 `COUNT(*) GROUP BY passenger_count, year` | **1.36s** | 4.01s | **2.9x** |
| Q04 `GROUP BY passenger_count, year, ROUND(distance) ...` | **1.38s** | 3.99s | **2.9x** |
Results verified identical to DuckDB. The gap **widens on smaller files** — ~10x on the 417 MB / 1M-row sample, where DuckDB's process and CSV-reader startup dominate; on 8 GB the actual parse+aggregate work dominates and csvql holds a clean ~2.8x.
Reproduce: [`bench/bench_taxi.sh`](bench/bench_taxi.sh) — `./bench/bench_taxi.sh --sample` (417 MB, quick) or `./bench/bench_taxi.sh 1` (full 20M rows, ~8 GB download).
**Memory & storage — same 8 GB / 20M-row file** (peak memory footprint, single cold run):
| Query | csvql peak | DuckDB peak |
| ----- | ---------- | ----------- |
| Q01 | **29 MB** | 178 MB |
| Q02 | **30 MB** | 210 MB |
| Q03 | **34 MB** | 208 MB |
| Q04 | **38 MB** | 219 MB |
**~6x less memory** — and csvql needs **0 bytes of extra storage**: it queries the CSV in place via mmap, no ingest. DuckDB's fast "with storage" path first materializes a **2.1 GB native store (21.7 s one-time ingest)** before it can reach comparable query times; querying the raw CSV directly (as csvql does), it uses ~6x the memory and stays ~2.8x slower.
Reproduce: `./bench/bench_taxi.sh --resources 1` (or `--resources --sample`).
**At scale, csvql reads raw CSV about as fast as your OS can hand it the bytes.** On the 8 GB file, `SELECT COUNT(*)`, `GROUP BY cab_type`, and `GROUP BY + AVG` all run in **~1.31 s** — the same time as `cat file > /dev/null` (~1.30 s) on the same machine. The parsing, grouping, and aggregation are effectively free; the whole query is bounded by the file read itself. There is no meaningful parsing overhead left to remove — csvql is already at the read ceiling, which is why the raw-CSV gap over DuckDB (which does more work per byte) holds at ~2.8x.
Run the full suite (all sections): [`bench/bench_all.sh`](bench/bench_all.sh)
<details>
<summary><b>How is csvql so fast?</b></summary>
- **Memory-mapped I/O** — zero-copy reading at 1.4 GB/sec
- **7-core parallel execution** — lock-free architecture, 669% CPU utilization
- **SIMD field parsing** — vectorized comma detection
- **Radix sort** — O(8N) with IEEE 754 f64→u64 bit trick and pass-skipping
- **Top-K heap** — O(N log K) for LIMIT queries, avoids sorting entire dataset
- **Hardware-aware thresholds** — ARM vs x86 tuned for L1 cache
- **Zero per-row allocations** — arena buffers, zero-copy slices
- **Adaptive GROUP BY pre-sizing** — hash table capacity tuned to chunk size, eliminates rehash cycles
- **Zero-copy worker scans** — each thread iterates a direct mmap slice, no `pread` syscalls or seam buffers
See [ARCHITECTURE.md](ARCHITECTURE.md) for the full optimization story.
</details>
<details>
<summary><b>Benchmark methodology</b></summary>
DuckDB and DataFusion CLIs default to displaying only 40 rows, making them appear faster than they are. Our benchmarks use `-csv` mode (DuckDB) and `FORMAT CSV` (ClickHouse) to force full output materialization. DataFusion CLI caps output at ~8K rows regardless of settings, so full-output numbers are unavailable.
See [BENCHMARKS.md](BENCHMARKS.md) for the complete analysis.
</details>
## SQL Reference
### Supported
| Feature | Syntax |
| ------------- | ----------------------------------------------------------------------- |
| **SELECT** | `SELECT col1, col2` or `SELECT *` |
| **AS alias** | `SELECT expr AS alias` — rename any column or expression in output |
| **DISTINCT** | `SELECT DISTINCT col1, col2` — deduplicates output rows |
| **FROM** | `FROM 'file.csv'` or `FROM -` (stdin) |
| **WHERE** | `=`, `!=`, `>`, `>=`, `<`, `<=` with auto numeric coercion |
| **LIKE** | `WHERE col LIKE 'pattern'` — `%` any sequence, `_` any single char |
| **ILIKE** | `WHERE col ILIKE 'pattern'` — same as LIKE but case-insensitive |
| **BETWEEN** | `WHERE col BETWEEN low AND high` — inclusive numeric or string range |
| **IN** | `WHERE col IN ('a', 'b', 'c')` — membership test |
| **IS NULL** | `WHERE col IS NULL` / `WHERE col IS NOT NULL` — empty-field test |
| **NOT** | `WHERE NOT expr` — logical negation of any condition |
| **AND / OR** | `WHERE cond1 AND cond2` / `WHERE cond1 OR cond2` — compound conditions |
| **JOIN** | `FROM 'a.csv' a [INNER] JOIN 'b.csv' b ON a.key = b.key` |
| **GROUP BY** | `GROUP BY col1` or `GROUP BY alias` — groups rows; accepts SELECT aliases |
| **COUNT** | `COUNT(*)` or `COUNT(col)` — with or without `GROUP BY` |
| **SUM** | `SUM(col)` or `SUM(CASE WHEN cond THEN n ELSE m END)` — conditional sum |
| **AVG** | `AVG(col)` — full precision; with or without `GROUP BY` |
| **CASE WHEN** | `CASE WHEN col OP val THEN n ELSE m END` inside any aggregate function |
| **MIN / MAX** | `MIN(col)`, `MAX(col)` — with or without `GROUP BY` |
| **HAVING** | `HAVING expr` — filter groups after aggregation (e.g. `HAVING COUNT(*) > 5`) |
| **STRFTIME** | `STRFTIME('%Y-%m', col)` — date bucketing in `SELECT` and `GROUP BY` |
| **DATE_PART** | `DATE_PART('year', col)` — extract `year`/`month`/`day`/`hour`/`minute`/`second`; alias for `STRFTIME` in `SELECT` and `GROUP BY` |
| **UPPER / LOWER** | `SELECT UPPER(col), LOWER(col)` — case conversion |
| **TRIM** | `SELECT TRIM(col)` — strip leading and trailing whitespace |
| **LENGTH** | `SELECT LENGTH(col)` — byte length of the value |
| **SUBSTR** | `SELECT SUBSTR(col, start, len)` — substring (1-based, `len` optional) |
| **REPLACE** | `SELECT REPLACE(col, 'from', 'to')` — replace all occurrences of a substring |
| **SPLIT_PART**| `SELECT SPLIT_PART(col, 'delim', n)` — n-th field (1-based) after splitting on delim |
| **GREATEST / LEAST** | `SELECT GREATEST(a, b, ...)`, `LEAST(a, b, ...)` — row-wise max/min (numeric or lexicographic) |
| **ABS / CEIL / FLOOR** | `SELECT ABS(col), CEIL(col), FLOOR(col)` — numeric functions |
| **MOD** | `SELECT MOD(col, n)` — modulo by a numeric literal |
| **ROUND** | `SELECT ROUND(col)` — round to integer; `ROUND(col, n)` — round to `n` decimal places |
| **COALESCE** | `SELECT COALESCE(col, 'default')` — replace empty/null with fallback |
| **CAST** | `SELECT CAST(col AS INTEGER/FLOAT/TEXT)` — type conversion |
| **DATEDIFF** | `DATEDIFF('unit', start_col, end_col)` — duration between two datetime columns. Units: `second`, `minute`, `hour`, `day`, `week`, `month` (≈30 days), `year` (≈365 days). Auto-detects ISO-8601, US (MM/DD/YYYY), EU (DD.MM.YYYY) and mixed formats in the same file |
| **DATEADD** | `DATEADD('unit', amount, date_col)` — add/subtract interval from a datetime column. `amount` may be negative. Units: `second`, `minute`, `hour`, `day`, `week`, `month` (≈30 days), `year` (≈365 days). Returns `YYYY-MM-DD HH:MM:SS` |
| **ORDER BY** | `ORDER BY col [ASC\|DESC]`, multi-column `ORDER BY col1 ASC, col2 DESC`, alias, or positional (`ORDER BY 1`) |
| **LIMIT** | `LIMIT n` |
### Aggregate Examples
```bash
# Scalar aggregates (whole table)
csvql "SELECT COUNT(*), SUM(salary), AVG(salary), MIN(age), MAX(age) FROM 'data.csv'"
# Grouped aggregates
csvql "SELECT department, COUNT(*), AVG(salary) FROM 'data.csv' GROUP BY department ORDER BY department"
# HAVING — filter groups after aggregation
csvql "SELECT department, SUM(salary) FROM 'data.csv' GROUP BY department HAVING SUM(salary) > 500000"
csvql "SELECT category, COUNT(*) FROM 'orders.csv' GROUP BY category HAVING COUNT(*) > 1000"
# 聚合函数中的 CASE WHEN — 条件计数和求和
csvql "SELECT department, COUNT(*) AS total, SUM(CASE WHEN city = 'London' THEN 1 ELSE 0 END) AS london_count FROM 'data.csv' GROUP BY department"
csvql "SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active, SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive FROM 'data.csv'"
csvql "SELECT product, COUNT(*) AS total, SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns FROM 'orders.csv' GROUP BY product ORDER BY returns DESC"
# DISTINCT
csvql "SELECT DISTINCT city FROM 'data.csv' ORDER BY city"
csvql "SELECT DISTINCT city, department FROM 'data.csv'"
# DISTINCT with WHERE
csvql "SELECT DISTINCT department FROM 'data.csv' WHERE salary > 100000"
## 标量函数示例
标量函数在 `SELECT` 中逐行转换列值。它们也可以用于 `GROUP BY` 投影。
```bash
# 字符串函数
csvql "SELECT UPPER(name), LOWER(city), TRIM(notes) FROM 'data.csv'"
csvql "SELECT name, LENGTH(name), SUBSTR(name, 1, 3) FROM 'data.csv'"
# 数值函数
csvql "SELECT name, ABS(balance), CEIL(score), FLOOR(score) FROM 'data.csv'"
csvql "SELECT name, MOD(age, 10) AS age_decade FROM 'data.csv'"
csvql "SELECT name, ROUND(price) AS rounded, ROUND(price, 2) AS price_2dp FROM 'data.csv'"
# COALESCE — 用回退值替换空值
csvql "SELECT name, COALESCE(email, 'unknown') FROM 'data.csv'"
csvql "SELECT COALESCE(phone, COALESCE(email, 'no contact')) FROM 'contacts.csv'"
# CAST — 显式类型转换
csvql "SELECT name, CAST(price AS INTEGER), CAST(id AS TEXT) FROM 'products.csv'"
csvql "SELECT CAST(year AS INTEGER) AS yr, SUM(revenue) FROM 'data.csv' GROUP BY yr"
# ILIKE — 不区分大小写的 LIKE
csvql "SELECT * FROM 'data.csv' WHERE name ILIKE '%smith%'"
csvql "SELECT * FROM 'data.csv' WHERE email ILIKE '%@gmail.com'"
# 标量函数与 GROUP BY 配合使用
csvql "SELECT UPPER(city), COUNT(*) FROM 'data.csv' GROUP BY city"
csvql "SELECT LOWER(department) AS dept, AVG(salary) FROM 'data.csv' GROUP BY department"
# 将标量函数与 AS 别名混合使用
csvql "SELECT UPPER(name) AS Name, CAST(salary AS INTEGER) AS Salary FROM 'data.csv' ORDER BY Salary DESC"
```
## WHERE 过滤器示例
```bash
# 比较运算符
csvql "SELECT name, salary FROM 'data.csv' WHERE salary > 80000"
# BETWEEN — 包含范围（数字或字符串）
csvql "SELECT name, salary FROM 'data.csv' WHERE salary BETWEEN 50000 AND 80000"
csvql "SELECT * FROM 'orders.csv' WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'"
# IN — 成员测试
csvql "SELECT name FROM 'data.csv' WHERE city IN ('London', 'Paris', 'Berlin')"
# IS NULL / IS NOT NULL — 测试缺失（空）字段
csvql "SELECT * FROM 'data.csv' WHERE email IS NULL"
csvql "SELECT * FROM 'data.csv' WHERE email IS NOT NULL"
# NOT — 取反任意条件
csvql "SELECT * FROM 'data.csv' WHERE NOT city IN ('London', 'Paris')"
csvql "SELECT * FROM 'data.csv' WHERE NOT salary BETWEEN 40000 AND 60000"
# AND / OR — 复合条件
csvql "SELECT * FROM 'data.csv' WHERE age > 30 AND department = 'Engineering'"
csvql "SELECT * FROM 'data.csv' WHERE city = 'London' OR city = 'Berlin'"
csvql "SELECT * FROM 'data.csv' WHERE status LIKE 'active%' AND salary > 50000"
# AS 别名 + ORDER BY 别名或位置
csvql "SELECT name AS employee, salary AS pay FROM 'data.csv' ORDER BY pay DESC LIMIT 10"
csvql "SELECT city, COUNT(*) AS cnt FROM 'data.csv' GROUP BY city ORDER BY cnt DESC"
csvql "SELECT name, salary FROM 'data.csv' ORDER BY 2 DESC LIMIT 5" # ORDER BY 位置
```
## ORDER BY 示例
```bash
# 单列 ORDER BY
csvql "SELECT name, salary FROM 'data.csv' ORDER BY salary DESC LIMIT 10"
csvql "SELECT * FROM 'data.csv' ORDER BY name ASC"
# 多列 ORDER BY — 按主键排序，然后用次键打破 ties
csvql "SELECT name, department, salary FROM 'data.csv' ORDER BY department ASC, salary DESC"
csvql "SELECT * FROM 'data.csv' ORDER BY city, age, name"
csvql "SELECT name, city, salary FROM 'employees.csv' WHERE salary > 80000 ORDER BY city ASC, salary DESC"
# 多列 ORDER BY 与 GROUP BY 结果
csvql "SELECT department, AVG(salary) AS avg_sal FROM 'data.csv' GROUP BY department ORDER BY avg_sal DESC, department ASC"
# ORDER BY 别名（从 SELECT 子句解析）
csvql "SELECT name, salary AS pay FROM 'data.csv' ORDER BY pay DESC LIMIT 5"
# ORDER BY 位置（基于 1 的列索引）
csvql "SELECT name, city, salary FROM 'data.csv' ORDER BY 3 DESC LIMIT 10"
```
## 时间序列和日期分组
`STRFTIME('%fmt', column)` 提取或截断日期组件，用于时间序列聚合。
支持的格式说明符： `%Y` （年）、 `%m` （月）、 `%d` （日）、 `%H` （小时）、 `%M` （分钟）、 `%S` （秒）。
输入日期可以是 ISO-8601 日期（ `YYYY-MM-DD` ）或 datetime（ `YYYY-MM-DD HH:MM:SS` ）。
```bash
# 每月收入趋势 — 按完整的 STRFTIME 表达式分组
csvql "SELECT STRFTIME('%Y-%m', order_date), COUNT(*), SUM(price) FROM 'orders.csv' GROUP BY STRFTIME('%Y-%m', order_date)"
# 使用 AS 别名的相同查询 — 按别名分组
csvql "SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(price) AS revenue FROM 'orders.csv' GROUP BY month ORDER BY month"
# 按类别划分的逐年分析
csvql "SELECT category, STRFTIME('%Y', order_date) AS yr, SUM(price) FROM 'orders.csv' GROUP BY category, yr"
# 日期范围过滤 + 每月分组 + HAVING
csvql "SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*), SUM(price) FROM 'orders.csv' WHERE order_date >= '2026-01-01' GROUP BY month HAVING COUNT(*) > 1000000"
# 每日活跃用户
csvql "SELECT STRFTIME('%Y-%m-%d', event_date) AS day, COUNT(DISTINCT user_id) FROM 'events.csv' GROUP BY day ORDER BY day"
```
## 日期和时间函数
`DATEDIFF` 和 `DATEADD` 支持多种日期时间格式。
| 格式 | 示例 |
|--------|---------|
| ISO-8601（空格） | `2026-01-15 09:30:00` |
| ISO-8601（T） | `2026-01-16T10:00:00` |
| 美国（MM/DD/YYYY） | `01/15/2026 08:00:00` |
| 欧洲（DD.MM.YYYY） | `15.01.2026 07:30:00` |
```bash
# 订单工作流：从订单到取货（以分钟为单位）
csvql "SELECT order_id, DATEDIFF('minute', ordered_at, picked_at) AS pick_min FROM 'orders.csv' WHERE picked_at != ''"
# 交付时间（以天为单位）
csvql "SELECT order_id, DATEDIFF('day', shipped_at, delivered_at) AS ship_days FROM 'orders.csv' WHERE shipped_at != '' AND delivered_at != '' ORDER BY ship_days DESC"
# SLA 检查 — 选择具有取货时间的订单，然后在 shell 中过滤（暂不支持 WHERE 中的 DATEDIFF）
csvql "SELECT order_id, customer_name, DATEDIFF('hour', ordered_at, picked_at) AS hrs FROM 'orders.csv' WHERE picked_at != ''"
# 按状态划分的平均处理时间
csvql "SELECT status, AVG(DATEDIFF('minute', ordered_at, packaged_at)) AS avg_proc_min FROM 'orders.csv' WHERE packaged_at != '' GROUP BY status ORDER BY avg_proc_min"
# DATEADD — 计算 SLA 截止日期
csvql "SELECT order_id, ordered_at, DATEADD('hour', 2, ordered_at) AS pick_deadline FROM 'orders.csv'"
# 预计交货日期（发货日期 + 2 天）
csvql "SELECT order_id, shipped_at, DATEADD('day', 2, shipped_at) AS est_delivery FROM 'orders.csv' WHERE shipped_at != ''"
# 支持的单位：second, minute, hour, day, week, month（约 30 天）, year（约 365 天）
csvql "SELECT order_id, DATEDIFF('second', ordered_at, picked_at) AS pick_secs FROM 'orders.csv'"
csvql "SELECT order_id, DATEADD('week', -1, delivered_at) AS sent_reminder FROM 'orders.csv'"
```
**混合格式自动工作** — 单个 CSV 可以有部分日期为 `2026-01-15 09:30:00` ，其他日期为 `01/15/2026 08:00:00` ，`DATEDIFF` 可以处理它们。
## 连接示例
```bash
# 基本 INNER JOIN — 使用别名从两个表中选择列
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e INNER JOIN 'departments.csv' d ON e.dept_id = d.id"
# 裸 JOIN（INNER 是可选的）
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id"
# JOIN with WHERE — 在连接的列上过滤
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id WHERE d.dept_name = 'Engineering'"
# SELECT * on join 返回两个表的所有列
csvql "SELECT * FROM 'orders.csv' o JOIN 'customers.csv' c ON o.customer_id = c.id"
# JOIN with LIMIT
csvql "SELECT e.name, d.dept_name FROM 'employees.csv' e JOIN 'departments.csv' d ON e.dept_id = d.id LIMIT 10"
```
**注意：**
- 使用限定列引用（`alias.col`）时需要表别名
- 非限定列名首先从左表解析，然后从右表解析
- 右表完全加载到内存（构建侧）；左表流式传输（探测侧）
## 简单模式
位置参数：`csvql <file> [columns] [filter] [limit] [sort]`
```bash
csvql data.csv "name,salary" "age>30" 10 "salary:desc"
```
请参阅 [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md) 以获取完整参考。
## MCP 服务器
csvql 作为 [Model Context Protocol](https://modelcontextprotocol.io/) 服务器，让 AI 助手（Claude、Copilot 等）可以直接查询您的 CSV 文件。
```bash
csvql --mcp
```
### 为什么查询而不是粘贴？
1 MB CSV 文件的成本约为 **~560,000 tokens** — 它甚至无法容纳 200K-token 的上下文窗口。粘贴实际数据集在几百 KB 后就变得不可能，在那之前成本就很高。使用 `csvql --mcp` 代理程序 *查询* 文件，并仅获取所需的行：
| CSV 大小 | 粘贴到上下文 | 通过 `csvql --mcp` 查询 | 节省 |
| -------- | ------------------ | ----------------------- | ------- |
| 1 MB | 559K tokens ❌ *(溢出)* | ~540 tokens | **1,000x** |
| 10 MB | 5.6M tokens ❌ | ~550 tokens | **10,000x** |
| 100 MB | 55M tokens ❌ | ~565 tokens | **98,000x** |
| 417 MB | 230M tokens ❌ | ~560 tokens | **~410,000x** |
查询成本是 **固定的** — 它是 SQL 加上几个结果行，与文件大小无关 — 因此 417 MB 文件的成本与 1 MB 文件相同，为 ~560 tokens。五个实际问题，针对 DuckDB 的 NYC-taxi 数据集进行回答；使用 `tiktoken` 进行 token 计数（确切的 `cl100k`）。重现：[`bench/bench_tokens.py`](bench/bench_tokens.py)。您的数据永远不会离开您的机器。
### 暴露的工具
| 工具 | 描述 |
|------|-------------|
| `csv_query(sql)` | 执行任何支持的 SQL 查询，返回结果为 JSON |
| `csv_schema(file)` | CSV 文件的列名和示例行 |
| `csv_list(directory?)` | 列出目录中的 CSV 文件 |
### 通过 MCP 支持的查询
`csv_query` 接受 csvql 支持的完整 SQL 方言。您可以向 AI 助手提出以下问题：
| 自然语言提示 | 发送到 `csv_query` 的 SQL |
|---|---|
| "显示按收入排名前 10 位的客户" | `SELECT customer, SUM(revenue) AS total FROM 'sales.csv' GROUP BY customer ORDER BY total DESC LIMIT 10` |
| "2025 年每月的订单数量？" | `SELECT STRFTIME('%Y-%m', order_date) AS month, COUNT(*) AS orders FROM 'orders.csv' WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31' GROUP BY month ORDER BY 1` |
| "交付平均需要多长时间？" | `SELECT AVG(DATEDIFF('hour', shipped_at, delivered_at)) AS avg_hours FROM 'orders.csv' WHERE delivered_at != ''` |
| "标记订单中挑选超过 SLA 的情况" | `SELECT order_id, DATEDIFF('minute', ordered_at, picked_at) AS mins FROM 'orders.csv' WHERE picked_at != ''` （标量函数暂不支持 WHERE — 在 shell 中过滤 `mins > 90`） |
| "为货物添加 2 天的预计交货日期" | `SELECT order_id, DATEADD('day', 2, shipped_at) AS est_delivery FROM 'orders.csv' WHERE shipped_at != ''` |
| "哪些员工没有部门？" | `SELECT name FROM 'employees.csv' WHERE department IS NULL` |
| "列出所有城市、去重、排序" | `SELECT DISTINCT city FROM 'data.csv' ORDER BY city` |
| "按部门划分，平均工资 > 80k" | `SELECT department, AVG(salary) AS avg_sal FROM 'data.csv' GROUP BY department HAVING AVG(salary) > 80000 ORDER BY avg_sal DESC` |
| "连接订单和客户，过滤地区" | `SELECT o.id, c.name FROM 'orders.csv' o JOIN 'customers.csv' c ON o.customer_id = c.id WHERE c.region = 'West'` |
| "薪资范围 50k-70k" | `SELECT name, salary FROM 'data.csv' WHERE salary BETWEEN 50000 AND 70000 ORDER BY salary` |
| "不在伦敦或巴黎的员工" | `SELECT name, city FROM 'data.csv' WHERE NOT city IN ('London', 'Paris')` |
**完整的 WHERE 子句支持：** `=`, `!=`, `>`, `>=`, `<`, `<=`, `LIKE`, `BETWEEN`, `IN`, `IS NULL`, `IS NOT NULL`, `NOT`, `AND`, `OR`
**完整的 SELECT 支持：** 列投影，`AS` 别名，`DISTINCT`，`COUNT`/`SUM`/`AVG`/`MIN`/`MAX`，`GROUP BY`，`HAVING`，`ORDER BY`（按名称、别名或位置），`LIMIT`，`STRFTIME()`，`DATE_PART()`，`JOIN`，`UPPER`/`LOWER`/`TRIM`/`LENGTH`/`SUBSTR`/`REPLACE`/`SPLIT_PART`/`GREATEST`/`LEAST`，`ABS`/`CEIL`/`FLOOR`/`MOD`/`ROUND`，`COALESCE`，`CAST`，`DATEDIFF`，`DATEADD`，`EXTRACT`
### 设置
**一键命令（推荐）** — 在 Claude Code 和 Claude Desktop 中注册 csvql，无需手动配置：
```bash
csvql install # add --print to dry-run first
```
它运行 `claude mcp add` 以添加适用于 Claude Code（如果存在 CLI）的功能，并将 `mcpServers.csvql` 条目合并到 Claude Desktop 配置中，保留其他服务器。之后请重新启动 Claude。
**Claude Desktop（一键）** — 抓取适用于您所在平台的 `csvql-<platform>.mcpb` 文件，并将其打开 Claude Desktop（设置 → 扩展）。无需终端。您可以使用 [`scripts/build-mcpb.sh`](scripts/build-mcpb.sh) 自行构建。
<details>
<summary>手动配置（如果您更喜欢这样做）</summary>
**VS Code（Copilot）** — 在工作区中创建 `.vscode/mcp.json`：
```json
{
"servers": {
"csvql": {
"type": "stdio",
"command": "/usr/local/bin/csvql",
"args": ["--mcp"]
}
}
}
```
**Claude Desktop** — 添加到 `~/Library/Application Support/Claude/claude_desktop_config.json`:
```json
{
"mcpServers": {
"csvql": {
"command": "/usr/local/bin/csvql",
"args": ["--mcp"]
}
}
}
```
</details>
连接后，您可以要求您的 AI 助手直接查询 CSV 文件：
> *"今年按收入排名前 5 位的产品类别是什么？"*
#### 选项
`query()` 和 `queryCsv()` 都接受一个可选的第二个参数：
| 选项 | 类型 | 描述 |
| ----------------- | ---------- | -------------------------------------------------------------------- |
| `delimiter` | `字符串` | 源字段分隔符，当不是逗号时 — `'\t'` 用于 TSV，`'\|'` 用于管道分隔等。 |
| `comment` | `字符串` | 跳过以此字符串开头的行 — 例如 `'#'` 用于 shell 风格的注释。 |
| `skipEmptyLines` | `布尔值` | 在查询之前去除空白/仅包含空格的行。默认 `false`。 |
```javascript
// TSV 文件
csvql.query("SELECT * FROM 'scores.tsv' WHERE score > 90", { delimiter: '\t' })
// 具有注释行和空白行的文件
csvql.query("SELECT * FROM 'data.csv'", { comment: '#', skipEmptyLines: true })
// 连接两个 TSV 文件
csvql.query(
"SELECT e.name, d.name as dept FROM 'employees.tsv' e JOIN 'departments.tsv' d ON e.dept_id = d.id",
{ delimiter: '\t' }
)
```
**内存：** 引擎内部会流式读取文件 — 内存占用保持在 ~5 MB，与文件大小无关。 与 `csv-parse` 或 `papaparse` 的同步 API 进行比较，它们会将所有行作为 JS 对象实例化（42 MB / 100 万行的文件为 200–650 MB）。
#### ETL — CSV → 过滤 → 数据库
最常见的实际模式是：读取 CSV，仅保留所需的行，然后插入数据库。每个库处理此操作的方式都揭示了根本性的差异。
**csv-parse** 没有查询语言 — 它仅是一个解析器。要过滤，必须首先将整个文件加载到内存中作为 JS 对象，然后调用 `.filter()`：
```javascript
// csv-parse：将所有 100k 行加载到堆中，然后在 JS 中过滤
const all = parse(fs.readFileSync('employees.csv'), { columns: true, cast: true });
const rows = all.filter(r =>
r.department === 'Engineering' && r.active === 'true' && r.salary > 80000
);
// ⚠ cast:true 将数字转换为 JS 对象，但不转换为布尔字符串 — 'true' 仍为字符串，
// 因此 r.active === true 默默地返回 0 行。您必须与 'true' 进行比较。
db.insert(rows);
```
**csvql** 将 WHERE 子句推送到 Zig 引擎。只有匹配的行才会被返回到 JS — 堆成本随结果集缩放，而不是源文件：
```javascript
// csvql：引擎首先过滤，仅 9,991 行到达 JS
const rows = csvql.query(`
SELECT id, name, city, salary, department
FROM 'employees.csv'
WHERE department = 'Engineering'
AND active = 'true'
AND salary > 80000
`);
db.insert(rows);
```
100k 行的结果（42 MB CSV，过滤器匹配 ~10%）：
| | csv-parse | csvql |
| --------------------- | ---------------- | ---------------------------- |
| 加载到 JS 的行数 | 100,000 (所有) | 9,991 (仅匹配) |
| 时间 | ~200 毫秒 | ~22 毫秒 |
| 堆分配 | +31 MB | +3 MB |
| 在 10 GB CSV 文件上 | OOM 或非常慢 | 相同 ~22 毫秒，相同 ~3 MB 堆 |
在 10 GB 文件上，csv-parse 加载整个数据集到内存中，然后才有一行到达数据库。csvql 的堆成本保持不变，因为引擎永远不会实例化不匹配过滤器的行。
完整的可运行示例：`node --experimental-sqlite nodejs/example_etl.js`
**基准测试：** `node --expose-gc nodejs/bench.js` — 与 `csv-parse` 和 `papaparse` 在 1M 行上的比较。
**功能比较：** `node nodejs/compare.js` — 10 个常见任务的并排代码和输出。
---
### Python
```bash
pip install csvql
```
```python
import csvql
# 字典列表
rows = csvql.query("SELECT city, COUNT(*) as n FROM 'employees.csv' GROUP BY city")
# [{'city': 'Austin', 'n': '3'}, ...]
# 原始 CSV 字符串
csv_text = csvql.query_csv("SELECT * FROM 'employees.csv' WHERE salary > 100000")
# pandas DataFrame （需要 pandas）
df = csvql.query_df("SELECT region, SUM(revenue) FROM 'sales.csv' GROUP BY region")
# 普通元组 — 最低开销
headers, rows = csvql.query_tuples("SELECT name, age FROM 'employees.csv'")
```
---
## 文档
| 文档 | 描述 |
| ---------------------------------------------------- | --------------------------------------------------- |
| [ARCHITECTURE.md](ARCHITECTURE.md) | 引擎设计，优化技术 |
| [BENCHMARKS.md](BENCHMARKS.md) | 详细的性能分析 vs DuckDB, ClickHouse |
| [SIMPLE_QUERY_LANGUAGE.md](SIMPLE_QUERY_LANGUAGE.md) | 简单模式语法参考 |
| [docs/LIBRARY.md](docs/LIBRARY.md) | 将 CSV 解析器用作 Zig 库 |
| [CONTRIBUTING.md](CONTRIBUTING.md) | 贡献指南 |
## 路线图
| 功能 | 问题 | 状态 |
| ----------------------------------- | ---------------------------------------------------- | ------------------- |
| `--no-header` / `--delimiter` 标志 | [#12](https://github.com/melihbirim/csvql/issues/12) | ✅ 已发布 (v0.5.0) |
| WHERE 中的 `LIKE` 运算符 | [#13](https://github.com/melihbirim/csvql/issues/13) | ✅ 已发布 |
| `--json` / `--jsonl` 输出格式 | [#14](https://github.com/melihbirim/csvql/issues/14) | ✅ 已发布 |
| `HAVING` 子句 | | ✅ 已发布 |
| `STRFTIME()` 日期分组 | | ✅ 已发布 |
| MCP 服务器 (`--mcp`) | | ✅ 已发布 |
| `AS` 别名 in SELECT & ORDER BY | | ✅ 已发布 |
| `BETWEEN low AND high` | | ✅ 已发布 |
| `IS NULL` / `IS NOT NULL` | | ✅ 已发布 |
| 条件前的 `NOT` 前缀 | | ✅ 已发布 |
| `ORDER BY` 位置 (`ORDER BY 1`)| | ✅ 已发布 |
| `GROUP BY` 别名 (`GROUP BY month`) | | ✅ 已发布 |
| 聚合中的 `CASE WHEN` | | ✅ 已发布 |
| WHERE 中的 `ILIKE` | | ✅ 已发布 |
| SELECT 中的 `UPPER`, `LOWER`, `TRIM`, `LENGTH`, `SUBSTR` | | ✅ 已发布 |
| SELECT 中的 `ABS`, `CEIL`, `FLOOR`, `MOD` | | ✅ 已发布 |
| SELECT 中的 `ROUND(col)` / `ROUND(col, n)` | | ✅ 已发布 |
| SELECT 中的 `COALESCE` | | ✅ 已发布 |
| SELECT 中的 `CAST` | | ✅ 已发布 |
| `DATE_PART()`, `DATEDIFF`, `DATEADD`, `EXTRACT` | | ✅ 已发布 |
| `JOIN` (内部，哈希连接) | | ✅ 已发布 |
| `--threads` 并行控制 | [#51](https://github.com/melihbirim/csvql/issues/51) | ✅ 已发布 (v1.7.0) |
| `--no-input-header` (无标题 CSV，`c1..cN`) | [#49](https://github.com/melihbirim/csvql/issues/49) | ✅ 已发布 (v1.7.0) |
| MCP 令牌防护 + `csvql install` + `.mcpb` 包 | [#54](https://github.com/melihbirim/csvql/issues/54) | ✅ 已发布 (v1.7.0) |
| `--root` 文件访问沙盒 | [#58](https://github.com/melihbirim/csvql/issues/58) | ✅ 已发布 (v1.7.0) |
| `--audit` 查询日志 | [#62](https://github.com/melihbirim/csvql/issues/62) | ✅ 已发布 (v1.7.0) |
| HTTP/SSE MCP 传输（共享服务） | [#60](https://github.com/melihbirim/csvql/issues/60) | 计划中 |
| 更多 SQL 函数 (`GROUP_CONCAT`, `VARIANCE`, `SPLIT_PART`, `OFFSET`, …) | [好办的问题](https://github.com/melihbirim/csvql/labels/good%20first%20issue) | 需要帮助 |
## 贡献
欢迎贡献 — 错误报告，性能改进，功能，文档。参见 [CONTRIBUTING.md](CONTRIBUTING.md)。
新来者？ [**好办的问题**](https://github.com/melihbirim/csvql/labels/good%20first%20issue) 具有文件指针和明确的标准 — 一个很好的起点（新的 SQL 函数，输出格式等）。
## 许可证
MIT — 参见 [LICENSE.md](LICENSE.md)。
---
**使用 Zig 构建** · **比 DuckDB 快 9 倍** · **MCP 服务器** · [GitHub](https://github.com/melihbirim/csvql)
MCP 配置
以下是此 MCP Server 的配置信息，可直接复制到 Cursor 或其他 MCP 客户端中使用。
mcp.json
连接信息
猜你喜欢
暂无推荐服务器