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

# Query Wherobots Catalogs from Snowflake

> Connect Snowflake to a Wherobots catalog through an Iceberg REST catalog integration to read and write Wherobots tables, including Iceberg v3 geometry columns, from Snowflake.

Tables in Wherobots Managed catalogs are [Apache Iceberg](https://iceberg.apache.org/) tables, and each
Managed catalog exposes an Iceberg REST endpoint. You can connect Snowflake to that endpoint with a
[catalog integration](https://docs.snowflake.com/en/user-guide/tables-iceberg-configure-catalog-integration-rest)
and query or write Wherobots tables from Snowflake, without copying data.

Wherobots issues Snowflake short-lived storage credentials for each table it accesses, so you don't
need to configure an external volume or share storage keys. Snowflake acts as the Wherobots user whose
API key you configure, with that user's permissions on the catalog.

The integration supports:

* Reading Iceberg v2 and v3 tables.
* Creating tables and inserting rows through a catalog-linked database.
* Iceberg v3 `geometry` columns, which Snowflake maps to its native `GEOMETRY` type.

> [!NOTE]
>
> Raster columns aren't supported. Raster is a Wherobots-specific type that the Iceberg specification doesn't
> define, so Snowflake can't interpret raster values. Query raster data in Wherobots instead.

## Before you start

Ensure that you have the following:

> **Wherobots Requirements**
>
> * A Wherobots [API key](https://docs.wherobots.com/get-started/wherobots-cloud/api-keys.md) that you created for your own user, not one
>   created for a [service principal](https://docs.wherobots.com/get-started/wherobots-cloud/service-principals.md).
> * Your Wherobots **User ID**, shown in [**Settings > Details**](https://cloud.wherobots.com/settings).
>   It must belong to the same user as the API key.
> * Access to the Managed catalog you want to connect. Foreign catalogs, such as
>   [Databricks Unity Catalog](https://docs.wherobots.com/get-started/initial-storage/connect-to-unity-catalog.md) catalogs, aren't supported.

> **Snowflake Requirements**
>
> * A role with the `CREATE INTEGRATION` and `CREATE DATABASE` privileges on the account.
>   By default, only `ACCOUNTADMIN` can grant `CREATE INTEGRATION`.
> * `USAGE` on a warehouse.

## Connect Snowflake to a Wherobots catalog

**1. Find your catalog ID**

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

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

**2. Create the catalog integration**

In Snowflake, run the following statement. Replace the placeholders with your catalog ID, your
Wherobots User ID, your API key, and the catalog database (namespace) you want Snowflake to use by default.

```sql theme={"system"}
CREATE CATALOG INTEGRATION wherobots_catalog
  CATALOG_SOURCE = ICEBERG_REST
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE = 'YOUR_DATABASE'
  REST_CONFIG = (
    CATALOG_URI = 'https://iceberg.cloud.wherobots.com/iceberg/catalogs/YOUR_CATALOG_ID'
    CATALOG_API_TYPE = PUBLIC
    ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
  )
  REST_AUTHENTICATION = (
    TYPE = OAUTH
    OAUTH_TOKEN_URI = 'https://iceberg.cloud.wherobots.com/iceberg/catalogs/YOUR_CATALOG_ID/v1/oauth/tokens'
    OAUTH_CLIENT_ID = 'YOUR_WHEROBOTS_USER_ID'
    OAUTH_CLIENT_SECRET = 'YOUR_API_KEY_HERE'
    OAUTH_ALLOWED_SCOPES = ('catalog')
  )
  ENABLED = TRUE;
```

> [!NOTE]
>
> `OAUTH_CLIENT_ID` is your Wherobots **User ID**, and `OAUTH_CLIENT_SECRET` is an API key that belongs to
> that same user. Snowflake exchanges them for a short-lived access token on every connection.

**3. Verify the connection**

```sql theme={"system"}
SELECT SYSTEM$VERIFY_CATALOG_INTEGRATION('wherobots_catalog');
SELECT SYSTEM$LIST_NAMESPACES_FROM_CATALOG('wherobots_catalog');
SELECT SYSTEM$LIST_ICEBERG_TABLES_FROM_CATALOG('wherobots_catalog', 'YOUR_DATABASE');
```

The first statement returns `"success": true`, and the others list the databases and tables in your
Wherobots catalog.

## Read a Wherobots table

To read individual tables, register each one in a Snowflake database and schema of your choice:

```sql theme={"system"}
CREATE ICEBERG TABLE my_table
  CATALOG = 'wherobots_catalog'
  CATALOG_NAMESPACE = 'YOUR_DATABASE'
  CATALOG_TABLE_NAME = 'your_table';

SELECT * FROM my_table LIMIT 10;
```

To pick up changes that Wherobots or other engines make to the table later, refresh it:

```sql theme={"system"}
ALTER ICEBERG TABLE my_table REFRESH;
```

## Read and write through a catalog-linked database

A catalog-linked database mirrors one or more Wherobots databases in Snowflake. Tables are discovered
automatically, and you can create tables and insert rows that Wherobots can read back.

```sql theme={"system"}
CREATE DATABASE wherobots_linked
  LINKED_CATALOG = (
    CATALOG = 'wherobots_catalog',
    ALLOWED_NAMESPACES = ('YOUR_DATABASE')
  );

SELECT SYSTEM$CATALOG_LINK_STATUS('wherobots_linked');
SHOW SCHEMAS IN DATABASE wherobots_linked;
```

The link keeps syncing in the background, so `executionState` stays `RUNNING`. The first sync has succeeded
when `lastLinkAttemptStartTime` shows a timestamp, `failureDetails` is empty, and `SHOW SCHEMAS` lists your
Wherobots database. If `failureDetails` isn't empty, fix the reported error before continuing.

Wherobots database and table names are lowercase, so put them in double quotes:

```sql theme={"system"}
SELECT * FROM wherobots_linked."YOUR_DATABASE"."your_table" LIMIT 10;

CREATE ICEBERG TABLE wherobots_linked."YOUR_DATABASE"."from_snowflake" (
  id INT,
  name STRING
) ICEBERG_VERSION = 3;

INSERT INTO wherobots_linked."YOUR_DATABASE"."from_snowflake" VALUES (1, 'hello from Snowflake');
```

The new table appears in your Wherobots catalog and can be queried from Wherobots like any other table.

## Work with geometry

Wherobots stores a geometry column in one of two ways, depending on how the column was created. Run
`DESCRIBE TABLE` in Snowflake to see which one a column uses:

| Wherobots geometry column | Column type in Snowflake | How to use it |
| - | - | - |
| Created with a CRS, for example `GEOMETRY(4326)` (Iceberg v3 tables only) | `GEOMETRY(<SRID>)` | Use it directly with Snowflake geospatial functions, for example `ST_ASWKT(geom)`. |
| Created without a CRS (Iceberg v2 tables, and older Iceberg v3 tables) | `BINARY` | Convert it with `TO_GEOMETRY(geom)`. See the note below about the SRID. |

```sql theme={"system"}
-- Geometry column created with a CRS
SELECT id, ST_ASWKT(geom), ST_SRID(geom) FROM my_table;

-- Geometry column created without a CRS (EWKB)
SELECT id, ST_ASWKT(TO_GEOMETRY(geom)), ST_SRID(TO_GEOMETRY(geom)) FROM my_legacy_table;
```

To convert a column created without a CRS, see
[Upgrade Existing Geometry Tables](https://docs.wherobots.com/reference/havasu/geometry/upgrade-geometry-tables.md).

> [!NOTE]
>
> Binary geometry columns can be encoded as EWKB or WKB. EWKB includes the SRID, so `TO_GEOMETRY(geom)` keeps it.
> WKB has no SRID, so `TO_GEOMETRY(geom)` returns a geometry with SRID `0`. If `ST_SRID(TO_GEOMETRY(geom))` returns `0`,
> pass the table's SRID as the second argument instead:
>
> ```sql theme={"system"}
> SELECT id, ST_ASWKT(TO_GEOMETRY(geom, 4326)) FROM my_legacy_table;
> ```

To write geometry from Snowflake, create the table with a `GEOMETRY` column and `ICEBERG_VERSION = 3`:

```sql theme={"system"}
CREATE ICEBERG TABLE wherobots_linked."YOUR_DATABASE"."places" (
  id INT,
  geom GEOMETRY
) ICEBERG_VERSION = 3;

INSERT INTO wherobots_linked."YOUR_DATABASE"."places"
  SELECT 1, TO_GEOMETRY('SRID=4326;POINT(-122.33 47.61)');
```

## Troubleshooting

> **Not authorized: invalid\_client**
>
> The User ID and API key in the catalog integration don't match. Check that `OAUTH_CLIENT_ID` is your
> Wherobots User ID, and that `OAUTH_CLIENT_SECRET` is a valid API key that belongs to that user, with no
> extra spaces. Then recreate the integration with `CREATE OR REPLACE CATALOG INTEGRATION`.

> **Schema does not exist or not authorized (catalog-linked database)**
>
> The catalog-linked database hasn't completed a successful sync yet, or the database name isn't quoted.
> Check that `SYSTEM$CATALOG_LINK_STATUS` shows a `lastLinkAttemptStartTime` timestamp and an empty
> `failureDetails`, and put lowercase Wherobots names in double quotes.

> **Catalog integration does not exist or not enabled**
>
> The `CREATE CATALOG INTEGRATION` statement didn't run, or ran under a different role. Run
> `SHOW CATALOG INTEGRATIONS` with the role you use for queries.


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