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:

Redshift

IN -> Redshift_01.sql

CREATE DATABASE database_name;

Output Code:

Snowflake

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:

Redshift

IN -> Redshift_02.sql

CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;

Output Code:

Snowflake

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:

Redshift

IN -> Redshift_03.sql

CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;

Output Code:

Snowflake

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:

Redshift

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:

Snowflake

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

Redshift

IN -> Redshift_05.sql

CREATE DATABASE database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid';

Output Code

Snowflake

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

Redshift

IN -> Redshift_06.sql

CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';

Output Code

Snowflake

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

Redshift

IN -> Redshift_07.sql

CREATE DATABASE database_Isolation
ISOLATION LEVEL SNAPSHOT;

Output Code

Snowflake

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

• SSC-EWI-0073: Pending Functional Equivalence Review