TPT Export Operator

Pradeep

TPT Export Operator

Export operator is a data producer operator which works similar to native Teradata Fastexport utility. Teradata advises to use TPT Export over native Teradata Fastexport utility as it is better optimised for performance over native export.

TPT Export can be used to

Export/Extract large volume data from Teradata database table to file/client.
Provide data to the consumer operators such as 'LOAD','UPDATE','STREAM' etc.

There are five data formats in which data can be exported using TPT.

Delimited
1. Most commonly used format in which data fields are separated by delimiter character/s.
2. Default delimiter is pipe '|'.
3. All the columns in the schema must be defined as 'VARCHAR', also columns must be casted to 'VARCHAR' in the query
4. If IndicatorMode is mentioned as the attribute and the format is defined 'DELIMITED' then TPT will fail with below error.
```
Define_Operator_Import: TPT19208 Attribute 'IndicatorMode' conflicts with data format 'DELIMITED'.
```
Text
1. Data is separated by fixed length characters in this format.
2. TPT will fail with below error if all the columns in schema and query 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 is best when loading between Teradata Systems.
Formatted In this format data is 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 Export:

'TYPE' of the operator should be defined as EXPORT. In the 'example 1' below, 'Producer_Query_Detail' is the place where TPT export is defined.
Four basic attributes are mandatory( that are UserName, UserPassword, SelectStmt and Tdpid) for functioning of TPT Export.
Degree of parallelism can be controlled by defining number of instances in the APPLY block ('2' in the 'example 1' below).
Consumer operator is defined in order to re-direct data produced by TPT export to file.
Attribute 'TenacityHours' is the number of hours job will try before failing if the resources are not available. Default value is 4 hours.
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.
Attribute DateForm specify which dataformat teradata should assume for date columns. 'AnsiDate' & 'IntegerDate' are 2 options and default is IntegerDate.
Be aware that if multiple instance of the consumer file is used then it will create number of outfiles equal to number of instances.
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 Export script(tpt_export_unformatted.tpt) for UNFORMATTED format.

DEFINE JOB EXPORT_EMPLOYEE_DATA
DESCRIPTION 'Exports employee data to a formatted file using EXPORT'
(
    DEFINE OPERATOR Consumer_File_Detail
    DESCRIPTION 'Defining a consumer operator for storing retrieved data to a file'
    TYPE DATACONNECTOR CONSUMER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR FileName='$HOME/tpt/outfile/emp_formatted.out',
        VARCHAR FORMAT= 'UNFORMATTED',
        VARCHAR OpenMode='Write'
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    DESCRIPTION 'Defining a Schema to describe the structure of the output file'
    (
         emp_no INTEGER,
         ename_name VARCHAR(50),
         hire_date ANSIDATE
    );
    
    DEFINE OPERATOR Producer_Query_Detail
    TYPE EXPORT
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
       VARCHAR UserName='tutorial_user',
       VARCHAR UserPAssword='tutorial_password',
       VARCHAR SelectStmt = 'SELECT emp_no,ename_name,hire_date FROM tutorial_db.employee;',
       VARCHAR Tdpid='dbc',
       INTEGER MaxSessions=6,
       INTEGER minsessions=2,
       VARCHAR DateForm='ANSIDATE'
    );
    
    APPLY 
    TO OPERATOR( Consumer_File_Detail )
    SELECT * FROM OPERATOR( Producer_Query_Detail[2] );
);

Example 2: Sample TPT Export script(tpt_export_formatted.tpt) for FORMATTED format.

DEFINE JOB EXPORT_EMPLOYEE_DATA
DESCRIPTION 'Exports employee data to a formatted file using EXPORT'
(
    DEFINE OPERATOR Consumer_File_Detail
    DESCRIPTION 'Defining a consumer operator for storing retrieved data to a file'
    TYPE DATACONNECTOR CONSUMER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR FileName='$HOME/tpt/outfile/emp_formatted.out',
        VARCHAR FORMAT= 'FORMATTED',
        VARCHAR OpenMode='Write'
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    DESCRIPTION 'Defining a Schema to describe the structure of the output file'
    (
         emp_no INTEGER,
         ename_name VARCHAR(50),
         hire_date ANSIDATE
    );
    
    DEFINE OPERATOR Producer_Query_Detail
    TYPE EXPORT
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
       VARCHAR UserName='tutorial_user',
       VARCHAR UserPAssword='tutorial_password',
       VARCHAR SelectStmt = 'SELECT emp_no,ename_name,hire_date FROM tutorial_db.employee;',
       VARCHAR Tdpid='dbc',
       INTEGER MaxSessions=6,
       INTEGER minsessions=2,
       VARCHAR DateForm='ANSIDATE'
    );
    
    APPLY 
    TO OPERATOR( Consumer_File_Detail )
    SELECT * FROM OPERATOR( Producer_Query_Detail[2] );
);

Example 3: Sample TPT Export script(tpt_export_delimited.tpt) for DELIMITED format.

DEFINE JOB EXPORT_EMPLOYEE_DATA
DESCRIPTION 'Exports employee data to a formatted file using EXPORT'
(
    DEFINE OPERATOR Consumer_File_Detail
    DESCRIPTION 'Defining a consumer operator for storing retrieved data to a file'
    TYPE DATACONNECTOR CONSUMER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR DirectoryPath='$HOME/tpt/outfile',
        VARCHAR FileName='emp_delimited.out',
        VARCHAR FORMAT= 'DELIMITED',
        VARCHAR OpenMode='Write',
        VARCHAR TextDelimiter='|~|'
    );
    
    DEFINE SCHEMA Define_Employee_Schema
    DESCRIPTION 'Defining a Schema to describe the structure of the output file'
    (
         emp_no VARCHAR(11),
         ename_name VARCHAR(50),
         hire_date VARCHAR(10)
    );
    
    DEFINE OPERATOR Producer_Query_Detail
    TYPE EXPORT
    SCHEMA Define_Employee_Schema
    ATTRIBUTES
    (
       VARCHAR UserName='tutorial_user',
       VARCHAR UserPAssword='tutorial_password',
       VARCHAR SelectStmt = 'SELECT CAST(emp_no AS VARCHAR(11)) emp_no,ename_name,CAST(hire_date AS VARCHAR(10)) hire_date FROM tutorial_db.employee;',
       VARCHAR Tdpid='dbc',
       INTEGER MaxSessions=6,
       INTEGER minsessions=2,
       INTEGER TenacityHours=2,
       INTEGER TenacitySleep=10
    );
    
    APPLY 
    TO OPERATOR( Consumer_File_Detail )
    SELECT * FROM OPERATOR( Producer_Query_Detail[2] );
);

Syntax 1: Executing formatted export script

tbuild -f tpt_export_formatted.tpt

Syntax 2: Executing delimited export script

tbuild -f tpt_export_delimited.tpt

Download script files

Teradata Parallel Transporter (TPT) EXPORT is one the teradata utility to extract data from teradata tables.

dbmstutorials.com

TPT Export Operator