CREATE DATABASE | Snowflake Documentation (original) (raw)

Creates a new database in the system.

This command supports the following variants:

In addition, this command can be used to:

Tip

In addition to SQL, you can also use other interfaces, such as Snowflake REST APIs, Snowflake Python APIs, and Snowflake CLI. See Alternate interfaces.

See also:

ALTER DATABASE , DESCRIBE DATABASE , DROP DATABASE , SHOW DATABASES , UNDROP DATABASE

DESCRIBE SHARE , SHOW SHARES, CREATE LISTING, CREATE OR ALTER

Syntax

Standard Database

CREATE [ OR REPLACE ] [ TRANSIENT ] DATABASE [ IF NOT EXISTS ] [ CLONE [ { AT | BEFORE } ( { TIMESTAMP => | OFFSET => | STATEMENT => } ) ] [ IGNORE TABLES WITH INSUFFICIENT DATA RETENTION ] [ IGNORE HYBRID TABLES ] ] [ DATA_RETENTION_TIME_IN_DAYS = ] [ MAX_DATA_EXTENSION_TIME_IN_DAYS = ] [ EXTERNAL_VOLUME = ] [ CATALOG = ] [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ] [ DEFAULT_DDL_COLLATION = '' ] [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ] [ COMMENT = '' ] [ CATALOG_SYNC = '' ] [ CATALOG_SYNC_NAMESPACE_MODE = { NEST | FLATTEN } ] [ CATALOG_SYNC_NAMESPACE_FLATTEN_DELIMITER = '' ] [ [ WITH ] TAG ( = '' [ , = '' , ... ] ) ]

Standard Database (from a listing)

CREATE DATABASE FROM LISTING ''

Shared Database (from a Share)

CREATE DATABASE FROM SHARE .

Secondary Database (Database Replication)

CREATE DATABASE AS REPLICA OF . [ DATA_RETENTION_TIME_IN_DAYS = ]

Variant syntax

CREATE OR ALTER DATABASE

Creates a new database if it doesn’t already exist, or transforms an existing database into the database defined in the statement. A CREATE OR ALTER DATABASE statement follows the syntax rules of a CREATE DATABASE statement and has the same limitations as anALTER DATABASE statement.

The following modifications are supported:

For more information, see CREATE OR ALTER DATABASE usage notes.

CREATE OR ALTER [ TRANSIENT ] DATABASE [ DATA_RETENTION_TIME_IN_DAYS = ] [ MAX_DATA_EXTENSION_TIME_IN_DAYS = ] [ EXTERNAL_VOLUME = ] [ CATALOG = ] [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ] [ DEFAULT_DDL_COLLATION = '' ] [ LOG_LEVEL = '' ] [ TRACE_LEVEL = '' ] [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ] [ COMMENT = '' ]

CREATE DATABASE … CLONE

Creates a new database with the same parameter values:

CREATE [ OR REPLACE ] DATABASE [ IF NOT EXISTS ] CLONE [ ... ]

For more details, see CREATE … CLONE.

Required parameters

_name_

