Tables

Translation reference to convert Teradata TABLE statement to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

Teradata's TABLE statement is translated to Snowflake TABLE syntax.

For more information on Teradata TABLEs, check here.

Sample Source Patterns

Simple Create Table

Teradata

IN -> Teradata_01.sql

CREATE TABLE table1, no fallback,
no before journal,
no after journal (
  c1 INTEGER NOT NULL,
	f1 INTEGER NOT NULL,
	p1 INTEGER NOT NULL,
  DATE,
  TIME,
	FOREIGN KEY(f1) REFERENCES WITH CHECK OPTION table2 (d1)
)
UNIQUE PRIMARY INDEX(c1)
PARTITION BY COLUMN(p1);

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE table1 (
	c1 INTEGER NOT NULL,
	f1 INTEGER NOT NULL,
	p1 INTEGER NOT NULL,
	DATE,
	TIME,
	FOREIGN KEY(f1) REFERENCES table2 (d1) ,
	UNIQUE (c1)
)
--** SSC-PRF-0007 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (p1)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Table Kind Clause - SET and MULTISET

Teradata's kind clause determines whether duplicate rows are permitted (MULTISET) or not (SET).

Teradata

IN -> Teradata_02.sql

-- Set semantics
CREATE SET TABLE table1 (
    column1 INTEGER
);

--Multiset semantics
CREATE MULTISET TABLE table2(
    column1 INTEGER
);

Snowflake

OUT -> Teradata_02.sql

-- Set semantics
--** SSC-FDM-TD0024 - SET TABLE FUNCTIONALITY NOT SUPPORTED. TABLE MIGHT HAVE DUPLICATE ROWS **
CREATE OR REPLACE TABLE table1 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

--Multiset semantics
CREATE OR REPLACE TABLE table2 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Volatile and Global Temporary Tables

Teradata's Volatile and Global Temporary tables are used for the temporary storage of data. Their difference lies in that the table definition (DDL) of Global Temporary tables is persisted in the Data Dictionary, while Volatile tables definition is not stored.

Teradata

IN -> Teradata_03.sql

--Global Temporary Table
CREATE MULTISET GLOBAL TEMPORARY TABLE table1 (
    column1 INTEGER
);

--Volatile Table
CREATE MULTISET VOLATILE TABLE table3 (
    column1 INTEGER
);

Snowflake

OUT -> Teradata_03.sql

--Global Temporary Table
--** SSC-FDM-0009 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED. **
CREATE OR REPLACE TABLE table1 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

--Volatile Table
CREATE OR REPLACE TEMPORARY TABLE table3 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

With data and with no data option

Teradata

IN -> Teradata_04.sql

-- With data
CREATE TABLE table1 AS table2 WITH DATA

-- With no data
CREATE TABLE table1 AS table2 WITH NO DATA

Snowflake

OUT -> Teradata_04.sql

-- With data
CREATE OR REPLACE TABLE table1 CLONE table2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

-- With no data
--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR table1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE OR REPLACE TABLE table1 LIKE table2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Snowflake's Reserved & Limited Keywords

Snowflake SQL reserves all ANSI keywords (with the exception of type keywords such as CHAR, DATE, DECIMAL, etc.), as well as some additional keywords (ASC, DESC, MINUS, etc.) that are reserved by other popular databases. Additionally, Snowflake reserves keywords REGEXP and RLIKE (which function like the ANSI reserved keyword LIKE) and SOME (which is a synonym for the ANSI reserved keyword ANY).

For this reason, when SnowConvert converts a column with a reserved name it changes its format to XXX_<name>_XXX. Next, you will find the list of reserved and limited keywords.

