Query a table in Snowflake Open Catalog using Snowflake¶

To query a table registered in Snowflake Open Catalog using Snowflake, you can create an Apache Iceberg™ table that uses an external catalog.

The table represents the Iceberg table in Snowflake Open Catalog and provides read-only access.

Prerequisites¶

Before you start, you need the following:

An Iceberg table registered with Open Catalog.
A service connection that Snowflake can use to connect to Open Catalog. You can use an existing service connection that you’ve set up roles and privileges for, or Configure a service connection for Snowflake. If you configure a new service connection, you must also configure access control for it.

Step 1: Create an external volume in Snowflake¶

If you don’t have one already, start by creating an external volume in Snowflake that provides access to the cloud storage location where you store your table data and metadata.

Complete the instructions for your cloud storage service:

Step 2: Create a catalog integration for Open Catalog¶

Next, use the CREATE CATALOG INTEGRATION command to create a catalog integration in Snowflake that uses OAuth to connect to Open Catalog using your service connection credentials.

CREATE OR REPLACE CATALOG INTEGRATION open_catalog_int
  CATALOG_SOURCE = POLARIS
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE= 'myOpenCatalogNamespace'
  REST_CONFIG = (
    CATALOG_URI = 'https://<orgname>-<my-snowflake-open-catalog-account-name>.snowflakecomputing.cn/polaris/api/catalog'
    WAREHOUSE = 'myOpenCatalogName'
  )
  REST_AUTHENTICATION = (
    TYPE = OAUTH
    OAUTH_CLIENT_ID = 'my-client-id'
    OAUTH_CLIENT_SECRET = 'my-client-secret'
    OAUTH_ALLOWED_SCOPES = ( 'PRINCIPAL_ROLE:ALL' )
  )
  ENABLED = TRUE;

Copy

Note

To find your Snowflake organization name (<orgname>), follow the steps in Finding the organization and account name for an account.
To find <my-snowflake-open-catalog-account-name, see Find the account name for a Snowflake Open Catalog account in the Snowflake Open Catalog documentation.

Step 3: Create an externally managed table¶

Create an Iceberg table in Snowflake using the external volume and catalog integration that you previously configured.

For CATALOG_TABLE_NAME, specify the table name as it appears in Open Catalog.

CREATE ICEBERG TABLE open_catalog_iceberg_table
  CATALOG = 'open_catalog_int'
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG_TABLE_NAME = 'my_iceberg_table';

Copy

You can optionally enable automated refreshes of the table metadata by specifying AUTO_REFRESH = TRUE. For more information, see Automatically refresh Apache Iceberg™ tables.

Note

To retrieve a list of tables or namespaces in your remote catalog, you can use the following functions:

Step 4: Query the table using Snowflake¶

You can now use Snowflake to query the table in Open Catalog. You can also join the query results with other Snowflake tables.

SELECT id, date
  FROM open_catalog_iceberg_table
  LIMIT 10;

Copy