Teradata Parallel Transporter (TPT) LOAD is one the teradata utility to load data into teradata tables.

TPT Load Operator

Load operator is a data consumer operator which works similar to native Teradata Fastload utility. Teradata advises to use TPT Load over native Teradata Fastload utility as it is better optimised for performance over native load.

TPT Load can be used to
There are five data formats of files in which data can be imported using TPT.

Points to be noted for TPT Load:
  1. 'TYPE' of the operator should be defined as LOAD. In the 'example 1' below, 'Consumer_Table_Detail' is the place where TPT load is defined.
  2. Target table should be empty for TPT LOAD else job will fail with below error.
    TPT10508: RDBMS error 2636: [tablename] must be empty for Fast Loading.
    
  3. Basic attributes which are mandatory( that are UserName, UserPassword, TargetTable, TargetDatabase, LogTable and Tdpid) for functioning of TPT Load.
  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 Load.
  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 Load script(tpt_load_unformatted.tpt) for UNFORMATTED format file.
DEFINE JOB IMPORT_EMPLOYEE_DATA
Description 'TPT fastload script'
(
    DEFINE OPERATOR Consumer_Table_Detail
    TYPE LOAD
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetDatabase='tutorial_db',
        VARCHAR TargetTable='emp_unformatted',
        VARCHAR LogTable='emp_load_log',
        VARCHAR DateForm='AnsiDate'
    );
    
    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 IndicatorMode='N',
        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[3])
    SELECT * FROM OPERATOR (Producer_File_Detail);
); 

Example 2: Sample TPT Load script(tpt_load_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 LOAD
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='dept_delimited',
        VARCHAR LogTable='dept_pradeep_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 Load script(tpt_load_delimited_multiple_files.tpt) loading data from multiple files.
DEFINE JOB Import_Employee_Data
Description 'Test fastload script'
(
    DEFINE OPERATOR Consumer_Table_Detail
    TYPE LOAD
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR TdPid='dbc',
        VARCHAR UserName='tutorial_user',
        VARCHAR UserPassWord='tutorial_password',
        VARCHAR TargetTable='dept_delimited',
        VARCHAR LogTable='dept_pradeep_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,
        VARCHAR FileName,
        VARCHAR TextDelimiter,
        VARCHAR Format,
        VARCHAR OpenMode,
        VARCHAR IndicatorMode,
        VARCHAR DateForm
    );
    
    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
        ATTRIBUTES
        (
            DirectoryPath='$HOME/tpt/outfile',
            FileName='emp_delimited_src_1.out',
            TextDelimiter='|~|',
            Format='Delimited',
            OpenMode='Read',
            IndicatorMode='N',
            DateForm='AnsiDate'
        )
     )
    UNION ALL
    SELECT * FROM OPERATOR 
    (
        Producer_File_Detail
        ATTRIBUTES
        (
            DirectoryPath='$HOME/tpt/outfile',
            FileName='emp_delimited_src_2.out',
            TextDelimiter='|~|',
            Format='Delimited',
            OpenMode='Read',
            IndicatorMode='N',
            DateForm='AnsiDate'
        )
    );
);

Note: UNION ALL can be used in APPLY to load data from multiple source files.


Syntax 1: Executing unformatted Load script
tbuild -f tpt_load_unformatted.tpt

Syntax 2: Executing delimited Load script
tbuild -f tpt_load_delimited.tpt

Syntax 3: Executing load with multiple files script
tbuild -f tpt_load_delimited_multiple_files.tpt


Download script files