DuckDB CLI: A Practical Guide with Examples

September 21, 2026 by Chat2DB

The DuckDB CLI is one of those tools that quietly replaces several others. It is a single binary with no dependencies, no server to start and no configuration file. Point it at a CSV, a directory of Parquet files or a JSON dump and you can run full SQL over them immediately — joins, window functions, aggregations — at speeds that make awk pipelines look quaint.

If you routinely reach for grep | cut | sort | uniq -c to answer a question about a data file, the DuckDB CLI does the same job in SQL, faster, and on files far larger than memory.

Installation

The CLI is a standalone binary. Pick whichever is convenient:

# macOS / Linux — official install script
curl https://install.duckdb.org | sh
 
# Homebrew
brew install duckdb
 
# Windows
winget install DuckDB.cli
 
# Direct download — extract and put on PATH
# https://duckdb.org/docs/installation/

Verify:

duckdb --version

Start it with no arguments for an in-memory database that disappears on exit, or give it a filename for a persistent one:

duckdb                 # in-memory, nothing persisted
duckdb analytics.duckdb  # creates or opens a database file
duckdb -readonly analytics.duckdb   # safe exploration of a shared file

A DuckDB database is a single file, which makes it trivially portable — copy it, commit it to object storage, email it. There is no data directory and no server process.

Dot commands

Like sqlite3 and psql, the CLI has meta-commands prefixed with a dot. These are the ones worth knowing:

.help                  list all dot commands
.tables                list tables
.schema [table]        show CREATE statements
.databases             list attached databases
.mode <mode>           set output format
.headers on|off        show column headers
.output <file>         redirect results to a file
.once <file>           redirect only the next query
.read <file.sql>       execute a SQL script
.timer on              show query execution time
.maxrows <n>           row display limit (default 40)
.maxwidth <n>          terminal width for rendering
.excel                 open the next result in a spreadsheet
.shell <cmd>           run a shell command
.quit                  exit

.timer on and .mode are the two you will use constantly.

Querying files directly

This is the CLI's defining feature. You do not import data — you query the file in place.

CSV

-- Just select from the path
SELECT * FROM 'sales.csv' LIMIT 10;
 
-- Glob multiple files
SELECT count(*) FROM 'data/sales_*.csv';
 
-- Explicit reader when you need options
SELECT * FROM read_csv('sales.csv',
                       header = true,
                       delim = ',',
                       sample_size = -1);      -- scan all rows for type inference

DuckDB sniffs the delimiter, quoting, header row and column types automatically, and it is right most of the time. When it is not, sample_size = -1 forces a full scan, and explicit types fix stubborn columns:

SELECT * FROM read_csv('messy.csv',
                       header = true,
                       types = {'order_id': 'VARCHAR', 'amount': 'DECIMAL(18,2)'},
                       ignore_errors = true,
                       null_padding = true);

ignore_errors = true skips malformed rows instead of aborting — useful for a first look at a file of unknown quality. To see what was rejected and why:

SELECT * FROM read_csv('messy.csv', store_rejects = true);
SELECT * FROM reject_errors;

Inspect what the sniffer decided before trusting it:

SELECT * FROM sniff_csv('sales.csv');

That returns the detected delimiter, quote character, header flag and full column list with types — and a ready-made read_csv call you can copy.

Parquet

SELECT * FROM 'events.parquet' LIMIT 5;
 
-- A partitioned dataset, with partition columns from the directory names
SELECT year, month, count(*)
FROM read_parquet('s3://bucket/events/**/*.parquet', hive_partitioning = true)
GROUP BY year, month
ORDER BY year, month;
 
-- Inspect without reading data
SELECT * FROM parquet_schema('events.parquet');
SELECT * FROM parquet_metadata('events.parquet');

Parquet is where DuckDB shines. It reads only the columns your query references and uses row-group statistics to skip chunks that cannot match your WHERE clause. Aggregating one column out of fifty over a multi-gigabyte dataset touches a small fraction of the bytes.

JSON

SELECT * FROM 'records.json';
SELECT * FROM read_json_auto('nested.json');
 
-- Newline-delimited JSON, common in log files
SELECT * FROM read_ndjson_auto('logs.ndjson');
 
-- Reach into nested structures
SELECT json_extract_string(payload, '$.user.email') AS email,
       json_extract(payload, '$.items[0].sku')      AS first_sku
