Teradata TPT Export script

If writing a typical TPT script is tedious task for you then you may try using existing TPT templates to quickly write a TPT script. Teradata provides list of templates for all the TPT operators which you can use to quickly create a TPT script. In this post, we will see how to export data from a Teradata table using default templates available in tbuild.

Below is the TPT script content [exp_Customer.tpt].
DEFINE JOB EXPORT_CUSTOMER
DESCRIPTION 'Export Customer table to a delimited file'
(
DEFINE SCHEMA sch_CUSTOMER FROM TABLE 'DS_TBL_DB.CUSTOMER';
APPLY TO OPERATOR ($FILE_WRITER()[1] ATTR (FileName = 'op_CUSTOMER.dat') )
SELECT * FROM OPERATOR ($EXPORT(sch_CUSTOMER)[1] ATTR (
SelectStmt = 'select * from DS_TBL_DB.CUSTOMER ;'
)
);
);

I am using just 2 “OPERATOR” in the TPT script and will use below variable file to pass parameters for corresponding operators. Below is the variable file content [var_Customer.txt].

 SourceTdpId        = '192.168.100.100'
,SourceUserName     = 'dbc'
,SourceUserPassword = 'dbc'
,SourceOpenmode     = 'Read'
,FileWriterFormat			= 'Delimited'
,FileWriterOpenMode			= 'Write'
,FileWriterDirectoryPath	= '/root/raj/data/'
,FileWriterTextDelimiter	= '#'

You can check out other options available in template file at below location:

/opt/teradata/client/15.00/tbuild/template

*Replace 15.00 with the client version you are using.
To run the TPT script use below command:

tbuild -f exp_Customer.tpt -v var_Customer.txt

Log may look like below:

TDExpress15.0.0.8_Sles10:~/raj # tbuild -f exp_Customer.tpt -v var_Customer.txt
Teradata Parallel Transporter Version 15.00.00.00
Job log: /opt/teradata/client/15.00/tbuild/logs/root-5.out
Job id is root-5, running on TDExpress15008_Sles10
Teradata Parallel Transporter $FILE_WRITER[1]: TPT19006 Version 15.00.00.00
$FILE_WRITER[1]: TPT19010 Instance 1 directing private log report to 'dtacop-root-20672-1'.
$FILE_WRITER[1]: TPT19007 DataConnector Consumer operator Instances: 1
Teradata Parallel Transporter Export Operator Version 15.00.00.00
$EXPORT: private log not specified
$FILE_WRITER[1]: TPT19003 ECI operator ID: '$FILE_WRITER-20672'
$FILE_WRITER[1]: TPT19222 Operator instance 1 processing file '/root/raj/data/op_CUSTOMER.dat'.
$EXPORT: connecting sessions
$EXPORT: sending SELECT request
$EXPORT: entering End Export Phase
$EXPORT: Total Rows Exported:  100000
$FILE_WRITER[1]: TPT19221 Total files processed: 1.
$EXPORT: disconnecting sessions
$EXPORT: Total processor time used = '0.08 Second(s)'
$EXPORT: Start : Sun Jun 30 10:47:51 2019
$EXPORT: End   : Sun Jun 30 10:47:58 2019
Job step MAIN_STEP completed successfully
Job root completed successfully
Job start: Sun Jun 30 10:47:46 2019
Job end:   Sun Jun 30 10:47:58 2019

Now on our youtube channel , Chris asked how can we create mutliple file parts while exporting the data from table. This can be really helpful if you are handling huge number of records. This can be done by simply specifying the file writer instances in the script. Just change $FILE_WRITER()[1] to $FILE_WRITER()[2] to create 2 file parts. However this does not mean there always be data in all file parts. It will generate 2 File parts but if data is less you may not see data in all file parts.
Let’s run the script and see the logs.

TDExpress15.0.0.8_Sles10:~/raj # tbuild -f exp_Customer.tpt -v var_Customer.txt
Teradata Parallel Transporter Version 15.00.00.00
Job log: /opt/teradata/client/15.00/tbuild/logs/root-6.out
Job id is root-6, running on TDExpress15008_Sles10
Teradata Parallel Transporter $FILE_WRITER[1]: TPT19006 Version 15.00.00.00
$FILE_WRITER[1]: TPT19010 Instance 1 directing private log report to 'dtacop-root-21143-1'.
$FILE_WRITER[2]: TPT19010 Instance 2 directing private log report to 'dtacop-root-21144-2'.
Teradata Parallel Transporter Export Operator Version 15.00.00.00
$EXPORT: private log not specified
$FILE_WRITER[1]: TPT19007 DataConnector Consumer operator Instances: 2
$FILE_WRITER[1]: TPT19003 ECI operator ID: '$FILE_WRITER-21143'
$FILE_WRITER[1]: TPT19222 Operator instance 1 processing file '/root/raj/data/op_CUSTOMER.dat-1'.
$FILE_WRITER[2]: TPT19222 Operator instance 2 processing file '/root/raj/data/op_CUSTOMER.dat-2'.
$EXPORT: connecting sessions
$EXPORT: sending SELECT request
$EXPORT: entering End Export Phase
$EXPORT: Total Rows Exported:  100000
$FILE_WRITER[1]: TPT19221 Total files processed: 2.
$EXPORT: disconnecting sessions
$EXPORT: Total processor time used = '0.08 Second(s)'
$EXPORT: Start : Sun Jun 30 10:58:26 2019
$EXPORT: End   : Sun Jun 30 10:58:33 2019
Job step MAIN_STEP completed successfully
Job root completed successfully
Job start: Sun Jun 30 10:58:21 2019
Job end:   Sun Jun 30 10:58:33 2019

So in this post we saw how to use default Teradata tbuild templates to write TPT script quickly.
Also we saw sample TPT Export script and how you can change FILE WRITER instances to generate multiple file parts than just 1.
Hope this helps. If any query or question, feel free to leave a comment.

1 Comment

  1. Hi , i would like to know how to import data from odbc data source to teradata tables using $odbc template

Leave a Reply

Your email address will not be published. Required fields are marked *