Connect DuckDB to Wherobots catalogs

DuckDB is a single-node analytical database that runs inside your own process. Its iceberg extension connects to the Iceberg REST endpoint of a Wherobots-managed catalog, so you can read tables without starting a Wherobots runtime. Queries run on your machine, and DuckDB reads table files from cloud storage over the network. Wherobots issues the storage credentials for each table, so you don’t configure storage keys. The integration supports:

Reading Iceberg v2 and v3 tables, including tables with deleted rows.
Reading geometry columns, with or without a declared coordinate reference system (CRS). See Work with geometry.

The integration doesn’t support:

Changing existing tables. INSERT and DELETE fail with Forbidden_403 when DuckDB commits. Write to these tables from WherobotsDB instead.
Raster columns. Selecting a raster column, including with SELECT *, fails with Mismatch Type Error. Name the other columns in your query, or query raster data in WherobotsDB.
Foreign catalogs, such as AWS Glue or Databricks Unity Catalog. ATTACH fails with Unauthorized_401. To query them, connect WherobotsDB to external catalogs.

For the scope of this workflow and compatibility considerations, see Connect external engines to Wherobots catalogs.

Before you start

Ensure that you have the following:

Python with the duckdb package. The samples on this page ran on DuckDB 2.0.0.
Your Wherobots User ID, shown in Settings > Details, and a Wherobots API key that belongs to that user.
Access to the Wherobots-managed catalog you want to read. The samples on this page use the wherobots_open_data catalog.

Connect DuckDB to a Wherobots catalog

Install DuckDB

pip install duckdb

Find your catalog ID

List the catalogs you can access with your API key. Note the id of the catalog you want to read. Its type must be MANAGED:

curl -H "X-API-Key: YOUR_API_KEY_HERE" https://api.cloud.wherobots.com/catalogs

Set your credentials

export WHEROBOTS_USER_ID="YOUR_WHEROBOTS_USER_ID"
export WHEROBOTS_API_KEY="YOUR_API_KEY_HERE"

Request an access token

DuckDB’s built-in OAuth login fails against this endpoint, so request the token yourself and pass it to DuckDB. Replace YOUR_CATALOG_ID with the ID from the earlier step.

import json
import os
import urllib.parse
import urllib.request

CATALOG_ID = "YOUR_CATALOG_ID"
ENDPOINT = f"https://iceberg.cloud.wherobots.com/iceberg/catalogs/{CATALOG_ID}"


def request_token():
    request = urllib.request.Request(
        f"{ENDPOINT}/v1/oauth/tokens",
        data=urllib.parse.urlencode({
            "grant_type": "client_credentials",
            "client_id": os.environ["WHEROBOTS_USER_ID"],
            "client_secret": os.environ["WHEROBOTS_API_KEY"],
            "scope": "catalog",
        }).encode(),
    )
    with urllib.request.urlopen(request) as response:
        return json.load(response)["access_token"]

The token expires after one hour. To continue after that, see Troubleshooting.

Attach the catalog

import duckdb

con = duckdb.connect()
con.execute("INSTALL iceberg; LOAD iceberg; INSTALL spatial; LOAD spatial;")
con.execute(f"CREATE SECRET wherobots_token (TYPE ICEBERG, TOKEN '{request_token()}')")
con.execute(f"""
    ATTACH 'wherobots_open_data' AS wherobots (
        TYPE ICEBERG,
        ENDPOINT '{ENDPOINT}',
        SECRET wherobots_token,
        DEFAULT_REGION 'us-west-2'
    )
""")

Set DEFAULT_REGION to the AWS region of the catalog’s storage. The wherobots_open_data catalog is in us-west-2. DuckDB uses this region when the storage credentials from Wherobots don’t include one.

Verify the connection

con.sql("""
    SELECT name
    FROM (SHOW ALL TABLES)
    WHERE database = 'wherobots' AND schema = 'overture_maps_foundation'
    ORDER BY name
    LIMIT 3
""").show()

The output lists tables in the overture_maps_foundation namespace, also known as a database:

┌─────────────────────┐
│        name         │
│       varchar       │
├─────────────────────┤
│ addresses_address   │
│ base_bathymetry     │
│ base_infrastructure │
└─────────────────────┘

