Quickstart

DuckDB Quickstart Guide

DuckDB is an in-process SQL OLAP database system designed for high-speed analytical queries. It can read CSV, Parquet, and JSON files directly without importing, making it perfect for data exploration and transformation.

Installation

brew install duckdb

Or via Homebrew on Linux, or download precompiled binaries from GitHub.

Basic Usage

Interactive shell

duckdb

Query CSV file directly

duckdb -c "SELECT * FROM read_csv('data.csv') LIMIT 10"

Query Parquet file

duckdb -c "SELECT COUNT(*) FROM read_parquet('data.parquet')"

Query JSON

duckdb -c "SELECT * FROM read_json('data.json')"

Common Operations

Read and filter data

duckdb :memory: "SELECT name, age FROM read_csv('people.csv') WHERE age > 30"

Aggregate and group

duckdb -c "SELECT category, COUNT(*) as count FROM read_csv('sales.csv') GROUP BY category"

Join multiple files

duckdb -c "SELECT * FROM read_csv('orders.csv') o JOIN read_csv('customers.csv') c ON o.customer_id = c.id"

Write results to file

duckdb -c "COPY (SELECT * FROM read_csv('input.csv')) TO 'output.parquet' (FORMAT PARQUET)"

File Formats Supported

• CSV - read_csv('file.csv')
• Parquet - read_parquet('file.parquet')
• JSON - read_json('file.json')
• JSON Lines - read_ndjson('file.ndjson')
• Excel - read_excel('file.xlsx')
• Avro - read_avro('file.avro')
• Iceberg - read_iceberg('table')

Database Persistence

Work in-memory (temporary)

duckdb :memory:

Create persistent database

duckdb mydb.duckdb
# Now all queries are saved to mydb.duckdb

Open existing database

duckdb mydb.duckdb

Advanced Queries

Window functions

duckdb -c "
SELECT name, salary, 
       AVG(salary) OVER (PARTITION BY dept) as dept_avg
FROM read_csv('employees.csv')
"

CTEs (Common Table Expressions)

duckdb -c "
WITH filtered AS (
  SELECT * FROM read_csv('data.csv') WHERE age > 25
)
SELECT COUNT(*) FROM filtered
"

Generate data

duckdb -c "SELECT * FROM range(1, 10)"

Statistical functions

duckdb -c "
SELECT 
  AVG(value) as mean,
  STDDEV(value) as std,
  MIN(value) as min,
  MAX(value) as max
FROM read_csv('metrics.csv')
"

Real-world Use Cases

Analyze log files

duckdb -c "
SELECT timestamp, COUNT(*) as errors
FROM read_json('app.log')
WHERE level = 'ERROR'
GROUP BY timestamp
"

Data quality checks

duckdb -c "
SELECT 
  SUM(CASE WHEN id IS NULL THEN 1 ELSE 0 END) as null_ids,
  COUNT(DISTINCT id) as unique_ids
FROM read_csv('data.csv')
"

Convert file formats

# CSV to Parquet
duckdb -c "COPY (SELECT * FROM read_csv('data.csv')) TO 'data.parquet' (FORMAT PARQUET)"

# JSON to CSV
duckdb -c "COPY (SELECT * FROM read_json('data.json')) TO 'data.csv' (FORMAT CSV)"

Quick data validation

duckdb -c "
SELECT COUNT(*), COUNT(DISTINCT name), 
       MAX(LENGTH(name)) as max_name_len
FROM read_csv('users.csv')
"

Performance Tips

• DuckDB automatically parallelizes queries across CPU cores
• Use Parquet format for large datasets (much faster than CSV)
• Filter data early in queries to reduce memory usage
• Use appropriate data types (don't read everything as VARCHAR)
• DuckDB spills to disk for very large workloads

Command-line Options

• -c <QUERY> - Execute query and exit
• -readonly - Open database in read-only mode
• -unsigned - Enable unsigned integer types
• -json - Output results as JSON lines
• -csv - Output as CSV (default)
• -noheader - No header row in output
• -separator <SEP> - Custom delimiter (default: ,)

Scripting

From file

duckdb mydb.duckdb < queries.sql

Multiple queries

duckdb -c "
  CREATE TABLE my_table AS SELECT * FROM read_csv('input.csv');
  SELECT * FROM my_table LIMIT 10;
"

Memory Management

DuckDB automatically:

• Uses multi-threading for parallelization
• Spills to disk when memory is insufficient
• Optimizes query execution plans
• Manages buffer pools efficiently

For very large datasets:

# Query with explicit memory limit
duckdb -c "PRAGMA memory_limit='4GB'; SELECT * FROM read_parquet('big.parquet')"

Integration with Other Tools

Pipe with other commands

cat data.csv | duckdb -c "SELECT * FROM read_csv('/dev/stdin') WHERE value > 100"

Combined with jq

duckdb -c "SELECT * FROM read_json('data.json')" | jq '.[] | select(.active == true)'

Resources

• Official Documentation
• GitHub Repository
• SQL Reference
• Function Reference
• Awesome DuckDB

Troubleshooting

"File not found" error

Ensure path is relative or absolute:

duckdb -c "SELECT * FROM read_csv('./data/file.csv')"

Out of memory

Reduce data or use Parquet format:

# Convert CSV to Parquet first
duckdb -c "COPY (SELECT * FROM read_csv('large.csv')) TO 'large.parquet' (FORMAT PARQUET)"

Slow queries

Use EXPLAIN to see query plan:

duckdb -c "EXPLAIN SELECT * FROM read_csv('data.csv') WHERE id > 1000"