CREATE SCHEMA
Grammar Syntax
CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION username ]
[ QUOTA {quota [MB | GB | TB] | UNLIMITED} ] [ schema_element [ ... ]
CREATE SCHEMA AUTHORIZATION username [ QUOTA {quota [MB | GB | TB] | UNLIMITED} ]
[ schema_element [ ... ] ]
For more information please refer to Redshift CREATE SCHEMA
documentation.
Sample Source Patterns
Basic samples
Input Code:
CREATE SCHEMA s1;
CREATE SCHEMA IF NOT EXISTS s2;
CREATE SCHEMA s3
CREATE TABLE t1
(
col1 INT
)
CREATE VIEW v1 AS SELECT * FROM t1;
Output Code:
CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
CREATE SCHEMA IF NOT EXISTS s2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
CREATE SCHEMA IF NOT EXISTS s3
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
CREATE TABLE t1
(
col1 INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
CREATE VIEW v1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
AS SELECT * FROM
t1;
Authorization Clause
Input Code:
CREATE SCHEMA s1 AUTHORIZATION miller;
Output Code:
CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
Please be aware that for this case, the authorization clause is removed from the code since Snowflake schemas are owned by roles, not individual users. For more information please refer to Snowflake GRANT OWNERSHIP
documentation.
Quota Clause
Input Code:
CREATE SCHEMA s1 QUOTA UNLIMITED;
CREATE SCHEMA s2 QUOTA 10 TB;
Output Code:
CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
CREATE SCHEMA IF NOT EXISTS s2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
Create Schema Authorization
In Redshift when the schema name is not specified but the authorization clause is defined, a new schema is created with the owner's name. For this reason this behavior is replicated in Snowflake.
Input Code:
CREATE SCHEMA AUTHORIZATION miller;
Output Code:
CREATE SCHEMA IF NOT EXISTS miller
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "09/23/2024" }}'
;
Related EWIs
There are no known issues.
Last updated