
DuckDB's embedded, in-process analytical database engine offers unparalleled performance for local data processing. Its true power, however, is unlocked through its robust extension ecosystem. This guide details the practical application of three critical extensions: spatial for geospatial analytics, httpfs for direct querying of remote Parquet files over HTTP(S), and iceberg for inspecting Apache Iceberg table metadata. We will explore their architectural underpinnings, provide runnable code examples, discuss performance considerations, and highlight common production pitfalls.
DuckDB's design prioritizes modularity and extensibility. The core engine provides fundamental SQL parsing, optimization, and execution capabilities. Extensions augment this core by introducing new functionalities, including:
GEOMETRY for spatial data.ST_Intersects or READ_PARQUET.httpfs for S3, GCS, and generic HTTP(S) access.Extensions are dynamically loaded, allowing users to tailor their DuckDB environment to specific analytical needs without bloating the core engine. This "bring your own functionality" model ensures a lean, high-performance base while offering extensive capabilities on demand.
The typical workflow for using an extension involves two SQL commands:
INSTALL <extension_name>;: Downloads and installs the extension binaries. This usually requires an internet connection the first time.LOAD <extension_name>;: Loads the installed extension into the current DuckDB session, making its functions and types available.This separation allows for efficient management and ensures that only necessary components consume resources.
spatial ExtensionGeospatial data is ubiquitous, from logistics and urban planning to environmental monitoring. The spatial extension brings powerful GIS capabilities directly into DuckDB, enabling complex spatial queries on local data without requiring a separate geospatial database.
The spatial extension introduces the GEOMETRY data type, which can represent points, lines, polygons, and multi-geometries. It supports standard geospatial data formats and operations:
POINT (10 20), POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)).The extension provides functions for converting between these formats, creating geometries, and performing spatial relationships (e.g., intersection, containment, distance).
INSTALL spatial;
LOAD spatial;
Consider a scenario where we have a dataset of points (e.g., sensor locations) and a set of polygons (e.g., administrative boundaries). We want to find which sensors fall within which boundaries.
First, let's generate some sample GeoJSON data for demonstration.
import duckdb
import json
# Sample GeoJSON data
# Polygon 1: A square area
polygon_geojson_1 = {
"type": "Feature",
"geometry": {
"type": "Polygon",
"coordinates": [[
[0, 0], [0, 10], [10, 10], [10, 0], [0, 0]
]]
},
"properties": {"name": "Area A"}
}
# Polygon 2: Another square area, slightly offset
polygon_geojson_2 = {
"type": "Feature",
"geometry": {
"type": "Polygon",
"coordinates": [[
[5, 5], [5, 15], [15, 15], [15, 5], [5, 5]
]]
},
"properties": {"name": "Area B"}
}
# Points: Some inside, some outside, some in overlap
point_geojson_1 = {
"type": "Feature",
"geometry": {"type": "Point", "coordinates": [2, 2]},
"properties": {"sensor_id": "S001"}
}
point_geojson_2 = {
"type": "Feature",
"geometry": {"type": "Point", "coordinates": [7, 7]},
"properties": {"sensor_id": "S002"}
}
point_geojson_3 = {
"type": "Feature",
"geometry": {"type": "Point", "coordinates": [12, 12]},
"properties": {"sensor_id": "S003"}
}
point_geojson_4 = {
"type": "Feature",
"geometry": {"type": "Point", "coordinates": [20, 20]},
"properties": {"sensor_id": "S004"}
}
# Create DuckDB in-memory database
con = duckdb.connect(database=':memory:', read_only=False)
# Install and load spatial extension
con.execute("INSTALL spatial;")
con.execute("LOAD spatial;")
# Create tables and insert data
con.execute("""
CREATE TABLE polygons (
id INTEGER,
name VARCHAR,
geometry GEOMETRY
);
""")
con.execute("""
CREATE TABLE points (
id INTEGER,
sensor_id VARCHAR,
geometry GEOMETRY
);
""")
# Insert polygons
con.execute(f"""
INSERT INTO polygons (id, name, geometry) VALUES
(1, '{polygon_geojson_1['properties']['name']}', ST_GeomFromGeoJSON('{json.dumps(polygon_geojson_1['geometry'])}')),
(2, '{polygon_geojson_2['properties']['name']}', ST_GeomFromGeoJSON('{json.dumps(polygon_geojson_2['geometry'])}'));
""")
# Insert points
con.execute(f"""
INSERT INTO points (id, sensor_id, geometry) VALUES
(1, '{point_geojson_1['properties']['sensor_id']}', ST_GeomFromGeoJSON('{json.dumps(point_geojson_1['geometry'])}')),
(2, '{point_geojson_2['properties']['sensor_id']}', ST_GeomFromGeoJSON('{json.dumps(point_geojson_2['geometry'])}')),
(3, '{point_geojson_3['properties']['sensor_id']}', ST_GeomFromGeoJSON('{json.dumps(point_geojson_3['geometry'])}')),
(4, '{point_geojson_4['properties']['sensor_id']}', ST_GeomFromGeoJSON('{json.dumps(point_geojson_4['geometry'])}'));
""")
# Perform spatial intersection query
print("Sensors intersecting with polygons:")
result = con.execute("""
SELECT
p.sensor_id,
poly.name AS polygon_name
FROM
points AS p,
polygons AS poly
WHERE
ST_Intersects(p.geometry, poly.geometry);
""").fetchdf()
print(result)
# Example: Calculate distance between two points
print("\nDistance between S001 and S002:")
distance_result = con.execute("""
SELECT
ST_Distance(
(SELECT geometry FROM points WHERE sensor_id = 'S001'),
(SELECT geometry FROM points WHERE sensor_id = 'S002')
) AS distance;
""").fetchdf()
print(distance_result)
con.close()
Architectural Explanation:
The ST_GeomFromGeoJSON function parses the GeoJSON string and converts it into DuckDB's internal GEOMETRY representation. This representation is optimized for spatial operations. The ST_Intersects function then performs a spatial join, efficiently determining overlaps between geometries. For complex queries involving many geometries, DuckDB's query optimizer leverages spatial indexing (if available and enabled by the extension, though explicit spatial index creation like in PostGIS is not a primary DuckDB feature; it relies on internal optimizations for spatial joins).
GEOMETRY types is more efficient than parsing WKT/WKB/GeoJSON strings repeatedly.ST_Simplify can significantly reduce processing time.ST_Intersects(ST_Envelope(geom1), ST_Envelope(geom2))) can quickly eliminate non-overlapping geometries before more expensive precise intersection checks.httpfs ExtensionAccessing data stored remotely in cloud object storage (S3, GCS, Azure Blob Storage) or via standard HTTP(S) is a fundamental requirement for modern data architectures. The httpfs extension allows DuckDB to directly query Parquet, CSV, and JSON files from these remote sources without requiring a full download of the entire file.
The httpfs extension operates by leveraging HTTP Range requests. Instead of downloading the entire remote file, DuckDB requests specific byte ranges corresponding to the data blocks or column chunks required by the query. This is particularly efficient for:
WHERE clause can be evaluated against metadata or statistics embedded in the Parquet file (e.g., min/max values for a column), DuckDB can skip entire row groups that do not satisfy the predicate, fetching even less data.This "zero-ETL" approach minimizes network transfer, reduces latency, and allows DuckDB to act as a powerful analytical engine directly on data lakes.
INSTALL httpfs;
LOAD httpfs;
We will query a publicly available Parquet dataset, such as the NYC Taxi Trip Record Data, which is often hosted on S3.
import duckdb
import os
# Create DuckDB in-memory database
con = duckdb.connect(database=':memory:', read_only=False)
# Install and load httpfs extension
con.execute("INSTALL httpfs;")
con.execute("LOAD httpfs;")
# Configure S3 credentials if accessing private buckets.
# For public buckets, these are not strictly necessary, but good practice for consistency.
# Replace with your actual credentials or environment variables.
# con.execute("SET s3_access_key_id='YOUR_ACCESS_KEY_ID';")
# con.execute("SET s3_secret_access_key='YOUR_SECRET_ACCESS_KEY';")
# con.execute("SET s3_region='us-east-1';") # Or your bucket's region
# Example: Querying a public Parquet file from S3
# This URL points to a small sample of NYC Yellow Taxi data for 2023-01
s3_parquet_url = "s3://nyc-tlc/trip data/yellow_tripdata_2023-01.parquet"
print(f"Querying remote Parquet file: {s3_parquet_url}")
# Query 1: Count total rows (demonstrates full scan if no pushdown)
print("\nQuery 1: Count total rows")
result_count = con.execute(f"SELECT COUNT(*) FROM '{s3_parquet_url}';").fetchdf()
print(result_count)
# Query 2: Select specific columns and apply a filter (demonstrates column pruning and predicate pushdown)
print("\nQuery 2: Select specific columns and filter by trip distance")
result_filtered = con.execute(f"""
SELECT
vendor_id,
tpep_pickup_datetime,
trip_distance,
total_amount
FROM
'{s3_parquet_url}'
WHERE
trip_distance > 10
LIMIT 10;
""").fetchdf()
print(result_filtered)
# Query 3: Aggregate data (demonstrates more complex processing)
print("\nQuery 3: Average trip distance by vendor")
result_avg_distance = con.execute(f"""
SELECT
vendor_id,
AVG(trip_distance) AS avg_distance
FROM
'{s3_parquet_url}'
GROUP BY
vendor_id;
""").fetchdf()
print(result_avg_distance)
con.close()
Architectural Explanation:
When READ_PARQUET('s3://...') is executed, DuckDB's httpfs extension first reads the Parquet file's footer and schema metadata. This metadata contains information about column types, row group boundaries, and statistics. The query optimizer then uses this information to determine which byte ranges (corresponding to specific columns and row groups) are necessary to fulfill the query. Only these specific ranges are fetched over HTTP(S), minimizing data transfer. For instance, in "Query 2", DuckDB only fetches the vendor_id, tpep_pickup_datetime, trip_distance, and total_amount columns, and potentially skips row groups where the trip_distance min/max range does not overlap with > 10.
httpfs is the network. High latency or low bandwidth to the remote storage will directly impact query performance.SELECT col1, col2 instead of SELECT *). This is the most significant performance gain for wide tables.WHERE clauses that can leverage these statistics.httpfs handles large files, many small Parquet files can lead to increased overhead due to more metadata reads and HTTP requests. Optimal Parquet file sizes are typically in the range of 128MB to 1GB.s3_access_key_id, s3_secret_access_key, s3_region, s3_endpoint, etc.) are managed securely. Avoid hardcoding them in production environments. Use environment variables, IAM roles (for EC2/ECS), or temporary credentials.iceberg ExtensionApache Iceberg is an open table format designed for large, high-performance analytic tables. It provides features like schema evolution, hidden partitioning, partition evolution, and time travel. The iceberg extension for DuckDB allows you to inspect and query Iceberg table metadata, and subsequently query the underlying data files.
An Iceberg table is not a single file but a collection of files organized by metadata. Key components include:
The DuckDB iceberg extension primarily focuses on reading the Iceberg metadata. When you use iceberg_scan(), DuckDB parses the Iceberg metadata files (starting from the table's root metadata location) to understand the table's schema, partitioning, and the locations of the actual data files. It then uses the httpfs extension (if the data files are remote) to read these data files.
Important Note: The DuckDB iceberg extension is currently read-only. It cannot create, modify, or write to Iceberg tables. Its primary use case is for quick inspection and analytical querying of existing Iceberg datasets.
INSTALL iceberg;
LOAD iceberg;
For this example, we'll assume an existing Iceberg table stored on S3. If you don't have one, you can create a dummy one using Spark or Flink, or point to a public Iceberg sample. We'll use a hypothetical path.
import duckdb
import os
# Create DuckDB in-memory database
con = duckdb.connect(database=':memory:', read_only=False)
# Install and load httpfs and iceberg extensions
con.execute("INSTALL httpfs;")
con.execute("LOAD httpfs;")
con.execute("INSTALL iceberg;")
con.execute("LOAD iceberg;")
# Configure S3 credentials if accessing private buckets
# con.execute("SET s3_access_key_id='YOUR_ACCESS_KEY_ID';")
# con.execute("SET s3_secret_access_key='YOUR_SECRET_ACCESS_KEY';")
# con.execute("SET s3_region='us-east-1';")
# Path to the Iceberg table's metadata directory (e.g., s3://your-bucket/your-iceberg-table/metadata)
# Replace with a real Iceberg table path if you have one.
# For demonstration, we'll use a placeholder.
# A real Iceberg table path would look like: s3://bucket/path/to/table
# The iceberg extension expects the path to the table directory, not the metadata directory specifically.
iceberg_table_path = "s3://duckdb-iceberg-sample/nyc_taxi_trips" # Example public Iceberg table
print(f"Accessing Iceberg table at: {iceberg_table_path}")
# Query 1: Inspect Iceberg table schema and metadata
# The iceberg_scan function allows you to query the table directly.
print("\nQuery 1: Inspecting Iceberg table schema and a few rows")
try:
result_schema = con.execute(f"""
SELECT *
FROM iceberg_scan('{iceberg_table_path}')
LIMIT 5;
""").fetchdf()
print(result_schema)
except duckdb.Error as e:
print(f"Error querying Iceberg table: {e}")
print("Please ensure the Iceberg table path is correct and accessible.")
# Query 2: Perform an aggregation on the Iceberg table data
print("\nQuery 2: Average trip distance from Iceberg table")
try:
result_avg_distance_iceberg = con.execute(f"""
SELECT
vendor_id,
AVG(trip_distance) AS avg_distance
FROM
iceberg_scan('{iceberg_table_path}')
GROUP BY
vendor_id;
""").fetchdf()
print(result_avg_distance_iceberg)
except duckdb.Error as e:
print(f"Error querying Iceberg table: {e}")
print("Please ensure the Iceberg table path is correct and accessible.")
con.close()
Architectural Explanation:
When iceberg_scan() is called, DuckDB first locates the metadata directory within the specified iceberg_table_path. It then reads the latest version-hint.text or directly the latest vX.metadata.json file to identify the current snapshot. From the snapshot, it reads the manifest list files, which in turn point to the manifest files. Finally, the manifest files provide the paths to the actual data files (e.g., Parquet files). DuckDB then uses the httpfs extension to read these individual data files, applying column pruning and predicate pushdown as described in the httpfs section. This multi-step metadata resolution is transparent to the user, who simply queries the iceberg_scan() function.
iceberg extension is read-only. It cannot be used to create, append, update, or delete data in Iceberg tables.DuckDB's performance is highly dependent on efficient resource utilization.
PRAGMA memory_limit='8GB'; -- Set to 8 Gigabytes
PRAGMA threads=4; -- Use 4 threads
httpfs and iceberg to access remote resources, external access must be enabled.
SET enable_external_access=true;
httpfs as it minimizes network I/O.WHERE clauses are as selective as possible. DuckDB's optimizer will push these predicates down to the data source (e.g., Parquet file statistics) to reduce the amount of data read.GEOMETRY types.CREATE SPATIAL INDEX syntax like PostGIS, its query optimizer is designed to efficiently handle spatial joins. For very large spatial datasets, consider pre-processing to simplify geometries or using bounding box filters.httpfs and iceberg, ensure underlying Parquet files are well-optimized:
WHERE clauses.Extension Not Found/Loaded:
Error: Catalog Error: Function with name 'ST_Intersects' does not exist.INSTALLed or LOADed in the current session. INSTALL downloads, LOAD activates.INSTALL <extension_name>; and LOAD <extension_name>; at the beginning of your session. Ensure internet connectivity during INSTALL.HTTPFS Credentials and Permissions:
Error: IO Error: S3 Error [AWS_ERROR_S3_ACCESS_DENIED] or [AWS_ERROR_S3_INVALID_ACCESS_KEY_ID]SET commands for s3_access_key_id, s3_secret_access_key, s3_region, or insufficient IAM permissions on the bucket/object.s3:GetObject and s3:ListBucket permissions. For GCS, ensure gcs_access_key_id and gcs_secret_access_key are set, or use service account credentials.Network Latency and Egress Costs:
SELECT specific_cols) and predicate pushdown (WHERE filter_conditions). Monitor egress costs.Memory Exhaustion:
Error: Out of Memory: Failed to allocate X bytesPRAGMA memory_limit='XGB';. Optimize queries to reduce intermediate result sizes. For httpfs, ensure strong predicate pushdown and column pruning. Consider processing data in chunks if possible.Data Type Mismatches (Spatial):
Error: Conversion Error: Could not convert string '...' to GEOMETRY.ST_GeomFromWKT, `ST_A data engineer is designing a DuckDB-based analytics pipeline that needs to query large Parquet files stored in an S3 bucket. The pipeline will run on an EC2 instance with limited local storage but good network connectivity. Which DuckDB extension and approach would be most efficient for this scenario, considering both performance and resource utilization?
Clean AI CLI logs, build cron expressions, decode JWTs, and calculate chmod permissions offline.

Columnar data formats benchmarked: disk compression with Parquet, in-memory zero-copy with Arrow, and vectorized query execution with DuckDB.
Read more
Ditch Pandas memory bloat: accelerate data pipelines with Polars (Rust multi-threading, LazyFrames, Arrow) and DuckDB (in-process vectorized SQL, out-of-core Parquet streaming).
Read more
ClickHouse vs DuckDB benchmarked on 100M rows: comparing client-side embedded OLAP against distributed column-oriented warehouses.
Read more