FROM read_json_auto('orders.json');

Excel

INSTALL excel; LOAD excel;
SELECT * FROM read_xlsx('report.xlsx', sheet = 'Q3');

Mixing sources in one query

Because every source is just a table expression, you can join across formats in a single statement — a CSV against Parquet against a table in the database file:

SELECT c.region,
       count(*)           AS orders,
       sum(o.amount)      AS revenue
FROM 'orders.parquet'  AS o
JOIN 'customers.csv'   AS c ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY c.region
ORDER BY revenue DESC;

There is no ETL step here. No loading, no schema definition, no import. This is the workflow that makes the DuckDB CLI worth learning.

Output formats

.mode controls how results are rendered, and DuckDB has more useful modes than most CLIs:

.mode duckbox     pretty box drawing (default)
.mode csv         comma-separated
.mode json        JSON array
.mode jsonlines   newline-delimited JSON
.mode markdown    Markdown table
.mode line        one column per line — good for wide rows
.mode table       ASCII table
.mode box         Unicode box
.mode latex       LaTeX tabular
.mode insert      INSERT statements
.mode trash       discard output — for benchmarking

Combined with .output, this makes the CLI a format converter:

.mode csv
.headers on
.output regional_summary.csv
SELECT region, sum(amount) AS revenue FROM 'orders.parquet' GROUP BY region;
.output

.mode markdown is handy for pasting results into documentation or a pull request. .mode line saves your terminal when a table has forty columns.

For writing files, COPY is usually better than redirecting output, because it controls compression and partitioning:

-- CSV → Parquet with compression
COPY (SELECT * FROM 'huge.csv')
TO 'huge.parquet' (FORMAT parquet, COMPRESSION zstd);
 
