Query a table in Snowflake Open Catalog using Snowflake¶

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:

• Amazon S3

• Google Cloud Storage

• Azure Storage

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://my_account.snowflakecomputing.com/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;

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';

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

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;