Teradata FastExport is a command utility tool that can transfer large amount of data from Teradata database to a file. One of the commonly used scenarios is to export data from a table or view to a text file and then load the export file into a different server.
Teradata environment
Follow the following series to install Teradata and utility tools if you don't have a Teradata environment to work with.
Ensure all utilities are installed.
Example table
An example table named TestDb.FASTLOAD_CSV3 has been created previously viaFastLoad. This tutorial will export this file as a text file.
infoDue to limitations in FastLoad, it can not be exported as a standard CSV file. If you want to export a standard CSV file, please use TPT.
The table includes the following records:
FastExport job
A typical FastExport job includes the following steps:
- Log on to Teradata Database.
- Retrieve the specified data from Teradata Database.
- Export the data to the specified file or OUTMOD routine on a client system.
- Log off of Teradata Database.
Create the job script file
Create a job file named export.fastexport. The content looks like the following:
.LOGTABLE TestDb.FASTLOAD_CSV3_LOG_TBL;
.LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
.BEGIN EXPORT;
.EXPORT OUTFILE F:\tmp\test_fastexport.csv MODE RECORD FORMAT TEXT;
SEL CAST(ID AS VARCHAR(10)) AS ID,
CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
Comments
FROM FASTLOAD_CSV3;
.END EXPORT;
.LOGOFF ;
The above statement export the data as text file.
Run FastExport script
Command fexpcan be used to run the script job:
fexp < export.fastexport
The following is the sample output:
========================================================================
= =
= FastExport Utility Release FEXP.16.10.00.03 =
= Platform WIN32 =
= PID 16984 =
= =
========================================================================
= =
= Copyright 1990-2017 Teradata Corporation. ALL RIGHTS RESERVED. =
= =
========================================================================
**** 12:06:28 UTY2411 Processing start date: MON SEP 14, 2020
========================================================================
= =
= Logon/Connection =
= =
========================================================================
0001 .LOGTABLE TestDb.FASTLOAD_CSV3_LOG_TBL;
0002 .LOGON 192.168.119.128/DBC,;
**** 12:06:29 UTY8400 Teradata Database Release: 16.10.00.03
**** 12:06:29 UTY8400 Teradata Database Version: 16.10.00.03
**** 12:06:29 UTY8400 Default character set: ASCII
**** 12:06:29 UTY8400 Current RDBMS has UDT support
**** 12:06:29 UTY8400 Current RDBMS has Large Decimal support
**** 12:06:29 UTY8400 Current RDBMS has Statement Info Parcel support
**** 12:06:29 UTY8400 Current RDBMS has FEXP w/o Spooling support
**** 12:06:29 UTY8400 Current RDBMS has TASM support
**** 12:06:29 UTY8400 Maximum supported buffer size: 1M
**** 12:06:29 UTY8400 Data Encryption supported by RDBMS server
**** 12:06:29 UTY6211 A successful connect was made to the RDBMS.
**** 12:06:29 UTY6217 Logtable 'TestDb.FASTLOAD_CSV3_LOG_TBL' has been created.
========================================================================
= =
= Processing Control Statements =
= =
========================================================================
0003 DATABASE TestDb;
**** 12:06:29 UTY1016 'DATABASE' request successful.
0004 .BEGIN EXPORT;
0005 .EXPORT OUTFILE F:\tmp\test_fastexport.csv MODE RECORD FORMAT TEXT;
0006
SEL CAST(ID AS VARCHAR(10)) AS ID,
CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
Comments
FROM TestDb.FASTLOAD_CSV3;
0007 .END EXPORT;
========================================================================
= =
= FastExport Initial Phase =
= =
========================================================================
**** 12:06:29 UTY8700 Options in effect for this FastExport task:
. Sessions: 4 session(s).
. Mode: RECORD
. Blocksize: 64330 bytes.
. Outlimit: No limit in effect.
**** 12:06:29 UTY8715 FastExport is submitting the following request:
Select NULL from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 220) and
(Seq = 1) and (FExptSeq = 0);
**** 12:06:29 UTY8715 FastExport is submitting the following request:
SET QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
CHECK WORKLOAD FOR BT;BEGIN FASTEXPORT;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
CHECK WORKLOAD FOR
SEL CAST(ID AS VARCHAR(10)) AS ID,
CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
Comments
FROM FASTLOAD_CSV3;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
CHECK WORKLOAD END;
**** 12:06:29 UTY0844 Session count 2 returned by the DBS overrides
user-requested session count.
**** 12:06:29 UTY8705 EXPORT session(s) requested: 2.
**** 12:06:29 UTY8706 EXPORT session(s) connected: 2.
**** 12:06:29 UTY8715 FastExport is submitting the following request:
BT;BEGIN FASTEXPORT;
**** 12:06:29 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from TestDb.FASTLOAD_CSV3_LOG_TBL
WHERE (LogType = 210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq)
from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 210) and (Seq = 1)));
**** 12:06:29 UTY8715 FastExport is submitting the following request:
SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq
(INTEGER), FExptCkpt (VARBYTE(1024)) from TestDb.FASTLOAD_CSV3_LOG_TBL
WHERE (LogType = 212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq)
from TestDb.FASTLOAD_CSV3_LOG_TBL where (LogType = 212) and (Seq = 1)));
**** 12:06:29 UTY8715 FastExport is submitting the following request:
SEL CAST(ID AS VARCHAR(10)) AS ID,
CAST(Col_Attr AS VARCHAR(1)) AS Col_Attr,
CAST(Col_Value AS VARCHAR(10)) AS Col_Value,
CAST(CreatedDate AS VARCHAR(10)) AS CreatedDate,
Comments
FROM TestDb.FASTLOAD_CSV3;
**** 12:06:29 UTY8724 Select request submitted to the RDBMS.
**** 12:06:29 UTY8725 Select execution completed. 2 data blocks generated.
**** 12:06:29 UTY8756 Retrieval Rows statistics:
Elapsed time: 00:00:00 (hh:mm:ss)
CPU time: 0.015625 Seconds
MB/sec: N/A
MB/cpusec: 0.0134888
**** 12:06:29 UTY8715 FastExport is submitting the following request:
INS TestDb.FASTLOAD_CSV3_LOG_TBL (LogType, Seq) VALUES (220, 1)
**** 12:06:29 UTY8715 FastExport is submitting the following request:
END FASTEXPORT;ET;
**** 12:06:30 UTY8710 Processing complete for this FastExport task.
========================================================================
= =
= FastExport Task Complete =
= =
========================================================================
**** 12:06:30 UTY1024 Session modal request, 'DATABASE', re-executed.
**** 12:06:30 UTY1024 Session modal request, 'SET
QUERY_BAND='UTILITYNAME=FASTEXP;' UPDATE FOR SESSION;', re-executed.
**** 12:06:30 UTY8722 4 total records written to output file.
0008 .LOGOFF ;
========================================================================
= =
= Logoff/Disconnect =
= =
========================================================================
**** 12:06:30 UTY6216 The restart log table has been dropped.
**** 12:06:30 UTY6212 A successful disconnect was made from the RDBMS.
**** 12:06:30 UTY2410 Total processor time used = '0.3125 Seconds'
. Start : 12:06:28 - MON SEP 14, 2020
. End : 12:06:30 - MON SEP 14, 2020
. Highest return code encountered = '0'.