19 October 2018        Add to Favorites   Report

CREATE SCHEMA command syntax in Snowflake

The schema is a logical grouping of tables/views in Snowflake. To create a new schema, navigate to the correct database and execute the below command,

Example:

create schema myschema;

Syntax:

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


NOTE:

  • 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
  • DATA_RETENTION_TIME_IN_DAYS : 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

Once the schema is created you can use the below command to verify if the schema is created properly

show schemas;

To start creating tables or views in the new schema simply use the "USE SCHEMA" command to navigate to the new schema. Creating a new schema also sets it as the active schema.

USE SCHEMA myschema;

Just to be sure, use the below command:

select current_warehouse(), current_database(), current_schema();

We are all set to create tables in the new schema now!


Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied