Teradata Parallel Transporter aka TPT is new generation utility tool aim to create one stop tool for all the activities related to loading and exporting of date from/to Teradata database. Teradata advise to prefer TPT over existing utilities such as fastload, fastexport, multiload & tpump.
Some of the operation that can be performed using TPT:DEFINE JOB [tpt_job_name] DESCRIPTION '[description of tpt job, description statement is optional]' ( DEFINE OPERATOR [operator_name] [Define operator statements & attribute]; DEFINE SCHEMA [schema_name] [Define operator statements & attribute]; APPLY [apply statements to for defined operators]; );
USING CHARACTER SET UTF8 DEFINE JOB [tpt_job_name] DESCRIPTION '[description of tpt job, description statement is optional]' ( DEFINE OPERATOR [operator_name] [Define operator statements & attribute]; DEFINE SCHEMA [schema_name] [Define operator statements & attribute]; APPLY [apply statements to for defined operators]; );
USING CHARACTER SET UTF8 DEFINE JOB [tpt_job_name] DESCRIPTION '[description of tpt job, description statement is optional]' ( INCLUDE '$HOME/tpt/schema/external_schema_file.schema'; DEFINE OPERATOR [operator_name] [Define operator statements & attribute]; APPLY [apply statements to for defined operators]; );
APPLY ('DML Statement') TO OPERATOR (tpt_consumer_operator_name1 [n]) SELECT * FROM (OPERATOR tpt_producer_operator_name); Note: 1. 'n' specify the number of instances of each operator to control the degree of parallelism. 2. Supports multiple data sources using the UNION ALL query. 3. Supports multiple data targets using multiple APPLY clauses. 4. Supports derivation of new columns using CASE clause.
DEFINE JOB [tpt_job_name] DESCRIPTION '[description of tpt job, description statement is optional]' ( DEFINE OPERATOR [operator_name] [Define operator statements & attribute]; DEFINE SCHEMA [schema_name] [Define operator statements & attribute]; STEP [step_name] ( APPLY ( [apply statements to for defined operators (insert into table] [other required statements] ) ); );
DEFINE JOB [tpt_job_name] DESCRIPTION '[description of tpt job, description statement is optional]' ( DEFINE OPERATOR [operator_name] [Define operator statements & attribute]; DEFINE SCHEMA [schema_name] [Define operator statements & attribute]; STEP [step_name1] ( APPLY [executable statements such as drop table] [executable statements such as create table] [other required statements] ); STEP [step_name2] ( APPLY ( [apply statements to for defined operators (insert into table)] [other required statements] ) ); );
Operator Type | Description |
DATACONNECTOR PRODUCER | This operator used as a producer to get data from source. |
DATACONNECTOR CONSUMER | This operator used as a consumer to push data to target. |
DDL | This is standalone operator for perfomring DDL/DML operation. |
EXPORT | Export operator works similar to Teradata FASTEXPORT. |
FASTEXPORT OUTMOD | FastExport OUTMOD Adapter operator works similar to Teradata FASTEXPORT OUTMOD. |
FASTLOAD INMOD | FastLoad INMOD Adapter operator works similar to Teradata FASTLOAD INMOD. |
INSERTER | Consumer operator that to insert data into given table. |
LOAD | Load operator works similar to Teradata FASTLOAD. |
MULTILOAD INMOD | MultiLoad INMOD Adapter operator works similar to Teradata MULTILOAD INMOD. |
ODBC | This operator allows to read data from ODBC compliant data sources. |
OS COMMAND | This is standalone operator for executing OS commands. |
SELECTOR | Producer operator to retrieve data from a given table. |
STREAM | Stream operator works similar to Teradata TPUMP. |
UPDATE | Update operator works similar to Teradata MULTILOAD. |
DEFINE OPERATOR [tpt_required_operator_name] DESCRIPTION '[desc of operator, desc statement is optional]' TYPE [Operator type ] SCHEMA [* or actual tpt_schema_name] ATTRIBUTES ( VARCHAR attribute_name1 [optionally can assign value here with "='atrribute_value'"] INTEGER attribute_name2 ); Note: 1. Attributes name defined should be valid for the operator. 2. '*' defer schema identification until run time.
DEFINE OPERATOR define_consumer_operator TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR UserName, VARCHAR UserPassword, VARCHAR LogTable, VARCHAR TargetTable, VARCHAR Tdpid );
DEFINE SCHEMA [tpt_schema_name] DESCRIPTION '[description of schema, description statement is optional]' ( column1 VARCHAR(11), column3 DATE), column2 INTEGER ); Note: If export/import format of the file is delimited with some character/pattern then all the fields in schema should be defined as VARCHAR only.
DEFINE SCHEMA Define_Schema_structure ( dept_no INTEGER, department_name VARCHAR(30), loc_name VARCHAR(30) );