SnowflakeDocumentation Home

CREATE DATABASE

Grammar Syntax 

CREATE DATABASE database_name 
[ { [ WITH ]
    [ OWNER [=] db_owner ]
    [ CONNECTION LIMIT { limit | UNLIMITED } ]
    [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ]
    [ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
  }
  | { [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid }
  | { FROM { { ARN '<arn>' } { WITH DATA CATALOG SCHEMA '<schema>' | WITH NO DATA CATALOG SCHEMA } }
             | { INTEGRATION '<integration_id>'} }
  | { IAM_ROLE  {default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>' } }

For more information please refer to Redshift CREATE DATABASE documentation.

Sample Source Patterns

Basic samples

Input Code:

IN -> Redshift_01.sql
CREATE DATABASE database_name;

Output Code:

OUT -> Redshift_01.sql
CREATE DATABASE IF NOT EXISTS database_name
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/25/2024" }}';

Collate Clause

Input Code:

IN -> Redshift_02.sql
CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;

Output Code:

OUT -> Redshift_02.sql
CREATE DATABASE IF NOT EXISTS database_collate
DEFAULT_DDL_COLLATION='en-ci'
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Connection Limit Clause

Input Code:

IN -> Redshift_03.sql
CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;

Output Code:

OUT -> Redshift_03.sql
CREATE DATABASE IF NOT EXISTS database_connection
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

The connection limit clause is removed since the connection concurrency in snowflake is managed by warehouse. More information here.

From ARN Clause

Input Code:

IN -> Redshift_04.sql
CREATE DATABASE database_fromARN
FROM ARN 'arn' WITH NO DATA CATALOG SCHEMA IAM_ROLE 'arn:aws:iam::<account-id>:role/<role-name';

Output Code:

OUT -> Redshift_04.sql
CREATE DATABASE IF NOT EXISTS database_fromARN
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

This clause is removed since it is used to reference Amazon Resources, not valid in Snowflake.

From Datashare Clause

Input Code

IN -> Redshift_05.sql
CREATE DATABASE database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid';

Output Code

OUT -> Redshift_05.sql
CREATE DATABASE IF NOT EXISTS  database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FromDatashareAttribute' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

The transformation for Datashare is planned to be delivered in the future.

Owner Clause

Input Code

IN -> Redshift_06.sql
CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';

Output Code

OUT -> Redshift_06.sql
CREATE DATABASE IF NOT EXISTS database_Owner
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Please be aware that for this case, the owner clause is removed from the code since Snowflake databases are owned by roles, not individual users. For more information please refer to Snowflake GRANT OWNERSHIP documentation.

Isolation Level Clause

Input Code

IN -> Redshift_07.sql
CREATE DATABASE database_Isolation
ISOLATION LEVEL SNAPSHOT;

Output Code

OUT -> Redshift_07.sql
CREATE DATABASE IF NOT EXISTS database_Isolation
ISOLATION LEVEL SNAPSHOT !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IsolationLevelAttribute' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

The transformation for Isolation Level is planned to be delivered in the future.

Related EWIs

PreviousCREATE SCHEMANextDELETE

Last updated