Duckdb En

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)

Execution arguments

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

Output control

Queries

Editing

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

History

Editing

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

DuckDB CLI AI Skill

A comprehensive AI skill for Claude Code that provides expert assistance with DuckDB CLI operations.

What is this?

This is a skill file for Claude Code (Anthropic's CLI tool). When activated, it gives Claude deep knowledge about DuckDB CLI, enabling it to help you with:

• SQL queries on CSV, Parquet, and JSON files
• Data conversion between formats
• Database operations and analysis
• Command-line arguments and dot commands
• Output formatting and configuration

Installation

Claude Code (recommended)

Copy SKILL.md to your Claude Code skills directory:

mkdir -p ~/.claude/skills/duckdb
cp SKILL.md ~/.claude/skills/duckdb/

Then use /duckdb in Claude Code to activate the skill.

Other AI Tools

The SKILL.md file follows standard markdown conventions and can be adapted for use with other AI assistants that support custom instructions or system prompts.

What's Included

The skill covers all official DuckDB CLI documentation:

• Quick Start - Read files directly with SQL
• Command Line Arguments - All flags and options
• Data Conversion - CSV, Parquet, JSON transformations
• Dot Commands - Schema inspection, output control
• Output Formats - All 18 available formats
• Keyboard Shortcuts - Navigation, history, editing
• Autocomplete - Context-aware completion
• Configuration - ~/.duckdbrc settings
• Safe Mode - Restricted file access mode

Example Usage

Once the skill is activated, you can ask Claude things like:

• "Convert this CSV to Parquet"
• "Show me statistics for sales.csv"
• "Join these two files on customer_id"
• "What's the DuckDB command to export as JSON?"

Documentation Sources

Based on official DuckDB documentation:

• CLI Overview
• Arguments
• Dot Commands
• Output Formats
• Editing
• Autocomplete
• Syntax Highlighting
• Safe Mode

License

MIT