Creating and Maintaining MOTF Table | Teradata MOTF - Creating and Maintaining Managed OTF Table - Teradata Vantage

Usage Consideration

• Keyword Managed is mandatory for creating a managed OTF table.
• DATALAKE clause is required to reference an existing data lake object. This links the table to the external catalog and storage bucket, similar to Java OTF and native OTF configurations.
• Initially, managed OTF tables support only 2-dot notation (for example, database.table) for access, consistent with standard Teradata table access. However, readers using Java OTF or native OTF can still access managed OTF data using 3-dot notation (for example, datalake.database.table).
• TABLE PROPERTIES clause is used when the Teradata dictionary table name differs from the external catalog name. This clause functions similarly to alias tables in native OTF. TABLE PROPERTIES clause allows specification of additional table-level properties.
• PARTITIONED BY clause specifies partitioning columns and supports expressions, including Teradata-specific functions like RANGE_N, CASE_N, and others used in OFS ORDER BY clauses. These expressions are not stored in OTF metadata files.

  There is some change in function behaviour, if it is part of PARTITIONED BY:

  Function	Description
  year()	Extract a date or timestamp year, as years from 1970
  month()	Extract a date or timestamp month, as months from 1970-01-01
  day()	Extract a date or timestamp day, as days from 1970-01-01
  hour()	Extract a timestamp hour, as hours from 1970-01-01 00:00:00

• SORTED BY clause defines sort columns within each partition.
• Rows within a transaction are grouped into objects based on partition values. All rows with the same partition value are stored in a single object. The value of the partitioning are recorded in both exp_min and exp_max metadata fields, which are identical due to the absence of value splitting.
• Optional TBLPROPERTIES clause is used to control any other table properties.
• File Format: Only Parquet is supported initially. It is the default format if none is specified.
• PARTITION BY COLUMN is not allowed for managed OTF tables. An error is reported if specified to avoid confusion with PARTITIONED BY.
• Teradata-specific types such as LOBs, UDTs, etc., are not supported. Errors are reported for such cases.
• Teradata extensions such as PRIMARY INDEX(PI), soft RI and constraints can be specified on a managed OTF table. For PI, OTF metadata is built by hiding hidden hash column using alter table drop hidden hash column.
• A single table join index (STJI) can be created on a managed OTF table.
  • The STJI can be in BFS format or OFS
  • OFS STJI can reside in a customer-managed bucket.
• Managed OTF tables can be altered to add/drop referential integrity (RI) constraints or change the map.
• Syntax Differences from Native OTF:
  • In Native OTF, parentheses in PARTITIONED BY is optional for single expressions; required for multiple expressions.
  • In MOTF, parentheses in PARTITIONED BY is always required.
• Table rename only changes the name in Teradata dictionary.
  • If the catalog table name is already provided during create, it remains unchanged.
  • If the catalog table name is not provided during create or is null, the catalog table name is populated with the previous table name.
• A managed OTF table can be created via CREATE TABLE AS syntax where the source can be a select or a direct table (BFS/OFS/MOTF).
  • In BFS/OFS Source, unsupported syntax elements are ignored in the target table; a warning is reported (similar to OFS → BFS behavior).
  • In Managed Table Source, syntax elements are copied, except for catalog database and table name.

Create Managed Table Syntax

/* 
** If external catalog has a conflict with database and table name,
** we can provide a different name with the below syntax. 
** The assumption is that the database 'otf_db' already exists in external catalog.
*/
/* create database in td catalog, if not created already */
CREATE DATABASE td_db as perm = 1e8;
/* create database in external catalog, if not created already */
CREATE DATABASE iceberg_glue.otf_db;
CREATE MANAGED TABLE td_db.td_table, 
DATALAKE = iceberg_glue
( 
      c1 VARCHAR(50) NOT NULL, 
      c2 INT, 
      dt TIMESTAMP(6), 
      country VARCHAR(50) 
)
PARTITIONED BY (year(dt), country) 
SORTED BY (c1 , c2) 
TBLPROPERTIES
( 
      'write.format.default' = 'parquet',
      'catalog_database_name' = 'otf_db',
      'catalog_table_name' = 'otf_table'
);
where 
  'td_db' and 'td_table' are Teradata database and table names respectively 
                   and
  'otf_db' and 'otf_table' are database and table names respectively in 
  external catalog

Create Managed Table: Same Object Names

 /* create database in external catalog, if not created already */
CREATE DATABASE iceberg_glue.td_db;
CREATE MANAGED TABLE td_db.td_table,
DATALAKE = iceberg_glue
(
      c1 VARCHAR(50) NOT NULL,
      c2 INT,
      dt TIMESTAMP(6),
      country VARCHAR(50)
)
PARTITIONED BY (year(dt), country)
SORTED BY (c1 , c2)
TBLPROPERTIES
(
      'write.format.default'='parquet'
);

where

'td_db' and 'td_table' are the database and table names.

Create Managed Table: Different Object Names

If there is a conflict between the external catalog and the database or table name, an alternative name can be specified using the following syntax. This assumes that the database 'otf_db' already exists in the external catalog.

/* create database in external catalog, if not created already */
CREATE DATABASE iceberg_glue.otf_db;
CREATE MANAGED TABLE td_db.td_table,
DATALAKE = iceberg_glue
(
      c1 VARCHAR(50) NOT NULL,
      c2 INT,
      dt TIMESTAMP(6),
      country VARCHAR(50)
)
PARTITIONED BY (year(dt), country)
SORTED BY (c1 , c2)
TBLPROPERTIES
(
      'write.format.default'='parquet',
      'catalog_database_name' = 'otf_db',
      'catalog_table_name' = 'otf_table'
);

where

'td_db' and 'td_table' are Teradata database and table names respectively

and

'otf_db' and 'otf_table' are database and table names respectively in the external catalog

Primary Index

CREATE MANAGED TABLE td_db.td_table_pi,
                datalake = iceberg_glue
    ( c1 VARCHAR(50) NOT NULL,
      c2 INT,
      dt TIMESTAMP(6),
      country VARCHAR(50) )
PARTITIONED BY (year(dt), country)
SORTED BY (c1)
TBLPROPERTIES(
      'write.format.default'='parquet')
PRIMARY INDEX(c2) ;

Table Level Retention

CREATE MANAGED TABLE td_db.td_table_pi,
                datalake = iceberg_glue,
                RETENTIONDAYS=45
    ( c1 VARCHAR(50) NOT NULL,
      c2 INT,
      dt TIMESTAMP(6),
      country VARCHAR(50) )
PARTITIONED BY (year(dt), country)
SORTED BY (c1)
TBLPROPERTIES(
      'write.format.default'='parquet')
PRIMARY INDEX(c2) ;

The RETENTIONDAYS clause can be used to specify the table-level retention period.

Join Index

CREATE JOIN INDEX jix AS
SELECT c1, rowid from td_db.td_table
where country = 'India';