DuckDB Query CSV, Parquet, JSON, Excel and S3 Files

DuckDB treats a file path as a table. Put the path in quotes after FROM, and DuckDB works out the format from the extension and the column names and types from the data. Nothing is loaded into the database, so the file can sit on your disk, on S3 or behind an HTTPS link.

SELECT channel, count(*) AS orders, sum(gross_cents) / 100 AS revenue
FROM 'sales.csv'
GROUP BY channel
ORDER BY revenue DESC;

The examples use the 1bench demo shop: sales, customers and products, exported to files. Relative paths resolve from the folder DuckDB was started in. Every statement below gives the same results on DuckDB 1.4, 1.5 and 2.0.

Query a CSV file

read_csv sniffs the delimiter, quoting, header and column types from the first 20,480 rows. Call it directly when you need to override any of that:

Example: read a semicolon-separated export with no header row.

SELECT *
FROM read_csv('customers_noheader.txt', delim = ';', header = false,
              names = ['id', 'name', 'email', 'country', 'plan', 'signup_date', 'ltv'])
LIMIT 5;

Type detection only looks at the sample. If the first 30,000 rows of amount are numbers and row 30,001 says n/a, the query fails partway through with "Conversion Error: CSV Error on Line: 30002 ... Could not convert string "n/a" to 'BIGINT'". Fix it with types = {'amount': 'VARCHAR'}, sample_size = -1 (the column then reads as VARCHAR) or ignore_errors = true (the bad row is dropped). Compressed files such as sales.csv.gz are read directly, and read_csv('/dev/stdin') reads a CSV piped into the duckdb shell.

Query a Parquet file

Parquet stores column types and per-column statistics, so there is nothing to sniff and DuckDB reads only the columns a query uses.

SELECT product_id, sum(quantity) AS units
FROM 'sales.parquet'
WHERE occurred_at >= '2025-10-01'
GROUP BY product_id
ORDER BY units DESC
LIMIT 10;

parquet_metadata shows the row groups and the min and max of each column, which is what DuckDB uses to skip data, and parquet_schema lists the stored types.

SELECT path_in_schema, type, stats_min, stats_max
FROM parquet_metadata('sales.parquet');

Query files on S3

The httpfs extension handles s3://, https:// and gs:// paths, and DuckDB installs and loads it the first time you use one. Store the keys once as a secret; it lasts for the session, or use CREATE PERSISTENT SECRET to keep it.

CREATE SECRET (TYPE s3, KEY_ID 'AKIA...', SECRET '...', REGION 'us-east-1');

SELECT count(*) FROM 's3://shop-data/sales.parquet';

Without a secret, a private bucket answers "HTTP 403 Forbidden ... No credentials are provided". Wrong keys give the same 403 with "Credentials are provided, but they did not work". CREATE SECRET (TYPE s3, PROVIDER credential_chain) picks up your AWS CLI profile or environment variables instead of keys typed into SQL.

For MinIO, Cloudflare R2 or another S3-compatible store, add the endpoint and path-style URLs. Leave out USE_SSL false on a plain HTTP server and you get "SSL connect error".

CREATE SECRET minio (
  TYPE s3, KEY_ID 'minio', SECRET 'minio12345',
  ENDPOINT 'localhost:9000', URL_STYLE 'path', USE_SSL false,
  SCOPE 's3://shop-data'
);

SCOPE ties a secret to one bucket or prefix, so you can keep separate keys for several buckets in one session. FROM duckdb_secrets() lists them. Public files need no secret at all:

SELECT count(*) FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/lineitem.parquet';

Query JSON files

read_json accepts both newline-delimited JSON (one object per line) and a single top-level array, and turns nested objects into STRUCT columns and nested arrays into lists.

SELECT name, country FROM read_json('customers.json');

Example: reach into nested fields with dots, and turn an array of line items into rows with unnest.

SELECT id, customer.name, customer.country FROM 'orders.json';

SELECT id, unnest(items, recursive := true) FROM 'orders.json';

