Page Comparison

On this Page

Table of Contents

maxLevel	2
exclude	Older Versions\|Additional Resources\|Related Links\|Related Information

Snap type:

Read

Description:

This Snap allows you to export 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:

Code Block
SELECT TOP [limit] * FROM [table] WHERE [where clause] ORDER BY [ordering]

Note
You will need the Teradata Tools & Utilities (TTU) to have access to the Teradata FastExport component.

Expected downstream Snaps: A Snap monitoring the exit code to check result status.
Expected input: None.
Expected output: A single document containing the console output and return status from the external Teradata application.

Prerequisites:

None

Support and limitations:

Works in Ultra Task Pipelines.

Account:

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring Teradata Database Accounts for information on setting up this type of account.

Views:

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.
Error	This Snap has at most one document error view and produces zero or more documents in the view.

Settings

Label

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Schema name

The database schema name. In case it is not defined, then the suggestion for the table name will retrieve all tables names of all schemas. The property is suggest-able and will retrieve available database schemas during suggest values.

Example: SYS

Default value: [None]

Table name

Required. Name of table to execute a select query on.

Example: people

Default value: [None]

Where clause

Where clause of 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. Please see the examples below.

Examples:

Without using expressions

EmpId = 12
email = 'you@example.com'

Using expressions

"EMPNO=$EMPNO and ENAME=$EMPNAME"
email = $email
emp=$emp
"emp='" + $emp + "'"
"EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"

Warning

title	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: [None]

Order by

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

Example:
name
email

Default value: [None]

Limit rows

The number of rows the query should return.

Default value: [None]

Output fields

Enter or select output field names for SQL SELECT statement. 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's 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.

To select all fields, leave it at default.

Example: email, address, first, last, etc.

Default value: [None]

Fetch Output Fields In Schema

Select this check box 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 Fetch Output Fields In Schema check box.

Default value: Not selected

Match data types

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 the one when SELECT * FROM is implied and all the contents of the table are displayed.

Default value: Not selected

Multiexcerpt include macro

name	ME_Number_of_retries
page	Teradata Execute

Multiexcerpt include macro

name	ME_Number_of_retries_Desc
page	Teradata Execute

Multiexcerpt include macro

name	ME_Retry_Interval
page	Teradata Execute

Multiexcerpt include macro

name	ME_Retry_Interval_Desc
page	Teradata Execute

Staging mode

Required when 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

File Action

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

Default value: Error

FEXP Location

The location of the Teradata FastExport component.

Default value: /usr/bin/fexp

Character Set

Select the character set in which data should be encoded when you export data from the Teradata Database.

Info
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

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: [None]

Data Format

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.

Date format

Required. Date format used in the output file in text mode. Options available include Integer and Text.

Default value: Integer

Multiexcerpt include macro

name	Snap Execution
page	SOAP Execute

Multiexcerpt include macro

name	Snap_Execution_Introduced
page	Anaplan Read

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.

Versions Compared

Old Version 20

New Version Current

Key

Settings

See Also