How to Run SQL Directly on a CSV File With the DuckDB CLI

You have a CSV file and a question, and the question is a GROUP BY. The shortest path is one command in a terminal: DuckDB starts, reads the file, answers and exits. There is no server to run, no table to create and no import to wait for. This article is the practical tour of that command: how to install it, the forms a query on a file can take, the two statements that tell you what is in the file before you ask anything, how to read from a pipe and how to get CSV out. The larger argument for querying in place, with its measurements, is in the guide to querying CSV files with DuckDB.

What you need

The duckdb binary, which is a single file with no dependencies. Download it for your platform from the installation page, or use a package manager such as Homebrew on macOS. Check it:

duckdb --version

v2.0.0 (Cyanoptera) d54cbbe3df

Everything below also works from the interactive shell, which you get by typing duckdb with no arguments, and from Python through the duckdb package. I use -c in the examples because a command that takes a string and prints a result is easy to copy and to put in a script.

The first query

The file is the four row customers file from this section:

id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75

A file name in single quotes goes where a table name would go:

duckdb -box -c "SELECT name, balance FROM 'customers.csv' WHERE balance > 10 ORDER BY balance DESC"

┌───────────┬─────────┐
│   name    │ balance │
├───────────┼─────────┤
│ Ana Silva │ 120.5   │
│ Cy Dubois │ 15.75   │
└───────────┴─────────┘

That is the whole of it. DuckDB saw a path ending in .csv, called its CSV reader with automatic detection, and returned the two customers with a balance above 10. -box is an output mode that draws the result as a table; the default depends on whether the output is a terminal, and I name a mode in scripts so the output does not change with the environment.

Look before you ask: DESCRIBE and SUMMARIZE

The first two statements to run on any new file say what DuckDB made of it. DESCRIBE shows the column names and the types the sniffer chose:

duckdb -box -c "DESCRIBE SELECT * FROM 'customers.csv'"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ BIGINT      │ YES  │ NULL │ NULL    │ NULL  │
│ name        │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ email       │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
│ signup_date │ DATE        │ YES  │ NULL │ NULL    │ NULL  │
│ balance     │ DOUBLE      │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘

id is BIGINT, name and email are VARCHAR, signup_date is DATE and balance is DOUBLE. If one of those is not what you expect, a ZIP code that became a number or a date that stayed text, the next article, on the read_csv options, shows how to override it. SUMMARIZE goes further and profiles each column:

duckdb -box -c "SUMMARIZE SELECT id, balance, signup_date FROM 'customers.csv'"

┌─────────────┬─────────────┬────────────┬────────────┬───────────────┬─────────────────────┬────────────────────┬────────────┬────────────┬────────────┬───────┬─────────────────┐
│ column_name │ column_type │    min     │    max     │ approx_unique │         avg         │        std         │    q25     │    q50     │    q75     │ count │ null_percentage │
├─────────────┼─────────────┼────────────┼────────────┼───────────────┼─────────────────────┼────────────────────┼────────────┼────────────┼────────────┼───────┼─────────────────┤
│ id          │ BIGINT      │ 1          │ 4          │ 4             │ 2.5                 │ 1.2909944487358056 │ 2          │ 2          │ 4          │ 4     │ 0.00            │
│ balance     │ DOUBLE      │ 0.0        │ 120.5      │ 3             │ 45.416666666666664  │ 65.4992048297789   │ 3.9375     │ 15.75      │ 94.3125    │ 4     │ 25.00           │
│ signup_date │ DATE        │ 2025-01-15 │ 2025-03-01 │ 4             │ 2025-02-09 00:00:00 │ NULL               │ 2025-01-24 │ 2025-02-11 │ 2025-02-25 │ 4     │ 0.00            │
└─────────────┴─────────────┴────────────┴────────────┴───────────────┴─────────────────────┴────────────────────┴────────────┴────────────┴────────────┴───────┴─────────────────┘

For each column it returns the type, minimum and maximum, an approximate count of distinct values, the average and standard deviation for numbers, quartiles, the row count and the percentage of NULLs. The balance column is 25% null, which is the one empty balance among the four rows, and the date column shows the earliest and latest dates. On a file of any size this is the fastest way to find the surprises: a maximum that is far too large, a column that is entirely NULL, an id column whose distinct count is lower than its row count.

Forms of the query

