Teradata TPT – High-Speed Bulk Loading a Flat File

DWH Pro Admin

November 1, 2020

minutes reading time


This article demonstrates how to load a flat file into an empty table using Teradata TPT. The example was tested on Teradata 16.20.

TPT offers a lot of advanced possibilities to load a file but is often overstraining for simple tasks. The example I will show you here uses a simplified method to load flat files with any number of columns and a selectable separator into a target table on Teradata.

We assume that the target table exists and is empty:

CREATE TABLE DWHPRO.FlatFile
(
	ColA CHAR(10),
	ColB CHAR(10)
) PRIMARY INDEX (ColA);

Below is the example TPT script. It would help if you defined the parameter TargetTable, which will be associated with the $INSERT template. The $FILE_READER template receives information about the flat file from a parameter file. Further, the logon is defined in the parameter file:

/* Parameter file dwhprovars.txt */
/********************************************************/
/* TPT LOAD Operator setting                            */
/********************************************************/
 LoadTdpId                  = '127.0.0.1'
,LoadUserName               = 'tuning'
,LoadUserPassword           = '********'
,LoadPrivateLogName         = 'LOAD_OPERATOR_LOG'
,LoadTraceLevel             = 'None'

/********************************************************/
/* TPT DataConnector Producer Operator setting          */
/********************************************************/
,FileReaderFileName         = 'dwhpro.txt'
,FileReaderFormat           = 'Delimited'
,FileReaderOpenmode         = 'Read'
,FileReaderDirectoryPath    = './'
,FileReaderTextDelimiter    = ';'
,FileReaderPrivateLogName   = 'FILE_READER_LOG'
/* Script SimpleFlatFile */
DEFINE JOB FlatFile
DESCRIPTION 'Load a flat file'
(

  SET TargetTable = 'FlatFile';
  SET LogTable = @TargetTable || '_LOG';

  STEP MAIN_STEP
  (
    APPLY $INSERT TO OPERATOR ( $LOAD [1] )
    SELECT * FROM OPERATOR ( $FILE_READER [1] );
  );
);

With this setup, we can load any flat file with a separator using Fastload. The separator can be defined using FileReaderTextDelimiter. In our example, we use “;” – by default, the TPT expects a pipe character. Below is our example file:

a;b
c;d
e;f

tbuild -f SimpleFlatFile -v dwhprovars.txt -j dwhpro

Teradata TPT - High-Speed Bulk Loading a Flat File 1

As we have seen, TPT templates are a way to do simple tasks using TPT without writing complex scripts. TPT can switch automatically from Fastload to Multiload if the target table is not empty. Still, this functionality is not supported in our example with templates. Therefore the user has to delete all rows in advance.

What are TPT Templates?

TPT templates are the textual definition of operators and can be included easily, as shown above. For example, $FILE_READER defines a DATACONNECTOR PRODUCER operator. The templates can be found in the following path in the Linux file system:

/opt/teradata/client/16.20/tbuild/template

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>