> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wherobots.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect DuckDB to Wherobots catalogs

> Read tables in Wherobots-managed catalogs from DuckDB through the Iceberg REST catalog API.

[DuckDB](https://duckdb.org/) is a single-node analytical database that runs inside your own process. Its [`iceberg` extension](https://duckdb.org/docs/stable/core_extensions/iceberg/overview) 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](#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](https://docs.wherobots.com/get-started/initial-storage/data-federation-overview.md).

For the scope of this workflow and compatibility considerations, see [Connect external engines to Wherobots catalogs](https://docs.wherobots.com/data-engineering/catalogs/external-engines.md).

## 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**](https://cloud.wherobots.com/settings), and a Wherobots [API key](https://docs.wherobots.com/get-started/wherobots-cloud/api-keys.md) 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

**1. Install DuckDB**

```bash theme={"system"}
pip install duckdb
```

**2. 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`:

```bash theme={"system"}
curl -H "X-API-Key: YOUR_API_KEY_HERE" https://api.cloud.wherobots.com/catalogs
```

**3. Set your credentials**

```bash theme={"system"}
export WHEROBOTS_USER_ID="YOUR_WHEROBOTS_USER_ID"
export WHEROBOTS_API_KEY="YOUR_API_KEY_HERE"
```

**4. 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.

```python theme={"system"}
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](#troubleshooting).

**5. Attach the catalog**

```python theme={"system"}
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.

**6. Verify the connection**

```python theme={"system"}
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:

```text theme={"system"}
┌─────────────────────┐
│        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:

```python theme={"system"}
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()
```

```text theme={"system"}
┌─────────────────────────────────────────────────────────┬──────────────────────────────────────────────┐
│                          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](#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:

```python theme={"system"}
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())
```

```text theme={"system"}
[(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`:

```python theme={"system"}
con.sql("""
    SELECT column_name, column_type
    FROM (DESCRIBE wherobots.sentinel2.l2a_source_items)
    WHERE column_name = 'geometry'
""").show()
```

```text theme={"system"}
┌─────────────┬───────────────────────┐
│ 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

> [accordion] 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](#connect-duckdb-to-a-wherobots-catalog) shows.

> [accordion] 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.

> [accordion] 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:
>
> ```python theme={"system"}
> 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.

> [accordion] 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)`.

> [accordion] 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`.

> [accordion] 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`:
>
> ```python theme={"system"}
> 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()
> ```
>
> ```text theme={"system"}
> ┌────────────┐
> │ intersects │
> │  boolean   │
> ├────────────┤
> │ true       │
> └────────────┘
> ```

## Next steps

> [card] [Wherobots open data](https://docs.wherobots.com/tutorials/spatial-catalog/introduction.md)
>
> Find the datasets in the `wherobots_open_data` catalog.

> [card] [Geometry tables](https://docs.wherobots.com/data-engineering/iceberg-tables/geometry-tables.md)
>
> Create tables with geometry columns that declare a CRS.

> [card] [DuckDB iceberg extension](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs)
>
> Learn the `ATTACH` options for Iceberg REST catalogs.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.