Teradata FastExport

Overview

The Teradata FastExport Snap exports data from a Teradata database by providing a table name and configuring the connection.

The Snap writes the exported data to the specified file and provides the console output and status code on the output view for any downstream Snap checking for successful execution.

Queries produced by the Snap have the format:

SELECT <columns> FROM <schema.table> WHERE <where clause> ORDER BY <order clause>
Note: You will need the Teradata Tools and Utilities (TTU) to have access to the Teradata FastExport component.

Snap views

Type Description Examples of upstream and downstream Snaps
Input

This Snap has at most one document input view.

Anything that is immediately substituted by the Snap itself will work, but anything that is used as a substitution in a query will fail.
Output

This Snap has at most one document output view.

FASTLOAD, Binary, and Unformat are all binary and support non-textual fields (INT, DATE, etc.). Text is fixed-width text, but explicitly only supports text fields.

Expected downstream Snaps: A Snap monitoring the exit code to check result status.

Expected output: A single document containing the console output and return status from the external Teradata application.
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: Teradata FastExport
Schema name

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

Default value: N/A

Example: SYS
Table name

String/Expression		 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+ "'"

Default value: N/A

Example: EmpId = 12
Order by

String/Expression		 Enter 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 rows

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

Default value: N/A

Example: 100
Output fields

String/Expression		 Enter or select output field names for SQL SELECT statement. To select all fields, leave it at default. Fields available include:
  • out - console output
  • err - console error output
  • status - exit status (see FastExport reference manual for explanation)
  • script - generated FastExport script if user wants to try running it manually
  • sql - generated SQL statement if there is any question about what was done
  • logtable - name of random logtable used by FastExport application. Logtable names have the form SnapfeUUID where UUID is a standard random UUID with all dashes replaced by underscores. This creates a huge tablename with essentially no chance of collision with an existing file. The logtable is deleted automatically.

Default value: N/A

Example: email, address, first, last
Fetch Output Fields In Schema

Checkbox		 Select this checkbox to include only the selected fields or columns in the Output Schema (second output view). If you do not provide any Output fields, all the columns are visible in the output. If you provide output fields, we recommend you to select this checkbox.

Default value: Deselected
Match data types

Checkbox		 Conditional. This property applies only when the Output fields property is provided with any field value(s). If this property is selected, the Snap tries to match the output data types same as when the Output fields property is empty (SELECT * FROM ...). The output preview would be in the same format as when accessing all the columns.

Default value: Deselected
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;
Number of retries

Integer

The number of retry attempts the Snap makes in case of a connection failure.

Default value: 3
Retry interval (seconds)

Integer

The interval in seconds between retry attempts.

Default value: 1
Staging mode

Dropdown list		 Required if the value in the Number of retries field is greater than 0. Specify the location to store input documents between retries, from the following options:
  • In memory: The query results are stored in the Snaplex memory. If the query is too large to fit in the memory space, it may cause the Snap to fail, so choose the On disk option.
  • On disk: The query results are stored on the disk in a temporary (tmp) directory that is managed by the SnapLogic platform. This directory is deleted automatically when the Snap terminates.

To disable staging, enter 0 in the Number of retries field.

Default value: In memory

Example: On disk
Fast Export Configure the FastExport settings for data export.
File Action

Dropdown list		 Required. Select an action to take when the specified file already exists in the directory. Options available include:
  • Overwrite
  • Ignore
  • Error

Default value: Error

Example: Overwrite
FEXP Location

String/Expression		 The location of the Teradata FastExport component.

Default value: /usr/bin/fexp

Example: /opt/teradata/client/bin/fexp
Character Set

Dropdown list		 Select the character set in which data should be encoded when you export data from the Teradata Database. Teradata Database allows a character set to be established when invoking the FastExport. The available options are:
  • Default: When you execute the Teradata FastExport Snap, if you have not used any character set name and not specified any character set in the client system for encoding, then the Snap uses the default specification available in the DBC.Hosts table in the Teradata database system.
  • UTF-8: If you are using the UTF-8 character set encoding on the network-attached platforms or IBM z/OS, then use the UTF-8 character set. In general, the command language and the job output should be the same as the client character set used by the job. However, the UTF-8 character set also supports the network-attached platforms that use UTF-16 character set.

Default value: Default

Example: UTF-8
Output File

String/Expression		 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

Example: /data/exports/output.txt
Data Format

Dropdown list		 Export file format. Possible values are:
  • FASTLOAD
  • BINARY
  • TEXT
  • UNFORMAT

See the Format Syntax Element for the EXPORT command in the Teradata FastExport Reference for detailed information.

Default value: FASTLOAD

Example: TEXT
Date format

Dropdown list		 Required. Date format used in the output file in text mode. Options available include:
  • Integer
  • Text

Default value: Integer

Example: Text
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.

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;.