Teradata Parallel Transporter (Teradata PT) supports moving data from a table on a Teradata Database system, or from an ODBC-compliant RDBMS, to a Teradata table on a different Teradata Database system without landing the data to disk.
There are performance benefits, cost savings, and ease of script maintenance associated with using Teradata PT to move data without landing it to disk.
Performance benefits
Unlike the traditional Teradata utilities, Teradata PT can start data exporting and data loading at the same time without having to wait for data exporting to complete. A Teradata PT producer operator, such as the Export operator, can export data from a table and send it to a Teradata PT data stream. A Teradata PT consumer operator, such as the Load operator, can consume the data from the data stream and load it into a Teradata Database table. Data streams consist of in-memory buffers where data is passed from a producer to a consumer operator.
A user of the traditional Teradata utilities can run the Teradata FastExport utility to export data to a disk file and then the Teradata FastLoad utility to load data from the disk file to an empty Teradata Database table. If a user FastExports data into a named pipe, the user can then use FastLoad to read the data from the named pipe.
We should add that when two scripts are needed to move data, there is always the possibility of inadvertently running FastLoad, for example, before FastExport.
Cost savings
When a very large amount of data (greater than 50 gigabytes) is moved through a Teradata PT data stream without landing the data to disk, a Teradata PT user saves on the cost of purchasing additional disk drives. A user can use the disk space that was used for some other purpose.
Single-script maintenance
Unlike the traditional Teradata utilities, a single Teradata PT job script can be coded to move data without landing it to disk. If a user needs to make a change to the Teradata PT job, the user only needs to change the Teradata PT job script.
Traditional Teradata utilities require two scripts: one FastExport script to export the data and one script to load the data.
Sample scripts
Here are two sample Teradata PT job scripts.
In each script, the Export operator extracts the data from the SOURCE_EMP_TABLE table and the Load operator loads the data into an empty table called TARGET_EMP_TABLE.
Both scripts use job variables. But the second script uses more job variables than the first, in addition to concatenating quoted scripts and job variables.
Sample Script 1
DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
DESCRIPTION ‘MOVE DATA WITHOUT LANDING THE DATA TO DISK’
(
/*** Schema Definition ***/
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION ‘SAMPLE EMPLOYEE SCHEMA’
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
/*** Export Operator Definition ***/
DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION ‘TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR’
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = ‘exportoper_privatelog’,
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TdpId = @SourceTdpId,
VARCHAR UserName = @SourceUserName,
VARCHAR UserPassword = @SourceUserPassword,
VARCHAR SelectStmt = ‘SELECT * FROM SOURCE_EMP_TABLE;’
);
/*** Load Operator Definition ***/
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION ‘TERADATA PARALLEL TRANSPORTER LOAD OPERATOR’
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = ‘loadoper_privatelog’,
INTEGER MaxSessions = 16,
INTEGER MinSessions,
VARCHAR TargetTable = ‘TARGET_EMP_TABLE’,
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR ErrorTable1 = ‘LOADOPER_ERRTABLE1’,
VARCHAR ErrorTable2 = ‘LOADOPER_ERRTABLE2’,
VARCHAR LogTable = ‘LOADOPER_LOGTABLE’
);
/*** Apply Statement ***/
APPLY
(‘INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);’)
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);
Sample job variable file for Sample Script 1
SourceTdpId = ‘MySourceTdpid’
,SourceUserName = ‘MySourceUserName’
,SourceUserPassword = ‘MySourceUserPassword’
,TargetTdpId = ‘MyTargetTdpId’
,TargetUserName = ‘MyTargetUserName’
,TargetUserPassword = ‘MyTargetUserPassword’
Sample Script 2
DEFINE JOB MOVE_DATA_WITHOUT_LANDING_TO_DISK
DESCRIPTION ‘MOVE DATA WITHOUT LANDING THE DATA TO DISK’
(
/*** Schema Definition ***/
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION ‘SAMPLE EMPLOYEE SCHEMA’
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
/*** Export Operator Definition ***/
DEFINE OPERATOR EXPORT_OPERATOR
DESCRIPTION ‘TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR’
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = ‘exportoper_privatelog’,
INTEGER MaxSessions = 8,
INTEGER MinSessions,
VARCHAR TdpId = @SourceTdpId,
VARCHAR UserName = @SourceUserName,
VARCHAR UserPassword = @SourceUserPassword,
VARCHAR SelectStmt = ‘SELECT * FROM ‘ || @SourceTable || ‘;’
);
/*** Load Operator Definition ***/
DEFINE OPERATOR LOAD_OPERATOR
DESCRIPTION ‘TERADATA PARALLEL TRANSPORTER LOAD OPERATOR’
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = ‘loadoper_privatelog’,
INTEGER MaxSessions = 16,
INTEGER MinSessions,
VARCHAR TargetTable = @TargetTable,
VARCHAR TdpId = @TargetTdpId,
VARCHAR UserName = @TargetUserName,
VARCHAR UserPassword = @TargetUserPassword,
VARCHAR ErrorTable1 = @ErrorTable1,
VARCHAR ErrorTable2 = @ErrorTable2,
VARCHAR LogTable = @LogTable
);
/*** Apply Statement ***/
APPLY
(‘INSERT INTO ‘ || @TargetTable || ‘ (:EMP_ID, :EMP_NAME);’)
TO OPERATOR (LOAD_OPERATOR [1])
SELECT * FROM OPERATOR (EXPORT_OPERATOR [2]);
);
Sample job variable file for Sample Script 2
SourceTdpId = ‘MySourceTdpid’
,SourceUserName = ‘MySourceUserName’
,SourceUserPassword = ‘MySourceUserPassword’
,TargetTdpId = ‘MyTargetTdpId’
,TargetUserName = ‘MyTargetUserName’
,TargetUserPassword = ‘MyTargetUserPassword’
,SourceTable = ‘SOURCE_EMP_TABLE’
,TargetTable = ‘TARGET_EMP_TABLE’
,ErrorTable1 = ‘LOADOPER_ERRTABLE1’
,ErrorTable2 = ‘LOADOPER_ERRTABLE2’
,LogTable = ‘LOADOPER_LOGTABLE’
For information on the concatenation of quoted strings and job variables, see Teradata Parallel Transporter User Guide.
HERNK'S JOURNAL 