TPT FastExport

Overview

The TPT FastExport Snap exports data from a Teradata table to a flat file using the Teradata Parallel Transporter (TPT) utility.

  • Expected downstream Snaps: A Snap monitoring the exit code to check result status.
  • Expected input: None or documents with values to evaluate expression properties in the Snap. Each input document triggers one execution of the script.
  • Expected output: A single document containing the console output and return status from the external TPT application for each input document.

Prerequisites

TPT Snaps (TPT Insert, TPT Update, TPT Delete, TPT Upsert and TPT Load) use the 'tbuild' utility for the respective operations.

In order to use these Snaps in the pipelines, the respective TPT utilities should be installed/available on the Snapplexes.

Note: All the required TPT utilities must be installed on the node where JCC is running or the Snap simply executes but may not perform the operation. Any utility/library file missing on the node may cause the Snap to perform a failed operation.

Basic steps for installing the TPT utilities:

Procedure to install the Teradata tools and utilities:

  • Download the Teradata Tools and Utilities base such as TTU 16.20.25.00 Linux - Base for Linux environment.
  • If the OS is 64 bit, install 32 bit support packages on the environment and also any further dependencies.
  • Install the Teradata Tools and Utilities (that includes tbuild, fastload, mload, etc.)
  • The default installation also configures the PATH in the OS.

Flow: If the tbuild or fastload is already available on the OS path then the corresponding TPT Snap can invoke the required utility, however, if these binaries are not available on the OS path, then you need to provide the absolute path of the respective binary in the Snap.

Snap views

Type Description Examples of upstream and downstream Snaps
Input

This Snap has at most one document input view.
Output

This Snap has one or two output views. The first output view produces one Document containing the console output and return status from the external Teradata application. The second output view provides the table and column metadata.
Learn more about Error handling.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field/Field set Description
Label

String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: TPT FastExport
Schema name

String/Expression/ Suggestion		 The database schema name. In case it is not defined, the suggestion for the table name will retrieve all the table names of all the schemas. The property is suggestible and will retrieve available database schemas during suggest values.

Default value: N/A

Example: SYS
Table name

String/Expression/ Suggestion		 Required. Name of the table to execute a select query on.

Default value: N/A

Example: people
Where clause

String/Expression		 Where clause of the select statement. This supports document value substitution (such as $person.firstname will be substituted with the value found in the incoming document at the path). However, you may not use a value substitution after "IS" or "is" word.

Without using expressions:

Using expressions:

  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • email = $email
  • emp=$emp
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
CAUTION: Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled.

Default value: N/A
Order by

String/Expression/ Suggestion		 Enter in the columns in the order in which you want to order by. The default database sort order will be used.

Default value: N/A

Example: name, email
Limit offset

Integer/Expression		 The starting row for the query.

Default value: N/A
Limit rows

Integer/Expression		 The maximum number of rows the query should return.

Default value: N/A
Output fields

String/Expression/ Suggestion		 Enter or select output field names for SQL SELECT statement. To select all fields, leave it at default.

Default value: N/A

Example: email, address, first, last
Query band

String/Expression		 Specify the name-value pairs to use in the session's generated query band statement. The query band is passed to the Teradata database as a list of name-value pairs separated by semi-colons.

Default value: N/A

Example: ProjectName=dstage1;user=test;
File Action

Dropdown list		 Required. Select an action when the specified file already exists in the directory. The options available include: OVERWRITE, IGNORE and ERROR.

Default value: ERROR
TBUILD location

String/Expression		 Required. Location of the Teradata application.
Note: The TBUILD location will be available on the Snaplex.

Default value: /usr/bin/tbuild
Output directory

String/Expression/ Suggestion		 Required. Attribute specifies the full path to the output directory. Specify a local directory path. An absolute path should be entered. Use the suggest bubble to get the list of subdirectories in the current directory. If this property is blank, it will list all subdirectories in the top directory of the local file system.

