Teradata - Tables

Teradata Architecture :

Teradata Architecture

The above diagram shows a very basic architecture of TERADATA and is very useful and easy for learning purpose. The first component is PE (Parsing Engine)

1) Session Control –> It checks for user authorization before processing any SQL queries. A PE can support up-to 120 sessions.

2) Parser –> It checks for the SQL syntax and user rights to access various database objects referred in the SQL query submitted by user.

3) Optimizer –> It create a plan or execution steps to follow in order to perform actions on database objects as per SQL query submitted by user.

4) Dispatcher –> It passes the execution steps to BYNET. Dispatcher also plays an important role by combining all the responses received and send it to user.

5.) Next component is BYNET. It is used for communication between PE’s and AMP’s (Access Module Processor). There are two BYNET’s available in any TERADATA environment: BYNET-0 & BYNET-1. Two BYNET’s allows for continuos sending and receiving messages between PE’s and AMP’s. 

6.) Next to BYNET, we have AMP’s. AMP’s can be considered as the worker in TERADATA Architecture. Each AMP has it’s own dedicated VDISK (Virtual DISK) to which it queries and process the steps planned by Optimizer. AMP’s work only on their own VDISK and do not have access to other AMP’s VDISK. Once the AMP perform the steps, it send back response to PE via BYNET where all the responses from various AMP’s is collected and sent back to user.

Copy table structure with data

Use the following SQL to copy table with data in Teradata: 

CREATE TABLE PROD_DB.D_PRODUCTS AS 
PROD_DB.D_PRODUCTS WITH DATA;

The same results can be achieved by issuing the following statements: 

CREATE TABLE PROD_DB.D_PRODUCTS AS PROD_DB.D_PRODUCTSWITH NO DATA;INSERT PROD_DB.D_PRODUCTS SELECT * FROM PROD_DB.D_PRODUCTS;

Note that the CREATE TABLE AS statement will not work if the source table has referential integrity constraints or any columns are defined as identity columns. 

COPY TABLE STRUCTURE

Run the following SQL in Teradata to copy table structure only without data

CREATE TABLE PROD_DB.D_PRODUCTS AS 
PROD_DB.D_PRODUCTS WITH NO DATA;

Or

CREATE TABLE PROD_DB.D_PRODUCTS AS(
    SELECT * FROM PROD_DB.D_PRODUCTS)WITH NO DATA;

Teradata temporary tables

Teradata database provides various options in case of a need to use temporary tables. The temporary tables are especially useful when performing complicated calculations, they usually make multiple, complex SQL query simpler and increase overall SQL query performance.
Temporary tables are especially useful for reporting and performing operations on summarized values.

TERADATA TEMPORARY TABLE TYPES:

Derived temporary tables

• Derived tables are local to an SQL query.
• Not included in the DBC data dictionary database, the definition is kept in cache.
• They are specified on a query level with an AS keyword in an sql statement

Volatile temporary tables

• Local to a session ( deleted automatically when the session terminates)
• Not included in the DBC data dictionary database and table definition is stored in cache. However, the volatile tables need to have unique names across the session.
• Created by the CREATE VOLATILE TABLE sql statement

Global temporary tables

• Global tables are local to a session and deleted automatically when the session terminates
• A permanent table definition is stored in the DBC data dictionary database (DBC.Temptables)
• Defined with the CREATE GLOBAL TEMPORARY TABLE sql