Query a table

Refer to a table as <attached name>.<namespace>.<table>. This sample finds libraries in a bounding box around Boston:

con.sql("""
    SELECT
        names['primary'] AS name,
        ST_AsText(geometry) AS geometry
    FROM wherobots.overture_maps_foundation.places_place
    WHERE contains(lower(names['primary']), 'library')
      AND ST_Intersects(geometry, ST_MakeEnvelope(-71.19, 42.22, -70.92, 42.40))
    ORDER BY name
    LIMIT 5
""").show()

┌─────────────────────────────────────────────────────────┬──────────────────────────────────────────────┐
│                          name                           │                   geometry                   │
│                         varchar                         │                   varchar                    │
├─────────────────────────────────────────────────────────┼──────────────────────────────────────────────┤
│ Academic Library                                        │ POINT (-71.095681 42.348524)                 │
│ African Studies Library                                 │ POINT (-71.10827048 42.35052328)             │
│ Albert Alphin Library                                   │ POINT (-71.0903056363387 42.346227142268916) │
│ Annenberg Library at Pine Manor College                 │ POINT (-71.15659546 42.32076095)             │
│ Archbishop Iakovos Library and Learning Resource Center │ POINT (-71.12908543 42.31708149)             │
└─────────────────────────────────────────────────────────┴──────────────────────────────────────────────┘

Filter to read fewer files

DuckDB uses filters on plain columns and on GEOMETRY columns to skip data files, so the ST_Intersects filter in Query a table also reduces the files DuckDB reads. DuckDB doesn’t skip files for a filter on a column decoded from BLOB. On those tables, also filter on a plain column if one narrows the rows. To check how many files a query reads, run it with EXPLAIN ANALYZE and find Total Files Read in the plan. These counts come from test runs:

Filter	Files read
`ST_Intersects` on a `GEOMETRY(4326)` column, in a table with 32 files	8
`ST_Intersects(ST_GeomFromWKB(geom), ...)` on a column with no declared CRS, in a table with 4 files	4

Save query results

COPY writes a result to a local file:

con.execute("""
    COPY (
        SELECT names['primary'] AS name, geometry
        FROM wherobots.overture_maps_foundation.places_place
        WHERE contains(lower(names['primary']), 'library')
          AND ST_Intersects(geometry, ST_MakeEnvelope(-71.19, 42.22, -70.92, 42.40))
    ) TO 'boston_libraries.parquet' (FORMAT parquet)
""")
print(con.sql("SELECT count(*) FROM 'boston_libraries.parquet'").fetchall())

[(189,)]

Work with geometry

The DuckDB column type depends on what the column declares. Run SHOW CREATE TABLE in WherobotsDB to see the declaration:

Column in `SHOW CREATE TABLE`	Column type in DuckDB	How to use it
`GEOMETRY(4326)`	`GEOMETRY('OGC:CRS84')`	Use it directly with DuckDB spatial functions.
`GEOMETRY(3857)`	`GEOMETRY('EPSG:3857')`	Use it directly with DuckDB spatial functions.
`GEOMETRY`, with no declared CRS	`BLOB`	Decode it with `ST_GeomFromWKB(geom)`. The result has no CRS.
`GEOMETRY`, with no declared CRS, stored as WKT or GeoJSON text	`VARCHAR`	Decode WKT with `ST_GeomFromText(geom)` and GeoJSON with `ST_GeomFromGeoJSON(geom)`. The result has no CRS.

Some tables have no declared CRS, such as the us_census.tiger_* tables in wherobots_open_data, and tables you created without one. To see the type in DuckDB, run DESCRIBE:

con.sql("""
    SELECT column_name, column_type
    FROM (DESCRIBE wherobots.sentinel2.l2a_source_items)
    WHERE column_name = 'geometry'
""").show()

┌─────────────┬───────────────────────┐
│ column_name │      column_type      │
│   varchar   │        varchar        │
├─────────────┼───────────────────────┤
│ geometry    │ GEOMETRY('OGC:CRS84') │
└─────────────┴───────────────────────┘

