Creating, Using and Dropping ALIAS Tables | VantageCloud Lake - Creating, Using and Dropping ALIAS Tables - Teradata Vantage

Alias tables offer a convenient method for referencing tables within a DATALAKE object. By establishing an alias table, users can access the DATALAKE table as if it were a standard Teradata table, ensuring compatibility with all client tools and utilities. This approach serves as an alternative to the traditional 3-level dot notation for referencing a DATALAKE table.

Syntax

CREATE ALIAS TABLE [<database_name>.]<table_name>,
<datalake_option> , <datalake_table_option>;

Where
<datalake_option> ::= DATALAKE = <datalake_name>
<datalake_table_option> ::= DATALAKE TABLE = <datalake_databasename>.<datalake_tablename>

database_name [Optional] Name of database in which to create the alias table.
Default: Current DATABASE value.

CREATE ALIAS TABLE Syntax Elements

table_name Name for the alias table.

datalake_name Name of existing Datalake to associate with the alias table.

datalake_databasename Name of database where the Datalake table is.

datalake_tablename Name of Datalake table.


DROP ALIAS TABLE [<database_name>.]<table_name>

Where
<database_name> ::= !! name of the database
<table_name> ::= !! name of the alias table

Example

CREATE ALIAS TABLE db1.Orders,
DATALAKE = datalake_glue,
DATALAKE TABLE = MyDatabase.orders;

DROP ALIAS TABLE DB1.Orders;

Usage Considerations

• The alias table now supports READ functionality.
• Only one DATALAKE or DATALAKE TABLE option is permitted.
• Alter on an OTF alias table is not allowed.
• The HELP TABLE will present column and datatype information as though the HELP TABLE command was executed on the Datalake table.
• SHOW TABLE displays the CREATE TABLE syntax utilized for generating the alias table.
• The command DROP alias table is designed to remove only the alias table without affecting the actual OTF table stored in the datalake.

Limitations

The use of alias tables for writing functionality is not supported.