Default value: N/A

Example: /home/Snap/tpt/ or file:///home/Snap/tpt/
Output File

String/Expression		 Required. Location of exported data file. If there is a white space, then the path should be in quotes as per the Teradata documentation.

Default value: N/A
File Format

Dropdown list		 Required. Attribute that specifies the logical record format of the exported data file. The options available include: Binary, Delimited, Formatted, Unformatted and Text.
Note: Text format requires all column data types to be CHAR or DATE in the selected Output fields. If DATE data type is included, the Date format property should be set to Text.

Default value: Delimited
Text Delimiter Type

Dropdown list		 Required. Attribute that specifies Type of Text Delimiter that is to be used. The options available include: TextDelimiter and TextDelimiterHEX.

Default value: TextDelimiter
Text Delimiter

String/Expression		 Attribute that specifies the bytes that separate fields in delimited records. Any number of characters can be defined via the attribute assignment. The default delimiter character is the pipe character ( | ). To embed a pipe delimiter character in your data, precede the pipe character with a backslash ( \ ). Use Hex code for Hex Type and Text for the other.
Note: To use the tab character as the delimiter character, specify TextDelimiter = 'TAB'. Use uppercase "TAB" not lowercase "tab". The backslash is required if you want to embed a tab character in your data.

Default value: |
Escape Text Delimiter Type

Dropdown list		 Required. Attribute that specifies Type of Escape Text Delimiter that is to be used. The options available include: EscapeDelimiterText and EscapeDelimiterHEX.

Default value: EscapeTextDelimiter
Escape Text Delimiter

String/Expression		 Attribute that allows you to define the delimiter escape character within delimited data. There is no default data. When processing data in delimited format, if the backslash precedes the delimiter, that instance of the delimiter is included in the data rather than marking the end of the column. Use Hex code for Hex Type and Text for the other.

Default value: \
Date format

Dropdown list		 Required. Date format used in the output file in text mode. The options available include: Integer and Text.
Note: If this property is set to 'Integer' and the File Format property is set to Binary/Formatted/Unformatted, the DATE type values are written to the output file as an integer. If this property is 'Integer', and the File Format property is 'Text' and a DATE data type output field is included, the Snap will fail with an error "... TPT19108 Data Format 'TEXT' requires all 'CHAR/ANSIDATE' schema...."

Default value: Integer
Quoted Data

Checkbox		 Required. Determines if data is expected to be enclosed within quotation marks.

Default value: Deselected
Snap execution

Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute. Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only. Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled. Disables the Snap and all Snaps that are downstream from it.

Temporary files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When processing larger datasets that exceed the available compute memory, the Snap writes unencrypted pipeline data to local storage to optimize the performance. These temporary files are deleted when the pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex node properties, which can also help avoid pipeline errors because of the unavailability of space. Learn more about Temporary Folder in Configuration Options.

Troubleshooting

Error Cause Resolution
Failure: TPT script failed to execute

error code: 8

 The Snap is unable to read the Fastload absolute path. Leave the field blank or update the Fastload absolute path with .exe extension to prevent your pipeline from failing. For example, if you used /usr/bin/fastload as the Fastload absolute path, change it to /usr/bin/fastload.exe.
Failure: Failed to execute query

error code: 5992

 A syntax error was found in the QUERY_BAND. Check that the query band is in the form specified in the Query band field above. For example, if you used priority=1;workload=high in the Query band, add a semi-colon and update the value to priority=1;workload=high;.
Note: For the Suggest in the Order by columns and the Output fields properties, the value of the Table name property should be an actual table name instead of an expression. If it is an expression, it will display an error message "Could not evaluate accessor: ..." when the Suggest button is pressed. This is because, at the time the Suggest button is pressed, the input document is not available for the Snap to evaluate the expression in the Table name property. The input document is available to the Snap only during the preview or execution time.