Categories:

Database, Schema, & Share DDL

CREATE DATABASE

Creates a new database in the system.

In addition, this command can be used to:

See also:

ALTER DATABASE , SHOW DATABASES

DESCRIBE SHARE , SHOW SHARES

Syntax

Standard Database

CREATE [ OR REPLACE ] [ TRANSIENT ] DATABASE [ IF NOT EXISTS ] <name>
    [ CLONE <source_db>
          [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]
    [ DATA_RETENTION_TIME_IN_DAYS = <num> ]
    [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
    [ COMMENT = '<string_literal>' ]

Shared Database (from a Share)

CREATE DATABASE <name> FROM SHARE <provider_account>.<share_name>

Secondary Database (Database Replication)

CREATE DATABASE <name>
    AS REPLICA OF <snowflake_region>.<account_name>.<primary_db_name>
    AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }

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.

Secure Data Sharing Parameters

provider_account.share_name

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

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

AS REPLICA OF snowflake_region.account_name.primary_db_name

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 name of the primary database. In addition to the database name, additional segments are required, depending on where the account that stores the primary database is located relative to the account where you are creating its replica. For more information, expand the following table:

Referencing a database in another account, region, etc.

Qualified Database Name

Account Where Replica is Created

database_name

Same account as the primary database.

account.database_name

Same region but a different account from the account that stores the primary database.

snowflake_region.account.database_name

Same region group but a different region from the account that stores the primary database. Specify the Snowflake Region ID for your region where each Snowflake account is hosted.

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, see Understanding & 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 source_db

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

AT | BEFORE ( TIMESTAMP => timestamp | OFFSET => time_difference | 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.

DATA_RETENTION_TIME_IN_DAYS = num

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, see Parameters.

Values:

  • Standard Edition: 0 or 1

  • Enterprise Edition:

    • 0 to 90 for permanent databases

    • 0 or 1 for transient databases

Default:

  • Standard Edition: 1

  • Enterprise Edition (or higher): 1 (unless a different default value was specified at the account level)

Note

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

DEFAULT_DDL_COLLATION = 'collation_specification'

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.

COMMENT = 'string_literal'

Specifies a comment for the database.

Default: No value

Database Replication Parameters

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = TRUE | FALSE

Specifies whether to perform automatic background maintenance of materialized views in the secondary database.

  • TRUE enables automatic background maintenance of materialized views in the secondary database. If Automatic Clustering is enabled for a materialized view in the primary database, then this also enables automatical monitoring and reclustering of the materialized view in the secondary database.

  • FALSE disables automatic background maintenance of materialized views. During a refresh operation, only the materialized view definitions are replicated to the secondary database.

Default: FALSE

General Usage Notes

  • Creating a database automatically sets it as the active/current database for the current session (equivalent to using the USE DATABASE command for the database).

  • If a database with the same name already exists, an error is returned and the database is not created, unless the optional OR REPLACE keyword is specified in the command.

    Important

    Using OR REPLACE is the equivalent of using DROP DATABASE on the existing database and then creating a new database with the same name; however, the dropped database is not permanently removed from the system. Instead, it is retained in Time Travel. This is important because dropped databases in Time Travel contribute to data storage for your account. For more information, see Storage Costs for Time Travel and Fail-safe.

  • Creating a new database automatically creates two schemas in the database:

    • PUBLIC: Default schema for the database.

    • INFORMATION_SCHEMA: Schema which contains views and table functions that can be used for querying metadata about the objects in the database, as well as across all objects in the account.

  • Databases created from shares differ from standard databases in the following ways:

    • They do not have the PUBLIC or INFORMATION_SCHEMA schemas unless these schemas were explicitly granted to the share.

    • They cannot be cloned.

    • Properties, such as TRANSIENT and DATA_RETENTION_TIME_IN_DAYS, do not apply.

  • When a database is active/current, the PUBLIC schema is also active/current by default unless a different schema is used or the PUBLIC schema has been dropped.

Database Replication Usage Notes

  • Database replication uses Snowflake-provided compute resources instead of your own virtual warehouse to copy objects and data. However, the STATEMENT_TIMEOUT_IN_SECONDS session/object parameter still controls how long a statement runs before it is canceled. The default value is 172800 (2 days). Because the initial replication of a primary database can take longer than 2 days to complete (depending on the amount of metadata in the database as well as the amount of data in database objects), we recommend increasing the STATEMENT_TIMEOUT_IN_SECONDS value to 604800 (7 days, the maximum value) for the session in which you run the replication operation.

    Run the following ALTER SESSION statement prior to executing the ALTER DATABASE secondary_db_name REFRESH statement in the same session:

    ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = 604800;

    Note that the STATEMENT_TIMEOUT_IN_SECONDS parameter also applies to the active warehouse in a session. The parameter honors the lower value set at the session or warehouse level. If you have an active warehouse in the current session, set STATEMENT_TIMEOUT_IN_SECONDS to 604800 for this warehouse (using ALTER WAREHOUSE), too.

    For example:

    -- determine the active warehouse in the current session (if any)
SELECT CURRENT_WAREHOUSE();

+---------------------+
| CURRENT_WAREHOUSE() |
|---------------------|
| MY_WH               |
+---------------------+

-- change the STATEMENT_TIMEOUT_IN_SECONDS value for the active warehouse

ALTER WAREHOUSE my_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 604800;

    You can reset the parameter value to the default after the replication operation is completed:

    ALTER WAREHOUSE my_wh UNSET STATEMENT_TIMEOUT_IN_SECONDS;

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 Data Consumers.

Database Replication Examples

The following example creates a replica of the aws_us_west_2.myaccount1.mydb1 primary database in the aws_us_east_1.myaccount2 account, with automatic refreshing of materialized views in the replica enabled. The SQL statement is executed in the same AWS region group (public) but a different region from the account that stores the primary database:

CREATE DATABASE mydb1
  AS REPLICA OF aws_us_west_2.myaccount.mydb1
  AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = TRUE;