Keyword	Comment
A
ACCOUNT	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
ALL	Reserved by ANSI.
ALTER	Reserved by ANSI.
AND	Reserved by ANSI.
ANY	Reserved by ANSI.
AS	Reserved by ANSI.
B
BETWEEN	Reserved by ANSI.
BY	Reserved by ANSI.
C
CASE	Cannot be used as column reference in a scalar expression.
CAST	Cannot be used as column reference in a scalar expression.
CHECK	Reserved by ANSI.
COLUMN	Reserved by ANSI.
CONNECT	Reserved by ANSI.
CONNECTION	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
CONSTRAINT	Cannot be used as a column name in CREATE TABLE DDL.
CREATE	Reserved by ANSI.
CROSS	Cannot be used as table name or alias in a FROM clause.
CURRENT	Reserved by ANSI.
CURRENT_DATE	Cannot be used as column name (reserved by ANSI).
CURRENT_TIME	Cannot be used as column name (reserved by ANSI).
CURRENT_TIMESTAMP	Cannot be used as column name (reserved by ANSI).
CURRENT_USER	Cannot be used as column name (reserved by ANSI).
D
DATABASE	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
DELETE	Reserved by ANSI.
DISTINCT	Reserved by ANSI.
DROP	Reserved by ANSI.
E
ELSE	Reserved by ANSI.
EXISTS	Reserved by ANSI.
F
FALSE	Cannot be used as column reference in a scalar expression.
FOLLOWING	Reserved by ANSI.
FOR	Reserved by ANSI.
FROM	Reserved by ANSI.
FULL	Cannot be used as table name or alias in a FROM clause.
G
GRANT	Reserved by ANSI.
GROUP	Reserved by ANSI.
GSCLUSTER	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
H
HAVING	Reserved by ANSI.
I
ILIKE	Reserved by Snowflake.
IN	Reserved by ANSI.
INCREMENT	Reserved by Snowflake and others.
INNER	Cannot be used as table name or alias in a FROM clause.
INSERT	Reserved by ANSI.
INTERSECT	Reserved by ANSI.
INTO	Reserved by ANSI.
IS	Reserved by ANSI.
ISSUE	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
J
JOIN	Cannot be used as table name or alias in a FROM clause.
L
LATERAL	Cannot be used as table name or alias in a FROM clause.
LEFT	Cannot be used as table name or alias in a FROM clause.
LIKE	Reserved by ANSI.
LOCALTIME	Cannot be used as column name (reserved by ANSI).
LOCALTIMESTAMP	Cannot be used as column name (reserved by ANSI).
M
MINUS	Reserved by Snowflake and others.
N
NATURAL	Cannot be used as table name or alias in a FROM clause.
NOT	Reserved by ANSI.
NULL	Reserved by ANSI.
O
OF	Reserved by ANSI.
ON	Reserved by ANSI.
OR	Reserved by ANSI.
ORDER	Reserved by ANSI.
ORGANIZATION	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
Q
QUALIFY	Reserved by Snowflake.
R
REGEXP	Reserved by Snowflake.
REVOKE	Reserved by ANSI.
RIGHT	Cannot be used as table name or alias in a FROM clause.
RLIKE	Reserved by Snowflake.
ROW	Reserved by ANSI.
ROWS	Reserved by ANSI.
S
SAMPLE	Reserved by ANSI.
SCHEMA	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
SELECT	Reserved by ANSI.
SET	Reserved by ANSI.
SOME	Reserved by Snowflake.
START	Reserved by ANSI.
T
TABLE	Reserved by ANSI.
TABLESAMPLE	Reserved by ANSI.
THEN	Reserved by ANSI.
TO	Reserved by ANSI.
TRIGGER	Reserved by ANSI.
TRUE	Cannot be used as column reference in a scalar expression.
TRY_CAST	Cannot be used as column reference in a scalar expression.
U
UNION	Reserved by ANSI.
UNIQUE	Reserved by ANSI.
UPDATE	Reserved by ANSI.
USING	Cannot be used as table name or alias in a FROM clause.
V
VALUES	Reserved by ANSI.
VIEW	Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).
W
WHEN	Cannot be used as column reference in a scalar expression.
WHENEVER	Reserved by ANSI.
WHERE	Reserved by ANSI.
WITH	Reserved by ANSI.

Keyword

Comment

ACCOUNT

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

ALL

Reserved by ANSI.

ALTER

Reserved by ANSI.

AND

Reserved by ANSI.

ANY

Reserved by ANSI.

BETWEEN

Reserved by ANSI.

CASE

Cannot be used as column reference in a scalar expression.

CAST

Cannot be used as column reference in a scalar expression.

CHECK

Reserved by ANSI.

COLUMN

Reserved by ANSI.

CONNECT

Reserved by ANSI.

CONNECTION

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

CONSTRAINT

Cannot be used as a column name in CREATE TABLE DDL.

CREATE

Reserved by ANSI.

CROSS

Cannot be used as table name or alias in a FROM clause.

CURRENT

Reserved by ANSI.

CURRENT_DATE

Cannot be used as column name (reserved by ANSI).

CURRENT_TIME

Cannot be used as column name (reserved by ANSI).

CURRENT_TIMESTAMP

Cannot be used as column name (reserved by ANSI).

CURRENT_USER

Cannot be used as column name (reserved by ANSI).

DATABASE

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

DELETE

Reserved by ANSI.

DISTINCT

Reserved by ANSI.

DROP

Reserved by ANSI.

ELSE

Reserved by ANSI.

EXISTS

