Translation reference to convert Teradata WITH DEFAULT clause in column definitions to Snowflake
Some parts in the output code are omitted for clarity reasons.
Description
Teradata's WITH DEFAULT clause sets a system-default value to columns that are inserted with no values. This value is typically the equivalent of zero or empty.
Syntax:
WITHDEFAULT
The following table shows Teradata's data types, their corresponding type in Snowflake, and the default value to be set if supported.
Sample Source Patterns
Teradata
IN -> Teradata_01.sql
CREATETABLESAMPLE_TABLE( ID INT,-- Numeric Types big_integer_col BIGINTWITHDEFAULT, byteint_col BYTEINT WITHDEFAULT, decimal_col DECIMAL(10,2) WITHDEFAULT, double_precision_col DOUBLE PRECISIONWITHDEFAULT, float_col FLOATWITHDEFAULT, integer_col INTEGERWITHDEFAULT, number_col NUMBERWITHDEFAULT, numeric_col NUMERIC(10,2) WITHDEFAULT, real_col REALWITHDEFAULT, smallint_col SMALLINTWITHDEFAULT,-- Character Types char_col CHAR(50) WITHDEFAULT, character_col CHARACTER(50) WITHDEFAULT,--clob_col CLOB, char_varying_col CHAR VARYING(100) WITHDEFAULT,--long_varchar_col LONG VARCHAR WITH DEFAULT,--long_varchar_graphic_col LONG VARCHAR CHARACTER SET GRAPHIC WITH DEFAULT, varchar_col VARCHAR(255) WITHDEFAULT,--varchar_graphic_col VARCHAR(255) CHARACTER SET GRAPHIC WITH DEFAULT,-- Date and Time Types date_col DATEWITHDEFAULT, time_col TIMEWITHDEFAULT, time_precision_col TIME(6) WITHDEFAULT, timestamp_col TIMESTAMPWITHDEFAULT, timestamp_precision_col TIMESTAMP(6) WITHDEFAULT, tz_timestamp_col TIMESTAMP WITH TIME ZONEWITHDEFAULT, tz_timestamp_precision_col TIMESTAMP(6) WITH TIME ZONEWITHDEFAULT, interval_col INTERVAL DAY(4) WITHDEFAULT, interval_day_to_hour_col INTERVAL DAY(4) TOHOURWITHDEFAULT, interval_hour_col INTERVAL HOUR(2) WITHDEFAULT, interval_minute_col INTERVAL MINUTE(2) WITHDEFAULT, interval_month_col INTERVAL MONTHWITHDEFAULT, interval_second_col INTERVAL SECOND(2) WITHDEFAULT, interval_year_col INTERVAL YEAR(4) WITHDEFAULT,-- Binary Types-- blob_col BLOB(1000), byte_col BYTE(1000) WITHDEFAULT, varbyte_col VARBYTE(1000) WITHDEFAULT);
Snowflake
OUT -> Teradata_01.sql
CREATETABLESAMPLE_TABLE( ID INT,-- Numeric Types big_integer_col BIGINTDEFAULT0, byteint_col BYTEINT DEFAULT0, decimal_col DECIMAL(10,2) DEFAULT0, double_precision_col DOUBLE PRECISIONDEFAULT0, float_col FLOATDEFAULT0, integer_col INTEGERDEFAULT0, number_col NUMBER(38, 18)-- WITH DEFAULT-- /*** SSC-FDM-0028 - WITH DEFAULT FOR 'NUMBER' NOT SUPPORTED IN SNOWFLAKE ***/ , numeric_col NUMERIC(10,2) DEFAULT0, real_col REALDEFAULT0, smallint_col SMALLINTDEFAULT0,-- Character Types char_col CHAR(50) DEFAULT'', character_col CHARACTER(50)-- WITH DEFAULT-- /*** SSC-FDM-0028 - WITH DEFAULT FOR 'CHARACTER(50)' NOT SUPPORTED IN SNOWFLAKE ***/ ,--clob_col CLOB, char_varying_col CHAR VARYING(100)-- WITH DEFAULT-- /*** SSC-FDM-0028 - WITH DEFAULT FOR 'CHAR VARYING(100)' NOT SUPPORTED IN SNOWFLAKE ***/ ,--long_varchar_col LONG VARCHAR WITH DEFAULT,--long_varchar_graphic_col LONG VARCHAR CHARACTER SET GRAPHIC WITH DEFAULT, varchar_col VARCHAR(255) DEFAULT'',--varchar_graphic_col VARCHAR(255) CHARACTER SET GRAPHIC WITH DEFAULT,-- Date and Time Types date_col DATEDEFAULT CURRENT_DATE, time_col TIMEDEFAULT CURRENT_TIME, time_precision_col TIME(6) DEFAULTCURRENT_TIME(6), timestamp_col TIMESTAMPDEFAULT CURRENT_TIMESTAMP, timestamp_precision_col TIMESTAMP(6) DEFAULTCURRENT_TIMESTAMP(6), tz_timestamp_col TIMESTAMP_TZ DEFAULT LOCALTIMESTAMP, tz_timestamp_precision_col TIMESTAMP_TZ(6) DEFAULT LOCALTIMESTAMP(6), interval_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY(4) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0DAY',
interval_day_to_hour_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY(4) TO HOUR DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0DAY',
interval_hour_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL HOUR(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0HOUR',
interval_minute_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MINUTE(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0MINUTE',
interval_month_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MONTH DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0MONTH',
interval_second_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL SECOND(2) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0SECOND',
interval_year_col VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR(4) DATA TYPE CONVERTED TO VARCHAR ***/!!! DEFAULT '0YEAR',
-- Binary Types-- blob_col BLOB(1000), byte_col BINARY-- WITH DEFAULT-- /*** SSC-FDM-0028 - WITH DEFAULT FOR 'BYTE(1000)' NOT SUPPORTED IN SNOWFLAKE ***/ , varbyte_col BINARY(1000)-- WITH DEFAULT-- /*** SSC-FDM-0028 - WITH DEFAULT FOR 'VARBYTE(1000)' NOT SUPPORTED IN SNOWFLAKE ***/)COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
Known Issues
1. Unsupported types
As shown in the table in the description table, some types are not supported and no default value will be set when transforming the WITH DEFAULT clause.
Related EWIs
SSC-EWI-0036: Data type converted to another data type.