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

Convert Teradata Table DDL to BigQuery - Migration Guide

Teradata CREATE TABLE Statement - Example:

CREATE MULTISET TABLE EMPLOYEE ,FALLBACK , 
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
BLOCKCOMPRESSION = ALWAYS
(
EmployeeNo INTEGER TITLE 'Employee Number' NOT NULL,
FirstName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'First Name' COMPRESS ('John',''),
LastName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Last Name' COMPRESS ('Boilerplate_content',''),
DOB DATE FORMAT 'YYYY-MM-DD' TITLE 'Date of Birth' COMPRESS ,
JoinedDate DATE FORMAT 'YYYY-MM-DD' TITLE 'Join Date' COMPRESS ,
DepartmentNo BYTEINT,
FOREIGN KEY (EmployeeNo) REFERENCES SALARY(EmployeeNo))
PRIMARY INDEX UPI_EMPLOYEE(EmployeeNo);

BigQuery equivalent:

CREATE TABLE  EMPLOYEE 
(
EmployeeNo INT64 NOT NULL,
FirstName STRING ,
LastName STRING ,
DOB DATE,
JoinedDate DATE,
DepartmentNo INT64
);

This DDL was converted using the free query converter from Roboquery

Teradata Unsupported Table Options:

The following table options are not supported in BigQuery. Hence you will have to remove them from the DDL

  • SET / MULTISET
  • VOLATILE
  • [NO] FALLBACK
  • [NO] BEFORE JOURNAL, [NO] AFTER JOURNAL
  • CHECKSUM = DEFAULT | val
  • DEFAULT MERGEBLOCKRATIO
  • PRIMARY INDEX (col, ...)
  • UNIQUE PRIMARY INDEX
  • CONSTRAINT
  • DEFAULT
  • IDENTITY

Teradata Unsupported Column Options:

Remove the below column options from the DDL:

  • FORMAT 'format'
  • CHARACTER SET name. BigQuery always uses UTF-8 encoding.
  • [NOT] CASESPECIFIC
  • COMPRESS val | (val, ...)
  • TITLE

Teradata Temporary Table equivalent in BigQuery

TeradataBigQuery
Volatile TableCreate a Permanent table with expiration time

CREATE TABLE temp.name (col1, col2, ...)

OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR));

Global Temporary Table (GTT)Create a Permanent table with expiration time
With ClauseSupported

Indexes in BigQuery

All Primary Keys, Foreign keys, IDENTITY columns, INDEXES, COLLECT STATS, COMPRESS Values are not supported in BigQuery. You will have to remove them from the DDL

Try Roboquery Table Converter - Its fast, efficient and avoids manual errors