Teradata Parallel Transporter (TPT) Stream is one the teradata utility to load data into empty or populated teradata tables.

TPT Stream Operator

Stream operator is a data consumer operator which works similar to native Teradata Tpump utility. Teradata advises to use TPT Stream over native Teradata Tpump utility as it is better optimised for performance over native load and also old utilities are not enhanced in new version.
TPT Stream apply locks at the row hash level and not on the entire table.

TPT Stream Characteristics:
There are five data formats of files in which data can be imported using TPT.

Points to be noted for TPT Stream:
  1. 'TYPE' of the operator should be defined as STREAM. In the 'example 1' below, 'Consumer_Table_Detail' is the place where TPT stream is defined.
  2. Target table can be empty or populated for TPT Stream.
  3. Basic attributes which are mandatory( that are UserName, UserPassword, TargetTable, TargetDatabase, LogTable and Tdpid) for functioning of TPT Stream.
  4. Degree of parallelism can be controlled by defining number of instances in the APPLY block ('3' in the 'example 1' below).
  5. Producer operator is defined in order to pull data from file which will be used by TPT Stream.
  6. Attribute 'TenacityHours' is the number of hours job will try before failing if the resources are not available. Default value is 4 hours.
  7. Attribute 'TenacitySleep' is the number of minutes after which system will try to check for required resources and it must be defined along with 'TenacityHours'. Default value is 6 minutes.
  8. Attribute 'SkipRows' is number of records that need to be skipped from the input file.
    INTEGER SkipRows=2,
    
  9. Attribute 'SkipRowsEveryFile' can have either 'Y' & 'N' values and must be used complementary to 'SkipRows'. If multiple files are being processed and SkipRowsEveryFile is defined as 'Y' then it will skip rows defined in SkipRows attribute from all the files.
    VARCHAR SkipRowsEveryFile='N'
    
  10. Attribute DateForm specify which dataformat teradata should assume for date columns. 'AnsiDate' & 'IntegerDate' are 2 options and default is IntegerDate.
  11. Careful! always define maximum number of sessions(using MaxSessions) that can be allocated to the job, else it may end up taking all the sessions.

Example 1: Sample TPT Stream script(tpt_stream_unformatted.tpt) for UNFORMATTED format file.
DEFINE JOB Import_Employee_Data
Description 'Test fastload script'
(
    DEFINE OPERATOR Consumer_Table_Detail
    TYPE STREAM
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetDatabase='tutorial_db',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_stream_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no INTEGER,
        ename_name VARCHAR(50),
        hire_date ANSIDATE
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='$HOME/tpt/outfile',
        VARCHAR FileName='emp_unformatted.out',
        VARCHAR Format='UNFORMATTED',
        VARCHAR OpenMode='Read',
        VARCHAR DateForm='AnsiDate'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_unformatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(Consumer_Table_Detail)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);

Example 2: Sample TPT Stream script(tpt_stream_delimited.tpt) for DELIMITED format file with skipping initial records.
DEFINE JOB Import_Employee_Data
Description 'Test fastload script'
(
    DEFINE OPERATOR Consumer_Table_Detail
    TYPE STREAM
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetDatabase='tutorial_db',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_stream_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no VARCHAR(11),
        ename_name VARCHAR(50),
        hire_date VARCHAR(10)
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='$HOME/tpt/outfile',
        VARCHAR FileName='tpt_load_delimited_data_file.out',
        VARCHAR TextDelimiter='|~|',
        VARCHAR Format='Delimited',
        VARCHAR OpenMode='Read',
        VARCHAR IndicatorMode='N',
        VARCHAR DateForm='AnsiDate',
        INTEGER SkipRows=2,
        VARCHAR SkipRowsEveryFile='N'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_delimited(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(Consumer_Table_Detail)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);

Example 3: Sample TPT Stream script(tpt_stream_formatted.tpt) loading data for FORMATTED format
DEFINE JOB Import_Employee_Data
Description 'Test fastload script'
(
    DEFINE OPERATOR Consumer_Table_Detail
    TYPE STREAM
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetDatabase='tutorial_db',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='dept_tpt_stream_log',
        VARCHAR DateForm='AnsiDate',
        INTEGER MaxSessions=6
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    (
        emp_no INTEGER,
        ename_name VARCHAR(50),
        hire_date ANSIDATE
    );

    DEFINE OPERATOR Producer_File_Detail
    TYPE DATACONNECTOR PRODUCER
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='$HOME/tpt/outfile',
        VARCHAR FileName='emp_formatted.out',
        VARCHAR Format='FORMATTED',
        VARCHAR OpenMode='Read',
        VARCHAR DateForm='AnsiDate'
    );
    
    APPLY
    (
       'INSERT INTO tutorial_db.emp_formatted(emp_no,ename_name,hire_date) VALUES (:emp_no,:ename_name,:hire_date);'
    )
    TO OPERATOR(Consumer_Table_Detail)
    
    SELECT * FROM OPERATOR (Producer_File_Detail);
);


Syntax 1: Executing unformatted TPT Stream script
tbuild -f tpt_stream_unformatted.tpt

Syntax 2: Executing delimited TPT Stream script
tbuild -f tpt_stream_delimited.tpt

Syntax 3: Executing formatted TPT Stream script
tbuild -f tpt_stream_formatted.tpt


Download script files