DWHPro Insights

How to Export and Upload Teradata Tables with TPT Scripts: A Guide

By Roland Wenzlofsky ·

How to Export and Upload Teradata Tables with TPT Scripts: A Guide

These are adapted TPT templates. The export operator shown is SELECTOR, not bulk EXPORT. A four-row local-file export/import round trip on one Teradata system passed bidirectional MINUS comparisons; cross-system networking, restart and throughput were not tested.

Choose load method from row width, workload, network and utility resource limits. A fixed row-count threshold is a heuristic, not an engine limit.

The following example creates an unformatted export file in Teradata format with indicators.

Teradata TPT Export Script

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a unformatted file'  ( DEFINE SCHEMA FILE_SCHEMA  (
"COL1" CHAR(18)
,"COL2" VARCHAR(198)
,"COL3" DECIMAL(4,0)
,"COL4" INTEGER
);  
DEFINE OPERATOR SQL_SELECTOR 
TYPE SELECTOR SCHEMA FILE_SCHEMA ATTRIBUTES  
(
  VARCHAR PrivateLogName = 'selector_log',  
  VARCHAR TdpId = 'TD_SERVER', 
  VARCHAR UserName = 'dwhpro',  VARCHAR UserPassword = 'xxxxxx', 
  VARCHAR SelectStmt = 'SELECT * FROM DWHPRO.SourceTable;'  
);  
DEFINE OPERATOR FILE_WRITER TYPE DATACONNECTOR CONSUMER SCHEMA *  ATTRIBUTES  
( 
  VARCHAR PrivateLogName = 'dataconnector_log', 
  VARCHAR DirectoryPath = './data/',  
  VARCHAR FileName = 'test.txt',  
  VARCHAR Format = 'UNFORMATTED',  
  VARCHAR IndicatorMode = 'Y',  
  VARCHAR OpenMode = 'Write'
);  
APPLY TO OPERATOR (FILE_WRITER)
SELECT * FROM OPERATOR (SQL_SELECTOR);  
);

Teradata TPT Import Script

USING CHARACTER SET UTF8
DEFINE JOB IMPORT_DELIMITED_FILE  DESCRIPTION 
'Import rows to a Teradata target table'  
(  
  DEFINE OPERATOR Consumer_Table_Detail TYPE LOAD SCHEMA * ATTRIBUTES 
  (  
   VARCHAR TdpId = 'TD_Server',  
   VARCHAR UserName = 'dwhpro',  VARCHAR UserPassword = 'xxxxxx',  
   VARCHAR TargetDatabase = 'DWHPRO',  
   VARCHAR TargetTable = 'DWHPRO.TargetTable',  
   VARCHAR LogTable = 'target_table_log',  
   VARCHAR DateForm = 'AnsiDate'
  ); DEFINE SCHEMA Define_Target_Schema   
(
"COL1" CHAR(18)
,"COL2" VARCHAR(198)
,"COL3" DECIMAL(4,0)
,"COL4" INTEGER
);  

DEFINE OPERATOR Producer_File_Detail TYPE DATACONNECTOR PRODUCER SCHEMA Define_Target_Schema ATTRIBUTES 
(  
  VARCHAR DirectoryPath = './data',  
  VARCHAR Filename = 'test.txt',  
  VARCHAR Format = 'UNFORMATTED',  
  VARCHAR OpenMode = 'Read',  
  VARCHAR IndicatorMode = 'Y',  
  VARCHAR DateForm = 'AnsiDate'
); 
APPLY ('INSERT INTO DWHPRO.TargetTable(
COL1
,COL2
,COL3
,COL4
) VALUES (
:COL1
,:COL2
,:COL3
,:COL4
); ') 
TO OPERATOR (Consumer_Table_Detail) 
SELECT * FROM OPERATOR (Producer_File_Detail););

For a comprehensive understanding of Teradata TPT’s capabilities, please refer to the following guide:

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

For the UTF-8 fixture, the original six-byte CHAR field was too short and failed TPT02638/TPT12108. The corrected TPT schemas use CHAR(18) and VARCHAR(198) for the corresponding source fields in both export and import. Derive transport byte widths from source metadata and character sets; SQL character counts are not transport byte counts. NULLs, negatives, commas/quotes and non-ASCII text survived this fixture, with zero load error-table rows.

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.

1 thought on “How to Export and Upload Teradata Tables with TPT Scripts: A Guide”

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