Querying CSV, Parquet and JSON files

Most analysis starts with files. DuckDB can read them individually, in groups, and with control over how they are parsed.

Parquet: the easy case

SELECT start_station, count(*) AS trips
FROM 'data/trips/*.parquet'
GROUP BY start_station
ORDER BY trips DESC
LIMIT 10;

The glob pattern reads every file in the folder as one table. If the files are stored in Hive style folders such as year=2026/month=01/, add hive_partitioning = true and the folder names become columns you can filter on, letting DuckDB skip whole folders:

SELECT count(*)
FROM read_parquet('data/trips/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 1;

CSV: let it sniff, then check

read_csv detects the delimiter, header, quoting and column types by sampling the file.

DESCRIBE SELECT * FROM read_csv('stations.csv');

DESCRIBE shows the detected types. If a column was guessed wrong, for example postal codes read as integers losing their leading zeros, override it:

SELECT * FROM read_csv('stations.csv', types = {'postal_code': 'VARCHAR'});

For truly messy files, ignore_errors = true skips bad lines, and store_rejects = true keeps them in a rejects table so you can see what was skipped instead of losing rows silently.

JSON: records and nesting

SELECT * FROM read_json('events.json') LIMIT 5;

Newline delimited JSON and arrays of objects are both detected. Nested objects become STRUCT columns, which you can access with dot notation, and lists can be expanded with unnest:

SELECT e.user.id AS user_id, unnest(e.items) AS item
FROM read_json('events.json') AS e;

Turn files into tables when it helps

Querying files directly is great for exploration. When you will query the same data many times, store it in the DuckDB database file:

CREATE TABLE trips AS SELECT * FROM 'data/trips/*.parquet';

DuckDB's own storage format is compressed and columnar, so this is fast and compact.