seekdb File SQL — Files as Databases

seekdb can treat local files as tables. You write ordinary SQL against read_csv(), read_json() / read_ndjson(), and read_parquet(), and the seekdb SQL engine does filtering, aggregation, sorting, and JOINs. No pre-created tables, no import step, no data copies.

When to use

The user hands you a CSV/JSONL/Parquet file and asks for statistics, filtering, or reports.
The user wants to JOIN two files in different formats.
The user wants to export a table or query result back to CSV.

Enable the feature

File SQL is disabled by default. Turn it on first, and make sure the target directory is allowed by secure_file_priv:

SET GLOBAL enable_file_sql = ON;
-- secure_file_priv must cover the directory of your files, e.g.
-- secure_file_priv = /data/files

Path escaping is rejected: a query cannot read outside the secure_file_priv scope.

Reading files

| Function | Format | Notes | | --- | --- | --- | | read_csv('path') | CSV | header, UTF-8, LF/CRLF, quoted fields, embedded newlines | | read_json('path') | JSONL (one flat object per line) | field union across lines; missing fields become NULL | | read_ndjson('path') | NDJSON | same as read_json | | read_parquet('path') | Parquet | footer schema, multiple row groups, nullable columns, column pruning | | glob('pattern') | file discovery | returns one column file; does not read data |

Examples:

SELECT * FROM read_csv('/data/files/sales.csv') LIMIT 10;
DESCRIBE SELECT * FROM read_json('/data/files/events.jsonl');
SELECT f.file FROM glob('/data/files/*.parquet') f;

Named options are supported, e.g. read_csv('path', sample_size => -1). Column metadata is dynamic: it comes from the file itself (schema inference for CSV/JSONL, footer schema for Parquet).

Type inference (CSV / JSONL)

Deterministic, whole-file inference into: BIGINT, DOUBLE, BOOLEAN, VARCHAR, DATE, DATETIME, or NULL (all-NULL columns). Widen when values conflict (int + float → DOUBLE; anything non-numeric → VARCHAR). JSONL nested objects and arrays are rejected — keep records flat.

Query patterns

Single file — full SQL support:

SELECT region, COUNT(*) AS cnt, SUM(amount) AS total
FROM read_csv('/data/files/sales.csv')
WHERE amount > 0
GROUP BY region
HAVING cnt > 10
ORDER BY total DESC
LIMIT 20;

Two-file JOIN (native seekdb plan, cross-format allowed):

SELECT s.order_id, c.name
FROM read_csv('/data/files/sales.csv') s
LEFT JOIN read_parquet('/data/files/customers.parquet') c
  ON s.customer_id = c.id;

Export with COPY (target must not already exist; temp files are cleaned up on failure):

COPY (SELECT * FROM read_csv('/data/files/sales.csv') WHERE amount > 100)
TO '/data/files/big_sales.csv';

The exported file can be read back by read_csv.

Troubleshooting

Feature disabled error → SET GLOBAL enable_file_sql = ON.
Permission / path error → check that the file lives under secure_file_priv.
Wrong types in CSV → inference is whole-file; check for stray header or comment rows.
JSONL rejected → remove nested objects/arrays; one flat object per line.
File changed between runs → seekdb detects file changes and re-scans; if results look stale, re-run the query.
Target exists on COPY → COPY never overwrites; pick a new path.

Notes

Files are opened read-only; streaming with batched scans, no full-file materialization.
Do not point file SQL at huge files you only need a sample of — use LIMIT early.
Remote files and multi-file auto-merge are not supported.