tTeradataTPTExec

tTeradataTPTExec Properties

Component Family	Databases/Teradata
Function	As the combination of tTeradataFastLoad, tTeradataMultiLoad, tTeradataTPump, and tTeradataFastExport, tTeradataTPTExec loads the data from an existing file to a Teradata database.
Purpose	tTeradataTPTExec offers high performance in inserting data from an existing file to a table in a Teradata database.
Basic settings	Property Type	Either Built-In or Repository. Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions.
		Built-In: No property data stored centrally.
		Repository: Select the repository file in which the properties are stored. The database connection related fields that follow are completed automatically using the data retrieved.
	Execution platform	Select the Operating System that will be used to run the Job, either Windows or Unix.
	TDPID	Specify the Teradata director program identifier. It can be either the name or the IP address of the Teradata database system being accessed.
	Database name	Specify the name of the Teradata database.
	Username and Password	Specify the username and the password for the Teradata database authentication. To enter the password, click the […] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.
	Schema and Edit Schema	A schema is a row description. It defines the number of fields to be processed and passed on to the next component. The schema is either Built-In or stored remotely in the Repository. Since version 5.6, both the Built-In mode and the Repository mode are available in any of the Talend solutions. Note Avoid using a Teradata database keyword as a Db Column name in the schema. If you have to, be sure to enclose the column name in a pair of “. For example, when the keyword id is used as a database column name, the Db Column should be filled with “id”.
		Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.
		Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.
		Click Edit schema to make changes to the schema. If the current schema is of the Repository type, three options are available: View schema: choose this option to view the schema only. Change to built-in property: choose this option to change the schema to Built-in for local changes. Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion. If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the [Repository Content] window.
	Consumer Operator	Select a consumer operator from the drop-down list. Load: writes data into an empty Teradata table using the Teradata FastLoad utility protocol. Inserter: inserts data into Teradata tables with SQL sessions. Update: performs Insert, Update, or Delete operations using the Teradata MultiLoad utility protocol. Stream: loads data continuously into Teradata tables using the Teradata TPump utility. For more information about Teradata consumer operators, see http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Load_and_Unload_Utilities/B035_2445_071A/2445ch02.109.18.html.
	Action On Data	Select an action to be performed on the data from the drop-down list. Insert: inserts new records to the table. If duplicates are found, the Job stops. Update: updates existing records in the table. InsertOrUpdate: inserts new records or updates existing records based on the given reference key. Delete: removes records corresponding to the input flow. Note You must specify at least one column as the primary key on which the Update or Delete operation is based. You can do that by clicking the […] button next to Edit schema and selecting the check box(es) next to the column(s) you want to set as primary key(s). This list field appears only when the Update or Stream operator is selected from the Consumer Operator drop-down list.
	Producer Operator	Select a producer operator from the drop-down list. Currently, only the DataConnector operator is supported. DataConnector: accesses files either directly or through an access module, and then writes it to the data stream. For more information about Teradata producer operators, see http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Load_and_Unload_Utilities/B035_2445_020A/2445ch02.04.16.html.
	Table	Specify the name of the table to be written into the Teradata database. Note that only one table can be written at a time.
	Script generated folder	Specify the directory under which the Teradata Parallel Transporter script file will be created during the Job execution. This script file will be deleted at the end of the Job execution.
	Load file	Specify the file holding the data to be loaded into the Teradata database.
	Error file	Specify the file in which log messages will be recorded.
