CREATE CATALOG INTEGRATION (Snowflake Open Catalog)¶
Creates a new catalog integration for Apache Iceberg™ tables that integrate with Snowflake Open Catalog in the account or replaces an existing catalog integration.
- See also:
ALTER CATALOG INTEGRATION , DROP CATALOG INTEGRATION , SHOW CATALOG INTEGRATIONS, DESCRIBE CATALOG INTEGRATION
Syntax¶
CREATE [ OR REPLACE ] CATALOG INTEGRATION [ IF NOT EXISTS ]
<name>
CATALOG_SOURCE = POLARIS
TABLE_FORMAT = ICEBERG
CATALOG_NAMESPACE = '<open_catalog_namespace>'
REST_CONFIG = (
CATALOG_URI = '<open_catalog_account_url>'
WAREHOUSE = '<open_catalog_catalog_name>'
)
REST_AUTHENTICATION = (
TYPE = OAUTH
OAUTH_CLIENT_ID = '<oauth_client_id>'
OAUTH_CLIENT_SECRET = '<oauth_secret>'
OAUTH_ALLOWED_SCOPES = ('<scope 1>', '<scope 2>')
)
ENABLED = { TRUE | FALSE }
[ REFRESH_INTERVAL_SECONDS = <value> ]
[ COMMENT = '<string_literal>' ]
Required parameters¶
name
String that specifies the identifier (name) for the catalog integration; must be unique in your account.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example,
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more information, see Identifier requirements.
CATALOG_SOURCE = POLARIS
Specifies Snowflake Open Catalog as the catalog source.
TABLE_FORMAT = ICEBERG
Specifies Apache Iceberg™ as the table format supplied by the catalog.
CATALOG_NAMESPACE = 'open_catalog_namespace'
Specifies the namespace in Open Catalog. Snowflake uses this namespace for all Iceberg tables that you associated with this catalog integration.
You can override this value by specifying a namespace at the table level when you create a table.
REST_CONFIG = ( ... )
Specifies information about your Open Catalog account and catalog name.
CATALOG_URI = 'https://open_catalog_account_url'
Your Open Catalog account locator URL.
Example values:
https://<orgname>-<my-snowflake-open-catalog-account-name>.snowflakecomputing.com/polaris/api/catalog
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.
https://<account_locator>.<cloud_region_id>.<cloud>.snowflakecomputing.com/polaris/api/catalog
To find your
<account_locator>
,<cloud_region_id>
, and<cloud>
, see Format 2: Account locator in a region.
WAREHOUSE = 'open_catalog_name'
The WAREHOUSE parameter specifies the name of the catalog to use in Open Catalog.
REST_AUTHENTICATION = ( ... )
Specifies authentication details that Snowflake uses to connect to Open Catalog.
TYPE = OAUTH
Specifies OAuth as the authentication type to use.
OAUTH_CLIENT_ID = 'oauth_client_id'
The client ID of the OAuth2 credential associated with your Open Catalog service connection.
OAUTH_CLIENT_SECRET = 'oauth_secret'
The secret of the OAuth2 credential associated with your Open Catalog service connection.
OAUTH_ALLOWED_SCOPES = ( 'scope_1', 'scope_2')
The scope of the OAuth token. Only one scope is included in the Iceberg REST API specification, but catalogs can support more than one scope in their implementation.
ENABLED = {TRUE | FALSE}
Specifies whether the catalog integration is available to use for Iceberg tables.
TRUE
allows users to create new Iceberg tables that reference this integration. Existing Iceberg tables that reference this integration function normally.FALSE
prevents users from creating new Iceberg tables that reference this integration. Existing Iceberg tables that reference this integration cannot access the catalog in the table definition.
Optional parameters¶
REFRESH_INTERVAL_SECONDS = value
Specifies the number of seconds that Snowflake waits between attempts to poll the external Iceberg catalog for metadata updates for automated refresh.
Values: 30 to 86400, inclusive
Default: 30 seconds
COMMENT = 'string_literal'
String (literal) that specifies a comment for the integration.
Default: No value
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
CREATE INTEGRATION |
Account |
Only the ACCOUNTADMIN role has this privilege by default. The privilege can be granted to additional roles as needed. |
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
You can’t modify an existing catalog integration; use a CREATE OR REPLACE CATALOG INTEGRATION statement instead.
You can’t drop or replace a catalog integration if one or more Apache Iceberg™ tables are associated with the catalog integration.
To view the tables that depend on a catalog integration, you can use the SHOW ICEBERG TABLES command and a query using RESULT_SCAN that filters on the
catalog_name
column.Note
The column identifier (
catalog_name
) is case-sensitive. Specify the column identifier exactly as it appears in the SHOW ICEBERG TABLES output.For example:
SHOW ICEBERG TABLES; SELECT * FROM TABLE( RESULT_SCAN( LAST_QUERY_ID() ) ) WHERE "catalog_name" = 'my_catalog_integration_1';
Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
To troubleshooting issues with creating a catalog integration, see You can’t create a catalog integration for Open Catalog.
Examples¶
The following example creates a catalog integration for Open Catalog for a particular namespace in a catalog.
CREATE OR REPLACE CATALOG INTEGRATION open_catalog_int
CATALOG_SOURCE = POLARIS
TABLE_FORMAT = ICEBERG
CATALOG_NAMESPACE = 'my_catalog_namespace'
REST_CONFIG = (
CATALOG_URI = 'https://my_org_name-my_snowflake_open_catalog_account_name.snowflakecomputing.com/polaris/api/catalog'
WAREHOUSE = 'my_catalog_name'
)
REST_AUTHENTICATION = (
TYPE = OAUTH
OAUTH_CLIENT_ID = 'my_client_id'
OAUTH_CLIENT_SECRET = 'my_client_secret'
OAUTH_ALLOWED_SCOPES = ('PRINCIPAL_ROLE:ALL')
)
ENABLED = TRUE;