Teradata Parallel Transporter ( TPT )
What is TPT ?
TPT stands for Teradata parallel transporter. As the name implies “Parallel Transporting”.
This is the new generation Load/unload utility provided by teradata.
This act’s as a integrated ETL suite which helps to EXTRACT data from multiple source, apply theTRANSFORMATION logic and LOAD the data in target Teradata database.
TPT has all the features to run the stand alone teradata load/unload utilities i.e (mload, tpump, fastexport, fastload) .
The TPT operator equivalent to standalone utilities are listed below
Mload=Update
Tpump=Stream
Fastload=Load
FastExport=Export
As per the info the legacy utilities i.e (mload, tpump, fastexport, fastload) . will still continue to work as before and support latest teradata version but any new features addition in load/unload utility will be done on TPT.
TPT is a utility tool of teradata and has all the capabilities of ETL along the with features of other teradata load/unload utilities.
In simple terms TPT can be described as nutshell of all teradata standalone utilities along with additional features.
Architecture of Teradata Parallel Transporter
Why use TPT ?
TPT is a flexible and high performance load/unload tool for the Teradata Database that supports ODBC source operators.
TPT efficiently transports vast amounts of data using core data warehousing fundamentals of parallelism and scalability.
Unlike conventional utilities and products in which multiple data sources are usually processed in a serial manner, Teradata Parallel Transporter can access multiple data sources in parallel. This ability can lead to increased throughput. Teradata Parallel Transporter also allows different specifications for different data sources and, if their data is UNION-compatible, merges them together.”
The current teradata protocol i.e Mload, Tpump, Fastload, Fastexport has all the features integrated in one single utility(TPT) : POWER OF ONE
Below text as per teradata website
“Simplified data transfer between one Teradata Database and another; only one script is required to export from the production-and-load test system.
Ability to load dozens of files using a single script makes development and maintenance of the data warehouse easier.
Distribution of workloads across CPUs on the load server eliminates bottlenecks in the data load process.
Data flows through multiple instances of UPDATE OPERATOR and in-memory data streams to update tables.
Option is available to export data to in-memory data stream instead of landing data.
The open database connectivity (ODBC) operator reads from the ODBC driver, which could pull data from any database; for example, DB2 or Oracle.
Accessibility to myriad data sources via open standards is possible.
Multiple operators can scan directories for files to load and can combine the data in the in-memory data stream with UNION ALL operation and stream operator loads.
Script-building wizard is available to aid first-time users”
Combine export and load functions into a single job
The TPT script can be created in step so that incase of failure of job the job process can be restarted from the step where it has failed.
We can scale a load job using parallel load streams from multiple input files.
Automatically load files from multiple directories.
Combine an export and load into a single job.
Load data directly from any database with the plug-in Access Modules
Symbolic substitution for the script language to determine job behavior at runtime.
Creating generic scripts that minimize maintenance cost and maximizes reusability.
Since the four utilities have converged into Teradata Parallel Transporter, only one scripting language is used. This simplicity creates new opportunities for the user, such as:
Solving more complex load scenarios.
Switching between load protocols more easily.
Unlimited symbol substitution
Fewer required scripts.
Simultaneous Teradata-to-Teradata export and load scenario.
When to use TPT ?
TPT can be used to load/unload data in teradata database environment
Since this is a new generation utility which has an integrated feature of all standalone teradata load/unload utility, as per teradata all the new advancement and features will be done on TPT rather then old utilities therefore it is recommended to develop all the new application based on TPT rather than working on old standalone utilities.
Any organization/Individual interested in increased performance, improved ease of use, and a better interface for ETL then TPT is the best way to start with , It provides the optimum use of teradata feature like parallelism ,Scalability and capability to outperform in a datawarehouse environment.
When extracting large amounts of data from external non-Teradata databases.
ODBC operator is recommended when running TPT scripts and utilities without requirement for a third party ETL/ELT adapter. TPT does support SQL-like transformations
Important Features of TPT
The most important feature of TPT is its Scalability and Parallelism behavior.
Scalability & Performance Features of Parallel Transporter
Below are some of the features of Parallel Transporter that can be used to increase load throughput:
Parallel input file processing.
Sometimes splitting the I/O processing up over multiple input files will help overcome a performance bottleneck.
Directory scan feature – A single Parallel Transporter script can have multiple processes read multiple files in multiple directories.
Multiple processes reading the same input file – in some cases having multiple read processes reading the same file can increase data throughput.
Multiple processes sending data to the Teradata Database – performance can be improved by splitting the CPU time across multiple processes that are manipulating the data and then loading buffers to be sent to the Teradata Database.
Multiple processes exporting the same table.
How to use TPT ?
Parallel Transporter can be invoked through 4 interfaces:
Application Program Interface (API) – used by leading ETL vendors for tight, parallel, high-performance integration
Script – used when a customer doesn’t have an ETL tool
Command line (sometimes referred to as the Easy Loader interface) – used to load data from a flat file with a single command line
Wizard – used to generate simple scripts. Use this tool as a way to learn the script language and not as a production load interface.
TPT Using Script
Creating a script.
The structure of TPT script is as mentioned below
Define the job name for the script
Provide the description of job
Schema Defination
Operator Defination #1
Operator Defination #2
….
….
Operator Defination #n
Apply definition
It also requires an job Variable file.
Sample template :
DEFINE JOB load_sample_country
(
DEFINE SCHEMA countries_schema @LoadTargetTable;
APPLY $INSERT @LoadTargetTable
TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($EXPORT(countries_schema));
);
Here is the job variable file – jobvar.txt
LoadPrivateLogName = ‘loadoper_private_log’,
LoadTargetTable = ‘sample_country’,
LoadWorkingDatabase = ‘dbname’,
LoadErrorTable1 = ‘e1__sample_country’,
LoadErrorTable2 = ‘e2_sample_country’,
LoadLogTable = ‘log_sample_country’,
LoadMaxSessions = 5,
LoadMinSessions = 1,
ExportPrivateLogName = ‘exportoper_private_log’,
ExportMaxSessions = 5,
ExportMinSessions = 1,
ExportSelectStmt = ‘select * from dbname.countries;’,
TargetUserName = ‘username’,
TargetUserPassword = ‘password’,
TargetTdpId = ‘ipaddress’,
SourceUserName = ‘username’,
SourceUserPassword = ‘password’,
SourceTdpId = ‘ipaddress’
Command for a TPT script.
Running the Script : tbuild -f <scriptname> -v <jobvariablename>
Pausing the Script :
Stoping the Script :
Viewing the logs :
How to execute Macro/SP inside TPT ?
We can execute macro/SP inside the DDL operator, Its used to execute DDL task but with enhancement capabilities of TPT scripting this feature of execution was extended in DDL operator itself.
The DDL Operator
The DDL Operator is a standalone operator that allows DDL and other SQL statements to perform activities such as CREATE, ALTER, DROP, GIVE, GRANT, REVOKE, INSERT, UPDATE, DELTE and INSERT/SELECT.
Statements not supported are SELECT, HELP and SHOW.
All SQL or DDL commands must be hard-coded into the SQL statements and are submitted to TPT as part of the APPLY clause.
SCRIPT OF EXECUTION
DEFINE JOB CREATE_TABLE
(
DEFINE OPERATOR DDL_OPERATOR
DESCRIPTION ‘TPT DDL OPERATOR’
TYPE DDL
ATTRIBUTES(
VARCHAR TDPID
VARCHAR USERNAME
VARCHAR PASSWORD
);
APPLY
(‘EXEC TDWORLD_MACRO;’)
TO OPERATOR (DDL_OPERATOR());
);
Sample TPT scripts.
GT11
/**************************************************************/
/* */
/* Script Name: GT11.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script exports 10 rows from a Teradata table */
/* and loads the 10 rows into a different, empty */
/* Teradata table. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses two job steps. */
/* */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source and target tables. */
/* */
/* The second job step called "load_data" uses the Export */
/* Operator to export the rows from a Teradata source table */
/* and uses the Load Operator to load the rows into an empty */
/* Teradata target table. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and load the rows into */
/* a different, empty Teradata table without landing the */
/* rows to a data file. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT11.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB BACKUP_EMPLOYEE_TABLE
DESCRIPTION 'BACKUP SAMPLE EMPLOYEE TABLE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR LOAD_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_loadoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TargetTable = 'TARGET_EMP_TABLE',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR ErrorTable1 = 'GT11_LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'GT11_LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'GT11_LOADOPER_LOGTABLE'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
STEP setup
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('DROP TABLE TARGET_EMP_TABLE;'),
('DROP TABLE GT11_LOADOPER_ERRTABLE1;'),
('DROP TABLE GT11_LOADOPER_ERRTABLE2;'),
('DROP TABLE GT11_LOADOPER_LOGTABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP load_data
(
APPLY
('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
TO OPERATOR (LOAD_OPERATOR () [1] )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
);
GT44
/**************************************************************/
/* */
/* Script Name: GT44.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script exports 10 rows from a Teradata table */
/* to a file. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses two job steps. */
/* */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source table. */
/* */
/* The second job step called "export_to_file" uses the */
/* Export Operator to export the rows from a Teradata table */
/* and uses the Data Connector Operator to write the rows to */
/* a file. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and write the rows to */
/* a file. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT44.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB EXPORT_EMPLOYEE_TABLE_TO_FILE
DESCRIPTION 'EXPORT SAMPLE EMPLOYEE TABLE TO A FILE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_dataconnoper_privatelog',
VARCHAR FileName = 'GT44_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Unformatted'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
STEP setup
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP export_to_file
(
APPLY TO OPERATOR (FILE_WRITER() )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
);
GT62
/**************************************************************/
/* */
/* Script Name: GT62.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script loads 10 records from a file into an */
/* empty Teradata table. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses three job steps. */
/* */
/* The first job step called "setup_tables" uses the DDL */
/* Operator to setup the source and target tables. */
/* */
/* The second job step called "export_to_file" uses the */
/* Export Operator to export the rows from a Teradata table */
/* and uses the Data Connector Operator to write the rows to */
/* a file. */
/* */
/* The third job step called "load_data_from_file" uses the */
/* Data Connector Operator to read records from a file and */
/* uses the Load Operator to load the records into an empty */
/* Teradata table. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and write the rows to */
/* a file. */
/* * Read records from a file and load the records into an */
/* empty Teradata table. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT62.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE
DESCRIPTION 'LOAD SAMPLE EMPLOYEE TABLE FROM A FILE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_dataconnoper_writer_privatelog',
VARCHAR FileName = 'GT62_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Formatted'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
DEFINE OPERATOR LOAD_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_loadoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TargetTable = 'TARGET_EMP_TABLE',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR ErrorTable1 = 'GT62_LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'GT62_LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'GT62_LOADOPER_LOGTABLE'
);
DEFINE OPERATOR FILE_READER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_dataconnoper_reader_privatelog',
VARCHAR FileName = 'GT62_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Read',
VARCHAR Format = 'Formatted'
);
STEP setup_tables
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('DROP TABLE TARGET_EMP_TABLE;'),
('DROP TABLE GT62_LOADOPER_ERRTABLE1;'),
('DROP TABLE GT62_LOADOPER_ERRTABLE2;'),
('DROP TABLE GT62_LOADOPER_LOGTABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP setup_export_to_file
(
APPLY TO OPERATOR (FILE_WRITER() [1] )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
STEP load_data_from_file
(
APPLY
('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
TO OPERATOR (LOAD_OPERATOR () [1] )
SELECT * FROM OPERATOR (FILE_READER() [1] );
);
);
Sample job variable file for Sample Script 1
SourceTdpId = ‘MySourceTdpid’
,SourceUserName = ‘MySourceUserName’
,SourceUserPassword = ‘MySourceUserPassword’
,TargetTdpId = ‘MyTargetTdpId’
,TargetUserName = ‘MyTargetUserName’
,TargetUserPassword = ‘MyTargetUserPassword’
Comparision of TPT with other Teradata utilities
1) TPT v/s Mload (Update)
2) TPT v/s TPUMP (Stream)
3) TPT v/s Fastload (Load)
4) TPT v/s FastExport (Export)
What is TPT ?
TPT stands for Teradata parallel transporter. As the name implies “Parallel Transporting”.
This is the new generation Load/unload utility provided by teradata.
This act’s as a integrated ETL suite which helps to EXTRACT data from multiple source, apply theTRANSFORMATION logic and LOAD the data in target Teradata database.
TPT has all the features to run the stand alone teradata load/unload utilities i.e (mload, tpump, fastexport, fastload) .
The TPT operator equivalent to standalone utilities are listed below
Mload=Update
Tpump=Stream
Fastload=Load
FastExport=Export
As per the info the legacy utilities i.e (mload, tpump, fastexport, fastload) . will still continue to work as before and support latest teradata version but any new features addition in load/unload utility will be done on TPT.
TPT is a utility tool of teradata and has all the capabilities of ETL along the with features of other teradata load/unload utilities.
In simple terms TPT can be described as nutshell of all teradata standalone utilities along with additional features.
Architecture of Teradata Parallel Transporter
Why use TPT ?
TPT is a flexible and high performance load/unload tool for the Teradata Database that supports ODBC source operators.
TPT efficiently transports vast amounts of data using core data warehousing fundamentals of parallelism and scalability.
Unlike conventional utilities and products in which multiple data sources are usually processed in a serial manner, Teradata Parallel Transporter can access multiple data sources in parallel. This ability can lead to increased throughput. Teradata Parallel Transporter also allows different specifications for different data sources and, if their data is UNION-compatible, merges them together.”
The current teradata protocol i.e Mload, Tpump, Fastload, Fastexport has all the features integrated in one single utility(TPT) : POWER OF ONE
Below text as per teradata website
“Simplified data transfer between one Teradata Database and another; only one script is required to export from the production-and-load test system.
Ability to load dozens of files using a single script makes development and maintenance of the data warehouse easier.
Distribution of workloads across CPUs on the load server eliminates bottlenecks in the data load process.
Data flows through multiple instances of UPDATE OPERATOR and in-memory data streams to update tables.
Option is available to export data to in-memory data stream instead of landing data.
The open database connectivity (ODBC) operator reads from the ODBC driver, which could pull data from any database; for example, DB2 or Oracle.
Accessibility to myriad data sources via open standards is possible.
Multiple operators can scan directories for files to load and can combine the data in the in-memory data stream with UNION ALL operation and stream operator loads.
Script-building wizard is available to aid first-time users”
Combine export and load functions into a single job
The TPT script can be created in step so that incase of failure of job the job process can be restarted from the step where it has failed.
We can scale a load job using parallel load streams from multiple input files.
Automatically load files from multiple directories.
Combine an export and load into a single job.
Load data directly from any database with the plug-in Access Modules
Symbolic substitution for the script language to determine job behavior at runtime.
Creating generic scripts that minimize maintenance cost and maximizes reusability.
Since the four utilities have converged into Teradata Parallel Transporter, only one scripting language is used. This simplicity creates new opportunities for the user, such as:
Solving more complex load scenarios.
Switching between load protocols more easily.
Unlimited symbol substitution
Fewer required scripts.
Simultaneous Teradata-to-Teradata export and load scenario.
When to use TPT ?
TPT can be used to load/unload data in teradata database environment
Since this is a new generation utility which has an integrated feature of all standalone teradata load/unload utility, as per teradata all the new advancement and features will be done on TPT rather then old utilities therefore it is recommended to develop all the new application based on TPT rather than working on old standalone utilities.
Any organization/Individual interested in increased performance, improved ease of use, and a better interface for ETL then TPT is the best way to start with , It provides the optimum use of teradata feature like parallelism ,Scalability and capability to outperform in a datawarehouse environment.
When extracting large amounts of data from external non-Teradata databases.
ODBC operator is recommended when running TPT scripts and utilities without requirement for a third party ETL/ELT adapter. TPT does support SQL-like transformations
Important Features of TPT
The most important feature of TPT is its Scalability and Parallelism behavior.
Scalability & Performance Features of Parallel Transporter
Below are some of the features of Parallel Transporter that can be used to increase load throughput:
Parallel input file processing.
Sometimes splitting the I/O processing up over multiple input files will help overcome a performance bottleneck.
Directory scan feature – A single Parallel Transporter script can have multiple processes read multiple files in multiple directories.
Multiple processes reading the same input file – in some cases having multiple read processes reading the same file can increase data throughput.
Multiple processes sending data to the Teradata Database – performance can be improved by splitting the CPU time across multiple processes that are manipulating the data and then loading buffers to be sent to the Teradata Database.
Multiple processes exporting the same table.
How to use TPT ?
Parallel Transporter can be invoked through 4 interfaces:
Application Program Interface (API) – used by leading ETL vendors for tight, parallel, high-performance integration
Script – used when a customer doesn’t have an ETL tool
Command line (sometimes referred to as the Easy Loader interface) – used to load data from a flat file with a single command line
Wizard – used to generate simple scripts. Use this tool as a way to learn the script language and not as a production load interface.
TPT Using Script
Creating a script.
The structure of TPT script is as mentioned below
Define the job name for the script
Provide the description of job
Schema Defination
Operator Defination #1
Operator Defination #2
….
….
Operator Defination #n
Apply definition
It also requires an job Variable file.
Sample template :
DEFINE JOB load_sample_country
(
DEFINE SCHEMA countries_schema @LoadTargetTable;
APPLY $INSERT @LoadTargetTable
TO OPERATOR ($LOAD)
SELECT * FROM OPERATOR ($EXPORT(countries_schema));
);
Here is the job variable file – jobvar.txt
LoadPrivateLogName = ‘loadoper_private_log’,
LoadTargetTable = ‘sample_country’,
LoadWorkingDatabase = ‘dbname’,
LoadErrorTable1 = ‘e1__sample_country’,
LoadErrorTable2 = ‘e2_sample_country’,
LoadLogTable = ‘log_sample_country’,
LoadMaxSessions = 5,
LoadMinSessions = 1,
ExportPrivateLogName = ‘exportoper_private_log’,
ExportMaxSessions = 5,
ExportMinSessions = 1,
ExportSelectStmt = ‘select * from dbname.countries;’,
TargetUserName = ‘username’,
TargetUserPassword = ‘password’,
TargetTdpId = ‘ipaddress’,
SourceUserName = ‘username’,
SourceUserPassword = ‘password’,
SourceTdpId = ‘ipaddress’
Command for a TPT script.
Running the Script : tbuild -f <scriptname> -v <jobvariablename>
Pausing the Script :
Stoping the Script :
Viewing the logs :
How to execute Macro/SP inside TPT ?
We can execute macro/SP inside the DDL operator, Its used to execute DDL task but with enhancement capabilities of TPT scripting this feature of execution was extended in DDL operator itself.
The DDL Operator
The DDL Operator is a standalone operator that allows DDL and other SQL statements to perform activities such as CREATE, ALTER, DROP, GIVE, GRANT, REVOKE, INSERT, UPDATE, DELTE and INSERT/SELECT.
Statements not supported are SELECT, HELP and SHOW.
All SQL or DDL commands must be hard-coded into the SQL statements and are submitted to TPT as part of the APPLY clause.
SCRIPT OF EXECUTION
DEFINE JOB CREATE_TABLE
(
DEFINE OPERATOR DDL_OPERATOR
DESCRIPTION ‘TPT DDL OPERATOR’
TYPE DDL
ATTRIBUTES(
VARCHAR TDPID
VARCHAR USERNAME
VARCHAR PASSWORD
);
APPLY
(‘EXEC TDWORLD_MACRO;’)
TO OPERATOR (DDL_OPERATOR());
);
Sample TPT scripts.
GT11
/**************************************************************/
/* */
/* Script Name: GT11.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script exports 10 rows from a Teradata table */
/* and loads the 10 rows into a different, empty */
/* Teradata table. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses two job steps. */
/* */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source and target tables. */
/* */
/* The second job step called "load_data" uses the Export */
/* Operator to export the rows from a Teradata source table */
/* and uses the Load Operator to load the rows into an empty */
/* Teradata target table. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and load the rows into */
/* a different, empty Teradata table without landing the */
/* rows to a data file. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT11.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB BACKUP_EMPLOYEE_TABLE
DESCRIPTION 'BACKUP SAMPLE EMPLOYEE TABLE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR LOAD_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_loadoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TargetTable = 'TARGET_EMP_TABLE',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR ErrorTable1 = 'GT11_LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'GT11_LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'GT11_LOADOPER_LOGTABLE'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT11_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
STEP setup
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('DROP TABLE TARGET_EMP_TABLE;'),
('DROP TABLE GT11_LOADOPER_ERRTABLE1;'),
('DROP TABLE GT11_LOADOPER_ERRTABLE2;'),
('DROP TABLE GT11_LOADOPER_LOGTABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP load_data
(
APPLY
('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
TO OPERATOR (LOAD_OPERATOR () [1] )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
);
GT44
/**************************************************************/
/* */
/* Script Name: GT44.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script exports 10 rows from a Teradata table */
/* to a file. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses two job steps. */
/* */
/* The first job step called "setup" uses the DDL Operator to */
/* setup the source table. */
/* */
/* The second job step called "export_to_file" uses the */
/* Export Operator to export the rows from a Teradata table */
/* and uses the Data Connector Operator to write the rows to */
/* a file. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and write the rows to */
/* a file. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT44.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB EXPORT_EMPLOYEE_TABLE_TO_FILE
DESCRIPTION 'EXPORT SAMPLE EMPLOYEE TABLE TO A FILE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_dataconnoper_privatelog',
VARCHAR FileName = 'GT44_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Unformatted'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT44_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
STEP setup
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP export_to_file
(
APPLY TO OPERATOR (FILE_WRITER() )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
);
GT62
/**************************************************************/
/* */
/* Script Name: GT62.txt */
/* Description: This Teradata Parallel Transporter sample */
/* script loads 10 records from a file into an */
/* empty Teradata table. */
/* */
/**************************************************************/
/* Explanation: */
/* */
/* This script uses three job steps. */
/* */
/* The first job step called "setup_tables" uses the DDL */
/* Operator to setup the source and target tables. */
/* */
/* The second job step called "export_to_file" uses the */
/* Export Operator to export the rows from a Teradata table */
/* and uses the Data Connector Operator to write the rows to */
/* a file. */
/* */
/* The third job step called "load_data_from_file" uses the */
/* Data Connector Operator to read records from a file and */
/* uses the Load Operator to load the records into an empty */
/* Teradata table. */
/* */
/* This script shows how to use the following Teradata */
/* Parallel Transporter features: */
/* * Unconditional job steps. */
/* * Export rows from a Teradata table and write the rows to */
/* a file. */
/* * Read records from a file and load the records into an */
/* empty Teradata table. */
/* */
/**************************************************************/
/* This script also demonstrates using "job variables", */
/* which are defined in the external file "jobvars.txt". */
/* These variables can be used anywhere inside the job script */
/* by prepending the @ symbol to the variable name. */
/* */
/**************************************************************/
/* Required customizations before running this script: */
/* */
/* Inside the file "jobvars.txt", modify the values for */
/* the following job variables: */
/* */
/* MyTdpId, MyUserName, MyPassword */
/* */
/* MyTdpId is the name of the Teradata Database system. */
/* MyUserName is the username on the Teradata Database system.*/
/* MyPassword is the password associated with the username. */
/* */
/**************************************************************/
/* Execution: */
/* */
/* Here is the command to execute this script: */
/* */
/* tbuild -f GT62.txt -v jobvars.txt */
/* */
/**************************************************************/
DEFINE JOB LOAD_EMPLOYEE_TABLE_FROM_FILE
DESCRIPTION 'LOAD SAMPLE EMPLOYEE TABLE FROM A FILE'
(
DEFINE SCHEMA EMPLOYEE_SCHEMA
DESCRIPTION 'SAMPLE EMPLOYEE SCHEMA'
(
EMP_ID INTEGER,
EMP_NAME CHAR(10)
);
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DDL OPERATOR'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_ddloper_log',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountID,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR FILE_WRITER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR CONSUMER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_dataconnoper_writer_privatelog',
VARCHAR FileName = 'GT62_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Write',
VARCHAR Format = 'Formatted'
);
DEFINE OPERATOR EXPORT_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER EXPORT OPERATOR'
TYPE EXPORT
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_exportoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR SelectStmt = 'SELECT * FROM SOURCE_EMP_TABLE;'
);
DEFINE OPERATOR LOAD_OPERATOR()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER LOAD OPERATOR'
TYPE LOAD
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_loadoper_privatelog',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TargetTable = 'TARGET_EMP_TABLE',
VARCHAR TdpId = @MyTdpId,
VARCHAR UserName = @MyUserName,
VARCHAR UserPassword = @MyPassword,
VARCHAR AccountId,
VARCHAR ErrorTable1 = 'GT62_LOADOPER_ERRTABLE1',
VARCHAR ErrorTable2 = 'GT62_LOADOPER_ERRTABLE2',
VARCHAR LogTable = 'GT62_LOADOPER_LOGTABLE'
);
DEFINE OPERATOR FILE_READER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA EMPLOYEE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'GT62_dataconnoper_reader_privatelog',
VARCHAR FileName = 'GT62_employee.dat',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'Read',
VARCHAR Format = 'Formatted'
);
STEP setup_tables
(
APPLY
('DROP TABLE SOURCE_EMP_TABLE;'),
('DROP TABLE TARGET_EMP_TABLE;'),
('DROP TABLE GT62_LOADOPER_ERRTABLE1;'),
('DROP TABLE GT62_LOADOPER_ERRTABLE2;'),
('DROP TABLE GT62_LOADOPER_LOGTABLE;'),
('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER, EMP_NAME CHAR(10));'),
('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),
('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),
('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),
('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),
('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),
('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),
('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),
('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),
('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),
('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')
TO OPERATOR (DDL_OPERATOR () );
);
STEP setup_export_to_file
(
APPLY TO OPERATOR (FILE_WRITER() [1] )
SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
);
STEP load_data_from_file
(
APPLY
('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')
TO OPERATOR (LOAD_OPERATOR () [1] )
SELECT * FROM OPERATOR (FILE_READER() [1] );
);
);
Sample job variable file for Sample Script 1
SourceTdpId = ‘MySourceTdpid’
,SourceUserName = ‘MySourceUserName’
,SourceUserPassword = ‘MySourceUserPassword’
,TargetTdpId = ‘MyTargetTdpId’
,TargetUserName = ‘MyTargetUserName’
,TargetUserPassword = ‘MyTargetUserPassword’
Comparision of TPT with other Teradata utilities
1) TPT v/s Mload (Update)
2) TPT v/s TPUMP (Stream)
3) TPT v/s Fastload (Load)
4) TPT v/s FastExport (Export)
No comments:
Post a Comment
Please share your thoughts and let us know the topics you want covered