Different types of Utilities in Teradata

Teradata offers a variety of utilities to manage and optimize its database system. Some of the commonly used Teradata utilities include:-

1. BTEQ (Basic Teradata Query):- BTEQ is a command-line utility used for executing SQL queries, importing/exporting data, and generating reports. It allows interactive and batch processing of SQL commands.

2. FastLoad:- FastLoad is a high-speed utility used for loading large volumes of data into empty Teradata tables. It uses multiple sessions to load data in parallel, achieving fast loading speeds.

3. MultiLoad:- MultiLoad is a utility used for inserting, updating, deleting, and upserting (insert/update) data into Teradata tables. It supports loading data from multiple input sources and provides error handling capabilities.

4. FastExport:- FastExport is a utility used for exporting large volumes of data from Teradata tables to flat files. It can export data in parallel, making it suitable for efficient data extraction.

5. TPump (Teradata Parallel Data Pump):- TPump is a real-time data loading utility used for continuously loading data into Teradata tables. It provides capabilities for insert, update, delete, and upsert operations with minimal impact on system resources.

6. Teradata Parallel Transporter (TPT):- TPT is a comprehensive utility for data integration, transformation, and loading (ETL) tasks. It supports various data formats, parallel processing, and advanced transformation capabilities.

7. Teradata Database Query Scheduler (TQS):- TQS is a utility used for scheduling and managing SQL queries and utilities in Teradata environments. It provides capabilities for automated query execution and resource management.

These are some of the key Teradata utilities commonly used for data management, loading, and querying tasks. Each utility offers specific features and capabilities to meet different requirements in Teradata environments.

Let's go through examples of each Teradata utility with data and output:-

1. BTEQ (Basic Teradata Query):-

Example BTEQ script (example.bteq):-

.logon <Teradata_hostname>/<Username>,<Password>;

SELECT * FROM <Database_name>.<Table_name>;

.logoff;

To execute the script:-

bteq < example.bteq > output.txt

2. FastLoad:-

Example FastLoad script (example.fl):-

.LOGTABLE <Database_name>.<Log_table_name>;

.LOGON <Teradata_hostname>/<Username>,<Password>;

BEGIN LOADING <Database_name>.<Table_name>

ERRORFILES <Error_table_name> , <Error_table_name2>;

INSERT INTO <Database_name>.<Table_name>

VALUES (1, 'John'),

       (2, 'Emily');

END LOADING;

.LOGOFF;

To execute the script:-

fastload < example.fl > output.txt

3. MultiLoad:-

Example MultiLoad script (example.mload):-

.LOGON <Teradata_hostname>/<Username>,<Password>;

BEGIN IMPORT MLOAD TABLES <Database_name>.<Table_name>;

.LAYOUT input_layout;

.field id *;

.field name *;

.DML LABEL insert_row;

INSERT INTO <Database_name>.<Table_name>

VALUES(:id, :name);

.IMPORT INFILE 'data.txt' FORMAT VARTEXT '|' LAYOUT input_layout APPLY insert_row;

.END MLOAD;

.LOGOFF;

To execute the script:-

mload < example.mload > output.txt

4. FastExport:

Example FastExport script (example.fexp):-

.LOGON <Teradata_hostname>/<Username>,<Password>;

.EXPORT OUTFILE 'output.txt';

SELECT * FROM <Database_name>.<Table_name>;

.LOGOFF;

To execute the script:-

fastexport < example.fexp

5. TPump:-

Example TPump script (example.tpump):-

.LOGTABLE <Database_name>.<Log_table_name>;

.LOGON <Teradata_hostname>/<Username>,<Password>;

APPLY

     INSERT INTO <Database_name>.<Table_name>

     VALUES (:id, :name);

.DML LABELS insert_update;

.INMOD 'my_inmod.so';

.LOGOFF;

To execute the script:-

tpump < example.tpump > output.txt

6. Teradata Parallel Transporter (TPT):-

Example TPT script (example.tpt):-

DEFINE JOB load_data

DESCRIPTION 'Load data into Teradata table'

(

  DEFINE OPERATOR SQL_SELECTOR

  TYPE SELECTOR

  SCHEMA *

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'selector_log',

    VARCHAR SelectStmt = 'SELECT * FROM my_source_table'

  );

  DEFINE OPERATOR LOAD_OPERATOR

  TYPE LOAD

  SCHEMA *

  ATTRIBUTES

  (

    VARCHAR TdpId = '<Teradata_hostname>',

    VARCHAR UserName = '<Username>',

    VARCHAR UserPassword = '<Password>',

    VARCHAR TargetTable = '<Database_name>.<Table_name>'

  );

  APPLY

    ('INSERT INTO <Database_name>.<Table_name>(id, name)

      VALUES (?, ?);')

    TO OPERATOR (LOAD_OPERATOR [2])

    SELECT * FROM OPERATOR (SQL_SELECTOR);

);

To execute the script:-

tbuild -f example.tpt

7. Teradata Database Query Scheduler (TQS):-

Example TQS script (example.tqs):-

INSERT INTO <Database_name>.<Table_name>

SELECT * FROM <Database_name>.<Source_table>;

To schedule the script:-

tqscheduler -u <Username> -p <Password> -h <Teradata_hostname> -f example.tqs -s <Start_time> -e <End_time> -i <Interval>

These examples demonstrate how to use each Teradata utility to perform various database tasks such as querying, loading data, exporting data, and scheduling queries. Note that you need to replace placeholders like <Teradata_hostname>, <Username>, <Password>, <Database_name>, <Table_name>, etc., with your actual values.

Here are five frequently asked questions (FAQs) about the different types of Teradata utilities:-

1. What are Teradata utilities?

   - Teradata utilities are specialized tools provided by Teradata for various purposes such as data loading, backup and recovery, database administration, and performance optimization.

2. What are some common types of Teradata utilities?

   - Common types of Teradata utilities include FastLoad, MultiLoad, FastExport, TPump, BTEQ, ARC, TPT (Teradata Parallel Transporter), and Teradata Administrator.

3. What is the purpose of Teradata FastLoad utility?

   - Teradata FastLoad is a high-speed utility used for loading large volumes of data into empty tables in Teradata databases. It achieves fast loading speeds by bypassing row-level processing and using multiple sessions.

4. How does Teradata MultiLoad utility differ from FastLoad?

   - Teradata MultiLoad is a utility used for high-speed maintenance of tables, supporting various operations like INSERT, UPDATE, DELETE, and UPSERT. Unlike FastLoad, MultiLoad can work with non-empty tables and supports multiple operations in a single job.

5. What is Teradata FastExport utility used for?

   - Teradata FastExport is a utility used for exporting data from Teradata tables to flat files with high performance. It supports parallel data extraction and can handle large volumes of data efficiently.