How to Use DuckDB From the Command Line for Big Files

SQLite is excellent for small, transactional data, and slow when you ask a 20 GB file how many rows had status 500. DuckDB is built for that question: a columnar, vectorized engine that scans files directly, in one binary with no server. For command-line data work it is the biggest improvement in years. This post shows the commands you use most. It continues SQL on CSV files and SQLite for logs. Examples use DuckDB 1.2.

Install and query a file

curl -fsSL https://install.duckdb.org | sh
duckdb -c "select status, count(*) as n from 'access.csv' group by status order by n desc"

┌────────┬────────┐
│ status │   n    │
│ int64  │ int64  │
├────────┼────────┤
│    200 │ 912044 │
│    404 │  18211 │
│    500 │   2203 │
└────────┴────────┘

Check the installer before you pipe it to a shell, as discussed in curl pipe bash risk, or use your package manager or the binary release. The file name in quotes is a table, and DuckDB sniffs the delimiter, header and column types. It accepts CSV, TSV, JSON, JSON Lines and Parquet by extension. For explicit control use read_csv('f.csv', header=true, delim=';', types={'zip': 'VARCHAR'}); the type override avoids the leading-zero trap noted in the CSV post.

Many files, compressed

duckdb -c "select date_trunc('hour', ts) as hour, count(*) from read_csv('logs/2026-11-*.csv.gz', filename=true) group by 1 order by 1"
duckdb -c "select count(*) from read_json_auto('events/*.jsonl.gz')"

A glob reads every matching file as one table, gz included, and filename=true adds a column with the source path. Reading runs in parallel across cores. This replaces the zcat | awk | sort | uniq -c chain for questions that have more than one dimension, and it is typically faster, since the aggregation happens in compiled vectorized code.

Look at the data

duckdb -c "describe select * from 'access.parquet'"
duckdb -c "summarize 'access.parquet'"
duckdb -c ".mode line" -c "select * from 'access.csv' limit 1"

summarize prints per-column minimum, maximum, approximate distinct count, null share and quantiles in a single call, a good first look at an unknown file. describe shows the inferred schema. Output modes include -csv, -json, -markdown and -line for wide rows; the flags go on the command line: duckdb -json -c "...".

Export and convert

duckdb -c "copy (select * from 'access.csv' where status >= 500) to 'errors.parquet' (format parquet)"
duckdb -c "copy (select * from 'big.csv') to 'big.parquet' (format parquet, compression zstd)"
duckdb -csv -c "select ts, path from 'errors.parquet'" > errors.csv

Converting a CSV to Parquet once makes every later query faster and smaller on disk: Parquet is columnar and typed, so a query that touches two columns reads two columns. This is the standard way to speed up repeated analysis of a big text dataset. Output to standard out with -csv makes DuckDB another stage in a pipeline.

Memory and persistence

duckdb analysis.db -c "set memory_limit='4GB'; create table hits as select * from 'access.parquet'; select count(*) from hits"
duckdb analysis.db -c "show tables"

Without a file argument DuckDB works in memory and forgets everything at exit. Naming a database file keeps tables. memory_limit bounds the working memory; larger queries spill to temporary files in a directory you can set with temp_directory. Unlike SQLite it allows one writer process, and reading from several is fine.

Where this does not apply

DuckDB is an analytics engine: it is not the place for many small concurrent writes, and a file opened for writing cannot be shared across processes like SQLite's. For tiny data or a state store inside a script, use SQLite. Its version moves quickly and file formats have changed between versions, so keep Parquet as the interchange format. Queries on files over the network need the httpfs extension, which you must trust.

What makes it fast and what it is for

DuckDB is an analytical database that runs inside the command-line program with no server, built for scanning and aggregating large tables. It stores and processes data by column and in vectors, which makes queries that touch a few columns of many rows, the typical analysis, far faster than a row-oriented engine such as SQLite, and it uses all the cores. It reads files directly: SELECT * FROM 'data.csv' works, with the format detected from the extension, and Parquet, JSON and compressed files work the same way. For CSV it samples the file to detect types, which is convenient and can guess wrong, and the read_csv function takes explicit options for types, delimiters and headers when it does. Parquet, a columnar file format, is where the largest gains are: DuckDB reads only the columns and row groups a query needs, so a query over a multi-gigabyte Parquet file can finish in a second. Converting a big CSV to Parquet once and querying that is a common pattern for repeated analysis.

It is built for analysis and not for transactions with many concurrent writers. A DuckDB file can be opened for writing by one process at a time, so use it for reading and computing, not as an application's shared database.

Several files, remote files and inspecting the data

A glob in the file name reads many files as one table: SELECT count(*) FROM 'logs/*.csv.gz' decompresses and concatenates them, and the filename option adds a column telling which file each row came from, which is useful for finding the source of a bad row. Files with slightly different columns can be combined by name with the union option. With the httpfs extension, queries can read files over HTTP and from object storage, using credentials from your configuration, and the first use downloads the extension. Treat remote data like any download: it may be large and untrusted, so look at the schema before reading it all. DESCRIBE SELECT * FROM 'file.parquet' shows the columns and types without reading the data, SUMMARIZE gives statistics per column, and LIMIT 10 shows a sample. These three commands answer most first questions about an unfamiliar file in seconds and save you from loading anything you do not understand.

Output, memory and installing it safely

Write results with COPY (SELECT ...) TO 'out.parquet' or to CSV and JSON, which makes DuckDB a good converter between formats, and use .mode or the -csv and -json flags to print to standard output for pipes. By default it works in memory and spills to a temporary directory when a query is larger than the memory limit, controlled with the memory_limit and temp_directory settings, so a large sort or join slows down and does not crash, as long as there is disk space. Give it a persistent file, duckdb analysis.db, when you want tables to survive between sessions. Install it from your package manager, from the project's release archive after checking the checksum, or with a language package. Avoid running an installer piped from the network into a shell without reading it, as the related post on that risk explains, and pin the version in scripts, since the file format and some behaviors change between releases.

Checks and common mistakes

Look at the inferred types with DESCRIBE before trusting a query, since a column read as a date or an integer that should be text changes results silently. Quote file names that contain spaces and glob characters. Remember that a glob over a directory reads every matching file, including ones you forgot were there, so list the files first. When a result seems wrong, check the row count against wc -l, allowing for the header, and look at a few rows from each source file. For repeated jobs, put the SQL in a file and run it with duckdb -c ".read query.sql" or pipe it in, so the analysis is versioned and repeatable and not typed once into a terminal and lost.

See the DuckDB CLI documentation and CSV import.