Loading data into Teradata can be done quickly and fairly easily using the Teradata FastLoad utility, but the utility isn’t exactly friendly to new users. This article provides a quick introduction into the FastLoad utility, which is included in the free Teradata Tools & Utilities (TTU) package downloadable from teradata.com. FastLoad can do many more advanced things, but we’ll just cover the most basic usage in this article.
If you don’t already have the Teradata Tools & Utilities installed, visit http://downloads.teradata.com/download and click “TTU – Windows Install”. The FastLoad utility is also available on Teradata installations and comes with Teradata Express VMs.
When you start FastLoad, you’ll instantly see why it’s not the friendliest utility for beginners. It’s simply a command prompt, waiting for proprietary FastLoad commands.
FastLoad can be executed interactively as seen above, but the simplest method of execution is to create a text file that contains the commands and queries that will be executed, and to pass that script file to FastLoad to run in non-interactive mode. As our example, we will create a FastLoad script to create and populate a simple table called “footable” that will contain a BIGINT column “bar” and a VARCHAR(50) column “baz”.
An important note: FastLoad will only load data into empty tables. In our example, we will create a new table. If you need to load into an existing table, you can add SQL statements to the beginning of the script to either drop & recreate the table or use a DELETE statement to delete all rows from the table.
Our script will go through the following phases: establishing a connection to Teradata, creating the target table, defining the input file format, loading the data, ending the loading phase, then logging off. The input file format we’ll use for this example is a formatted Teradata export file, though FastLoad will also accept unformatted, binary, text, and variable-length text files. The formatted file option simplifies things a bit, since delimiters are not required.
First, establish a connection to Teradata.
LOGON host/user,pass;
Create the destination table. You could optionally drop the table before this line.
CREATE TABLE foodb.footable (
bar BIGINT NOT NULL,
baz VARCHAR(50))
UNIQUE PRIMARY INDEX (bar);
Configure the job to accept the formatted input file “footable.formatted” and show debugging information about the specified format.
SET RECORD FORMATTED;
DEFINE
f_bar (BIGINT),
f_baz (VARCHAR(50))
FILE = footable.formatted;
SHOW;
Begin the loading phase and specify the error tables that will be used. Don’t let the “ERRORFILES” name fool you– these are tables, not simple files. If the job fails for whatever reason, these tables (Error_1 and Error_2) may need to be dropped before the job is restarted. These table names must not conflict with existing tables.
BEGIN LOADING foodb.footable ERRORFILES Error_1, Error_2;
Insert the data into the table. Note that the data is actually staged at this point. The destination table will not be populated until after the loading phase has been ended, which will be triggered by the next line. Progress information will be shown periodically during this phase.
INSERT INTO foodb.footable (bar, baz) VALUES (:f_bar, :f_baz);
End the loading phase, which will cause Teradata to actually populate the destination table with the data that has been staged during the loading phase. This might take a long time (hours in some cases), depending on the size of the job, so don’t be surprised if the job appears to have frozen at this point. Unfortunately FastLoad does not print progress information for this stage of the job.
END LOADING;
Finally, quit the job and terminate the connection to Teradata. At this point, the data will be populated into the destination table.
LOGOFF;
The following is the script in full. It can be executed by saving as “footable.fastload”, then running “fastload
LOGON host/user,pass;
CREATE TABLE foodb.footable (
bar BIGINT NOT NULL,
baz VARCHAR(50))
UNIQUE PRIMARY INDEX (bar);
SET RECORD FORMATTED;
DEFINE
f_bar (BIGINT),
f_baz (VARCHAR(50))
FILE = footable.formatted;
SHOW;
BEGIN LOADING foodb.footable ERRORFILES Error_1, Error_2;
INSERT INTO foodb.footable (bar, baz) VALUES (:f_bar, :f_baz);
END LOADING;
LOGOFF;
For more information about FastLoad, see the Teradata FastLoad documentation, available at http://www.info.teradata.com/edownload.cfm?itemid=110590003.
