** FastLoad
- FastLoad is known for its lightning-like speed in loading vast amounts of data from flat files from a host into empty tables in Teradata. Part of this speed is achieved because it does not use the Transient Journal.
- It is because FastLoad assembles data into 64K blocks (64,000 bytes) to load it and can use multiple sessions simultaneously, taking further advantage of Teradata's parallel processing.
- The target table is empty, this method is typically the fastest way to load data. FastLoad will never attempt to insert rows into a table that already holds data.
- Supports only a few SQL commands that may be used with this utility : Create Table, Drop Table, Delete and Insert
** FastLoad Limitations:
Rule #1: No Secondary Indexes are allowed on the Target Table. High performance will only allow FastLoad to utilize Primary Indexes when loading.
Rule #2: No Referential Integrity is allowed. FastLoad cannot load data into tables that are defined with Referential Integrity (RI). This would require too much system checking to prevent referential constraints to a different table. FastLoad only does one table.
# In short, RI constraints will need to be dropped from the target table prior to the use of FastLoad.
Rule #3: No Triggers are allowed at load time. FastLoad is much too focused on speed to pay attention to the needs of other tables, which is what Triggers are all about. Additionally, these require more than one AMP and more than one table. FastLoad does one table only.
# Simply ALTER the Triggers to the DISABLED status prior to using FastLoad.
Rule #4: Duplicate Rows (in Multi-Set Tables) are not supported. Multiset tables are tables that allow duplicate rows — that is when the values in every column are identical. When FastLoad finds duplicate rows, they are discarded. While FastLoad can load data into a multi-set table, FastLoad will not load duplicate rows into a multi-set table because FastLoad discards duplicate rows!
FastLoad requires 3 key components:
a. Log Table b. Empty Target Table c. Two Error Tables
a :=> FastLoad needs a place to record information on its progress during a load. It uses the table called Fastlog in the SYSADMIN database. This table contains one row for every FastLoad running on the system
c :=> Each FastLoad requires 2 error tables and which will automatically create them for you; all you must do is to name them.
The first error table is for any translation errors or constraint violations. For example, a row with a column containing a wrong data type would be reported to the first error table.
The second error table is for errors caused by duplicate values for Unique Primary Indexes (UPI).
The Teradata RDBMS will only run a maximum number of fifteen FastLoads, MultiLoads, or FastExports at the same time. This maximum is determined by a value stored in the DBS Control record. It can be any value from 0 to 15.
FastLoad Has Two Phases:
a. Phase I or Acquisition b. Phase II or Application
a. Phase I – Acquisition
•The main objective of this phase is to send rows of data file from HOST computer to Teradata AMPs as fast as possible.
•Rows of the data file are packed into 64k blocks and send it to PE.
•PE will parse the SQL of Fastload and send the Explain plan to each AMP. It will by default create 1 session per AMP, so if your system has 200 AMP then it will make 200 sessions for 1 Fastload job.
# - It is advisable to restrict the session with the help of .SESSION command so that Fastload job don’t end up taking all the available resources of the system.
•After creation of sessions the 64k blocks data is passed to AMP with the help of PE and BYNET where it is quickly hashed according to its PI value.
•Based on this row hash value the rows are then redistributed to its proper AMP. Internal redistribution takes place within AMP, so that each AMP gets the correct row. To know more about this redistribution based on row hash please refer –Primary Index in Teradata
•Now each row is placed in its proper AMP, but they are not sorted till now.
•Any error in this phase will be recorded in the Error table 1.
b. Phase II – Application
•The main objective of this phase is to store each row into the actual target table.
•The rows are sorted by AMP which is temporarily stored in its DISK during phase 1
•These sorted rows then send to actual target table where they will reside permanently.
•All these operation are BLOCK level operation thus giving more speed when we compare it ROW level operation.
•Any error in this phase will be stored in Error table 2.
I. LOGON to TD System
II. Describe the INPUT FILE structure in the DEFINE statement
III. BEGIN LOADING statement, the script must name the target table and the two error tables for the load.
# There is no CREATE TABLE statement for the error tables in this script. Bcz, FastLoad will automatically create them for you once you name them in the script. Phase 1 uses "Err_tbl1" because it comes first and Phase 2 uses "Err_tbl2"
IV. SQL Insert statement
V. END LOADING
VI. LOGOFF
Notes:
The utility commands in FastLoad are similar to those in BTEQ. FastLoad commands were designed from the underlying commands in BTEQ. However, unlike BTEQ, most of the FastLoad commands do not allow a dot ["."] in front of them and therefore need a semicolon.
Types of record modes: a. FastLoad, b. Unformatted,
c. Binary, d. Text e. Variable Text.
Syntax:
.SET RECORD a*/b/c/d/e ['|' only for e ].
# The default is the Teradata RDBMS standard, FastLoad.
Syntax for BEGIN LOAD:
BEGIN LOADING [dbname.]table_name
ERRORFILES [dbname.]err_tbl1, [dbname.]err_tbl2,
[,CHECKPOINT n]
[,INDICATORS];
CHECKPONT and INDICATORS are subordinate commands in the BEGIN LOADING clause of the script.
CP: Fastload has the ability to save checkpoints during the loading process. Checkpoints are what enable utilities to pick up from where they left off if the loading process was interrupted in any way.
calc : CP= 64000/sum(row size)
- If CHECKPOINT wasn't specified, then CHECKPOINT defaults to 0 (no checkpoint). You can perform a manual restart using the RECORD statement. If the output print file shows that record 100000 was read, use something like the following command: [RECORD 100001;]. This statement will skip records 1 through 100000 and resume on record 100001
IN: INDICATORS is a keyword related to how FastLoad handles nulls in the input file. It identifies columns with nulls and uses a bitmap at the beginning of each row to show which fields contain a null instead of data. When the INDICATORS option is on, FastLoad looks at each bit to identify the null column. The INDICATORS option does not work with VARTEXT.
Few commands:
SESSIONS - It is written in the script just before the logon. The default is 1 session per available AMP. The purpose of multiple sessions is to enhance throughput when loading large volumes of data.
RECORD- Specifies the beginning record number (or with THRU, the ending record number) of the Input data source, to be read by FastLoad.
SLEEP- Specifies the amount of time in minutes to wait before retrying to logon and establish all sessions. This situation can occur if all of the loader slots are used or if the number of requested sessions are not available. The default is 6 minutes.
TENACITY- Specifies the amount of time, in hours, to retry to obtain a loader slot or to establish all requested sessions to logon. The default for FastLoad is "no tenacity",meaning that it will not retry at all. If several FastLoad jobs are executed at the same time, we recommend setting the TENACITY to 4, meaning that the system will continue trying to logon for the number of sessions requested for up to four hours.
ERRLIMIT- Specifies the maximum number of rejected ROWS allowed in error table 1 (Phase I).
Sample Script:
/** additional session ***/
TENACITY 4; /* the default is no tenacity, means no retry */
SLEEP 10; /* the default is 6, means retry in 6 minutes */
# - Tenacity is set to 4 hr; Wait 10 Min between retries.
/***** Section 1 *****/
/* In this section we give the LOGIN credentials which is required to connect to TD system. Sessions command is used to restrict the number of sessions Fastload will make to connect to TD. Default is one session per AMP. */
.logon 127.0.0.1/tduser,tduser
/***** Section 2 *****/
/* In this section we are defining the table which we want to load from Fastload. DROP commands are optional. There is no need to define the structure of ERROR tables they’ll be created itself by Fastload. */
drop table samples.empf;
drop table samples.err01;
drop table samples.err02;
create table samples.empf
(
eno varchar(5)
,ename varchar(5)
,dept varchar(5)
);
/***** Section 3 *****/
/*RECORD command is used to skip the starting rows from the data file. RECORD THRU command is used to skip the last rows of data file. SET RECORD command is used to define the records layout and the “,” is the delimiter which we are using in our data file to separate columns. */
.RECORD 1;
.RECORD THRU 2;
.SET RECORD VARTEXT "|";
/***** Section 4 *****/
/* DEFINE statement is used to define the structure of the data file. This should be in accordance with the actual target table structure. Fastload DEFINE statement allows only VARCHAR format. */
define
Inp_eno (varchar(5))
,Inp_ename (varchar(5))
,Inp_dept (varchar(5))
/***** Section 5 *****/
/* FILE command defines the data file path and name. */
file=C:\td\fl\empf_data_1.txt;
/***** Section 6 *****/
/* In this section we give the BEGIN loading statement. As soon as Fastload receives this statement it starts PHASE 1. */
begin loading
samples.empf
errorfiles samples.err01,samples.err02;
/***** Section 7 *****/
/* INSERT command is used to load the data file into actual target table. NOTE – For DATE columns we can use the data conversion by the syntax given below. */
insert into samples.empf
values (
:Inp_eno
,:Inp_ename
,:Inp_dept
);
/***** Section 8 *****/
/* END LOADING ends PHASE 1 and starts the execution of PHASE 2. LOGOFF is required to close all the sessions created by Fastload. */
end loading;
.logoff;
==================
** RESTART FastLoad :
- There are two types of FastLoad scripts: those that you can restart and those that you cannot without modifying the script.
- If any of the following conditions are true of the FastLoad script that you are dealing with, it is NOT restartable:
a. The Error Tables are DROPPED
b. The Target Table is DROPPED
c. The Target Table is CREATED
- If all of the following conditions are true, then FastLoad is ALWAYS restartable:
a. The Error Tables are NOT DROPPED in the script
b· The Target Table is NOT DROPPED in the script
c· The Target Table is NOT CREATED in the script
No comments:
Post a Comment