Getting Started with DuckDB

DuckDB CLI installation is simple—it's a single binary with no dependencies. On macOS, use Homebrew: brew install duckdb. On Linux/Windows, download the appropriate zip file from the GitHub releases page, unzip it, and run the duckdb executable directly. No installers or libraries are required.

Dot commands are special CLI-only instructions that begin with a period (.) and don't require a semicolon. They control the CLI environment rather than executing SQL. Key commands include .help (list commands), .tables (show tables), .mode (change output format), .timer on/off (show query timing), .read (execute SQL from a file), and .open (switch database files). Type .help for the full list.

DuckDB can query remote files directly using the httpfs extension. Simply use the URL in your query: SELECT * FROM 'https://example.com/data.csv';. If the URL doesn't end in .csv, use read_csv_auto(): SELECT * FROM read_csv_auto('https://bit.ly/shortlink');. The httpfs extension auto-loads when needed (DuckDB 0.8+) and supports HTTP, HTTPS, S3, and Google Cloud Storage.

Extensions add optional functionality to DuckDB while keeping the core lightweight. Some extensions (like parquet, json, icu) come pre-installed. Others (like httpfs, spatial, postgres_scanner) need to be installed with INSTALL extension_name; and loaded with LOAD extension_name;. Since DuckDB 0.8, many extensions auto-load when needed. Check installed extensions with SELECT * FROM duckdb_extensions();.

Use the .mode command to switch output formats. Table-based modes (good for few columns): duckbox (default), box, ascii, table, csv, list. Line-based modes (good for many columns): line, json, jsonlines. For example, .mode line displays each column on its own row—ideal for exploring wide datasets. You can also use CLI flags like duckdb --json for non-interactive use.

DuckDB CLI works seamlessly in pipelines. Use the -s or -c flag to run a command and exit: duckdb -csv -s "SELECT * FROM data.csv WHERE region='Europe'" > output.csv. For Parquet output, use COPY: duckdb -s "COPY (SELECT * FROM data) TO 'output.parquet' (FORMAT PARQUET)". DuckDB reads from stdin and writes to stdout, making it composable with other Unix tools.

The .duckdbrc file (located at $HOME/.duckdbrc) runs automatically when DuckDB CLI starts. Use it to set default configurations like custom prompts, enable timers, or run initialization SQL. Example content:

.timer on
.mode duckbox
.prompt 'DuckDB> '

This saves you from repeatedly typing the same setup commands each session.

Use the COPY ... TO syntax with FORMAT PARQUET: COPY (SELECT * FROM my_table WHERE condition) TO 'output.parquet' (FORMAT PARQUET);. This works from the CLI or scripts. For CSV export, you can pipe output directly: duckdb -csv -s "SELECT * FROM data" > output.csv. Parquet files are compressed and columnar, making them ideal for analytical workloads.

This error occurs when DuckDB can't determine the file format from the URL. If your URL doesn't end in a recognized extension (like .csv or .parquet), DuckDB treats it as a table name. Solution: Use the explicit reader function: SELECT * FROM read_csv_auto('https://bit.ly/shortlink'); instead of SELECT * FROM 'https://bit.ly/shortlink';. This tells DuckDB to parse the URL as a CSV file regardless of its extension.

DuckDB CLI is unique among database CLIs: (1) No server required—unlike psql or mysql, DuckDB runs entirely in-process; (2) Direct file querying—query CSV, Parquet, and JSON files without importing them first; (3) Remote file support—query files directly from HTTP, S3, or GCS URLs; (4) Analytical focus—optimized for OLAP workloads with columnar storage and vectorized execution. It's closest to sqlite3 in simplicity but far more powerful for analytics.

The DuckDB CLI excels at: (1) Data exploration—quickly analyze CSV/Parquet files without setup; (2) ETL pipelines—transform data between formats in shell scripts; (3) File format conversion—convert CSV to Parquet with a single command; (4) Remote data analysis—query files on S3 or HTTP without downloading; (5) Ad-hoc reporting—generate quick reports to CSV or JSON. It's particularly valuable when you need SQL power without database infrastructure.