Specifies the identifier for the 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 (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

Important

As a best practice for Database Replication and Failover, we recommend giving each secondary database the same name as its primary database. This practice supports referencing fully-qualified objects (i.e. '<db>.<schema>.<object>') by other objects in the same database, such as querying a fully-qualified table name in a view.

If a secondary database has a different name from the primary database, then these object references would break in the secondary database.

Secure Data Sharing parameters

_provideraccount_._sharename_

Specifies the identifier of the share from which to create the database. As documented, the name of the share must be fully-qualified with the name of the account providing the share.

Database replication parameters

Important

This section describes a limited database replication feature that is different from theaccount replication feature. Snowflake strongly recommends using the account replication feature to replicate and failover databases.

AS REPLICA OF _accountidentifier_._primarydbname_

Specifies the identifier for a primary database from which to create a replica (i.e. a secondary database). If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes.

Requires the account identifier and name of the primary database.

_accountidentifier_

Unique identifier of the account that stores the primary database. The preferred identifier is _organizationname_._accountname_. To view the list of accounts enabled for replication in your organization, query SHOW REPLICATION ACCOUNTS.

Though the legacy account locator can also be used as the account identifier, its use is discouraged as it may not work in the future. For more details about using the account locator as an account identifier, see Database Replication Usage Notes.

_primarydbname_

Name of the primary database. As a best practice, we recommend giving each secondary database the same name as its primary database.

Note

As a best practice for Database Replication and Failover, we recommend setting the optional parameterDATA_RETENTION_TIME_IN_DAYS to the same value on the secondary database as on the primary database.

Listing parameters

'_listingglobalname_'

Specifies the global name of the listing from which to create the database, which must meet the following requirements:

You must have the IMPORT LISTING privilege to create a database from a listing. You must have the IMPORT SHARE privilege to create a database from a share.

Optional parameters

TRANSIENT

Specifies a database as transient. Transient databases do not have a Fail-safe period so they do not incur additional storage costs once they leave Time Travel; however, this means they are also not protected by Fail-safe in the event of a data loss. For more information, seeUnderstanding and viewing Fail-safe.

In addition, by definition, all schemas (and consequently all tables) created in a transient database are transient. For more information about transient tables, see CREATE TABLE.

Default: No value (i.e. database is permanent)

CLONE _sourcedb_

Specifies to create a clone of the specified source database. For more details about cloning a database, see CREATE … CLONE.

AT | BEFORE ( TIMESTAMP => _timestamp_ | OFFSET => _timedifference_ | STATEMENT => _id_ )

When cloning a database, the AT | BEFORE clause specifies to use Time Travel to clone the database at or before a specific point in the past. If the specified Time Travel time is at or before the point in time when the database was created, the cloning operation fails with an error.

IGNORE TABLES WITH INSUFFICIENT DATA RETENTION

Ignore tables that no longer have historical data available in Time Travel to clone. If the time in the past specified in the AT | BEFORE clause is beyond the data retention period for any child table in a database or schema, skip the cloning operation for the child table. For more information, seeChild Objects and Data Retention Time.

IGNORE HYBRID TABLES

Ignore hybrid tables, which will not be cloned. Use this option to clone a database that contains hybrid tables. The cloned database includes other objects but skips hybrid tables.

If you don’t use this option and your database contains one or more hybrid tables, the command ignores hybrid tables silently. However, the error handling for databases that contain hybrid tables will change in an upcoming release; therefore, you may want to add this parameter to your commands preemptively.

DATA_RETENTION_TIME_IN_DAYS = _integer_

Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, as well as specifying the default Time Travel retention time for all schemas created in the database. For more details, see Understanding & using Time Travel.

For a detailed description of this object-level parameter, as well as more information about object parameters, seeParameters.

Values:

Default:

Note

A value of 0 effectively disables Time Travel for the database.

MAX_DATA_EXTENSION_TIME_IN_DAYS = _integer_

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for tables in the database to prevent streams on the tables from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

EXTERNAL_VOLUME = _externalvolumename_

Object parameter that specifies the default external volume to use for Apache Iceberg™ tables.

For more information about this parameter, see EXTERNAL_VOLUME.

CATALOG = _catalogintegrationname_

Object parameter that specifies the default catalog integration to use for Apache Iceberg™ tables.

For more information about this parameter, see CATALOG.

REPLACE_INVALID_CHARACTERS = { TRUE | FALSE }

Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�) in query results for anIceberg table. You can only set this parameter for tables that use an external Iceberg catalog.

Default: FALSE

DEFAULT_DDL_COLLATION = '_collationspecification_'

Specifies a default collation specification for all schemas and tables added to the database. The default can be overridden at the schema and individual table level.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

LOG_LEVEL = '_loglevel_'

Specifies the severity level of messages that should be ingested and made available in the active event table. Messages at the specified level (and at more severe levels) are ingested.

For more information about levels, see LOG_LEVEL. For information about setting the log level, seeSetting levels for logging, metrics, and tracing.

TRACE_LEVEL = '_tracelevel_'

Controls how trace events are ingested into the event table.

For information about levels, see TRACE_LEVEL. For information about setting trace level, seeSetting levels for logging, metrics, and tracing.

STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED }

Specifies the storage serialization policy for Apache Iceberg™ tables that use Snowflake as the catalog.

Default: OPTIMIZED

COMMENT = '_stringliteral_'

Specifies a comment for the database.

Default: No value

CATALOG_SYNC = '_snowflakeopencatalogintegrationname_'

Specifies the name of a catalog integration configured for Snowflake Open Catalog. If specified, Snowflake syncs Snowflake-managed Apache Iceberg™ tables in the database with an external catalog in your Snowflake Open Catalog account. For more information about syncing Snowflake-managed Iceberg tables with Open Catalog, see Sync a Snowflake-managed table with Snowflake Open Catalog.

For more information about this parameter, see CATALOG_SYNC.

Default: No value

CATALOG_SYNC_NAMESPACE_MODE = { NEST | FLATTEN }

Specifies the catalog sync namespace mode for Snowflake-managed Iceberg tables in the database that you sync with Snowflake Open Catalog. This property specifies whether Snowflake syncs the table to Open Catalog with one or two parent namespaces. It only applies if you’re setting the CATALOG_SYNC parameter. After you create the database, you can’t alter this property.

Default: NEST

CATALOG_SYNC_NAMESPACE_FLATTEN_DELIMITER = '_stringliteral_'

Specifies a delimiter, which Snowflake inserts in the flattened namespace that results when Snowflake syncs a Snowflake-managed Iceberg table to Snowflake Open Catalog with one parent namespace. This delimiter property only applies when you set the CATALOG_SYNC_NAMESPACE_MODEproperty to FLATTEN. Snowflake inserts this delimiter to avoid conflicts that could arise from flattening parent namespaces for different tables. After you create the database, you can’t alter this property.

