CREATE DATABASE (catalog-linked)¶
Creates a new catalog-linked database for Apache Icebergâ„¢ tables.
Syntax¶
CREATE DATABASE <name>
LINKED_CATALOG = ( catalogParams ),
[ EXTERNAL_VOLUME = '<external_vol>' ]
[ COMMENT = '<string_literal>' ]
Where:
catalogParams ::= CATALOG = '<catalog_int>', [ ALLOWED_NAMESPACES = ('<namespace1>', '<namespace2>', ... ) ] [ BLOCKED_NAMESPACES = ('<namespace1>', '<namespace2>', ... ) ] [ NAMESPACE_MODE = { IGNORE_NESTED_NAMESPACE | FLATTEN_NESTED NAMESPACE } ] [ NAMESPACE_FLATTEN_DELIMITER = '<string_literal>' ]
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.
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 descend from 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 descend from them.
If you specify both ALLOWED_NAMESPACES and BLOCKED_NAMESPACES, the BLOCKED_NAMESPACES list takes precedence. For example, if
ns1.ns2
is allowed, butns1
is blocked, then Snowflake won’t syncns1.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 thenamespace3aa
namespace:my_catalog_linked_db |-- namespace3 | |-- namespace3a | |-- namespace3aa | |-- iceberg_table_5
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.
Valid characters: Any characters allowed in Snowflake identifiers.
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.
COMMENT = 'string_literal'
Specifies a comment for the database.
Default: No value
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. |
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¶
Supported only when using a catalog integration for Snowflake Open Catalog or for Iceberg REST.
You can use the ALLOWED_NAMESPACES parameter to limit the scope of automatic table discovery to a particular namespace in your remote catalog. Specifying multiple namespaces isn’t currently supported.
Snowflake doesn’t sync any access control (users or roles) that you have configured in your remote catalog.
Creating other Snowflake objects (such as views) in a catalog-linked database isn’t supported.
Using vended credentials isn’t currently supported for writing to externally managed tables.
For Iceberg tables in a catalog-linked database:
Snowflake doesn’t copy table properties that you have set in the remote catalog (for example, retention policies or buffers).
Automated refresh is enabled by default.
Dropped or renamed tables in the remote catalog aren’t reflected in the catalog-linked database.
Snowflake-specific features such as replication, cloning, sharing (including listings), and data governance aren’t supported.
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:
CREATE DATABASE my_linked_db
LINKED_CATALOG = (
CATALOG = 'my_catalog_int',
NAMESPACE_MODE = FLATTEN_NESTED_NAMESPACE,
NAMESPACE_FLATTEN_DELIMITER = '-'
)
EXTERNAL_VOLUME = 'my_external_vol';
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')
);