Categories:

Database, Schema, & Share DDL

CREATE SCHEMA

Creates a new schema in the current database.

In addition, this command can be used to clone an existing schema, either at its current state or at a specific time/point in the past (using Time Travel). For more information about cloning a schema, see Cloning Considerations.

See also:

ALTER SCHEMA , SHOW SCHEMAS

Syntax

CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name>
  [ WITH MANAGED ACCESS ]
  [ CLONE <source_schema>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]
  [ DATA_RETENTION_TIME_IN_DAYS= <num> ]
  [ COMMENT = '<string_literal>' ]

Required Parameters

name

Specifies the identifier for the schema; must be unique for the database in which the schema is created.

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 Syntax.

Optional Parameters

TRANSIENT

Specifies a schema as transient. Transient schemas 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 tables created in a transient schema are transient. For more information about transient tables, see CREATE TABLE.

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

WITH MANAGED ACCESS

Specifies a managed schema. Managed access schemas centralize privilege management with the schema owner.

In regular schemas, the owner of an object (i.e. the role that has the OWNERSHIP privilege on the object) can grant further privileges on their objects to other roles. In managed schemas, the schema owner manages all privilege grants, including future grants, on objects in the schema. Object owners retain the OWNERSHIP privileges on the objects; however, only the schema owner can manage privilege grants on the objects.

CLONE source_schema

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

AT | BEFORE ( TIMESTAMP => timestamp | OFFSET => time_difference | STATEMENT => id )

When cloning a schema, the AT | BEFORE clause specifies to use Time Travel to clone the schema 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 schema, as well as specifying the default Time Travel retention time for all tables created in the schema. 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. For more information about table-level retention time, see CREATE TABLE and Understanding & Using Time Travel.

Values:

  • Standard Edition: 0 or 1

  • Enterprise Edition:

    • 0 to 90 for permanent schemas

    • 0 or 1 for transient schemas

Default:

  • Standard Edition: 1

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

Note

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

COMMENT = 'string_literal'

Specifies a comment for the schema.

Default: No value

Usage Notes

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

    Important

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

  • Creating a schema automatically sets the schema as active/current (equivalent to using the USE SCHEMA command for the schema).

  • In a managed access schema, the schema owner manages grants on the contained objects (e.g. tables or views) but has no other privileges (USAGE, SELECT, DROP, etc.) on the objects.

Examples

Create a permanent schema:

CREATE SCHEMA myschema;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |         | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+

Create a transient schema:

CREATE TRANSIENT SCHEMA tschema;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-12-10 09:35:32.326 -0800 | TSCHEMA            | N          | Y          | MYDB          | PUBLIC       |                                                           | TRANSIENT | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+

Create a managed access schema:

CREATE SCHEMA mschema WITH MANAGED ACCESS;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-12-10 09:36:47.738 -0800 | MSCHEMA            | N          | Y          | MYDB          | ROLE1        |                                                           | MANAGED ACCESS | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-12-10 09:35:32.326 -0800 | TSCHEMA            | N          | Y          | MYDB          | PUBLIC       |                                                           | TRANSIENT | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+