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

1. Create Teradata Managed OTF Table.

   Keyword Managed is mandatory for creating a Teradata Managed OTF table. Before creating the table, you must first create the Data Lake (DATALAKE) database and the corresponding Teradata database in OTF.

   Syntax

   CREATE MANAGED TABLE [databasename.]tablename
       { , motf_option [..., motf_option] }
       ( { column_declaration } [..., { column_declaration } ] )
       [[NO] PARTITIONED BY ({<transform> | <column_name>}[, ...])]
       [[NO] SORTED BY ( {<column_name> [<sort_type>]}[, ...])]
       [{TBLPROPERTIES | PROPERTIES} (<custom_clause_list>)]
       [index]
       AS_Clause WITH [NO] DATA;
   
   where
   
       motf_option is one of the following (options may be listed in any order, DAT
   
       [NO] FALLBACK [PROTECTION]
   
       { MAP = Map_Name [COLOCATE USING colocation_name] }
   
       DATALAKE = datalake_name
   
       [RETENTIONDAYS = retention_days]
   
       datalake_name is name of the data lake object
   
       retention_days represents table-level retention days for an MOTF table, it i
   
       column_declaration is
   
       cname data_type_declaration [column_attribute [... column_attribute]]
   
       column_attribute is
   
       data_type_attributes
   
   
       For data_type_declaration, enter:  HELP 'data_type declarations';
   
       For data_type_attributes, enter:  HELP 'data_type attributes';
   
       transform is 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>) |
       <column_name/constant> Arithmetic Operators <column_name/constant> where Ari
       substr(<column_name>, starting index, length) |
       <column_name/constant> % <column_name/constant> |
       Extract(month/year/day/hour from <column_name>) |
       CASE_N(conditional-expr1,...,conditional-exprn[,NOCASE[ORUNKNOWN]][,UNKNOWN]
       RANGE_N(test-valueBETWEENrange[,range]...[,NORANGE[ORUNKNOWN]][,UNKNOWN])
   
       hash_value is bucket number
   
       truncate_value is width or length
   
       sort_type is ASC
   
       custom_clause_list is
       custom_clause [... custom_clause]
   
       custom_clause is
       'name' = 'value'
   
       name is any Teradata Identifier.
   
       value is any Teradata literal value.
   
       index is one of the following:
   
       NO PRIMARY INDEX
   
       [UNIQUE] PRIMARY INDEX [name] (cname [..., cname])
   
       AS_Clause is <source_table> | <subquery_clause>
   
       source_table is <datalake_name>.<OTFdatabase_name>.<OTFtable_name> |
       [<database_name>.]<source_table_name>
   
       subquery_clause is SELECT * | <column-name>[, ...]
       FROM {[database_name.]<source_table_name> |
       <datalake_name>.<OTFdatabase_name>.<OTFtable_name>}
   
       database_name is Teradata database name or user name
   
       source_table_name is Teradata table name
   
    

   Example

   /*
   ** 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';
   

   An OFS single-table join index can only be created if a corresponding storage object exists.
2. Drop Teradata Managed OTF Table.

   Syntax

   DROP TABLE [databasename.]tablename ;

Usage Consideration

• 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.
• Teradata 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 Teradata 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. It is the default format if none is specified.
• PARTITION BY COLUMN is not allowed for Teradata 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 Teradata Managed OTF table.
• A single table join index (STJI) can be created on a Teradata Managed OTF table.
  • The STJI can be in BFS format or OFS
  • OFS STJI can reside in a customer-managed bucket.
• Teradata 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 Teradata Managed OTF, 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 Teradata Managed OTF table can be created via CREATE TABLE AS syntax where the source can be a select or a direct table (BFS/OFS/Teradata Managed OTF).
  • In BFS/OFS Source, unsupported syntax elements are ignored in the target table; a warning is reported (similar to OFS → BFS behavior).

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';