Teradata FastLoad - Load CSV File

access_time 8 days ago visibility15 comment 0

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

info Last modified by Administrator at 3 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer teradata-utilities

visibility 18
thumb_up 0
access_time 8 days ago

Teradata Parallel Transporter (TPT) provides rich functions to load data into Teradata and to export data. In article Load CSV into Teradata via TPT , it shows how to load CSV files into Teradata....

local_offer Azure local_offer teradata local_offer teradata-tool

visibility 939
thumb_up 0
access_time 3 years ago

This page provides the steps to create a Teradata database in Microsoft cloud computing platform Azure.

local_offer teradata local_offer teradata-utilities

visibility 6
thumb_up 0
access_time 8 days ago

BTEQ is a Teradata utility tool that can be used to run Teradata SQL statements incl. DDL, DML, etc. It can also be used to import data from text file into Teradata databases. It also works with XML and JSON files too. Like TPT and FASTLOAD, it can run in both batch and interactive modes. T...

About column

Teradata

Tutorials and information about Teradata.

rss_feed Subscribe RSS