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:
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 bookFor 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.
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
Will you please make a post of how to use mini-batches with tpt. ?