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)
CREATE OR REPLACETABLE "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.
CREATE OR REPLACETABLE "MySchema"."MyTable" ( "COL1" NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
CONSTRAINT"PK"PRIMARY KEY ("COL1") );
CREATE OR REPLACETABLE "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.
CREATE OR REPLACESEQUENCEMySchema.BaseTable_COL0 INCREMENT BY1STARTWITH621COMMENT ='FOR TABLE-COLUMN "MySchema.BaseTable".COL0';CREATE OR REPLACETABLE "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.
CREATE OR REPLACETABLET ( Col1 BINARY/*** MSC-WARNING - MSCEWI1036 - BLOB DATA TYPE CONVERTED TO BINARY ***/, Col5 VARCHAR/*** MSC-WARNING - MSCEWI1036 - CLOB DATA TYPE CONVERTED TO VARCHAR ***/ );
CREATE OR REPLACETABLE "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
CREATETABLETABLE1("COL1"VARCHAR(50) DEFAULT CURRENT_TIMESTAMP);CREATETABLETABLE1( COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME, COL2 TIMESTAMP(6) WITHLOCALTIMEZONEDEFAULT'1900-01-01 12:00:00', COL3 TIMESTAMP(6) WITH TIME ZONEDEFAULT'1900-01-01 12:00:00', COL4 TIMESTAMP(6) WITHOUT TIME ZONEDEFAULT'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
CREATE OR REPLACETABLETABLE1 ("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 REPLACETABLETABLE1 ( 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 ZONEDEFAULT'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
Some properties on the tables may be adapted to or commented out because the behavior in Snowflake is different.