Retain - Teradata Warehouse Miner

Purpose

The retain option allows you to copy one or more columns into the final analytic data set. By default, the result column name is the same as the input column name, but this can be changed. If a specific type is specified, it results in casting the retained column.

The Retain transformation is supported for all valid data types.

Required Parameters

columns

Controls the name of the output (transformed) column and its data type. The columns parameter is required by all transformations except Derive. A separate transformation is performed for each column in the list. If a column name is followed by a forward slash and a name, the name after the slash becomes the name of the transformed column in the resultant output table. Otherwise the column name is used as the output column name.

For the Derive transformation, the outputname parameter controls the naming of the transformed output column.

database

The database containing the input table.

retain

The parameter that identifies the type of transformation being performed.

tablename

The input table to build a predictive model from.

vartran

Required to run a variable transformation. Enclose the 'vartran' parameter in single quotes.

Optional Parameters

datatype

For all transformation types, the datatype casts the column to a desired database data type provided it is compatible with the transformed data.

Allowed output types include:

• byteint
• char
• date
• decimal
• float
• integer
• smallint
• time
• timestamp
• varchar
• bigint
• number

fallback

When true, requests a mirrored copy of the output table in the Teradata Database when outputstyle=table.

gensqlonly

When true, the SQL for the requested transformations is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.

indexcolumns

When true, requests the output table contain the index columns when outputstyle=table.

indexunique

When true, requests the output table contain a unique primary index when outputstyle=table.

lockingclause

Requests the generated SQL contain the given locking clause in the appropriate location depending on the output style.

An example of a locking clause when the output style defaults to select is:

LOCKING mydb.mytable FOR ACCESS;

multiset

When true, requests an output table that can contain duplicate rows when outputstyle=table.

noindex

When true, requests the output table contain no index columns when outputstyle=table.

outputdatabase

The database containing the resulting output table when outputstyle=table or view.

outputtablename

The name of the output table when outputstyle=table or view.

outputstyle

Allowed output styles are:

• select
• table
• view

If outputstyle is not specified, the function generates a SELECT statement and does not create a table or view.

outputstyle={select|table|view}

overwrite

When overwrite is set to true (default), the output tables are dropped before creating new ones.

whereclause

Requests the generated SQL containing the given WHERE clause in appropriate places in the generated SQL. This is independent of the output style requested.

Examples

These examples show how to use Retain. To run the provided examples, the td_analyze function must be installed in a database called twm and the TWM tutorial data must be installed in the twm_source database.

The first example demonstrates retaining multiple columns unchanged.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;retain={columns(cust_id,age,income)};');

The example shows retaining some columns unchanged, and with name and data type change.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;retain={columns(cust_id,age)}{columns(income/inc),datatype(bigint)};');