Teradata fast load:-
Fast load is use when we want to load large
volume of data into the empty Teradata table with the very fast speed. This is
usually use for initial load. Mostly when you want to load data initially from
other system to Teradata empty table, Teradata fast load is very useful tool.
Fast load is design to load millions of records in Teradata table in very less
time compare to other Teradata methods.
Advantages
of Teradata FastLoad:-
1)
FastLoad is very fast compare to other
load utilities
2)
Can load data from various sources
3)
Checkpoint restart
4)
Direct loading
5)
Automatic data allocation to the nodes
Limitations
to FastLoad:-
1)
The target table must be empty
2)
We cannot read or write data while loading
3)
Only a single table can be loaded in one job
4)
No secondary table are allowed on
target table
5)
No referential integrity is allowed
6)
No triggers are allowed on target
tables
7) Does
not support duplicate rows
Fast load always has three requirements
1)
Empty table
2)
Two error tables
3)
Log table
What is Empty table?
It is a target table where we want to load
data. This table must be empty before inserting rows into the table. It will
show error if the table is already having any records.
What are two error tables?
Fast load requires two error tables to log
errors which occur during the execution of the fast load. Fast load create the
error tables itself, all we need to do is to specify the name of the error
tables. Both the tables records error of the specific type. First error table
records any transaction or any constraint violation error. For example you have
specified the data type of DECIMAL and you are trying to insert data of varchar
type then it will log that record in the error table. Similarly, if you forgot
to convert the date data type in the proper format then this error will log
into the error table 1.
Error table 2 records the error about duplicate
values. If you are trying to insert duplicate values for unique primary index
then this record will be logged into the error table 2.
What is log table?
This is the table which gets created in the
sysadmin database automatically and holds all the progress happening while the
execution of fast load. Mostly the name of this table is fastload.
There are mainly two phases of fast load
1)
Acquisition phase
2)
Application phase
What is acquisition phase?
This is the phase where row from the data file
from the host system are send to the Teradata AMP (Access module process) very
fast. Rows of the data are packed into 64K blocks and send it to Teradata PE
(Parse engine). PE will create a execution plan and send it to each AMP. If
there are 100 AMP’s on your system then it will create 100 sessions. It is
suggested that restrict the session as per your requirement. Until now, each row is store in proper AMP
but they are still not sorted. Any error in this phase is recorded by error
table 1.
What is application phase?
The main motive of this phase is to store each
row in the actual table. The rows are sorted by AMP’s which were not sorted in
phase 1. These rows are sorted out in phase 2 and will send to actual target
table where they will resides permanently.
Any error in this phase will be recorded in error table 2.
We
can understand Teradata fast load utility with the help of below diagram:-
Below is the sample Fast load script. Save it
on a particular location:-
Note:-
Execute fast load script”-
Unlike Mload we can execute fast load. We just
need to replace the command “Mload”
to “fastload”. Command is as shown
below:-
Fastload <infile> log_file
After executing above command you can see log
file on the above specified location. Below is the part of the log file:-
We
need to ensure that there is highest return code encounter as 0 and also don’t
forget to cross verify by selecting records from that table. You can see the
summation or the count of total number of column that you have inserted. We can cross verify this with the help of BTEQ.
Read Also:-
No comments:
Post a Comment