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:
CREATE DATABASE database_name;
Output Code:
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:
CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;
Output Code:
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:
CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;
Output Code:
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:
CREATE DATABASE database_fromARN
FROM ARN 'arn' WITH NO DATA CATALOG SCHEMA IAM_ROLE 'arn:aws:iam::<account-id>:role/<role-name';
Output Code:
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
CREATE DATABASE database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid';
Output Code
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" }}';
Owner Clause
Input Code
CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';
Output Code
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
CREATE DATABASE database_Isolation
ISOLATION LEVEL SNAPSHOT;
Output Code
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" }}';
Related EWIs
SSC-EWI-0073: Pending Functional Equivalence Review
Last updated