Advanced settings	Field separator	Character, string or regular expression to separate fields.
	Define Log table	Select this check box to specify a log table so that log messages recorded in the log file will also be written into the log table.
	Set Script Parameters	Select this check box to define script parameters, which will be used when generating a script during the Job execution. If you do not specify them manually, the system will use their default values.
	Load Operator	Specify the load operator. This field appears only when the Set Script Parameters check box is selected.
	Data Connector	Specify the data connector. This field appears only when the Set Script Parameters check box is selected.
	Job Name	Specify the name of a Teradata Parallel Transporter Job which is defined using the Teradata tbuild command. For further information about the tbuild command, see http://developer.teradata.com/sites/all/files/documentation/linked_docs/html/online/B035-2436-088A/wwhelp/wwhimpl/js/html/wwhelp.htm. This field appears only when the Set Script Parameters check box is selected.
	Layout Name (schema)	Specify a schema for the data to be loaded. This field appears only when the Set Script Parameters check box is selected.
	Return mload error	Select this check box to specify the exit code number to indicate the point at which an error message should display in the console.
	Define character set	Select this check box to specify the character encoding to be used in your system.
	Apply TPT consumer operator optional attributes	Select this check box to define optional attribute(s) for the consumer operator that you have selected. For more information about optional attributes of each consumer operator, see Teradata Parallel Transporter Reference guide at http://developer.teradata.com/sites/all/files/documentation/linked_docs/2436020A_TPT-Reference-13.10.pdf.
	Optional attributes	Click the [+] button below the table to add as many rows as needed, each row for an optional attribute, and set the following two parameters for each attribute: Name: click the cell and select an optional attribute from the drop-down list. The list of optional attributes may vary depending on the consumer operator you have selected. For details about supported optional attributes for each consumer operator, see Supported optional attributes for each consumer operator. Value: enter the value for the corresponding optional attribute. For more information about the attribute value definition, see Teradata Parallel Transporter Reference guide at http://developer.teradata.com/sites/all/files/documentation/linked_docs/2436020A_TPT-Reference-13.10.pdf. This table appears only when the Apply TPT consumer operator optional attributes check box is selected.
	tStatCatcher Statistics	Select this check box to collect the log data at the component level.
Global Variables	EXIT_VALUE: the process exit code. This is an After variable and it returns an integer. ERROR_MESSAGE: the error message generated by the component when an error occurs. This is an After variable and it returns a string. This variable functions only if the Die on error check box is cleared, if the component has this check box. A Flow variable functions during the execution of a component while an After variable functions after the execution of the component. To fill up a field or expression with a variable, press Ctrl + Space to access the variable list and choose the variable to use from it. For further information about variables, see Talend Studio User Guide.
Usage	Used as a single-component Job or Sub-Job, this component offers high performance in inserting data from an existing file to a table in the Teradata database. For further information about the usage of this component, see http://developer.teradata.com/sites/all/files/documentation/linked_docs/html/online/B035-2436-088A/wwhelp/wwhimpl/js/html/wwhelp.htm.
Log4j	The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User Guide. For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.
Limitation	The Teradata client tool with the Teradata Parallel Transporter Base and Teradata Parallel Transporter Stream features needs to be installed on the machine where Jobs using this component are executed.

Supported optional attributes for each consumer operator

This section lists all supported optional attributes for each consumer
operator.

For the Load operator:

BufferSize, ErrorLimit, MaxSessions, MinSessions, TenacityHours,
TenacitySleep, AccountId, DataEncryption, DateForm, ErrorTable1,
ErrorTable2, LogonMech, LogonMechData, NotifyExit, NotifyExitlsDLL,
NotifyLevel, NotifyMethod, LogSQL, NotifyString, PauseAcq, PrivateLogName,
QueryBandSessInfo, WildcardInsert, WorkingDatabase, TraceLevel
For the Inserter operator:

AccountId, DataEncryption, DateForm, LogonMech, LogonMechData, LogSQL,
PrivateLogName, QueryBandSessInfo, ReplicationOverride, TraceLevel,
WorkingDatabase
For the Update operator:

PrivateLogName, BufferSize, ErrorLimit, MaxSessions, MinSessions,
TenacityHours, TenacitySleep, AccountId, AmpCheck, DataEncryption, DateForm,
DeleteTask, DropErrorTable, DropLogTable, DropWorkTable, ErrorTable1,
ErrorTable2, LogonMech, LogonMechData, LogSQL, NotifyExit, NotifyExitIsDLL,
NotifyLevel, NotifyMethod, NotifyString, PauseAcq, QueryBandSessInfo,
QueueErrorTable, WorkingDatabase, WorkTable, TraceLevel
For the Stream operator:

Buffers, ErrorLimit, MaxSessions, MinSessions, Pack, Rate, Periodicity,
TenacityHours, TenacitySleep, AccountId, AppendErrorTable, ArraySupport,
DataEncryption, DateForm, DropErrorTable, DropMacro, ErrorTable, LogonMech,
LogonMechData, MacroDatabase, OperatorCommandID, NotifyExit,
NotifyExitlsDLL, NotifyLevel, NotifyMethod, LogSQL, NotifyString,
PackMaximum, PrivateLogName, QueryBandSessInfo, QueueErrorTable,
ReplicationOverrride, Robust, WorkingDatabase, TraceLevel

Scenario: Loading data into a Teradata database

This scenario describes a Job that creates a new Teradata database table, writes data
into a delimited file, then loads the data from the file into this table, and finally
retrieves the data from the table and displays it on the console.

Dropping and linking the components

