csv-query

CSV Query

Query tabular data files using SQL via the Polars-powered sqlp command.

Cowork note: If relative paths don't resolve, call mcp__qsv__qsv_get_working_dir and mcp__qsv__qsv_set_working_dir to sync the working directory.

Decision Tree

Is the query simple (single column filter, basic select)?

• Yes -> Consider select + search for simpler operations
• No -> Use sqlp for full SQL support

Does the query involve joins, GROUP BY, window functions, or complex expressions?

• Yes -> Use sqlp (Polars SQL engine)

Is the CSV file very large (> 10MB)?

• Yes -> Consider converting to Parquet with mcp__qsv__qsv_to_parquet for faster repeated queries. Note: sqlp can also query CSV files of any size directly.

Steps

1. Prepare the file: Run mcp__qsv__qsv_index and mcp__qsv__qsv_stats with cardinality: true, stats_jsonl: true to create index and stats cache.

2. Read the stats cache: Read <FILESTEM>.stats.csv (e.g., data.stats.csv for data.csv) to understand column metadata before writing SQL. This is the most important step for writing efficient queries.

3. Run frequency on key columns: For columns you plan to GROUP BY, filter on, or join on, run mcp__qsv__qsv_frequency to see actual value distributions. This reveals the best filter values and whether a GROUP BY will produce a manageable result set.

4. Write and run SQL: Use mcp__qsv__qsv_sqlp with the SQL query informed by stats and frequency data. The table name in SQL is the filename stem (e.g., data.csv -> SELECT * FROM data). For Parquet files, use read_parquet('data.parquet') as the table source instead.

5. Refine if needed: Check results and adjust the query.

Using Stats to Write Better SQL

After reading the .stats.csv cache, use these columns to inform your SQL:

Stats Column	How to Use in SQL
type	Use correct casts and comparisons — don't quote integers, use date functions for Date/DateTime columns
min / max	Write precise WHERE clauses using actual data range (e.g., WHERE price BETWEEN 10.5 AND 999.99 instead of arbitrary bounds)
cardinality	Estimate GROUP BY result size — low cardinality (< 100) is fast; high cardinality (> 10K) may need LIMIT or a different approach
nullcount	Only add COALESCE or IS NOT NULL where nullcount > 0 — skip null handling for columns with zero nulls
sort_order	Skip ORDER BY if data is already sorted on that column (sort_order = "Ascending"/"Descending")
mean / stddev	Write outlier filters: WHERE col BETWEEN mean - 3*stddev AND mean + 3*stddev
median / q1 / q3	For skewed data (when mean and median diverge), use quartile-based ranges: WHERE col BETWEEN q1 AND q3 instead of mean ± stddev
skewness	If skewness > 1 or < -1, prefer median/quartile-based filters over mean-based ones
cv	High CV (> 100%) signals high relative variability — add LIMIT to GROUP BY queries and consider binning continuous values
outliers_percentage	If > 5%, consider excluding outliers before aggregation: WHERE col BETWEEN lower_inner_fence AND upper_inner_fence
sparsity	Columns with sparsity > 0.5 are mostly null — avoid using them as join keys or GROUP BY columns