DuckDB Extensions

Learning Focus

Use this lesson to discover and install DuckDB extensions that expand its capabilities beyond standard SQL.

How Extensions Work

DuckDB extensions are plugins that add:

• New file format support (Parquet, Excel, SQLite)
• New functions (spatial, full-text search)
• New protocol support (HTTPFS, Azure)

-- Install an extension (downloads once to local cache)
INSTALL extension_name;

-- Load extension for current session
LOAD extension_name;

-- Install + load in one step
INSTALL httpfs; LOAD httpfs;

Core Extensions (Pre-bundled)

These are included with DuckDB — just LOAD them:

Extension	Purpose
httpfs	Read from HTTP, S3, GCS, Azure
parquet	Read/write Parquet (auto-loaded)
json	JSON functions (auto-loaded)
fts	Full-text search
icu	Unicode collation & regex
tpch	TPC-H benchmark data generator
tpcds	TPC-DS benchmark data generator

Spatial Extension

INSTALL spatial; LOAD spatial;

-- Create spatial data
CREATE TABLE cities AS
SELECT city_name,
    ST_Point(longitude, latitude) AS geom
FROM read_csv_auto('cities.csv');

-- Distance between two points
SELECT ST_Distance(
    ST_Point(-73.9857, 40.7484),  -- New York
    ST_Point(-87.6298, 41.8781)   -- Chicago
) AS distance_degrees;

-- Points within radius
SELECT city_name
FROM cities
WHERE ST_DWithin(geom, ST_Point(-73.9857, 40.7484), 1.0);

Full-Text Search Extension

INSTALL fts; LOAD fts;

CREATE TABLE articles (id INTEGER, title VARCHAR, body VARCHAR);
INSERT INTO articles VALUES
    (1, 'DuckDB Tutorial', 'Learn how to use DuckDB for analytics'),
    (2, 'SQL Basics', 'Understanding SQL fundamentals');

-- Create FTS index
PRAGMA create_fts_index('articles', 'id', 'title', 'body');

-- Search
SELECT id, title, fts_main_articles.match_bm25(id, 'DuckDB analytics') AS score
FROM articles
ORDER BY score DESC;

Excel Extension

INSTALL excel; LOAD excel;

-- Read an Excel file
SELECT * FROM read_xlsx('report.xlsx');

-- Specify sheet
SELECT * FROM read_xlsx('report.xlsx', sheet = 'Q1_Sales');

SQLite Extension

INSTALL sqlite; LOAD sqlite;

-- Attach a SQLite database and query it directly
ATTACH 'legacy.db' AS sqlite_db (TYPE SQLITE);
SELECT * FROM sqlite_db.customers LIMIT 10;

Benchmark Data Generators

INSTALL tpch; LOAD tpch;

-- Generate TPC-H benchmark data (scale factor 1 = ~1GB)
CALL dbgen(sf = 1);

SHOW TABLES;  -- lineitem, orders, customer, etc.
SELECT COUNT(*) FROM lineitem;  -- 6M rows

Listing Available Extensions

-- See all installable extensions
SELECT * FROM duckdb_extensions();

-- Filter by loaded status
SELECT extension_name, installed, loaded
FROM duckdb_extensions()
WHERE installed = true;

Common Pitfalls

Pitfall	Consequence	Prevention
Forgetting LOAD after INSTALL	Extension not available	Always pair INSTALL with LOAD
LOAD on every session	Overhead in scripts	Add LOAD ext; to startup SQL
Installing outdated extension	Missing features	UPDATE EXTENSIONS; to update all

Quick Reference

INSTALL ext_name; LOAD ext_name;
SELECT * FROM duckdb_extensions();
UPDATE EXTENSIONS;

-- Common trio
INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;
INSTALL excel;  LOAD excel;

What's Next

• Next: Parallel Execution - Maximize CPU utilization in DuckDB.
• Module Overview - Return to this module index.