Create a new Job and add the following components by typing their names in
the design workspace or dropping them from the Palette: a tTeradataRow
component, a tFixedFlowInput component, a
tFileOutputDelimited component, a
TeradataTPTExec component, a tTeradataInput component, and a tLogRow component.
Connect tFixedFlowInput to tFileOutputDelimited using a Row > Main
connection.
Do the same to connect tTeradataInput to
tLogRow.
Connect tTeradataRow to tFixedFlowInput using a Trigger > On Subjob Ok
connection.
Do the same to connect tFixedFlowInput to
TeradataTPTExec and TeradataTPTExec to tTeradataInput.

Configuring the components

Creating a new Teradata database table

Double-click tTeradataRow to open its
Basic settings view.
Fill in the Host, Database, Username, and
Password fields with your Teradata
database connection details.

In the Query field, enter the following
SQL statement to create a new table named person with three columns id, name, sex.

CREATE SET TABLE samples.person,
FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
  id INTEGER NOT NULL,
  name VARCHAR(50),
  sex VARCHAR(20)
)
UNIQUE PRIMARY INDEX (id)

CREATE SET TABLE samples.person,

FALLBACK,

NO BEFORE JOURNAL,

NO AFTER JOURNAL

(

id INTEGER NOT NULL,

name VARCHAR(50),

sex VARCHAR(20)

)

UNIQUE PRIMARY INDEX (id)

Preparing the source data

Double-click tFixedFlowInput to open its
Basic settings view.
Click the […] button next to Edit schema to open the schema editor.
Click the [+] button to add three
columns: id of the integer type,
name and sex of the string type.
Click OK to close the schema editor and
accept the propagation prompted by the pop-up dialog box.
In the Mode area, select Use Inline Content (delimited file) and enter the
input data in the Content field.

1;Ford;Male 2;Rose;Female 3;Sabrina;Female 4;Teddy;Male 5;Kate;Male

1
2
3
4
5

1;Ford;Male
2;Rose;Female
3;Sabrina;Female
4;Teddy;Male
5;Kate;Male
Double-click tFileOutputDelimited to open
its Basic settings view.
In the File Name field, specify the file
into which the input data will be written. In this example, it is E:/person.csv.

Loading the source data into an empty table

Double-click TeradataTPTExec to open its
Basic settings view.
Fill in the TDPID, Database name, Username,
and Password fields with your Teradata
database connection details.
In the Table field, enter the name of the
table into which the source data will be loaded. In this example, it is
person.
In the Script generated folder field,
browse to the directory under which the Teradata Parallel Transporter script
file will be created during the Job execution. In this example, it is
E:/.
In the Load file field, browse to the
file that contains the source data. In this example, it is E:/person.csv.
In the Error file field, specify the file
in which log messages will be recorded. In this example, it is E:/error.log.
Click the […] button next to Edit schema to open the schema editor.

Click the [+] button to add three
columns: id of the integer type,
name and sex of the string type. Note that id and name in Db Column are enclosed in a pair of “ since they are Teradata database
keywords.

Click OK to validate these changes and
close the schema editor.
Click Advanced settings to open its view,
and then select the Apply TPT consumer operator
optional attributes check box and click the [+] button below the Optional attributes table to add the following attributes
needed: ErrorLimit, ErrorTable1, QueryBandSessInfo, and TraceLevel.

Note

For VARCHAR attributes, enter their values between double quotation
marks.

Retrieving data from the Teradata database table

Double-click tTeradataInput to open its
Basic settings view.
In the Table Name field, enter the name
of the table to read data from. In this example, it is person.
In the Query field, enter the following
SQL statement to retrieve data from the table person.

SELECT * FROM samples.person ORDER BY id

1

SELECT * FROM samples.person ORDER BY id
Click the […] button next to Edit schema to open the schema editor.
Click the [+] button to add three
columns: id of the integer type,
name and sex of the string type. Note that id and name in Db Column are enclosed in a pair of “ since they are Teradata database
keywords.
Click OK to close the schema editor and
accept the propagation prompted by the pop-up dialog box.
Double-click tLogRow to open its
Basic settings view.
In the Mode area, select the Table (print values in cells of a table) option
for a better display of the result.

Saving and executing the Job

Press Ctrl + S to save the Job.
Press F6 to execute the Job.

The data written into the specified Teradata database table is displayed
on the console.

Document get from Talend https://help.talend.com

Thank you for watching.

Docs 5.x

0 Comments

Inline Feedbacks

View all comments

tTeradataTPTExec – Docs for ESB 5.x