Teradata FastLoad - Load CSV File

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

Create a sample CSV file named test_fastload.csv with the following content:

1,A,10,2020-09-13,This is a comment without comma.
2,B,20,2020-09-13,This is a comment without comma.
3,C,30,2020-09-13,This is a comment without comma.
4,D,40,2020-09-13,This is a comment without comma.

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:

ERRLIMIT 0;
LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
DROP TABLE FASTLOAD_CSV;
DROP TABLE FASTLOAD_CSV_ERR_1;
DROP TABLE FASTLOAD_CSV_ERR_2;
CREATE SET TABLE FASTLOAD_CSV
(
ID VARCHAR(10) NOT NULL,
Col_Attr VARCHAR(1),
Col_Value VARCHAR(10),
CreatedDate VARCHAR(10),
Comments VARCHAR(255)
)
UNIQUE PRIMARY INDEX(ID);

BEGIN LOADING FASTLOAD_CSV ERRORFILES FASTLOAD_CSV_ERR_1, FASTLOAD_CSV_ERR_2;
	SET RECORD VARTEXT DELIMITER ',';
	DEFINE	ID (VARCHAR(10)), 
			Col_Attr (VARCHAR(1)), 
			Col_Value(VARCHAR(10)), 
			CreatedDate (VARCHAR(10)),
			Comments (VARCHAR(255))
			FILE = test_fastload.csv;
   
	INSERT INTO FASTLOAD_CSV (ID, Col_Attr, Col_Value, CreatedDate, Comments) 
		VALUES (:ID, :Col_Attr, :Col_Value, :CreatedDate, :Comments) ; 
END LOADING;
LOGOFF ;

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:

fastload < load-csv.fastload

The output looks like the following:

     ===================================================================
     =                                                                 =
     =          FASTLOAD UTILITY     VERSION 16.10.00.02               =
     =          PLATFORM WIN32                                         =
     =          PID      6636                                          =
     =                                                                 =
     ===================================================================

     ===================================================================
     =                                                                 =
     =          Copyright 1984-2017, Teradata Corporation.             =
     =          ALL RIGHTS RESERVED.                                   =
     =                                                                 =
     ===================================================================

**** 22:06:23 Processing starting at: Sun Sep 13 22:06:22 2020

0001 ERRLIMIT 2;

**** 22:06:23 Error limit set to: 2

     ===================================================================
     =                                                                 =
     =          Logon/Connection                                       =
     =                                                                 =
     ===================================================================

0002 LOGON 192.168.119.128/DBC,

**** 22:06:23 Teradata Database Release: 16.10.00.03
**** 22:06:23 Teradata Database Version: 16.10.00.03
**** 22:06:23 Number of AMPs available: 2
**** 22:06:23 Current CLI or RDBMS allows maximum row size: 64K
**** 22:06:23 Character set for this job: ASCII

0003 DATABASE TestDb;

**** 22:06:24 Command completed successfully

0004 DROP TABLE FASTLOAD_CSV;

**** 22:06:24 RDBMS error 3807: Object 'FASTLOAD_CSV' does not exist.

0005 DROP TABLE FASTLOAD_CSV_ERR_1;

**** 22:06:24 RDBMS error 3807: Object 'FASTLOAD_CSV_ERR_1' does not
              exist.

0006 DROP TABLE FASTLOAD_CSV_ERR_2;

**** 22:06:24 RDBMS error 3807: Object 'FASTLOAD_CSV_ERR_2' does not
              exist.

0007 CREATE SET TABLE FASTLOAD_CSV
     (
     ID VARCHAR(10) NOT NULL,
     Col_Attr VARCHAR(1),
     Col_Value VARCHAR(10),
     CreatedDate VARCHAR(10),
     Comments VARCHAR(255)
     )
     UNIQUE PRIMARY INDEX(ID);

**** 22:06:27 Command completed successfully


0008 BEGIN LOADING FASTLOAD_CSV ERRORFILES FASTLOAD_CSV_ERR_1, FASTLOAD_CSV_ERR_
     2;

**** 22:06:27 Session count 2 returned by the DBS overrides
              user-requested session count
**** 22:06:27 Number of FastLoad sessions connected = 2
**** 22:06:27 FDL4808 LOGON successful
**** 22:06:28 Number of AMPs available: 2
**** 22:06:28 BEGIN LOADING COMPLETE

0009    SET RECORD VARTEXT DELIMITER ',';

**** 22:06:28 Now set to read 'Variable-Length Text' records
**** 22:06:28 Delimiter character(s) is set to ','
**** 22:06:28 Command completed successfully

0010    DEFINE  ID (VARCHAR(10)),
                        Col_Attr (VARCHAR(1)),
                        Col_Value(VARCHAR(10)),
                        CreatedDate (VARCHAR(10)),
                        Comments (VARCHAR(255))
                        FILE = test_fastload.csv;

**** 22:06:28 FDL4803 DEFINE statement processed


     ===================================================================
     =                                                                 =
     =          Insert Phase                                           =
     =                                                                 =
     ===================================================================

0011    INSERT INTO FASTLOAD_CSV (ID, Col_Attr, Col_Value, CreatedDate, Comments)
                VALUES (:ID, :Col_Attr, :Col_Value, :CreatedDate, :Comments) ;

**** 22:06:28 Number of recs/msg: 220
**** 22:06:28 Starting to send to RDBMS with record 1
**** 22:06:28 Sending row 4
**** 22:06:28 Finished sending rows to the RDBMS

**** 22:06:28 Acquisition Phase statistics:
              Elapsed time: 00:00:00 (in hh:mm:ss)
              CPU time:     0 Seconds
              MB/sec:       N/A
              MB/cpusec:    N/A

     ===================================================================
     =                                                                 =
     =          End Loading Phase                                      =
     =                                                                 =
     ===================================================================

0012 END LOADING;

**** 22:06:28 END LOADING COMPLETE

     Total Records Read              =  4
     Total Error Table 1             =  0  ---- Table has been dropped
     Total Error Table 2             =  0  ---- Table has been dropped
     Total Inserts Applied           =  4
     Total Duplicate Rows            =  0

     Start:   Sun Sep 13 22:06:28 2020
     End  :   Sun Sep 13 22:06:28 2020

**** 22:06:28 Application Phase statistics:
              Elapsed time: 00:00:00 (in hh:mm:ss)

0013 LOGOFF ;

     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 22:06:28 Logging off all sessions
**** 22:06:28 Total processor time used = '0.25 Seconds'
     .        Start : Sun Sep 13 22:06:22 2020
     .        End   : Sun Sep 13 22:06:28 2020
     .        Highest return code encountered = '0'.
**** 22:06:28 FDL4818 FastLoad Terminated

Verify the data

Run the following SQL statement to verify the result:

SELECT * FROM TestDb.FASTLOAD_CSV;

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:

.LOGON 192.168.119.128/DBC,DBC
     ===================================================================
     =                                                                 =
     =          Logon/Connection                                       =
     =                                                                 =
     ===================================================================

0001 .LOGON 192.168.119.128/DBC,

**** 22:32:55 Teradata Database Release: 16.10.00.03
**** 22:32:55 Teradata Database Version: 16.10.00.03
**** 22:32:55 Number of AMPs available: 2
**** 22:32:55 Current CLI or RDBMS allows maximum row size: 64K
**** 22:32:55 Character set for this job: ASCII

References

Teradata FastLoad Reference