Translation reference to convert Teradata VIEW statement to Snowflake
Some parts in the output code are omitted for clarity reasons.
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
IN -> Teradata_01.sql
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
OUT -> Teradata_01.sql
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "table1", "table2" **
CREATE OR REPLACE VIEW view1
(
col1,
col2)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
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;
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
IN -> Teradata_02.sql
/*<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.
OUT -> Teradata_02.sql
/*<sc-view>RMSviews.EMPLOYEEB</sc-view>*/
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "EMPLOYEE" **
CREATE OR REPLACE VIEW RMSviews.EMPLOYEEB
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
SELECT
* FROM
RMSviews.EMPLOYEE
WHERE AREA = "AREAB";
/*<sc-view>Views.EMPLOYEEB</sc-view>*/
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "EMPLOYEE" **
--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR Views.EMPLOYEEB. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE OR REPLACE VIEW Views.EMPLOYEEB
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
SELECT
* FROM
Views.EMPLOYEE
WHERE AREA = "AREAB";
/*<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";
/*<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 logic difference
In Snowflake, access to objects and elements is based on users and privileges.