To read the CRS of a GEOMETRY value, use ST_CRS(geom). DuckDB has no ST_SRID function, and a value decoded from BLOB or VARCHAR has no CRS. To read the SRID that a column with no declared CRS stores, run ST_SRID(geom) in WherobotsDB. For example, the values in us_census.tiger_county have SRID 4269.

Troubleshooting

OAuth2 token request failed (BadRequest_400): invalid_request

You created the secret with CLIENT_ID and CLIENT_SECRET. Create it with TOKEN instead, as Connect DuckDB to a Wherobots catalog shows.

No region was provided via the vended credentials

ATTACH is missing the DEFAULT_REGION option. Add it with the AWS region of the catalog’s storage.

Unauthorized_401, or an error that ends in message Unauthorized

ATTACH fails with Unauthorized_401 when the token is invalid, when you pass the API key as TOKEN, or when the catalog is a foreign catalog. After a token expires, queries in an open session fail with an error that ends in "message":"Unauthorized".To continue with a managed catalog, request a new token and attach again:

con.execute("DETACH IF EXISTS wherobots")
con.execute(f"CREATE OR REPLACE SECRET wherobots_token (TYPE ICEBERG, TOKEN '{request_token()}')")
con.execute(f"""
    ATTACH 'wherobots_open_data' AS wherobots (
        TYPE ICEBERG,
        ENDPOINT '{ENDPOINT}',
        SECRET wherobots_token,
        DEFAULT_REGION 'us-west-2'
    )
""")

If the token request itself returns invalid_client, the User ID and API key don’t match. Check that the key belongs to that user.

No function matches the given name and argument types 'ST_Intersects(BLOB, GEOMETRY)'

The column has no declared CRS, so DuckDB reads it as BLOB. Decode it first with ST_GeomFromWKB(geom).

No function matches the given name and argument types 'st_geomfromwkb(GEOMETRY('OGC:CRS84'))'

The column declares a CRS, so DuckDB already reads it as GEOMETRY. Use the column directly, without ST_GeomFromWKB.

Cannot call function ST_Intersects with geometries of different coordinate reference systems

The CRS of your geometry doesn’t match the column. The fix depends on the coordinates in your geometry.If the coordinates are already in the column’s system, change only the label. A GEOMETRY(4326) column has the CRS OGC:CRS84, which DuckDB treats as different from EPSG:4326. Create the geometry with no CRS, as ST_MakeEnvelope(-71.19, 42.22, -70.92, 42.40) does, or set the matching CRS with ST_SetCRS(geometry, 'OGC:CRS84').If the coordinates are in another system, such as meters in EPSG:3857, convert them with ST_Transform. Changing the label leaves the coordinates as they are, so the query returns wrong results with no error. DuckDB 2.0 requires you to set the axis order before you call ST_Transform:

con.execute("SET geometry_always_xy = true")
con.sql("""
    SELECT ST_Intersects(
        ST_SetCRS(ST_Point(-71.06, 42.36), 'OGC:CRS84'),
        ST_Transform(
            ST_SetCRS(
                ST_MakeEnvelope(-7920000, 5210000, -7900000, 5220000),
                'EPSG:3857'
            ),
            'OGC:CRS84'
        )
    ) AS intersects
""").show()

┌────────────┐
│ intersects │
│  boolean   │
├────────────┤
│ true       │
└────────────┘

Next steps

Wherobots open data

Find the datasets in the wherobots_open_data catalog.

Geometry tables

Create tables with geometry columns that declare a CRS.

DuckDB iceberg extension

Learn the ATTACH options for Iceberg REST catalogs.

Start here

Havasu data catalogs

Vector data

Raster data

Iceberg tables

Maps and visualization

Connect DuckDB to Wherobots catalogs

Before you start

Connect DuckDB to a Wherobots catalog

Query a table

Filter to read fewer files

Save query results

Work with geometry

Troubleshooting

Next steps

Wherobots open data

Geometry tables

DuckDB iceberg extension

​Before you start

​Connect DuckDB to a Wherobots catalog

​Query a table

​Filter to read fewer files

​Save query results

​Work with geometry

​Troubleshooting

​Next steps

Wherobots open data

Geometry tables

DuckDB iceberg extension

Before you start

Connect DuckDB to a Wherobots catalog

Query a table

Filter to read fewer files

Save query results

Work with geometry

Troubleshooting

Next steps