DWHPro Insights

How to Load a Flat File into an Empty Table with Teradata TPT: A Simple Example

By Roland Wenzlofsky ·

How to Load a Flat File into an Empty Table with Teradata TPT: A Simple Example

This article illustrates loading a flat file into an empty Teradata table using TPT. The example was successfully tested on Teradata 16.20.

Although TPT does offer a wide range of advanced options for loading files, it can be overwhelming for basic tasks. In this demonstration, I will present a simplified approach to loading compatible delimited files with a matching schema and supported quoting, encoding and field widths into a Teradata target table.

We assume that the target table is present and empty.

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

Here is a TPT script example. Define the TargetTable parameter, which corresponds to the $INSERT template. The parameter file provides details about the flat file to the $FILE_READER template. Additionally, the parameter file includes the logon definition.

/* 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 = 'DWHPRO.FlatFile';
  SET LogTable = @TargetTable || '_LOG';

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

Using Fastload, we can load compatible delimited files after checking schema, quoting, encoding and field-width rules. The FileReaderTextDelimiter can define the separator. In our case, we use “;” instead of the default pipe character that TPT expects. The file we use as an example is shown below.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
a;b
c;d
e;f

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

TPTLoad

TPT templates simplify operator definitions. This script explicitly selects $LOAD and requires an empty target. Use a dedicated empty staging table; do not delete production rows to satisfy this requirement. The separate tdload tool can choose LOAD or UPDATE from target state. An adapted tdload fixture loaded two rows successfully; this exact tbuild template was not rerun unchanged. Protect the variables file containing credentials with owner-only permissions.

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

Building or improving a data pipeline?

Discuss ingestion, transformations, orchestration, quality checks and operational handover.

Explore data engineering services

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam