fbpx

This article demonstrates how to efficiently 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 that allows you 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. You need to define the parameter TargetTable, which will be associated with the $INSERT template. The $FILE_READER template receives its 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

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

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

You might also like

>