fbpx

Copy tables efficiently with Teradata TPT

By Artemios Vogiatzis

November 4, 2020


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 then 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), which can further simplify tbuild scripts and reduce implementation and debug 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

If you are in a shared host environment, where many users might watch command execution, 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 saved in the job log directory for further inspection and customization. They can be readily used by a tbuild command.

Copying Tables across the same Teradata System

The tdload tool can prove useful 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 back 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
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Artemios Vogiatzis

Artemios Vogiatzis is a seasoned IT professional with rich experience in distributed, international, and interdisciplinary environments, both in the academic and business worlds. Since 2017, he optimizes data pipelines to Teradata-based DWH for the leading bank in Austria. Among others, he is a Teradata Vantage Certified Developer and Professional Scrum Master (PSM I). His academic credentials include a B.Sc. in Mathematics, a B.Sc. and an M.Sc. in Computer Science, and a Ph.D. in Computer Engineering.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>