ETL and Data Transfer utilities in Teradata

Share via:

ETL and Data Transfer utilities in Teradata

Dear Readers,

In this article, we will learn about ETL and Data Transfer utilities in Teradata

Teradata database has various techniques to load the data into tables. Below are the 5 commonly used load utilities.

  1. Fast load
  2. Multi load
  3. Fast Export
  4. TPump
  5. TPT ( Teradata Parallel Transporter )

Each utility mentioned above has certain specifications. so, based on customer requirement which ever suits best, they can proceed with the corresponding utility to load the data.

Apart from the mentioned load utilities, we have data mover to move the data from one system to other system and backup/restore utilities ( ARC and DSC ) for the same purpose.

In this article, we are mainly focusing on Fast load utility.

FASTLOAD

Fast load utility is mainly used to load huge amount of data into empty tables.

The feature which makes unique to fast load is it loads huge amount of data with very high speed rate.

As it won’t maintain transient journal, it won’t allow duplicate values though the table is multiset table.

with one job, you can load data for one table only at a time.

How it works :

Phase 1

  • The Parsing engines read the records from the input file and sends a block to each AMP.
  • Each AMP stores the blocks of records.
  • Then AMPs hash each record and redistribute them to the correct AMP.
  • At the end of Phase 1, each AMP has its rows but they are not in row hash sequence.

Phase 2

  • Phase 2 starts when FastLoad receives the END LOADING statement.
  • Each AMP sorts the records on row hash and writes them to the disk.
  • Locks on the target table is released and the error tables are dropped

Below is the sample fast load script to load data

Sample input text file for above script :

How to execute fastload job :

Once the input file employee.txt is created and the FastLoad script is named as EmployeeLoad.fl, you can run the FastLoad script using the following command in UNIX and Windows.

fastload < fasloadscriptfile

Once the above command is executed, the FastLoad script will run and produce the log. In the log, you can see the number of records processed by FastLoad and status code.

Fastload terminology :

Following is the list of common terms used in FastLoad script.

LOGON − Logs into Teradata and initiates one or more sessions.

DATABASE − Sets the default database.

BEGIN LOADING − Identifies the table to be loaded.

ERRORFILES − Identifies the 2 error tables that needs to be created/updated.

CHECKPOINT − Defines when to take checkpoint.

SET RECORD − Specifies if the input file format is formatted, binary, text or unformatted.

DEFINE − Defines the input file layout.

FILE − Specifies the input file name and path.

INSERT − Inserts the records from the input file into the target table.

END LOADING − Initiates phase 2 of the FastLoad. Distributes the records into the target table.

LOGOFF − Ends all sessions and terminates FastLoad.

Limitations of Fastload :

It doesn’t support tables having secondary index, join index and foreign key references.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment