DuckDB CLI skills

DuckDB CLI Specialist

Helps with data analysis, SQL queries and file conversion via DuckDB CLI.

Quick Start

Read data files directly with SQL

CSV
duckdb -c "SELECT * FROM 'data.csv' LIMIT 10"

Parquet
duckdb -c "SELECT * FROM 'data.parquet'"

Multiple files with glob
duckdb -c "SELECT * FROM read_parquet('logs/*.parquet')"

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

Open persistent databases

Create/open database
duckdb my_database.duckdb

Read-only mode
duckdb -readonly existing.duckdb

Command Line Arguments

Output formats (as flags)

| Flag | Format |

|------|--------|

| -csv | Comma-separated |

| -json | JSON array |

| -table | ASCII table |

| -markdown | Markdown table |

| -html | HTML table |

| -line | One value per line |

Execution arguments

| Argument | Description |

|----------|-------------|

| -c COMMAND | Run SQL and exit |

| -f FILENAME | Run script from file |

| -init FILE | Use alternative to ~/.duckdbrc |

| -readonly | Open in read-only mode |

| -echo | Show commands before execution |

| -bail | Stop on first error |

| -header / -noheader | Show/hide column headers |

| -nullvalue TEXT | Text for NULL values |

| -separator SEP | Column separator |

Data Conversion

CSV to Parquet

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

Parquet to CSV

duckdb -c "COPY (SELECT * FROM 'input.parquet') TO 'output.csv' (HEADER, DELIMITER ',')"

JSON to Parquet

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

Convert with filtering

duckdb -c "COPY (SELECT * FROM 'data.csv' WHERE amount > 1000) TO 'filtered.parquet' (FORMAT PARQUET)"

Dot Commands

Schema inspection

| Command | Description |

|---------|-------------|

| .tables [pattern] | Show tables (with LIKE pattern) |

| .schema [table] | Show CREATE statements |

| .databases | Show attached databases |

Output control

| Command | Description |

|---------|-------------|

| .mode FORMAT | Change output format |

| .output file | Send output to file |

| .once file | Next output to file |

| .headers on/off | Show/hide column headers |

| .separator COL ROW | Set separators |

Queries

| Command | Description |

|---------|-------------|

| .timer on/off | Show execution time |

| .echo on/off | Show commands before execution |

| .bail on/off | Stop on error |

| .read file.sql | Run SQL from file |

Editing

| Command | Description |

|---------|-------------|

| .edit or \e | Open query in external editor |

| .help [pattern] | Show help |

Output Formats (18 available)

Data export

• -csv - Comma-separated for spreadsheets
• -tabs - Tab-separated
• -json - JSON array
• -jsonlines - Newline-delimited JSON (streaming)

Readable formats

• -duckbox (default) - Pretty ASCII with unicode box-drawing
• -table - Simple ASCII table
• -markdown - For documentation
• -html - HTML table
• -latex - For academic papers

Specialized

• -insert TABLE - SQL INSERT statements
• -column - Columns with adjustable width
• -line - One value per line
• -list - Pipe-separated
• -trash - Discard output

Keyboard Shortcuts (macOS/Linux)

Navigation

| Shortcut | Action |

|----------|--------|

| Home / End | Start/end of line |

| Ctrl+Left/Right | Jump word |

| Ctrl+A / Ctrl+E | Start/end of buffer |

History

| Shortcut | Action |

|----------|--------|

| Ctrl+P / Ctrl+N | Previous/next command |

| Ctrl+R | Search history |

| Alt+< / Alt+> | First/last in history |

Editing

| Shortcut | Action |

|----------|--------|

| Ctrl+W | Delete word backward |

| Alt+D | Delete word forward |

| Alt+U / Alt+L | Uppercase/lowercase word |

| Ctrl+K | Delete to end of line |

Autocomplete

| Shortcut | Action |

|----------|--------|

| Tab | Autocomplete / next suggestion |

| Shift+Tab | Previous suggestion |

| Esc+Esc | Undo autocomplete |

Autocomplete

Context-aware autocomplete activated with Tab:

• -Keywords - SQL commands
• -Table names - Database objects
• -Column names - Fields and functions
• -File names - Path completion

Database Operations

Create table from file

CREATE TABLE sales AS SELECT * FROM 'sales_2024.csv';

Insert data

INSERT INTO sales SELECT * FROM 'sales_2025.csv';

Export table

COPY sales TO 'backup.parquet' (FORMAT PARQUET);

Analysis Examples

Quick statistics

SELECT
    COUNT(*) as count,
    AVG(amount) as average,
    SUM(amount) as total
FROM 'transactions.csv';

Grouping

SELECT
    category,
    COUNT(*) as count,
    SUM(amount) as total
FROM 'data.csv'
GROUP BY category
ORDER BY total DESC;

Join on files

SELECT a.*, b.name
FROM 'orders.csv' a
JOIN 'customers.parquet' b ON a.customer_id = b.id;

Describe data

DESCRIBE SELECT * FROM 'data.csv';

Pipe and stdin

Read from stdin
cat data.csv | duckdb -c "SELECT * FROM read_csv('/dev/stdin')"

Pipe to another command
duckdb -csv -c "SELECT * FROM 'data.parquet'" | head -20

Write to stdout
duckdb -c "COPY (SELECT * FROM 'data.csv') TO '/dev/stdout' (FORMAT CSV)"

Configuration

Save common settings in ~/.duckdbrc:

.timer on
.mode duckbox
.maxrows 50
.highlight on

Syntax highlighting colors

.keyword green
.constant yellow
.comment brightblack
.error red

External Editor

Open complex queries in your editor:

.edit

Editor is chosen from: DUCKDB_EDITOR → EDITOR → VISUAL → vi

Safe Mode

Secure mode that restricts file access. When enabled:

• -No external file access
• -Disables .read, .output, .import, .sh etc.
• -Cannot be disabled in the same session

Tips

• -Use LIMIT on large files for quick preview
• -Parquet is faster than CSV for repeated queries
• -read_csv_auto and read_json_auto guess column types
• -Arguments are processed in order (like SQLite CLI)
• -WSL2 may show incorrect memory_limit values on some Ubuntu versions