duckdb

Files (39)

• marketplace.json · .claude-plugin/
• plugin.json · .claude-plugin/
• LICENSE
• README.md
• SKILL.md
• detect.md · commands/
• hooks.json · hooks/
• post-bash-check.js · hooks/
• a5.md · refs/
• autocomplete.md · refs/
• bigquery.md · refs/
• ducklake.md · refs/
• explain-analyze.md · refs/
• fts.md · refs/
• gdal-formats.md · refs/
• h3.md · refs/
• iceberg.md · refs/
• overture-maps.md · refs/
• python-api.md · refs/
• raster-tiling.md · refs/
• spatial-indexes.md · refs/
• a5-s2.md · refs/spatial/
• accessors.md · refs/spatial/
• aggregates.md · refs/spatial/
• conversion-io.md · refs/spatial/
• core-v15.md · refs/spatial/
• coverage-tiling.md · refs/spatial/
• creation.md · refs/spatial/
• index.md · refs/spatial/
• linear-ref.md · refs/spatial/
• macros.md · refs/spatial/
• measurement.md · refs/spatial/
• predicates.md · refs/spatial/
• table-functions.md · refs/spatial/
• transforms.md · refs/spatial/
• summarize.md · refs/
• vss.md · refs/
• wasm-patterns.md · refs/
• settings.json

SKILL.md (first lines)

# DuckDB v1.5 Skill

> DuckDB v1.5.0 "Variegata" — Released 2026-03-09.

## How to Think: Discovery → Understanding → Analysis

The most common mistake is jumping straight to queries. Instead, follow this workflow — it prevents wrong assumptions, bad joins, and wasted work.

### Phase 1: Discover the Data

Before writing any analytical query, find out what you're working with.

**Local files — use glob patterns to find them:**

```sql
-- Find what files exist (Parquet, CSV, JSON, GeoJSON, etc.)
FROM glob('data/*.parquet');
FROM glob('**/*.csv');
FROM glob('/path/to/project/**/*.geojson');
```

**Remote files — always load httpfs first, then probe:**

```sql
INSTALL httpfs; LOAD httpfs;
-- For S3, also set the region:
SET s3_region = 'us-west-2';

-- Remote Parquet (only reads metadata, not full file)
DESCRIBE FROM 'https://example.com/data.parquet';

-- S3 / Azure / GCS
DESCRIBE FROM 's3://bucket/path/file.parquet';

-- S3 buckets with dots in the name (e.g., source.coop) need path-style URLs,
-- because virtual-hosted style breaks SSL certificate validation:
SET s3_url_style = 'path';
DESCRIBE FROM 's3://us-west-2.opendata.source.coop/repo/data.parquet';

-- Hive-partitioned datasets — check partition structure
FROM parquet_metadata('s3://bucket/dataset/**/*.parquet') LIMIT 5;
```

**Databases — inspect what's available:**

```sql
-- Attached databases
SHOW ALL TABLES;
DESCRIBE table_name;

-- External DuckDB files (new in v1.5 — no ATTACH needed)
FROM read_duckdb('other.duckdb', table_name := 'my_table');

-- External databases via ODBC (new in v1.5, requires INSTALL odbc_scanner)
-- odbc_connect(conn_str) + odbc_query(conn, sql)
```

**Discover functions from DuckDB itself** — when unsure about a function signature:

```sql
-- Search for functions by name pattern
SELECT function_name, parameters, return_type
FROM duckdb_functions() WHERE function_name LIKE 'ST_Coverage%';

-- List all extensions and their status
FROM duckdb_extensions();

-- List all settings
FROM duckdb_settings() WHERE name LIKE '%geometry%';
```

**Geospatial files — check layers and metadata:**

```sql
LOAD spatial;
SELECT * FROM ST_Read_Meta('data.gpkg');       -- layers, CRS, field schemas
SELECT * FROM ST_Read_Meta('data.gdb');        -- ESRI File Geodatabase
```

### Phase 2: Understand the Shape

Once you know WHAT files exist, understand their structure before querying.

```sql
-- Schema: column names and types (instant, reads only metadata)
DESCRIBE FROM 'data.parquet';
DESCRIBE FROM 'data.csv';

-- Stats: min, max, approx_unique, avg, std, quartiles, nulls (scans full data)
SUMMARIZE FROM 'data.parquet';
SUMMARIZE FROM 'data.csv';

-- Quick sample: see actual values (avoids scanning everything)
FROM 'data.parquet' LIMIT 10;
FROM 'data.csv' LIMIT 10;

-- Row count estimate (fast for Parquet — reads metadata only)
SELECT count(*) FROM 'data.parquet';

-- For Parquet: row group structure, column statistics, file-level metadata
FROM parquet_metadata('data.parquet');
FROM parquet_schema('data.parquet');
FROM parquet_kv_metadata('data.parquet');  -- GeoParquet metadata lives here
```

This phase is critical because:
- DESCRIBE tells you column types — you'll know if geometry is WKB blob vs native GEOMETRY
- SUMMARIZE reveals data quality issues (null percentages, unexpected ranges, cardinality)
- A quick LIMIT sample shows actual values — field naming conventions, coordinate order, encoding
- For spatial data: you need to know the CRS before any spatial operations

### Detecting the CRS

Knowing the CRS is essential before any spatial operation. The method depends on how geometry is stored:

**Step 1: Identify the format.** Run DESCRIBE first — the column type tells you which case you're in:

| Column type | Format | CRS location |
|---|---|---|

From yharby/duckdb-skill, MIT.