扩展系统

8.1 扩展系统概述

DuckDB 采用插件化架构，核心保持轻量，通过扩展（Extension）添加功能。扩展分为两类：

SQL-- 查看所有可用扩展
SELECT * FROM duckdb_extensions();

-- 安装并加载扩展
INSTALL spatial;
LOAD spatial;

-- 一次安装多个
INSTALL httpfs; LOAD httpfs;
INSTALL excel;  LOAD excel;

-- Python 中安装扩展
# con.install_extension("spatial")
# con.load_extension("spatial")

8.2 httpfs 扩展：云存储直连

Amazon S3

SQLINSTALL httpfs; LOAD httpfs;

-- 方式1：环境变量（推荐，不在 SQL 中暴露密钥）
-- AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY / AWS_DEFAULT_REGION

-- 方式2：SET 命令
SET s3_region = 'cn-north-1';
SET s3_access_key_id = 'AKIAXXXXXXXXXXXXXXXX';
SET s3_secret_access_key = 'xxxxxxxxxxxxxxxxxxxx';

-- 方式3：使用 SECRETS（更安全，推荐）
CREATE SECRET my_s3 (
    TYPE S3,
    KEY_ID     'AKIAXXXXXXXXXXXXXXXX',
    SECRET     'xxxxxxxxxxxxxxxxxxxx',
    REGION     'cn-north-1'
);

-- 查询 S3 文件
SELECT * FROM 's3://my-bucket/data/orders.parquet';
SELECT * FROM 's3://my-bucket/logs/2024/**/*.csv';

-- 写入到 S3
COPY orders TO 's3://my-bucket/export/orders.parquet'
(FORMAT PARQUET, COMPRESSION ZSTD);

Google Cloud Storage

SQLCREATE SECRET my_gcs (
    TYPE GCS,
    KEY_ID 'your-hmac-key',
    SECRET 'your-hmac-secret'
);
SELECT * FROM 'gs://my-bucket/data.parquet';

Azure Blob Storage

SQLINSTALL azure; LOAD azure;

CREATE SECRET my_azure (
    TYPE AZURE,
    CONNECTION_STRING 'DefaultEndpointsProtocol=https;AccountName=...;AccountKey=...;'
);

SELECT * FROM 'azure://my-container/data.parquet';
-- 或使用 abfs:// 协议
SELECT * FROM 'abfs://my-container@account.dfs.core.windows.net/data.parquet';

8.3 spatial 扩展：空间数据分析

spatial 扩展让 DuckDB 具备 PostGIS 的空间查询能力，支持 GeoJSON、Shapefile、WKB/WKT 等格式。

SQLINSTALL spatial; LOAD spatial;

-- 读取 GeoJSON 文件
SELECT * FROM ST_Read('cities.geojson');

-- 读取 Shapefile
SELECT * FROM ST_Read('boundaries.shp');

-- 点在多边形内的查询（门店选址分析）
SELECT
    s.store_id,
    s.store_name,
    d.district_name
FROM stores s, districts d
WHERE ST_Within(
    ST_Point(s.longitude, s.latitude),
    d.geom
);

-- 计算两点间距离（单位：米）
SELECT
    a.city,
    b.city,
    ST_Distance_Sphere(
        ST_Point(a.lon, a.lat),
        ST_Point(b.lon, b.lat)
    ) / 1000 AS distance_km
FROM cities a, cities b
WHERE a.city < b.city;

-- 找出 5km 内的所有商家
SELECT b.name, ST_Distance_Sphere(my_location, b.geom) / 1000 AS km
FROM businesses b
WHERE km <= 5
ORDER BY km;

-- 将几何数据导出为 GeoJSON
SELECT name, ST_AsGeoJSON(geom) AS geojson FROM districts;

8.4 fts 扩展：全文搜索

SQLINSTALL fts; LOAD fts;

-- 创建测试表
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR,
    content TEXT
);

INSERT INTO articles VALUES
    (1, 'DuckDB Introduction', 'DuckDB is an embedded analytical database'),
    (2, 'Python Data Analysis', 'Using pandas and DuckDB for fast analysis'),
    (3, 'Parquet Format', 'Apache Parquet is a columnar storage format');

-- 为表创建全文搜索索引
PRAGMA create_fts_index('articles', 'id', 'title', 'content');

-- 全文搜索查询
SELECT id, title, fts_main_articles.match_bm25(id, 'DuckDB analysis') AS score
FROM articles
WHERE score IS NOT NULL
ORDER BY score DESC;

8.5 excel 扩展：读写 Excel 文件

SQLINSTALL excel; LOAD excel;

-- 读取 Excel 文件（.xlsx）
SELECT * FROM read_xlsx('sales_report.xlsx');

-- 读取指定 Sheet
SELECT * FROM read_xlsx('report.xlsx', sheet = 'Sheet2');

-- 导出到 Excel
COPY (
    SELECT region, SUM(amount) AS total
    FROM orders
    GROUP BY region
) TO 'summary.xlsx' WITH (FORMAT xlsx);

8.6 其他常用扩展

扩展名	功能	典型用法
sqlite	读写 SQLite 数据库	ATTACH 'db.sqlite' (TYPE SQLITE)
mysql	连接 MySQL 数据库	ATTACH 'mysql://user:pass@host/db' (TYPE MYSQL)
postgres	连接 PostgreSQL	ATTACH 'postgresql://...' (TYPE POSTGRES)
json	JSON 函数（已内置）	json_extract()、json_array_length()
icu	Unicode 字符串排序	多语言排序支持
delta	读取 Delta Lake 表	delta_scan('s3://...')
iceberg	读取 Iceberg 表	iceberg_scan('s3://...')
tpch	TPC-H 基准测试数据	CALL dbgen(sf=1) 生成测试数据