-- Write a Hive-partitioned dataset
COPY (SELECT * FROM events)
TO 'output/events'
(FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

Converting a large CSV to Parquet is often the single most valuable thing you can do to a dataset you will query repeatedly — it typically shrinks 5–10x and every later query gets faster.

Running from the shell

The CLI is designed to be scripted, not only used interactively.

# One query, then exit
duckdb -c "SELECT count(*) FROM 'events.parquet'"
 
# Run a SQL script
duckdb analytics.duckdb -f monthly_report.sql
 
# CSV straight to stdout for the next tool in the pipe
duckdb -csv -c "SELECT region, sum(amount) FROM 'orders.parquet' GROUP BY region"
 
# JSON output
duckdb -json -c "SELECT * FROM 'events.parquet' LIMIT 5"
 
# Markdown, for a report
duckdb -markdown -c "SELECT * FROM 'summary.csv'"
 
# Quiet mode, no banner — for scripts
duckdb -noheader -list -c "SELECT count(*) FROM 'events.parquet'"

It reads from stdin too, which slots it into ordinary Unix pipelines:

cat query.sql | duckdb analytics.duckdb
 
# Query a file being produced by another command
curl -s https://example.com/data.csv > /tmp/d.csv && \
  duckdb -c "SELECT count(*) FROM '/tmp/d.csv'"

A practical example — summarising a web server log without leaving the terminal:

duckdb -markdown -c "
SELECT status,
       count(*)                          AS hits,
       round(avg(bytes), 0)              AS avg_bytes,
       round(quantile_cont(duration_ms, 0.95), 1) AS p95_ms
FROM read_csv('access.log',
              delim = ' ',
              header = false,
              columns = {'ip':'VARCHAR','ts':'VARCHAR','path':'VARCHAR',
                         'status':'INTEGER','bytes':'BIGINT','duration_ms':'DOUBLE'})
GROUP BY status
ORDER BY hits DESC"

Extensions

Extensions load on demand and are cached after the first install:

INSTALL httpfs;  LOAD httpfs;    -- HTTP, S3, GCS, Azure
INSTALL spatial; LOAD spatial;   -- geospatial types and functions
INSTALL postgres; LOAD postgres; -- attach a live PostgreSQL database
INSTALL mysql;   LOAD mysql;
INSTALL sqlite;  LOAD sqlite;
INSTALL excel;   LOAD excel;
INSTALL fts;     LOAD fts;       -- full-text search
 
SELECT extension_name, installed, loaded FROM duckdb_extensions();

Querying object storage

INSTALL httpfs; LOAD httpfs;
 
CREATE SECRET s3_creds (
    TYPE s3,
    KEY_ID   'AKIA...',
    SECRET   '...',
    REGION   'us-east-1'
);
 
SELECT count(*) FROM 's3://my-bucket/events/2026/*/*.parquet';

DuckDB issues HTTP range requests and pulls only the byte ranges it needs, so querying a remote Parquet file does not mean downloading it.

Public HTTP works with no credentials at all:

SELECT * FROM 'https://example.com/public/data.parquet' LIMIT 10;

Attaching other databases

This is a genuinely useful trick. You can attach a live PostgreSQL or MySQL database and query it with DuckDB's engine, joining it against local files:

INSTALL postgres; LOAD postgres;
ATTACH 'host=db.example.com port=5432 dbname=appdb user=alice' AS pg (TYPE postgres, READ_ONLY);
 
.tables
 
-- Join a production table against a local Parquet file
SELECT u.country,
       count(*)      AS sessions,
       sum(e.revenue) AS revenue
FROM pg.public.users AS u
JOIN 'local_events.parquet' AS e ON e.user_id = u.id
GROUP BY u.country
ORDER BY revenue DESC;
 
-- Pull a table into DuckDB for repeated fast querying
CREATE TABLE users_local AS SELECT * FROM pg.public.users;

READ_ONLY is worth including when the target is production. When you need the other direction — browsing that PostgreSQL schema, editing rows, or managing several databases in one place — a full client is the better tool; Chat2DB (opens in a new tab) handles DuckDB, PostgreSQL, MySQL and others, and the web version (opens in a new tab) runs without an install.

Exploring unfamiliar data

DuckDB has several functions built specifically for the "what is in this file" question:

-- Column statistics, null counts, distinct counts, percentiles
SUMMARIZE SELECT * FROM 'unknown.csv';
 
-- Just the schema
DESCRIBE SELECT * FROM 'unknown.csv';
 
-- Sample rows without reading everything
SELECT * FROM 'huge.parquet' USING SAMPLE 1%;
SELECT * FROM 'huge.parquet' USING SAMPLE 100 ROWS;

SUMMARIZE is the standout. One command gives you, per column, the type, min, max, approximate distinct count, average, standard deviation, quartiles, null percentage and a sample value. It is usually the first thing to run against a file you have never seen.

Two syntax extensions save real typing during exploration:

-- Exclude columns instead of listing the ones you want
SELECT * EXCLUDE (internal_id, created_at) FROM 'wide_table.parquet';
 
-- Transform columns in place
SELECT * REPLACE (upper(country) AS country) FROM 'customers.csv';
 
-- Apply a function to matching columns
SELECT COLUMNS('.*_amount') FROM orders;
SELECT sum(COLUMNS('.*_amount')) FROM orders;

SELECT * EXCLUDE alone justifies learning DuckDB's dialect.

Why querying files directly is fast

It is worth understanding why this works, because it explains when it will not.

DuckDB is a vectorised columnar engine. Rather than processing one row at a time through a chain of function calls, it processes batches of roughly 2,048 values per column at once, which keeps data in CPU cache and lets the compiler emit SIMD instructions. For aggregation over many rows this is often an order of magnitude faster than a row-at-a-time engine.

Against Parquet it adds two more savings. Projection pushdown means a query referencing three columns of a fifty-column file reads only those three — columnar storage makes each column a contiguous run of bytes, so the rest is never touched. Predicate pushdown uses the min/max statistics Parquet stores per row group to skip entire chunks that cannot satisfy your WHERE clause. A filter on a sorted or clustered column can eliminate most of the file before any decompression happens.

CSV gets neither benefit. A CSV must be parsed end to end, every column, every row, because there is no structure to skip with. DuckDB's CSV reader is heavily optimised and parallelised, but it is still doing fundamentally more work. This is the concrete reason to convert repeatedly-queried CSVs to Parquet once:

COPY (SELECT * FROM 'events.csv') TO 'events.parquet' (FORMAT parquet, COMPRESSION zstd);

Typical results are a 5–10x reduction in size and a much larger speedup on selective queries, paid for once.

The corollary is that DuckDB is not a good fit for point lookups or high-concurrency transactional writes. It is a single-process analytical engine with no server and no concurrent writers beyond one. Reaching for it to serve an application's SELECT * FROM users WHERE id = ? is using the wrong tool — that is what PostgreSQL or SQLite are for. DuckDB's territory is scanning and aggregating, and within that territory it is very hard to beat.

Working with larger-than-memory data

A common misconception is that DuckDB requires the dataset to fit in RAM. It does not. The engine spills intermediate results to disk when a query exceeds its memory budget, so aggregations, sorts and joins over datasets much larger than memory complete — they just slow down when spilling starts.

The settings that govern this matter more as data grows:

SET memory_limit = '8GB';           -- soft budget before spilling
SET temp_directory = '/fast/ssd/duckdb';  -- where spill files go
SET preserve_insertion_order = false;     -- large cut in memory on big scans

preserve_insertion_order = false is the highest-value of these for bulk work. By default DuckDB guarantees that reading a file returns rows in file order, which forces it to buffer more than it otherwise would. When you are aggregating — where order is irrelevant — turning it off reduces peak memory substantially and often speeds the query up as a side effect.

Pointing temp_directory at fast local storage rather than a network mount is the difference between spilling being a modest slowdown and being catastrophic.

A practical pattern for genuinely large jobs is to process in partitions rather than all at once, writing results incrementally:

-- Aggregate month by month instead of scanning everything into one hash table
COPY (
  SELECT date_trunc('month', event_time) AS month,
         country,
         count(*) AS events
  FROM read_parquet('s3://bucket/events/**/*.parquet', hive_partitioning = true)
  WHERE year = 2026
  GROUP BY 1, 2
) TO 'monthly.parquet' (FORMAT parquet);

Performance and memory

SET memory_limit = '8GB';
SET threads = 8;
SET preserve_insertion_order = false;   -- lowers memory on large writes
SET temp_directory = '/tmp/duckdb';     -- where spilling happens
 
-- Progress bar for long queries
SET enable_progress_bar = true;
 
-- See the plan
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;   -- actual timings per operator

DuckDB spills to disk when a query exceeds memory_limit, so larger-than-memory aggregations and joins work — they just get slower. Setting temp_directory to fast local storage matters when that happens.

.timer on gives a quick wall-clock reading; EXPLAIN ANALYZE tells you which operator is actually expensive.

A worked example

Say you have a directory of daily CSV exports and want a monthly revenue summary written out as Parquet.

-- duckdb report.duckdb -f monthly.sql
.timer on
 
CREATE OR REPLACE VIEW orders AS
SELECT * FROM read_csv('exports/orders_*.csv',
                       header = true,
                       union_by_name = true,   -- tolerate column order changes
                       sample_size = -1);
 
SUMMARIZE SELECT * FROM orders;
 
CREATE OR REPLACE TABLE monthly AS
SELECT date_trunc('month', order_date)            AS month,
       region,
       count(*)                                    AS orders,
       count(DISTINCT customer_id)                 AS customers,
       round(sum(amount), 2)                       AS revenue,
       round(avg(amount), 2)                       AS avg_order,
       round(quantile_cont(amount, 0.5), 2)        AS median_order
FROM orders
WHERE status = 'completed'
GROUP BY 1, 2
ORDER BY 1, revenue DESC;
 
COPY monthly TO 'monthly_summary.parquet' (FORMAT parquet, COMPRESSION zstd);
 
.mode markdown
SELECT * FROM monthly WHERE month = (SELECT max(month) FROM monthly);

union_by_name = true is the detail that makes this robust: if one day's export added a column or reordered them, DuckDB aligns by name rather than position instead of producing garbage.

Run it with duckdb report.duckdb -f monthly.sql and it works as a cron job.

Summary

The DuckDB CLI earns its place by removing steps. There is no server to run, no import to wait for, no schema to declare. You point SQL at files — CSV, Parquet, JSON, Excel, remote object storage, even live PostgreSQL tables — and join across all of them in one query.

The commands worth committing to memory: SUMMARIZE for a first look at unknown data, .mode and .output for converting formats, read_csv with union_by_name and sample_size = -1 for messy files, COPY ... TO ... (FORMAT parquet) for turning slow CSVs into fast Parquet, and SELECT * EXCLUDE for keeping exploratory queries short. With those, most one-off data questions become a single line in a terminal.

ClickHouse vs Snowflake: Which to Choose in 2026 Memurai: Running Redis on Windows in 2026