The Teradata Parallel Transporter (TPT) is a Teradata Tools and Utilities (TTU) product. Teradata TPT offers under one roof an SQL-like scripting language that simplifies the syntax of old Teradata Utilities for handling external data (e.g., FastLoad, MultiLoad, TPump, BTEQ, and FastExport).
Copying Tables between Teradata Systems
A classic approach to perform a database-to-database table copy across two systems is to export the table into a file, transfer the file to the target system, and import it into an empty table in the target system (see example TPT export and import scripts). The latest versions of the Teradata PT tools support operator templates (e.g., $LOAD and $INSERT), further simplifying tbuild scripts and reducing implementation and debugging times.
A mostly overlooked tool in the TPT arsenal is tdload. Compare this one-liner to copy a table from a system to another system with any FastLoad or TPT script you are manually maintaining:
tdload --SourceTdpid 127.0.0.19 --SourceUserName ETLsrc --SourceUserPassword ETL_SRC_PASS --SourceWorkingDatabase MySrcDB --SourceTable MySrcTbl --TargetTdpid 127.0.0.20 --TargetUserName ETLtrgt --TargetUserPassword ETL_TRGT_PASS --TargetWorkingDatabase MyTrgtDB --TargetTable MyTrgtTbl a_job_name
The command-line options are self-explaining, and tdload –help provides additional details. In principle, tdload takes the source system (IP address or FQDN), logon user and password, database, and table to copy from and the respective targets. It acts as a wrapper to tbuild, passing the correct variables and operator templates, simplifying implementation, and saving debug effort.
The tool checks the target table contents and transparently decides to perform a bulk load (i.e., apply a $LOAD operator) or a transactional one (i.e., apply an $UPDATE operator). Then, it generates an appropriate tbuild script and executes it, as shown in the simplified examples below.
TPT Fastload for Empty Tables
/* example with empty target table */
TDExpress1620_Sles11:~ # tdload --SourceTdpid 127.0.0.1 --SourceUserName dbc --SourceUserPassword dbc --SourceWorkingDatabase tuning --SourceTable country_code --TargetTdpid 127.0.0.1 --TargetUserName dbc --TargetUserPassword dbc --TargetWorkingDatabase tuning --TargetTable copytbl copy_MySrcTbl_MyTrgtTbl
Teradata Load Utility Version 16.20.00.02 64-Bit Teradata Parallel Transporter Version 16.20.00.02 64-Bit Job log: /opt/teradata/client/16.20/tbuild/logs/copy_MySrcTbl_MyTrgtTbl-3.out Job id is copy_MySrcTbl_MyTrgtTbl-3, running on TDExpress1620_Sles11 Teradata Parallel Transporter Load Operator Version 16.20.00.02 $LOAD: private log specified: LoadLog Teradata Parallel Transporter Export Operator Version 16.20.00.02 $EXPORT: private log specified: ExportLog $LOAD: connecting sessions $EXPORT: connecting sessions $LOAD: preparing target table $LOAD: entering Acquisition Phase $EXPORT: sending SELECT request $EXPORT: entering End Export Phase $EXPORT: Total Rows Exported: 249 $EXPORT: Total Rows Discarded: 0 $LOAD: entering Application Phase $LOAD: Statistics for Target Table: 'copytbl' $LOAD: Total Rows Sent To RDBMS: 249 $LOAD: Total Rows Applied: 249 $LOAD: Total Rows in Error Table 1: 0 $LOAD: Total Rows in Error Table 2: 0 $LOAD: Total Duplicate Rows: 0 $EXPORT: disconnecting sessions $LOAD: disconnecting sessions $EXPORT: Performance metrics: $EXPORT: MB/sec in Export phase: could not be determined $EXPORT: Elapsed time from start to Select phase: 4 second(s) $EXPORT: Elapsed time in Select phase: < 1 second $EXPORT: Elapsed time in Export phase: < 1 second $EXPORT: Elapsed time from Export phase to end: 4 second(s) $EXPORT: Total processor time used = '0.044002 Second(s)' $EXPORT: Start : Mon Nov 2 17:44:57 2020 $EXPORT: End : Mon Nov 2 17:45:05 2020 $LOAD: Performance metrics: $LOAD: MB/sec in Acquisition phase: 0.002 $LOAD: Elapsed time from start to Acquisition phase: 4 second(s) $LOAD: Elapsed time in Acquisition phase: 3 second(s) $LOAD: Elapsed time in Application phase: < 1 second $LOAD: Elapsed time from Application phase to end: 1 second(s) $LOAD: Total processor time used = '0.064004 Second(s)' $LOAD: Start : Mon Nov 2 17:44:57 2020 $LOAD: End : Mon Nov 2 17:45:05 2020 Job step MAIN_STEP completed successfully Job copy_MySrcTbl_MyTrgtTbl completed successfully Job start: Mon Nov 2 17:44:57 2020 Job end: Mon Nov 2 17:45:05 2020
TPT Multiload for Populated Tables
/* second execution, target table contains data */
TDExpress1620_Sles11:~ # tdload --SourceTdpid 127.0.0.1 --SourceUserName dbc --SourceUserPassword dbc --SourceWorkingDatabase tuning --SourceTable country_code --TargetTdpid 127.0.0.1 --TargetUserName dbc --TargetUserPassword dbc --TargetWorkingDatabase tuning --TargetTable copytbl copy_MySrcTbl_MyTrgtTbl
Teradata Load Utility Version 16.20.00.02 64-Bit Teradata Parallel Transporter Version 16.20.00.02 64-Bit Job log: /opt/teradata/client/16.20/tbuild/logs/copy_MySrcTbl_MyTrgtTbl-4.out Job id is copy_MySrcTbl_MyTrgtTbl-4, running on TDExpress1620_Sles11 Teradata Parallel Transporter Export Operator Version 16.20.00.02 $EXPORT: private log specified: ExportLog Teradata Parallel Transporter Update Operator Version 16.20.00.02 $UPDATE: private log specified: UpdateLog $EXPORT: connecting sessions $UPDATE: connecting sessions $UPDATE: preparing target table(s) $UPDATE: entering DML Phase $UPDATE: entering Acquisition Phase $EXPORT: sending SELECT request $EXPORT: entering End Export Phase $EXPORT: Total Rows Exported: 249 $EXPORT: Total Rows Discarded: 0 $UPDATE: entering Application Phase $UPDATE: Statistics for Target Table: 'copytbl' $UPDATE: Rows Inserted: 249 $UPDATE: Rows Updated: 0 $UPDATE: Rows Deleted: 0 $UPDATE: entering Cleanup Phase $UPDATE: Error Table Statistics for Target Table : 'copytbl' $UPDATE: Total Rows in Error Table 1: 0 $UPDATE: Total Rows in Error Table 2: 0 $EXPORT: disconnecting sessions $UPDATE: disconnecting sessions $EXPORT: Performance metrics: $EXPORT: MB/sec in Export phase: could not be determined $EXPORT: Elapsed time from start to Select phase: 3 second(s) $EXPORT: Elapsed time in Select phase: < 1 second $EXPORT: Elapsed time in Export phase: < 1 second $EXPORT: Elapsed time from Export phase to end: 6 second(s) $UPDATE: Performance metrics: $UPDATE: MB/sec in Acquisition phase: 0.002 $UPDATE: Elapsed time from start to Acquisition phase: 3 second(s) $UPDATE: Elapsed time in Acquisition phase: 5 second(s) $UPDATE: Elapsed time in Application phase: < 1 second $UPDATE: Elapsed time from Application phase to end: 1 second(s) $UPDATE: Total processor time used = '0.040002 Second(s)' $UPDATE: Start : Mon Nov 2 17:47:03 2020 $UPDATE: End : Mon Nov 2 17:47:12 2020 $EXPORT: Total processor time used = '0.032002 Second(s)' $EXPORT: Start : Mon Nov 2 17:47:03 2020 $EXPORT: End : Mon Nov 2 17:47:12 2020 Job step MAIN_STEP completed successfully Job copy_MySrcTbl_MyTrgtTbl completed successfully Job start: Mon Nov 2 17:47:03 2020 Job end: Mon Nov 2 17:47:12 2020
Suppose you are in a shared host environment, where many users might watch command execution. In that case, you can maintain all sensitive information in a job variables file and use the “tdload -v jobvars.txt” instead. If you want to save and see the script that tdload generates for tbuild, add the “-S” option to the tdload call. The generated scripts are kept in the job log directory for further inspection and customization. A tbuild command can readily use them.
Copying Tables across the exact Teradata System
The tdload tool can prove helpful in another usage scenario: table copy across the same system but different databases with different usernames and passwords. The typical approach is to export from one database to a file and then import it to the second database. This file-based interaction can be avoided, as tdload can connect concurrently to the two databases with two different usernames and passwords (the SourceTdpId and TargetTdpip are now identical):
tdload --SourceTdpid 127.0.0.1 --SourceUserName ETLsrc --SourceUserPassword ETL_SRC_PASS --SourceWorkingDatabase MySrcDB --SourceTable MySrcTbl --TargetTdpid 127.0.0.1 --TargetUserName ETLtrgt --TargetUserPassword ETL_TRGT_PASS --TargetWorkingDatabase MyTrgtDB --TargetTable MyTrgtTbl a_job_name
Nice explanation. I tried the utility to copy data between 2 different teradata systems using source and target options, however am getting a strange error saying target table does not exist. I did give TargetWorkingDatabase as it is a different db than the default, but same error prevails. I am sure that the table exists. Please share ur insights into this.
Thank you for the kind words! What is the error message that you receive?