The file name shortcut has a few variants, and the one that suits a terminal best may not be the one you expect. DuckDB allows the FROM clause to come first, and a bare FROM is a complete query that returns everything:

duckdb -box -c "FROM 'customers.csv' SELECT count(*) AS customers, round(avg(balance), 2) AS average_balance"

┌───────────┬─────────────────┐
│ customers │ average_balance │
├───────────┼─────────────────┤
│ 4         │ 45.42           │
└───────────┴─────────────────┘

FROM 'customers.csv' SELECT count(*), round(avg(balance), 2) returned four customers and an average balance of 45.42. This is part of DuckDB's friendly SQL extensions, described on the friendly SQL page, and it reads in the order you think: the data first, then what you want from it. You can also call the function explicitly, FROM read_csv('customers.csv', header = true), when you need to pass options, which the next article covers.

Reading from a pipe

A file name is not required. /dev/stdin is a file that DuckDB can read, so any command that prints CSV can be the source:

cat customers.csv | duckdb -box -c "SELECT count(*) AS rows_from_a_pipe FROM read_csv('/dev/stdin')"

┌──────────────────┐
│ rows_from_a_pipe │
├──────────────────┤
│ 4                │
└──────────────────┘

Four rows arrived from the pipe. This is useful for the tools you already use: zcat file.csv.gz | duckdb ..., or the output of curl or qsv. One restriction is that the sniffer needs to read the input once to detect the dialect and again to parse, so for a pipe DuckDB buffers what it reads, and very large piped input costs memory. For a big file, give DuckDB the path.

Getting CSV out

-csv switches the output mode to CSV, which makes the command a filter in a pipeline:

duckdb -csv -c "SELECT id, name FROM 'customers.csv' WHERE id <= 2"

id,name
1,Ana Silva
2,"Smith, Jane"

The header and two rows come out as CSV, with the comma in Smith, Jane quoted. That is the same quoting rule as the escaping article describes. For writing to a file with more control, COPY (...) TO is the better tool, and writing query results back out covers its options.

When it is a big file

Nothing changes with the size. This is the same command against the 2,000,000 row orders file, grouping by country:

cd /batches/csv-0001-0200/work && time duckdb -box -c "SELECT country, count(*) AS orders, round(sum(amount), 2) AS revenue FROM 'orders-2m.csv' GROUP BY country ORDER BY revenue DESC LIMIT 3" 2>&1

┌─────────┬────────┬─────────────┐
│ country │ orders │   revenue   │
├─────────┼────────┼─────────────┤
│ FR      │ 286777 │ 22969870.61 │
│ IN      │ 286243 │ 22934328.0  │
│ BR      │ 285957 │ 22933345.89 │
└─────────┴────────┴─────────────┘

real	0m0.290s
user	0m0.364s
sys	0m0.261s

France had 286,777 orders and 22,969,870.61 in revenue, the top three countries came back sorted, and the whole command, including starting DuckDB, took 0.37 s of wall time. The file was parsed on every core and only the two columns the query used were converted, which is why a 130 MB file does not take long. If you run a handful of queries like this on one file, there is no need to load anything.

When something is wrong

The error you will see most is a path that does not exist. DuckDB stops before it runs anything:

duckdb -c "SELECT * FROM 'nothing-here.csv'" 2>&1 | head -4

IO Error: No files found that match the pattern "nothing-here.csv"
hint: -agent renders errors as JSON and results compactly for AI coding agents (duckdb -help lists all options)

IO Error: No files found that match the pattern "nothing-here.csv". Check the working directory first, since the shell runs the command from wherever you are. The second most common is a type error in a function (sum of a text column), which is the sniffer deciding a column is VARCHAR; DESCRIBE shows why. And the third is a file that is not really a CSV, such as an HTML error page saved with a .csv extension, which SUMMARIZE shows at once: one column named after the first line and nothing else.

What this does not give you

It does not keep anything: the data is read and the process exits, and the next command parses the file again. To keep results, write them with COPY ... TO or create a table in a database file by passing a path, duckdb my.duckdb. It does not validate the file for you beyond what the sniffer does. And it trusts the types it guesses, which is wrong for identifiers with leading zeros in any reader that guesses naively. DuckDB protects them, but a column of IDs that have no leading zeros yet will become integers, and when one with a leading zero arrives next month the type changes under you. For recurring files, write the schema down, as the read_csv options article shows.