For example, suppose you want to sync the customer.data.table1 and custom.erdata.table1 Snowflake-managed Iceberg tables to the catalog1external catalog in Open Catalog. By setting the CATALOG_SYNC_NAMESPACE_MODE property set to FLATTEN and specifying a hyphen (-) for the delimiter, Snowflake syncs these tables with Open Catalog with the following fully qualified names:

If you set the CATALOG_SYNC_NAMESPACE_MODE property to FLATTEN, a non-empty delimiter value is required. However, if you set theCATALOG_SYNC_NAMESPACE_MODE property to NEST, this delimiter property doesn’t apply and the configured value will be ignored.

Valid characters: 0-9, A-Z, a-z, _, $, -

TAG ( _tagname_ = '_tagvalue_' [ , _tagname_ = '_tagvalue_' , ... ] )

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 for objects and columns.

Access control requirements

A role used to execute this operation must have the followingprivileges 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, catalog integration Required if setting the EXTERNAL_VOLUME or CATALOG object parameters, respectively.
IMPORT LISTING Account Required to create a database from a listing.
IMPORT SHARE Account Required to create a database from a share.
MODIFY LOG LEVEL Account Required to set the LOG_LEVEL for a database.
MODIFY TRACE LEVEL Account Required to set the TRACE_LEVEL for a database.
OWNERSHIP Database Required only when executing a CREATE OR ALTER DATABASE statement for an existing database. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

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 onsecurable objects, see Overview of Access Control.

General usage notes

CREATE OR ALTER DATABASE usage notes

Database replication usage notes

Important

This section describes a limited database replication feature that is different from theaccount replication feature. Snowflake strongly recommends using the account replication feature to replicate and failover databases.

Examples

Create two permanent databases, one with a data retention period of 10 days:

CREATE DATABASE mytestdb;

CREATE DATABASE mytestdb2 DATA_RETENTION_TIME_IN_DAYS = 10;

SHOW DATABASES LIKE 'my%';

+---------------------------------+------------+------------+------------+--------+----------+---------+---------+----------------+ | created_on | name | is_default | is_current | origin | owner | comment | options | retention_time | |---------------------------------+------------+------------+------------+--------+----------+---------+---------+----------------| | Tue, 17 Mar 2016 16:57:04 -0700 | MYTESTDB | N | N | | PUBLIC | | | 1 | | Tue, 17 Mar 2016 17:06:32 -0700 | MYTESTDB2 | N | N | | PUBLIC | | | 10 | +---------------------------------+------------+------------+------------+--------+----------+---------+---------+----------------+

Create a transient database:

CREATE TRANSIENT DATABASE mytransientdb;

SHOW DATABASES LIKE 'my%';

+---------------------------------+---------------+------------+------------+--------+----------+---------+-----------+----------------+ | created_on | name | is_default | is_current | origin | owner | comment | options | retention_time | |---------------------------------+---------------+------------+------------+--------+----------+---------+-----------+----------------| | Tue, 17 Mar 2016 16:57:04 -0700 | MYTESTDB | N | N | | PUBLIC | | | 1 | | Tue, 17 Mar 2016 17:06:32 -0700 | MYTESTDB2 | N | N | | PUBLIC | | | 10 | | Tue, 17 Mar 2015 17:07:51 -0700 | MYTRANSIENTDB | N | N | | PUBLIC | | TRANSIENT | 1 | +---------------------------------+---------------+------------+------------+--------+----------+---------+-----------+----------------+

Create a database from a share provided by account ab67890:

CREATE DATABASE snow_sales FROM SHARE ab67890.sales_s;

For more detailed examples of creating a database from a share, see Consume imported data.

Database replication examples

Important

This section describes a limited database replication feature that is different from theaccount replication feature. Snowflake strongly recommends using the account replication feature to replicate and failover databases.

For an example of creating a replication group to replicate a single database to a target account, seeReplicate a single database.

CREATE OR ALTER DATABASE examples

Create a simple database

Create a database named db1:

CREATE OR ALTER DATABASE db1;

Alter database db1 to set the DATA_RETENTION_TIME_IN_DAYS and DEFAULT_DDL_COLLATION parameters:

CREATE OR ALTER DATABASE db1 DATA_RETENTION_TIME_IN_DAYS = 5 DEFAULT_DDL_COLLATION = 'de';

Unset a parameter previously set on database

The absence of a previously set parameter in the modified database definition results in unsetting it. In the following example, unset the DATA_RETENTION_TIME_IN_DAYS parameter for the database db1 created in the previous example:

CREATE OR ALTER DATABASE db1 DEFAULT_DDL_COLLATION = 'de';

Alternate interfaces