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

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

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

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

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

Snowflake

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

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

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

Snowflake

/--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

-- With data
CREATE TABLE table1 AS table2 WITH DATA

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

Snowflake

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

KeywordComment

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.

PreviousDDLNextIndex

Last updated