Teradata FastLoad is a command line utility that can be used to load large amount of data into an empty table on Teradata database. The performance will be greater than line by line or batch processing mechanism.
This article provides example of using FastLoad to load CSV file into Teradata database.
Example CSV file
SAS/ACCESS works by interfacing with the Load operator through the TPT API, which in turn uses the Teradata Fastload protocol for loading data. See your Teradata documentation for more information about the Load operator. This is the default FastLoad method. This takes Teradata utility slots, which might also cause other load jobs to fail. Specifying the FASTLOAD= data set option on a SAS library hides it from users. In a business intelligence environment, it might be difficult to determine whether this option is specified.
Create a sample CSV file named test_fastload.csv with the following content:
Create FastLoad job script
Create a FastLoad job script that performs the following actions:
- Logon to the database;
- Drop target table;
- Create target table;
- Load data into the target table;
To implement this, create a FastLoad script file named load-csv.fastload. The content looks like the following:
To run FastLoad job, the two error tables should not exist and also the target table should be empty.
The above script recreate the target table each time thus the logon user needs to have CREATE TABLE permission on the target database. It also sets the input format as variable text file with delimiter as ','.
Run FastLoad script
Command fastload can be used to run the script file:
The output looks like the following:
Verify the data
Run the following SQL statement to verify the result:
The result set looks like this:
Interactive mode
We can also run fastload using interactive mode. To do this, type fastload command in Command Prompt:
And then input each command with prefix '.'.
For example, use '.LOGON …' to logon to the database:
References
Teradata FastLoad Reference
- Teradata Tutorial
- Teradata Basics
- Teradata Advanced
- Teradata Useful Resources
- Selected Reading
FastLoad utility is used to load data into empty tables. Since it does not use transient journals, data can be loaded quickly. It doesn't load duplicate rows even if the target table is a MULTISET table.
Limitation
Target table should not have secondary index, join index and foreign key reference.
How FastLoad Works
FastLoad is executed in two phases.
![Teradata fastload user guide Teradata fastload user guide](https://i2.wp.com/aryan333.com//wp-content/uploads/2019/03/31-1.png?resize=960%2C613&ssl=1)
To run FastLoad job, the two error tables should not exist and also the target table should be empty.
The above script recreate the target table each time thus the logon user needs to have CREATE TABLE permission on the target database. It also sets the input format as variable text file with delimiter as ','.
Run FastLoad script
Command fastload can be used to run the script file:
The output looks like the following:
Verify the data
Run the following SQL statement to verify the result:
The result set looks like this:
Interactive mode
We can also run fastload using interactive mode. To do this, type fastload command in Command Prompt:
And then input each command with prefix '.'.
For example, use '.LOGON …' to logon to the database:
References
Teradata FastLoad Reference
- Teradata Tutorial
- Teradata Basics
- Teradata Advanced
- Teradata Useful Resources
- Selected Reading
FastLoad utility is used to load data into empty tables. Since it does not use transient journals, data can be loaded quickly. It doesn't load duplicate rows even if the target table is a MULTISET table.
Limitation
Target table should not have secondary index, join index and foreign key reference.
How FastLoad Works
FastLoad is executed in two phases.
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.
Sas Teradata Fastload
Example
Create a text file with the following records and name the file as employee.txt.
Following is a sample FastLoad script to load the above file into Employee_Stg table.
Executing a FastLoad Script
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.
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.
Teradata Fastload Download
FastLoad Terms
Following is the list of common terms used in FastLoad script.
Reference Manual Definition
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.