Support for OTF Database Views | Teradata OTF - Support for OTF Database Views - Teradata Vantage

View support for OTF tables is essential for Clearscape Analytics offerings, including teradataml. Database views for OTF tables are now supported.

Supported Features

• CREATE/REPLACE VIEW to define views that include OTF tables.
• CREATE/REPLACE VIEW to define views that join OTF, BFS, and OFS tables.
• DROP VIEW to remove views containing OTF tables.
• HELP VIEW.
• SELECT from VIEW.
• CTAS using the VIEW as source.
• VIEW definition is available after upgrades.

Limitations

• OTF database views are not automatically refreshed if the underlying OTF table schema changes. You must manually run a REPLACE VIEW statement to reflect any modifications.
• Since OTF table metadata is not stored in the Teradata dictionary, the database cannot detect schema changes in the underlying tables. You need to update the view definition when changes occur.

For example:

/* CREATE AN OTF DATABASE VIEW */
create view v1 as sel * from datalake_iceberg_glue.jantestdb.t1; 
/* SHOW VIEW shows the text of the DLL */
show view v1;  *** Text of DDL statement returned. 
 *** Total elapsed time was 1 second. 
 --------------------------------------------------------------------------- create view v1 as sel * from datalake_iceberg_glue.jantestdb.t1; 
 /* SELECT from View returns the data in the OTF table */
 sel * from v1; 
  *** Query completed. One row found. 2 columns returned. 
   *** Total elapsed time was 1 second.      
         i            j
    -----------  -----------     
         10           20  
 /* ALTER THE UNDERLYING TABLE */         
alter table datalake_iceberg_glue.jantestdb.t1 add k int; 
/* OTF Table shows the columns are schema evolution */
 sel * from datalake_iceberg_glue.jantestdb.t1;
 *** Query completed. One row found. 3 columns returned. 
 *** Total elapsed time was 1 second.
   i            j          k    
   --- -       ------ ---------
   10           20         ? 
 /* VIEW IS NOT UPDATED - still shows the old columns */
 sel * from v1; 
 *** Query completed. One row found. 2 columns returned. 
  *** Total elapsed time was 2 seconds. 
        i            j
    -----------  -----------     
         10           20  
/* Replacing the view will refresh the view */
replace view v1 as sel * from datalake_iceberg_glue.jantestdb.t1;
 *** View has been replaced.
 *** Total elapsed time was 1 second.
 BTEQ -- Enter your SQL request or BTEQ command:
sel * from v1;
 *** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.
          i            j            k
-----------  -----------  -----------
         10           20            ?
alter table datalake_iceberg_glue.jantestdb.t1 drop k; 
 *** Table has been modified.
 *** Total elapsed time was 1 second.
/* Base OTF table no longer contains column k */
 sel * from v1;
  *** Failure 3810 Column/Parameter 'OTF_DEMO.v1.k' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.