28 Jan 21 ·  Cpaul in Guides ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

Convert Teradata View DDL to BigQuery - Migration Guide

Teradata CREATE VIEW Statement Example:

CREATE VIEW Employee_Info_V 
     (number, name, position, department,sex, dob)
LOCKING ROW FOR ACCESS
    AS
SELECT employee.empno, name, jobtitle, deptno, sex, dob
FROM employee
WHERE jobtitle NOT IN ('Vice Pres', 'Manager')
;

BigQuery Equivalent:

CREATE OR REPLACE VIEW Employee_Info_V AS 
SELECT employee.empno, name, jobtitle, deptno, sex, dob
FROM employee
WHERE jobtitle NOT IN ('Vice Pres', 'Manager')
;

Teradata View Options that are not Supported in BigQuery:

The below Teradata View options needs to be removed in BigQuery

  • LOCKING ROW
  • Do not declare the list of columns in the CREATE VIEW Clause
  • AS OF Temporal Qualifiers
  • MODE NOWAIT

Teradata CREATE View Clause equivalent in BigQuery

Teradata SyntaxBigQuery equivalent
CREATE VIEW view_name AS SELECT ...CREATE VIEW view_name AS SELECT ...
CVCREATE VIEW view_name AS SELECT ...
REPLACE VIEW view_name AS SELECT ...CREATE OR REPLACE VIEW
Not Supported

CREATE VIEW IF NOT EXISTS
OPTIONS(view_option_list)
AS SELECT ...

Try the Roboquery View Converter - Its free and faster, to migrate the views from Teradata to BigQuery