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.
Hi , i would like to know how to import data from odbc data source to teradata tables using $odbc template