Query Excel files

read_xlsx comes from the excel extension, which also loads itself on first use. It reads .xlsx files, one sheet at a time.

SELECT name, country, lifetime_value_cents
FROM read_xlsx('customers.xlsx', sheet = 'customers');

SELECT * FROM read_xlsx('customers.xlsx', range = 'A1:C4');

Excel keeps every number as floating point, so id and lifetime_value_cents come back as DOUBLE (423183.0). Cast them, for example id::BIGINT, before joining on them.

Query multiple files with a glob

A glob or a list of paths reads many files as one table. * matches inside one folder and ** matches any depth. Add filename = true to see which file each row came from.

SELECT filename, count(*)
FROM read_csv('exports/*.csv', filename = true)
GROUP BY filename;

When the files do not share the same columns, use union_by_name = true. DuckDB then lines columns up by name and fills the gaps with NULL.

SELECT channel, count(*)
FROM read_csv('exports/*.csv', union_by_name = true)
GROUP BY channel;

Without it, DuckDB takes the column list from the first file it reads. If that file has fewer columns, the extra columns in later files are silently dropped: here sales_2025_08.csv has no channel column, so channel disappears from the whole result and SELECT channel fails with "Referenced column "channel" not found". If the first file has more columns, the query stops with "Schema mismatch between globbed files ... Column with name: "channel" is missing". read_parquet and read_json take the same union_by_name and filename options.

Query hive-partitioned folders

Folders named key=value, such as lake/channel=web/data_0.parquet, are the Hive layout that Spark, Athena and DuckDB's own COPY ... PARTITION_BY write. hive_partitioning = true turns the folder names into columns.

SELECT channel, count(*)
FROM read_parquet('lake/*/*.parquet', hive_partitioning = true)
GROUP BY channel;

A filter on a partition column skips whole folders. EXPLAIN shows it: with WHERE channel = 'web' the Read Parquet step lists the filter under File Filters and reports "Scanning Files: 1/4". DuckDB also detects key=value folders when you leave the option out, but setting it keeps the query explicit. The same paths work on S3, for example 's3://shop-data/lake/*/*.parquet'.

Join files with each other and with tables

Files, S3 objects and tables mix freely in one query, so you can join a Parquet file to a CSV, or a file to a table in an open .duckdb database.

SELECT c.country, count(*) AS orders, sum(s.gross_cents) / 100 AS revenue
FROM 'sales.parquet' s
JOIN 'customers.csv' c ON c.id = s.customer_id
GROUP BY c.country
ORDER BY revenue DESC
LIMIT 5;

Save a file as a view or a table

A view keeps the path and reads the file on every query, so it always shows the current contents. CREATE TABLE ... AS copies the rows into the database once, which is faster for repeated queries on a CSV.

CREATE VIEW sales_files AS FROM 's3://shop-data/sales.parquet';

CREATE TABLE sales_2025 AS FROM read_csv('sales.csv');

To add a file to a table that already exists, use INSERT INTO sales_2025 SELECT * FROM 'sales_new.csv'. SELECT * lines columns up by position, so name them on both sides when the file's order differs from the table's. COPY sales_2025 FROM 'sales_new.csv' does the same in one statement.

From the CLI and Python

The duckdb shell opens an in-memory database when you give it no file, which is all you need to query files from your own shell.

duckdb -c "SELECT channel, count(*) FROM 'sales.parquet' GROUP BY ALL"

In Python, duckdb.sql runs on a default in-memory connection and returns a relation. Call .show() to print it, .df() for a pandas DataFrame or .fetchall() for tuples.

import duckdb

top = duckdb.sql("""
    SELECT channel, count(*) AS orders
    FROM 'sales.parquet'
    GROUP BY channel
    ORDER BY orders DESC
""")
top.show()
df = top.df()

DuckDB 2.0 removed fetchone() from relations, failing with "Relations no longer have fetchone()", because a relation reruns its query on every call. Use .limit(1).fetchall() there, or call fetchone() on a connection's execute() result as before.