toolkitbox/duckdb repository overview

# Interactive mode with a database file
docker run -it --rm \
  -v $(pwd):/data \
  ghcr.io/pabpereza/toolkitbox/duckdb:latest \
  duckdb /data/mydatabase.duckdb

# Execute single query
docker run --rm \
  -v $(pwd):/data \
  ghcr.io/pabpereza/toolkitbox/duckdb:latest \
  duckdb /data/mydatabase.duckdb "SELECT * FROM users;"

# Create in-memory database for testing
docker run -it --rm \
  ghcr.io/pabpereza/toolkitbox/duckdb:latest \
  duckdb

apiVersion: v1
kind: Pod
metadata:
  name: duckdb-client
spec:
  containers:
  - name: duckdb-client
    image: ghcr.io/pabpereza/toolkitbox/duckdb:latest
    command: ["sleep", "infinity"]
    volumeMounts:
    - name: data
      mountPath: /data
  volumes:
  - name: data
    persistentVolumeClaim:
      claimName: duckdb-data

duckdb mydatabase.duckdb

duckdb -readonly mydatabase.duckdb

duckdb mydatabase.duckdb "SELECT * FROM users;"

duckdb

duckdb mydatabase.duckdb < script.sql

SELECT * FROM read_csv_auto('data.csv');

-- Create table from CSV
CREATE TABLE users AS SELECT * FROM read_csv_auto('users.csv');

SELECT * FROM read_parquet('data.parquet');

-- Export to Parquet
COPY users TO 'users.parquet' (FORMAT PARQUET);

SELECT * FROM read_json_auto('data.json');

-- Query CSV from URL
SELECT * FROM read_csv_auto('https://example.com/data.csv');

-- Query Parquet from S3
SELECT * FROM read_parquet('s3://bucket/data.parquet');

-- Create table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    email VARCHAR UNIQUE,
    created_at TIMESTAMP DEFAULT current_timestamp
);

-- Insert data
INSERT INTO users (id, name, email) VALUES (1, 'John', '[email protected]');

-- Select data
SELECT * FROM users;

-- Update data
UPDATE users SET name = 'Jane' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

-- Show table schema
.schema users

# Export to CSV
duckdb mydatabase.duckdb "COPY users TO 'users.csv' (HEADER, DELIMITER ',');"

# Export to Parquet
duckdb mydatabase.duckdb "COPY users TO 'users.parquet' (FORMAT PARQUET);"

# Import from CSV
duckdb mydatabase.duckdb "CREATE TABLE users AS SELECT * FROM read_csv_auto('users.csv');"

# Show help
.help

# Show tables
.tables

# Show schema
.schema

# Change output mode
.mode csv
.mode json
.mode line
.mode box

# Enable headers
.headers on

# Open another database
.open another.duckdb

# Show current settings
.show

# Exit
.exit

-- Window functions
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

-- Common Table Expressions (CTEs)
WITH monthly_sales AS (
    SELECT date_trunc('month', sale_date) as month, SUM(amount) as total
    FROM sales
    GROUP BY 1
)
SELECT * FROM monthly_sales ORDER BY month;

-- PIVOT
PIVOT sales ON product USING SUM(amount);