CREATE DATABASE (catalog-linked)¶

Creates a new catalog-linked database for Apache Iceberg™ tables that use an external Iceberg REST catalog.

Syntax¶

CREATE DATABASE <name>
  LINKED_CATALOG = ( catalogParams ),
  [ EXTERNAL_VOLUME = '<external_vol>' ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ CATALOG_CASE_SENSITIVITY = { CASE_SENSITIVE | CASE_INSENSITIVE } ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

Copy

Where:

catalogParams ::=
  CATALOG = '<catalog_int>',
  [ ALLOWED_NAMESPACES = ('<namespace1>', '<namespace2>', ... ) ]
  [ BLOCKED_NAMESPACES = ('<namespace1>', '<namespace2>', ... ) ]
  [ ALLOWED_WRITE_OPERATIONS = { NONE | ALL } ]
  [ NAMESPACE_MODE = { IGNORE_NESTED_NAMESPACE | FLATTEN_NESTED_NAMESPACE } ]
  [ NAMESPACE_FLATTEN_DELIMITER = '<string_literal>' ]
  [ SYNC_INTERVAL_SECONDS = <value> ]

Copy

Required parameters¶

name

Specifies the identifier for the catalog-linked database; must be unique for 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.

Optional parameters¶

EXTERNAL_VOLUME = my_external_vol

Specifies an external volume that provides access to the data and metadata for your remote Iceberg tables.

Not required if using vended credentials.

ALLOWED_WRITE_OPERATIONS = { NONE | ALL }

Specifies whether your catalog-linked database is read-only or writable.

NONE: Your catalog-linked database is read-only.

When your catalog-linked database is read only, any operation that you run that requires committing to the catalog fails. For example, DROP ICEBERG TABLE.
ALL: Your catalog-linked database is writable.

Warning

When your catalog-linked database has write permissions enabled, Snowflake propagates table drops to the remote catalog, which removes the table and data from both systems.

Default: ALL

COMMENT = 'string_literal'

Specifies a comment for the database.

Default: No value

CATALOG_CASE_SENSITIVITY = { CASE_SENSITIVE | CASE_INSENSITIVE }

Specifies the case sensitivity that your external Iceberg catalog uses for identifiers.

CASE_SENSITIVE: The external Iceberg catalog uses case-sensitive identifiers. For example, Snowflake Open Catalog is a case-sensitive catalog.
- Snowflake matches identifiers exactly as they appear, including case. Snowflake automatically converts unquoted identifiers to uppercase, but quoted identifiers must match exactly the case in your external catalog.
- However, if the external Iceberg catalog is actually case insensitive, and normalizes to lowercase, you must surround identifiers in double quotes.
These requirements only apply to identifying existing schemas, tables, and table columns.
CASE_INSENSITIVE: The external Iceberg catalog uses case-insensitive identifiers. For example, Unity Catalog and AWS Glue are case-insensitive catalogs.
- If the external Iceberg catalog is case insensitive and you run one of the following commands, you must surround identifiers in double quotes:
  - CREATE ICEBERG TABLE
  - CREATE SCHEMA
  - ALTER ICEBERG TABLE ADD COLUMN
  - ALTER ICEBERG TABLE RENAME COLUMN
- However, if the external Iceberg catalog is actually case sensitive, Snowflake treats unquoted identifiers as case-insensitive and automatically converts unquoted identifiers to uppercase. When you create or query objects, Snowflake matches identifiers regardless of case, as long as they are unquoted.
  
  Using this pattern is discouraged because Snowflake can’t resolve two different identifiers that differ in casing. This pattern only works when no two identifiers are different in casing only.
Except where otherwise noted, these requirements only apply to identifying existing schemas, tables, and table columns.

Default: CASE_INSENSITIVE

For more information on the requirements for identifier resolution, including examples, see Requirements for identifier resolution in a catalog-linked database.

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas.

WITH CONTACT ( purpose = contact [ , purpose = contact ...] ): Associate the new object with one or more contacts.

Catalog parameters (catalogParams)¶

CATALOG = catalog_int

Specifies the name of your catalog integration.

ALLOWED_NAMESPACES = ('namespace1', 'namespace2', ... )

Optionally specifies one or more namespaces in your remote catalog to limit the scope of automatic table discovery. Snowflake syncs the specified namespaces and all namespaces and tables that are nested under them. If a nested namespace is in the ALLOWED_NAMESPACES list but you set the NAMESPACE_MODE parameter to IGNORE_NESTED_NAMESPACE, Snowflake does not sync the nested namespace or any schemas and tables under it.

BLOCKED_NAMESPACES = ('namespace1', 'namespace2', ... )

Optionally specifies one or more namespaces in your remote catalog to block for automatic table discovery.

Snowflake blocks the specified namespaces and all namespaces and tables that are nested under them.

If you specify both ALLOWED_NAMESPACES and BLOCKED_NAMESPACES, the BLOCKED_NAMESPACES list takes precedence. For example, if ns1.ns2 is allowed, but ns1 is blocked, then Snowflake won’t sync ns1.ns2.

NAMESPACE_MODE = { IGNORE_NESTED_NAMESPACE | FLATTEN_NESTED_NAMESPACE }

Specifies how Snowflake handles namespaces for Iceberg tables in the catalog-linked database.

IGNORE_NESTED_NAMESPACE: Snowflake links only tables in the first namespace level for your catalog.
FLATTEN_NESTED_NAMESPACE: Snowflake links tables in all namespace levels for your catalog. For a table in a nested namespace, Snowflake uses the NAMESPACE_FLATTEN_DELIMITER parameter to construct a flattened namespace. With this option, you must set the NAMESPACE_FLATTEN_DELIMITER parameter.

For example, consider a table named iceberg_table_5 in the namespace3aa namespace:
```
my_catalog_linked_db
|-- namespace3
|   |-- namespace3a
|       |-- namespace3aa
|           |-- iceberg_table_5
```
Copy
If you set NAMESPACE_FLATTEN_DELIMITER = "/", you can specify "my_catalog_linked_db"."namespace3/namespace3a/namespace3aa"."iceberg_table_5" to reference the table.

Default: IGNORE_NESTED_NAMESPACE

NAMESPACE_FLATTEN_DELIMITER = 'string_literal'

Required if you set NAMESPACE_MODE = FLATTEN_NESTED_NAMESPACE. Specifies a delimiter, which Snowflake uses to construct flattened namespaces for tables in your catalog.

Important

The character that you choose for a delimiter can’t appear in your remote namespaces. During the autodiscovery process, Snowflake skips any namespace that contains the delimiter and does not create a corresponding schema in your catalog-linked database.

Valid characters: Any characters allowed in Snowflake identifiers.

SYNC_INTERVAL_SECONDS = value

Specifies the time interval in seconds that Snowflake should use for automatically discovering schemas and tables in your remote catalog.

Values: 30 to 86400 (1 day), inclusive

Default: 30 seconds

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE DATABASE	Account	Required to create a new database. Only the SYSADMIN role, or a higher role, has this privilege by default. The privilege can be granted to additional roles as needed.
USAGE	External Volume	Required to reference an existing external volume.
USAGE	Catalog integration	Required to reference an existing catalog integration.

Privilege

Object

Notes

CREATE DATABASE

Account

Required to create a new database.

Only the SYSADMIN role, or a higher role, has this privilege by default. The privilege can be granted to additional roles as needed.

USAGE

External Volume

Required to reference an existing external volume.

USAGE

Catalog integration

Required to reference an existing catalog integration.

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¶

For ALLOWED_NAMESPACES and BLOCKED_NAMESPACES, Snowflake doesn’t store nested namespaces if the set already contains the parent namespace. For example, if you create a database and specify ALLOWED_NAMESPACES = ('ns1', 'ns1.ns2', 'ns1.ns3'), Snowflake only stores ns1 since the other two are automatically included. If you use GET_DDL on the example database, Snowflake returns ALLOWED_NAMESPACES = ('ns1'). The same applies for BLOCKED_NAMESPACES.
For querying tables in a catalog-linked database:
- Snowflake automatically converts unquoted identifiers (table and column names) to uppercase. If your external Iceberg catalog uses case-sensitive identifiers, you must surround table and column names in double quotes.
  
  For more information about object identifiers, see Identifier requirements.
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.

Examples¶

Create a catalog-linked database with flattened, nested namespaces that uses an external volume.

CREATE DATABASE my_linked_db
  LINKED_CATALOG = (
    CATALOG = 'my_catalog_int',
    NAMESPACE_MODE = FLATTEN_NESTED_NAMESPACE,
    NAMESPACE_FLATTEN_DELIMITER = '-'
  )
  EXTERNAL_VOLUME = 'my_external_vol';

Copy

Create a catalog-linked database that uses vended credentials and specifies one allowed namespace:

CREATE DATABASE my_linked_db
  LINKED_CATALOG = (
    CATALOG = 'my_catalog_int_vended_creds',
    ALLOWED_NAMESPACES = ('my_namespace')
  );

Copy