Extending DuckDB: Spatial Analytics, Remote HTTPFS Parquet & Iceberg Integration

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 Extension Architecture Overview

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:

• New Data Types: Such as GEOMETRY for spatial data.
• New Functions: SQL functions like ST_Intersects or READ_PARQUET.
• New Storage Formats: Support for Parquet, CSV, JSON, and specialized formats.
• New Protocols: Like 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:

1. INSTALL <extension_name>;: Downloads and installs the extension binaries. This usually requires an internet connection the first time.
2. 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.

1. Spatial Analytics with the spatial Extension

Geospatial 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.

Core Concepts

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:

• Well-Known Text (WKT): A human-readable text markup for representing vector geometry objects. E.g., POINT (10 20), POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)).
• Well-Known Binary (WKB): A binary format for the same geometry objects, more efficient for storage and transmission.
• GeoJSON: A JSON-based format for encoding geographic data structures.

The extension provides functions for converting between these formats, creating geometries, and performing spatial relationships (e.g., intersection, containment, distance).

Installation and Loading

INSTALL spatial;
LOAD spatial;

Example: Loading GeoJSON and Performing Spatial Intersections

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).

Performance Considerations

• Data Representation: Storing geometries directly as GEOMETRY types is more efficient than parsing WKT/WKB/GeoJSON strings repeatedly.
• Simplification: For visualization or less precise analysis, simplifying complex geometries using functions like ST_Simplify can significantly reduce processing time.
• Bounding Box Filters: For very large datasets, pre-filtering using bounding box intersections (ST_Intersects(ST_Envelope(geom1), ST_Envelope(geom2))) can quickly eliminate non-overlapping geometries before more expensive precise intersection checks.
• Memory: Geospatial operations can be memory-intensive, especially with complex polygons. Ensure sufficient memory is allocated to DuckDB.

2. Remote HTTPFS Parquet with the httpfs Extension

Accessing 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.

Architectural Explanation

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:

• Column Pruning: If a query only selects a few columns, DuckDB only fetches the byte ranges for those specific columns.
• Predicate Pushdown: If a 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.

Installation and Loading

INSTALL httpfs;
LOAD httpfs;

Example: Querying Remote Parquet Files

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.

Performance Considerations

• Network Latency and Bandwidth: The primary bottleneck for httpfs is the network. High latency or low bandwidth to the remote storage will directly impact query performance.
• Region Proximity: Store your data in the same cloud region as your DuckDB instance (or the machine running it) to minimize network latency.
• Column Pruning: Always select only the columns you need (SELECT col1, col2 instead of SELECT *). This is the most significant performance gain for wide tables.
• Predicate Pushdown: Design your Parquet files with appropriate statistics (e.g., min/max values for frequently filtered columns) and use WHERE clauses that can leverage these statistics.
• Parquet File Size: While 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.
• Caching: DuckDB can cache remote data locally, improving performance for repeated queries on the same data.

Security Implications

• Credentials: For private S3/GCS buckets, ensure credentials (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.
• Public Access: Be cautious when granting public read access to buckets. Ensure only non-sensitive data is exposed.
• HTTPS: Always use HTTPS for remote access to encrypt data in transit.

3. Iceberg Integration with the iceberg Extension

Apache 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.

Architectural Explanation

An Iceberg table is not a single file but a collection of files organized by metadata. Key components include:

• Table Metadata File: Points to the current snapshot of the table.
• Manifest List Files: List manifest files for a snapshot.
• Manifest Files: List data files (Parquet, ORC, AVRO) that make up a partition or the entire table.
• Data Files: The actual data, typically in Parquet format.

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.

Installation and Loading

INSTALL iceberg;
LOAD iceberg;

Example: Inspecting an Iceberg Table's Metadata and Querying Data

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.

Limitations and Use Cases

• Read-Only: The primary limitation is that DuckDB's iceberg extension is read-only. It cannot be used to create, append, update, or delete data in Iceberg tables.
• Metadata Inspection: Excellent for quickly understanding an Iceberg table's schema, partitioning, and data file locations.
• Ad-hoc Analytics: Ideal for performing ad-hoc queries and analytics on existing Iceberg datasets without spinning up a distributed engine like Spark.
• Local Development/Testing: Useful for local development and testing against subsets of Iceberg data.

Performance Optimization & Memory Management

DuckDB's performance is highly dependent on efficient resource utilization.

• Memory Limit: Explicitly set a memory limit to prevent DuckDB from consuming all available RAM, especially when dealing with large datasets.
  sqlCopy

  PRAGMA memory_limit='8GB'; -- Set to 8 Gigabytes
  

• Thread Count: Control the number of threads DuckDB uses for parallel processing.
  sqlCopy

  PRAGMA threads=4; -- Use 4 threads
  

• External Access: For httpfs and iceberg to access remote resources, external access must be enabled.
  sqlCopy

  SET enable_external_access=true;
  

• Columnar Processing: DuckDB is a columnar database. Always select only the columns you need. This is crucial for performance, especially with httpfs as it minimizes network I/O.
• Predicate Pushdown: Ensure 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.
• Data Types: Use appropriate data types. For spatial data, ensure geometries are stored as GEOMETRY types.
• Spatial Indexing (Implicit): While DuckDB doesn't have explicit 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.
• Parquet File Optimization: For httpfs and iceberg, ensure underlying Parquet files are well-optimized:
  • Row Group Size: Aim for row groups of 128MB-1GB.
  • Column Statistics: Ensure statistics (min/max) are present for columns used in WHERE clauses.
  • Compression: Use efficient compression (e.g., Snappy, ZSTD).

Common Gotchas & Production Pitfalls

1. Extension Not Found/Loaded:

   • Issue: Error: Catalog Error: Function with name 'ST_Intersects' does not exist.
   • Cause: The extension was not INSTALLed or LOADed in the current session. INSTALL downloads, LOAD activates.
   • Fix: Run INSTALL <extension_name>; and LOAD <extension_name>; at the beginning of your session. Ensure internet connectivity during INSTALL.

2. HTTPFS Credentials and Permissions:

   • Issue: Error: IO Error: S3 Error [AWS_ERROR_S3_ACCESS_DENIED] or [AWS_ERROR_S3_INVALID_ACCESS_KEY_ID]
   • Cause: Incorrect S3/GCS credentials, missing SET commands for s3_access_key_id, s3_secret_access_key, s3_region, or insufficient IAM permissions on the bucket/object.
   • Fix: Verify credentials. Ensure the IAM role/user has 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.

3. Network Latency and Egress Costs:

   • Issue: Queries against remote data are slow, or cloud bills are unexpectedly high.
   • Cause: High network latency between DuckDB and remote storage, or excessive data transfer (egress) due to inefficient queries.
   • Fix: Co-locate DuckDB with your data (same cloud region). Optimize queries with column pruning (SELECT specific_cols) and predicate pushdown (WHERE filter_conditions). Monitor egress costs.

4. Memory Exhaustion:

   • Issue: Error: Out of Memory: Failed to allocate X bytes
   • Cause: DuckDB attempts to load too much data into memory for complex operations (e.g., large joins, complex spatial operations, or reading very large Parquet files without sufficient filtering).
   • Fix: Set PRAGMA 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.

5. Data Type Mismatches (Spatial):

   • Issue: Error: Conversion Error: Could not convert string '...' to GEOMETRY.
   • Cause: Input string for ST_GeomFromWKT, `ST_