Reserved by ANSI.

FALSE

Cannot be used as column reference in a scalar expression.

FOLLOWING

Reserved by ANSI.

FOR

Reserved by ANSI.

FROM

Reserved by ANSI.

FULL

Cannot be used as table name or alias in a FROM clause.

GRANT

Reserved by ANSI.

GROUP

Reserved by ANSI.

GSCLUSTER

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

HAVING

Reserved by ANSI.

ILIKE

Reserved by Snowflake.

Reserved by ANSI.

INCREMENT

Reserved by Snowflake and others.

INNER

Cannot be used as table name or alias in a FROM clause.

INSERT

Reserved by ANSI.

INTERSECT

Reserved by ANSI.

INTO

Reserved by ANSI.

ISSUE

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

JOIN

Cannot be used as table name or alias in a FROM clause.

LATERAL

Cannot be used as table name or alias in a FROM clause.

LEFT

Cannot be used as table name or alias in a FROM clause.

Reserved by ANSI.

LOCALTIME

Cannot be used as column name (reserved by ANSI).

LOCALTIMESTAMP

Cannot be used as column name (reserved by ANSI).

MINUS

Reserved by Snowflake and others.

NATURAL

Cannot be used as table name or alias in a FROM clause.

NOT

Reserved by ANSI.

NULL

Reserved by ANSI.

ORDER

Reserved by ANSI.

ORGANIZATION

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

QUALIFY

Reserved by Snowflake.

REGEXP

Reserved by Snowflake.

REVOKE

Reserved by ANSI.

RIGHT

Cannot be used as table name or alias in a FROM clause.

RLIKE

Reserved by Snowflake.

ROW

Reserved by ANSI.

ROWS

Reserved by ANSI.

SAMPLE

Reserved by ANSI.

SCHEMA

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

SELECT

Reserved by ANSI.

SET

Reserved by ANSI.

SOME

Reserved by Snowflake.

START

Reserved by ANSI.

TABLE

Reserved by ANSI.

TABLESAMPLE

Reserved by ANSI.

THEN

Reserved by ANSI.

TRIGGER

Reserved by ANSI.

TRUE

Cannot be used as column reference in a scalar expression.

TRY_CAST

Cannot be used as column reference in a scalar expression.

UNION

Reserved by ANSI.

UNIQUE

Reserved by ANSI.

UPDATE

Reserved by ANSI.

USING

Cannot be used as table name or alias in a FROM clause.

VALUES

Reserved by ANSI.

VIEW

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

WHEN

Cannot be used as column reference in a scalar expression.

WHENEVER

Reserved by ANSI.

WHERE

Reserved by ANSI.

WITH

Reserved by ANSI.

Known Issues

1. Create table options not supported

As shown in the example "Simple Create Table", Snowflake does not support Teradata create table options. They are removed.

2. Partition by performance issues

In the example "Simple Create Table", the partition by statement is removed due to performance considerations.

3. Primary Index moved

In Teradata, the primary index constraint is declared outside of the create table statement, but in Snowflake it is required to be inside, as shown in the example "Simple Create Table".

4. SET semantics not supported

As shown in the example "Table Kind Clause - SET and MULTISET", Snowflake does not support Teradata's SET semantics. They are removed.

5. Global Temporary table option not supported

As shown in the example "Volatile and Global Temporary Table", Snowflake does not support Teradata's Global Temporary table option. It will be removed.

6. Compress unsupported

COMPRESS (value1. value2, value3) is removed due to being unsupported.

7. On commit unsupported

On commit is removed due to being unsupported.

8. Block compression unsupported

Block compression is removed due to being unsupported.

9. Normalize unsupported

Normalize is removed due to being unsupported.

SSC-FDM-0009: GLOBAL TEMPORARY TABLE functionality not supported.
SSC-FDM-0019: Sematic information could not be loaded.
SSC-FDM-TD0024: Set table functionality not supported.
SSC-PRF-0007: CLUSTER BY performance review.

PreviousDDL NextWITH DEFAULT

Last updated 1 month ago

Description

Sample Source Patterns

Simple Create​ Table

Table Kind Clause - SET and MULTISET

Teradata

Snowflake

Volatile and Global Temporary Tables

Teradata

Snowflake

With data and with no data option

Snowflake's Reserved & Limited Keywords

Known Issues

1. Create table options not supported

2. Partition by performance issues

3. Primary Index moved

4. SET semantics not supported

5. Global Temporary table option not supported

6. Compress unsupported

7. On commit unsupported

8. Block compression unsupported

9. Normalize unsupported

Related EWIs

Simple Create Table