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

• Import/Load large volume of data from a file or Teradata database table to empty Teradata table.
• Recieves data from the producer operators such as 'DATACONNECTOR Consumer' etc.

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

• Delimited
  1. Most commonly used format in which data fields are separated by delimiter character/s in the file.
  2. All the columns in the schema must be defined as 'VARCHAR'.
• Text
  1. Field in data file should be separated by fixed length characters in this format.
  2. TPT will fail with below error if all the columns in schema are not defined as 'CHAR' datatype for 'TEXT' format.

     Define_Operator_Import: TPT19108 Data Format 'TEXT' requires all 'CHAR/ANSIDATE' schema.
     

• Unformatted This format does not have any delimiter and it is completely described by SCHEMA, this works if data file is exported from Teradata System using Unformatted format.
  
• Formatted When using this format field in data files must be prefixed by record length marker and followed by end of record marker.
  
  
• Binary Actual data is prefixed with 2 byte Integer in this format.

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

• TPT Load UnFormatted
• TPT Load Delimited
• TPT Load Multiple Files
• Delimited Data File
• Delimited Data Source File 1
• Delimited Data Source File 2