Tables

Translation reference to convert Teradata TABLE statement to Snowflake

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

Table

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

Table

CREATE TABLE PUBLIC.table1 (
c1 INTEGER NOT NULL,
f1 INTEGER NOT NULL,
p1 INTEGER NOT NULL,
DATE,
TIME,
UNIQUE (c1)
);

ALTER TABLE PUBLIC.table1 ADD CONSTRAINT f1
foreign key (f1) REFERENCES PUBLIC.table2 (d1) ;

Table Kind Clause - SET and MULTISET

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

Teradata

Table

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

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

Snowflake

Table

-- Set semantics
/*** MSC-WARNING - MSCEWI2015 - SET TABLE FUNCTIONALITY NOT SUPPORTED. TABLE MIGHT HAVE DUPLICATE ROWS ***/
CREATE TABLE PUBLIC.table1 (
column1 INTEGER
);

--Multiset semantics
CREATE TABLE PUBLIC.table2 (
column1 INTEGER
);

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

Table

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

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

Snowflake

Table

/--Global Temporary Table
/*** MSC-WARNING - MSCEWI2016 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED ***/
CREATE TABLE PUBLIC.table1 (
column1 INTEGER
);

--Volatile Table
CREATE TEMPORARY TABLE PUBLIC.table3 (
column1 INTEGER
);

With data and With no data option

Teradata

Table

-- With data
CREATE TABLE table1 AS table2 WITH DATA

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

Snowflake

Table

-- With data
CREATE TABLE PUBLIC.table1 CLONE table2;

-- With no data
CREATE TABLE PUBLIC.table1 LIKE table2;

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.

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. Foreign key as an alter table

In Teradata, the foreign key constraint can be declared inside the create table statement, but in Snowflake it is required to be declared as an alter table statement. Also notice that the with check option statement is removed, as shown in the example "Simple Create Table".

5. 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.

6. 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.

7. Compress unsupported

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

8. On commit unsupported

On commit is removed due to being unsupported.

9. Block compression unsupported

Block compression is removed due to being unsupported.

10. Normalize unsupported

Normalize is removed due to being unsupported.

Related EWIs

1. MSCEWI2015 - SET TABLE FUNCTIONALITY NOT SUPPORTED.

2. MSCEWI2016 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED.