duckdb

name

duckdb

description

Query and transform CSV and Parquet files using DuckDB SQL. Use when: (1) Querying CSV or Parquet files with SQL, (2) Transforming data between CSV/Parquet formats, (3) Aggregating, filtering, or joining data files, (4) Exporting query results to files, (5) Working with large datasets that need columnar analysis. Triggers on requests involving data analysis, file querying, format conversion, or SQL operations on CSV/Parquet files.

DuckDB Data Processing

What is DuckDB?

DuckDB is an in-process SQL OLAP database designed for analytical queries. Key features:

• Zero setup - No server, no configuration, just a single binary
• Query files directly - Run SQL on CSV, Parquet, and JSON without importing
• Fast analytics - Columnar storage with vectorized execution
• Familiar SQL - PostgreSQL-compatible syntax with extensions
• Portable - Works on Linux, macOS, Windows; embeds in Python, R, Node.js

Installation

# macOS (Homebrew)
brew install duckdb

# Ubuntu/Debian
sudo apt install duckdb

# Fedora
sudo dnf install duckdb

# pip (Python)
pip install duckdb

# From binary (Linux — auto-detects x86_64 or ARM64)
ARCH=$(uname -m); case "$ARCH" in arm64|aarch64) ARCH=aarch64 ;; *) ARCH=amd64 ;; esac
curl -LO "https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-${ARCH}.zip"
unzip "duckdb_cli-linux-${ARCH}.zip"
chmod +x duckdb
sudo mv duckdb /usr/local/bin/

Verify installation:

duckdb --version

Quick Reference

Query Files Directly

-- CSV files
SELECT * FROM 'data.csv';
SELECT * FROM read_csv('data.csv');
SELECT * FROM read_csv('data.csv', header = false);

-- Parquet files
SELECT * FROM 'data.parquet';
SELECT * FROM read_parquet('data.parquet');

-- Multiple files with glob patterns
SELECT * FROM read_csv('data/*.csv');
SELECT * FROM read_parquet('logs/*.parquet');

Export Results

-- To CSV
COPY (SELECT * FROM 'input.parquet' WHERE status = 'active')
TO 'output.csv' (HEADER, DELIMITER ',');

-- To Parquet
COPY (SELECT * FROM 'input.csv') TO 'output.parquet' (FORMAT PARQUET);

-- To JSON
COPY (SELECT * FROM 'data.csv') TO 'output.json' (FORMAT JSON, ARRAY true);

Common Transformations

-- Filter and aggregate
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM 'sales.csv'
WHERE date >= '2024-01-01'
GROUP BY category;

-- Join multiple files
SELECT a.*, b.description
FROM 'orders.csv' a
JOIN 'products.parquet' b ON a.product_id = b.id;

-- Convert CSV to Parquet
COPY (FROM 'data.csv') TO 'data.parquet' (FORMAT PARQUET);

-- Convert Parquet to CSV
COPY (FROM 'data.parquet') TO 'data.csv' (HEADER);

CLI Usage

One-liner Queries

# Query and display results
duckdb -c "SELECT * FROM 'data.csv' LIMIT 10"

# Query with output to file
duckdb -c "COPY (SELECT * FROM 'input.csv' WHERE amount > 100) TO 'filtered.csv' (HEADER)"

# In-memory database (default)
duckdb :memory: "SELECT COUNT(*) FROM 'large_file.parquet'"

Pipe Data Through DuckDB

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

# Transform and output to stdout
cat input.csv | duckdb -c "COPY (SELECT * FROM read_csv('/dev/stdin') WHERE active = true) TO '/dev/stdout' WITH (FORMAT csv, HEADER)"

Execute SQL File

duckdb < query.sql
duckdb my_database.duckdb < transform.sql

Read Functions Reference

read_csv Options

SELECT * FROM read_csv('file.csv',
    delim = ',',           -- Delimiter character
    header = true,         -- First row is header
    skip = 0,              -- Skip N rows at start
    nullstr = 'NULL',      -- String representing NULL
    columns = {            -- Explicit column types
        'id': 'INTEGER',
        'name': 'VARCHAR',
        'date': 'DATE'
    }
);

read_parquet Options

SELECT * FROM read_parquet('file.parquet',
    hive_partitioning = true,  -- Enable Hive partitioning
    union_by_name = true       -- Union schemas by column name
);

-- Read specific columns only (automatic projection pushdown)
SELECT id, name FROM read_parquet('large.parquet');

COPY Statement Reference

Export to CSV

COPY table_name TO 'output.csv' (
    HEADER true,           -- Include header row
    DELIMITER ',',         -- Column delimiter
    QUOTE '"',             -- Quote character
    NULL 'NULL'            -- NULL value representation
);

Export to Parquet

COPY table_name TO 'output.parquet' (
    FORMAT PARQUET,
    COMPRESSION 'zstd'     -- Options: snappy, gzip, zstd, lz4
);

Partitioned Export

-- Write partitioned Parquet files
COPY (SELECT * FROM 'data.csv')
TO 'output' (FORMAT PARQUET, PARTITION_BY (year, month));

Common Patterns

Schema Inspection

-- View CSV structure
DESCRIBE SELECT * FROM 'data.csv';

-- Get column names and types
SELECT column_name, column_type
FROM (DESCRIBE SELECT * FROM 'file.parquet');

Data Sampling

-- Random sample
SELECT * FROM 'large.parquet' USING SAMPLE 10%;

-- First N rows
SELECT * FROM 'data.csv' LIMIT 100;

Window Functions

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY date) as row_num
FROM 'transactions.csv';

Pivoting Data

PIVOT (SELECT * FROM 'sales.csv')
ON month
USING SUM(revenue)
GROUP BY product;

String Operations

SELECT
    LOWER(name) as name_lower,
    SPLIT_PART(email, '@', 2) as domain,
    CONCAT(first_name, ' ', last_name) as full_name
FROM 'users.csv';

Remote Files

-- HTTP/HTTPS
SELECT * FROM read_csv('https://example.com/data.csv');
SELECT * FROM read_parquet('https://example.com/data.parquet');

-- S3 (requires credentials setup)
SELECT * FROM read_parquet('s3://bucket/path/file.parquet');

Performance Tips

1. Parquet > CSV for large datasets - columnar format enables projection pushdown
2. Use column selection - only select needed columns: SELECT col1, col2 instead of SELECT *
3. Filter early - DuckDB pushes filters down to file readers
4. Glob patterns - process multiple files in one query with *.csv or **/*.parquet
5. Compression - DuckDB handles gzipped CSV (file.csv.gz) automatically