Teradata FastLoad - Load CSV File

access_time 3 months ago visibility284 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 months 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

Want to publish your article on Kontext?

Learn more

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 SQL local_offer teradata-functions

visibility 331
thumb_up 0
access_time 9 months ago

In SQL Server, TRY_CAST function returns a value cast to a specified data type is the cast is successful or null is the cast is not successful.  In Teradata, the equivalent function is TRYCAST. -- return int 1030 SELECT TRYCAST('01030' AS int); -- return 2019-01-01 as it is a valid date ...

local_offer teradata local_offer SQL local_offer teradata-sql-query

visibility 119
thumb_up 0
access_time 3 months ago

JSON data type is supported in Teradata from version 15.10. Together with native JSON type, a number of JSON functions are added to support extracting values from JSON, shredding JSON, etc.  info The following code snippets use string literal to demonstrate the usage of these functions; you ...

local_offer pyspark local_offer spark-2-x local_offer teradata local_offer SQL Server local_offer spark-database-connect

visibility 7666
thumb_up 1
access_time 9 months ago

In my previous article about  Connect to SQL Server in Spark (PySpark) , I mentioned the ways to read data from SQL Server databases as dataframe using JDBC. We can also use JDBC to write data from Spark dataframe to database tables. In the following sections, I'm going to show you how to ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS