Views

Translation reference to convert Teradata VIEW statement to Snowflake

Description

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

For more information on Teradata VIEW, check here.

Sample Source Patterns

Create View Transformation

Teradata

View

CREATE VIEW view1 (someTable.col1, someTable.col2) AS locking row for access
    SELECT
    my_table.col1, my_table.col2
    FROM table1 AS my_table
    WHERE my_table.col1 = 'SpecificValue'
    UNION ALL
    SELECT other_table.col2
    FROM table2 AS other_table
    WHERE my_table.col2 = other_table.col2)

Snowflake

View

CREATE OR REPLACE VIEW PUBLIC.view1 (col1, col2)
AS
   SELECT
   my_table.col1, my_table.col2
   FROM PUBLIC.table1 AS my_table
   WHERE my_table.col1 = 'SpecificValue'
   UNION ALL
   SELECT PUBLIC.other_table.col2
   FROM table2 AS other_table
   WHERE my_table.col2 = other_table.col2

Custom Schema Tag

The custom schema is specified in the comment section before the specification of the view, with an XML tag named “sc-view” that contains only the value of the schema and the view name separated with a period ‘.’ as shown below: <sc-view>SCHEMANAME.VIEWNAME</sc-view>

The custom schema will be used as a view qualifier, and then the name of the view and all the objects referred to in the FROM queries and inner queries will be using that custom schema. Therefore could be several views with the same name, but with different custom tags. Example: two views with the same name, will take the custom schema tag information to perform the translation.

Teradata

View

/*<sc-view>RMSviews.EMPLOYEEB</sc-view>*/
REPLACE VIEW EMPLOYEEB AS
SELECT * FROM EMPLOYEE
WHERE AREA = "AREAB";

/*<sc-view>Views.EMPLOYEEB</sc-view>*/
REPLACE VIEW EMPLOYEEB AS
SELECT * FROM EMPLOYEE
WHERE AREA = "AREAB";

Snowflake

The transformation for Snowflake will vary depending on the customized schema name MySchema, customized database name MyDatabase or not selecting a customized database or schema in the conversion settings.

Custome Schema

/*<sc-view>RMSviews.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW RMSviews.MySchema.EMPLOYEEB
AS
   SELECT * FROM RMSviews.MySchema.EMPLOYEE
   WHERE AREA = "AREAB";

/*<sc-view>Views.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW Views.MySchema.EMPLOYEEB
AS
   SELECT * FROM Views.MySchema.EMPLOYEE;

Custome Database

/*<sc-view>RMSviews.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW MyDatabase.RMSviews.EMPLOYEEB
AS
   SELECT * FROM MyDatabase.RMSviews.EMPLOYEE
   WHERE AREA = "AREAB";

/*<sc-view>Views.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW MyDatabase.Views.EMPLOYEEB
AS
   SELECT * FROM MyDatabase.Views.EMPLOYEE
   WHERE AREA = "AREAB";

Non selected

/*<sc-view>RMSviews.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW RMSviews.PUBLIC.EMPLOYEEB
AS
   SELECT * FROM RMSviews.PUBLIC.EMPLOYEE
   WHERE AREA = "AREAB";

/*<sc-view>Views.EMPLOYEEB</sc-view>*/
CREATE OR REPLACE VIEW Views.PUBLIC.EMPLOYEEB
AS
   SELECT * FROM Views.PUBLIC.EMPLOYEE
   WHERE AREA = "AREAB";

Known Issues

1. Locking row for access unsupported

AS locking row for access is removed as shown in the example "Create View Transformation".

Related EWIs

No related EWIs.