Create Table

In this section you could find information about TABLES, their syntax and current convertions.

1. Description

In Oracle, the CREATE TABLE statement is used to create one of the following types of tables: a relational table which is the basic structure to hold user data, or an object table which is a table that uses an object type for a column definition. (Oracle documentation)

Oracle syntax

CREATE [ { GLOBAL | PRIVATE } TEMPORARY | SHARDED | DUPLICATED | [ IMMUTABLE ] BLOCKCHAIN 
  | IMMUTABLE  ] 
   TABLE
  [ schema. ] table
  [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ]
  { relational_table | object_table | XMLType_table }
  [ MEMOPTIMIZE FOR READ ]
  [ MEMOPTIMIZE FOR WRITE ]
  [ PARENT [ schema. ] table ] ;

Snowflake Syntax

CREATE [ OR REPLACE ]
    [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE | TRANSIENT } ]
  TABLE [ IF NOT EXISTS ] <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [
        {
          DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [
              {
                ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              }
            ]
            [ { ORDER | NOORDER } ]
        }
      ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
  [ STAGE_FILE_FORMAT = (
     { FORMAT_NAME = '<file_format_name>'
       | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ]
     } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

For more Snowflake information review the following documentation.

2. Sample Source Patterns

2.1. Physical and Table Properties

Oracle

Query

CREATE TABLE "MySchema"."BaseTable"
(
    BaseId NUMBER DEFAULT 10 NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
  COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MyTableSpace"
  PARTITION BY LIST ("BaseId")
 (
    PARTITION "P20211231"  VALUES (20211231) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    ROW STORE COMPRESS ADVANCED LOGGING
    STORAGE(
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "MyTableSpace" 
  )
  PARALLEL;

Snowflake

Query

CREATE OR REPLACE TABLE "MySchema"."BaseTable"
    (
        BaseId NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/ DEFAULT 10 NOT NULL /*** MSC-WARNING - MSCEWI3041 - CONSTRAINT STATES WERE REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
    )
--    --** MSC-WARNING - MSCEWI3024 - TABLE PROPERTIES REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM. **
--    SEGMENT CREATION IMMEDIATE
--      PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
--      COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING LOGGING
--      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
--      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
--      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
--      TABLESPACE "MyTableSpace"
--      PARTITION BY LIST ("BaseId")
--     (
--        PARTITION "P20211231"  VALUES (20211231) SEGMENT CREATION DEFERRED
--        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
--        ROW STORE COMPRESS ADVANCED LOGGING
--        STORAGE(
--        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
--        TABLESPACE "MyTableSpace"
--      )
--      PARALLEL
              ;

Table properties are commented out because are not required after the migration in Snowflake.

2.2. Constraints and Constraint States

The following constraints and constraint states will be commented out

• ENABLE constraint state

• USING INDEX Clause

• CHECK Constraint

Oracle

Query

CREATE TABLE "MySchema"."BaseTable"
(
    BaseId NUMBER DEFAULT 10 NOT NULL ENABLE NOVALIDATE,
    "COL1" NUMBER CHECK( "COL1" IS NOT NULL ),
	  CHECK( "COL1" IS NOT NULL ),
    CONSTRAINT "Constraint1BaseTable" PRIMARY KEY (BaseId)
        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) ENABLE
);

Snowflake

Query

CREATE OR REPLACE TABLE "MySchema"."BaseTable"
    (
        BaseId NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/ DEFAULT 10 NOT NULL /*** MSC-WARNING - MSCEWI3041 - CONSTRAINT STATES WERE REMOVED FROM NOT NULL INLINE CONSTRAINT ***/,
        "COL1" NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/ NOT NULL
--                                                                                                                                     --** MSC-WARNING - MSCEWI1024 - THE FOLLOWING CHECK CONSTRAINT WAS COMMENTED OUT **
--                                                                                                                                     CHECK( "COL1" IS NOT NULL )
                                                                                                                                                                ,
--    --** MSC-WARNING - MSCEWI1035 - CHECK STATEMENT NOT SUPPORTED **
--    	  CHECK( "COL1" IS NOT NULL )
    	                             ,
        CONSTRAINT "Constraint1BaseTable" PRIMARY KEY (BaseId)
--    	  --** MSC-WARNING - MSCEWI3037 - USING INDEX CONSTRAINT STATE REMOVED FROM THE CONSTRAINT **
--            USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
--            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
--            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
--                                                         --** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT STATE ENABLE DISABLE **
--                                                         ENABLE
    );

2.3. Foreign Key

If there is a table with a NUMBER column with no precision nor scale, and another table with a NUMBER(*,0) column that references to the previously mentioned NUMBER column, we will comment out this foreign key.

Oracle

Query

CREATE TABLE "MySchema"."MyTable"
(
    "COL1" NUMBER, 
    CONSTRAINT "PK" PRIMARY KEY ("COL1")
);

Snowflake

Query

CREATE OR REPLACE TABLE "MySchema"."MyTable"
    (
        "COL1" NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
        CONSTRAINT "PK" PRIMARY KEY ("COL1")
    );

2.4. Virtual Column

Oracle

Query

CREATE TABLE "MySchema"."MyTable"
(
    "COL1" NUMBER GENERATED ALWAYS AS (COL1 * COL2) VIRTUAL
);

Snowflake

Query

CREATE OR REPLACE TABLE "MySchema"."MyTable"
    (
    --** MSC-WARNING - MSCEWI3040 - GENERATED ALWAYS STATEMENT REMOVED FROM THE COLUMN DEFINITION **
    --** MSC-WARNING - MSCEWI3040 - VIRTUAL KEYWORD REMOVED FROM THE COLUMN DEFINITION **
        "COL1" NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/ AS (COL1 * COL2)
    );

2.5. Identity Column

For identity columns, a sequence is created and assigned to the column.

Oracle

Query

CREATE TABLE "MySchema"."BaseTable"
(
	"COL0" NUMBER GENERATED BY DEFAULT ON NULL 
		AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 
		INCREMENT BY 1 
		START WITH 621 
		CACHE 20 
		NOORDER  NOCYCLE  NOT NULL ENABLE
);

Snowflake

Query

CREATE OR REPLACE SEQUENCE MySchema.BaseTable_COL0
	INCREMENT BY 1
	START WITH 621
COMMENT = 'FOR TABLE-COLUMN "MySchema.BaseTable".COL0';

CREATE OR REPLACE TABLE "MySchema"."BaseTable" (
	"COL0" NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
	--** MSC-WARNING - MSCEWI1048 - SEQUENCE -  GENERATED BY DEFAULT  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 621 CACHE 20 NOORDER NOCYCLE **
	DEFAULT MySchema.BaseTable_COL0.NEXTVAL NOT NULL /*** MSC-WARNING - MSCEWI3041 - CONSTRAINT STATES WERE REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
);

2.6. CLOB and BLOB column declaration

Columns declared as CLOB or BLOB will be changed to VARCHAR.

Oracle

Query

CREATE TABLE T
(
 Col1 BLOB DEFAULT EMPTY_BLOB(),
Col5 CLOB DEFAULT EMPTY_CLOB()
);

Snowflake

Query

CREATE OR REPLACE TABLE T
 (
   Col1 BINARY /*** MSC-WARNING - MSCEWI1036 - BLOB DATA TYPE CONVERTED TO BINARY ***/,
   Col5 VARCHAR /*** MSC-WARNING - MSCEWI1036 - CLOB DATA TYPE CONVERTED TO VARCHAR ***/
 );

2.7. Constraint Name

Oracle

Query

CREATE TABLE "CustomSchema"."BaseTable"(
 "PROPERTY" VARCHAR2(64) CONSTRAINT "MICROSOFT_NN_PROPERTY" NOT NULL ENABLE
  );

Snowflake

Query

CREATE OR REPLACE TABLE "CustomSchema"."BaseTable" (
  "PROPERTY" VARCHAR(64) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/ NOT NULL /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONSTRAINT "MICROSOFT_NN_PROPERTY" ***/ /*** MSC-WARNING - MSCEWI3041 - CONSTRAINT STATES WERE REMOVED FROM NOT NULL INLINE CONSTRAINT ***/
   );

2.8. Default columns with times

The columns declared as Date types will be cast to match with the specific date type.

Oracle

Query

CREATE TABLE TABLE1
(
"COL1" VARCHAR(50) DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE TABLE1
(
 COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
 COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME,
 COL2 TIMESTAMP(6) WITH LOCAL TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL3 TIMESTAMP(6) WITH TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL4 TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT '1900-01-01 12:00:00',
 COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM')
 );

Snowflake

Query

CREATE OR REPLACE TABLE TABLE1
 (
 "COL1" VARCHAR(50) DEFAULT TO_VARCHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH:MI:SS')
 );

 --** MSC-WARNING - MSCEWI1039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR TABLE1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
 CREATE OR REPLACE TABLE TABLE1
 (
  COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP :: TIMESTAMP(6),
  COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME :: TIMESTAMP(6),
  COL2 TIMESTAMP_LTZ(6) DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP_LTZ(6),
  COL3 TIMESTAMP_TZ(6) DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP_TZ(6),
  COL4 TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT '1900-01-01 12:00:00' :: TIMESTAMP(6) WITHOUT TIME ZONE,
  COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM') :: TIMESTAMP(6)
  );

3. Known Issues

1. Some properties on the tables may be adapted to or commented out because the behavior in Snowflake is different.

4. Related EWIs

MSCEWI1002: Removed next statement, not applicable in SnowFlake.

MSCEWI3041: Enable/disable constraint state removed from not null inline constraint.

MSCEWI3024: Table properties were removed because Snowflake does not require them.

MSCEWI1035: Check statement not supported.

MSCEWI3040: Virtual keyword removed from the column definition.

MSCEWI1036: Data type converted to another data type.