16.20 - Consumer Operators - Parallel Transporter

Teradata® Parallel Transporter User Guide

Product
Parallel Transporter
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-27
dita:mapPath
uah1527114222342.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval

The Teradata PT consumer operators in this section read data from the data stream and write it to a Teradata Database.

The Teradata PT job script invokes a consumer operator using an APPLY statement. For further information on using APPLY to specify a consumer operator, see Coding the APPLY Statement and the section on APPLY in Teradata Parallel Transporter Reference (B035-2436).

The following table briefly describes and compares the function of each Teradata PT operator that can be used as a consumer when loading data into a Teradata Database:

Operator Description
Load Operator Inserts data at high speed into a single empty Teradata Database table. Function is similar to the standalone FastLoad utility.

Features:

  • Best used for the initial data loads into Teradata Database tables.

Limitations:

  • Does not support UPDATE, SELECT, or DELETE operations.
  • The target table must be empty, with no defined secondary indexes.
  • Multiple parallel instances of the Load operator can be used in a job, but they must all insert data into the same table.

For details, see Teradata Parallel Transporter Reference (B035-2436).

SQL Inserter Operator Uses one or more SQL sessions to insert data into a Teradata Database in either an empty or populated table.

Features:

  • Protects data integrity. If an error is encountered during the INSERT operation, SQL Operator backs out all rows it has inserted for the job since the last checkpoint.
  • Use of multiple instances of SQL Inserter allows parallel loading of LOBs, XML, and JSON.

Limitations:

  • Is slower than other operators capable of writing to Teradata Database.
  • Will terminate the job if an attempted insert would duplicate an existing row in the target table.
  • If multiple sessions are used with the SQL Inserter operator and the data has duplicate primary index values, this could result in a potential Teradata Database deadlock 2631 error. To avoid this error, do not use multiple sessions with the TPT SQL Inserter operator.
  • If multiple sessions are used and the data accesses the same row-level lock on the Teradata Database, the job could result in a hang. To avoid a hang, do not use multiple sessions with the TPT SQL Inserter operator. Use only 1 session.

For details, see Teradata Parallel Transporter Reference (B035-2436).

Stream Operator Performs high-speed, low-steady volume SQL DML transactions, INSERT, UPDATE, DELETE, or UPSERT on Teradata Database tables. Function is similar to the standalone Teradata Tpump utility.

Stream operator or Update operator can be used for many similar tasks. For comparison of function to help you choose the best operator for the job, see Comparing Update and Stream Operators.

For details, see Teradata Parallel Transporter Reference (B035-2436).

Update Operator Performs high-speed, high-volume SQL DML transactions, INSERT, UPDATE, DELETE, or UPSERT on Teradata Database tables. Function is similar to the standalone Teradata MultiLoad utility.

Update operator or Stream operator can be used for many similar tasks. For comparison of function to help you choose the best operator for the job, see Comparing Update and Stream Operators.

For details, see Teradata Parallel Transporter Reference (B035-2436).

Consumer operators have a limit on the number of tables they can load simultaneously, as shown in the following:
Teradata PT Operator Maximum Target Tables
Load 1
Update 5
Stream 127
SQL Inserter 1