Creating an OTF Table from Another Table - Teradata Vantage

The CREATE TABLE .. AS SQL can be used to create an OTF table based off another table. If the WITH DATA option is used, then the data is also copied over to the new OTF table. If the WITH NO DATA clause is used, then the table is created without any data. The source table can be a BFS, OFS or OTF table.

Syntax

CREATE TABLE <datalake_name>.<OTFdatabase_name>.<OTFtable_name>
    [({<column_name> <column_type> [NOT NULL]}[, ...])]
[{PARTITIONED | PARTITION} BY {({<transform> | <column_name>}[, ...]) 
    [<transform> | <column_name>}]
[{SORTED | ORDER} BY {<column_name> [<sort_type>]}[, ...]]
[{TBLPROPERTIES | PROPERTIES} (<custom_clause_list>)]
AS_Clause
WITH [NO] DATA
<datalake_name> ::= !! name of the data lake object
<OTFdatabase_name> ::= !! name of the database in the OTF catalog
<OTFtable_name> ::= !! name of the table in the OTF database
<transform> ::= IDENTITY(<column_name>) | BUCKET(<hash_value>, <column_name>) | 
                TRUNCATE(<truncate_value>, <column_name>) |
                YEAR(<column_name>) | MONTH(<column_name>) | DAY(<column_name>) | 
                HOUR(<column_name>) | NULL(<column_name>)
<hash_value> ::= !! bucket number
<truncate_value> ::= !! width or length
<sort_type> ::= ASC | DESC
<custom_clause_list> ::= <custom_clause>[, ...]
<custom_clause> ::= <quote><name><quote>=<quote><value><quote> 
<name> ::= !! Teradata identifier
<value> ::= !! Teradata literal value
<AS_Clause> ::= <source_table> | <subquery_clause>
<source_table> ::= <datalake_name>.<OTFdatabase_name>.<OTFtable_name> |
                  [<database_name>.]<source_table_name>
<subquery_clause> ::= SELECT * | <column-name>[, ...] 
    FROM {[database_name.]<source_table_name> |
         <datalake_name>.<OTFdatabase_name>.<OTFtable_name>}
<database_name> ::= !! Teradata database name or user name
<source_table_name> ::= !! Teradata table name

Example

Create OTF table named datalake_iceberg_glue.MyDatabase.Tab1.

CREATE TABLE datalake_glue.MyDatabase.Tab1 
PARTITIONED BY (id)
SORTED BY id
AS (SELECT id FROM datalake_glue.MyDatabase.Tab2 as D) WITH DATA;

Usage Considerations

• The source table can be a BFS, OFS or OTF table.
• If the source table is a data lake table, the TABLE FORMAT must be the same for the source and destination (for example, from ICEBERG to ICEBERG).
• If the column data type in the base table is not supported in OTF, the table cannot be created and an error message will be displayed.
• The following CREATE TABLE options or clauses are not allowed:
  • Table kind, such as SET, MULTISET, GLOBAL TEMPORARY or VOLATILE
  • MAP
  • FALLBACK
  • WITH JOURNAL TABLE
  • LOG
  • CHECKSUM
  • FREESPACE
  • INDEX
  • ON COMMIT
  • MERGEBLOCKRATIO
  • DATABLOCKSIZE
  • BLOCKCOMPRESSION
  • WITH STATISTICS
• The same rules apply for TBLPROPERTIES/PROPERTIES as the create data lake table statement.

Usage Considerations: Delta Lake

Partition transforms and SORTED/ORDER BY order ASC, DESC are not supported for Delta Lake.