SCRIPT | SQL Operators & User-Defined Functions | Teradata Vantage - SCRIPT - Advanced SQL Engine - Teradata Database

Purpose

Executes a user-installed script or any LINUX command inside the database.

Authorization

You must have the EXECUTE FUNCTION privilege on TD_SYSFNLIB.SCRIPT to invoke this table operator.

Syntax

SCRIPT ( [ on_clause ] SCRIPT COMMAND ( runtime_literal_command )
  [ RETURNS ( { '*' | 'column_name data_type_specification' } [,...] ) ]
  [ DELIMITER ('delimiter_character') ]
  [ CHARSET ( { 'UTF-16' | 'LATIN' } ) ]
  [ QUOTECHAR ('quote_character') ]
  [ AUTH ('authorization_name') ]
  [ [AS] alias_name [( column_name [,...] )] ]
)

on_clause

ON { table_name | view_name | ( query_expression ) }
  [ AS correlation_name ]
  [ hash_or_partition_by [ order_by | local_order_by ] ]

hash_or_partition_by

{ { HASH | PARTITION } BY [ order_by | local_order_by ][,...] |
  PARTITION BY ANY
}

order_by

ORDER BY order_by_spec [,...]

local_order_by

ORDER BY local_order_by_spec [,...]

order_by_spec

{ column_name | column_position | sort_expression } [ ASC | DESC ]
  [ NULLS { FIRST | LAST } ]

local_order_by_spec

localOrderByList [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

ON clause

The SCRIPT function can have only one ON clause (single input). The ON clause can be specified with no options or with:

• HASH BY
• PARTITION BY
• PARTITION BY ANY
• an optional ORDER BY or LOCAL ORDER BY clause

SCRIPT_COMMAND

The script to be executed. The SCRIPT_COMMAND is a required keyword.

runtime_literal_command

The parameters to SCRIPT_COMMAND can be an executable name followed by the script name and other inputs, or any valid LINUX command.

RETURNS

An optional clause.

The names and types of the output columns returned by the script.

*

Specifies that all columns of the input table should be returned by the SCRIPT function.

'column_name data_type_specification'

The information inside the quotation marks. The data_type_specification is any Teradata data type with any appropriate modifiers, such as size or character type.

DELIMITER

An optional clause. The 'delimiter_character' is a tab by default. The delimiter is used to separate the column values in the input and output strings.

CHARSET

An optional clause. The default is LATIN. By specifying UTF-16, SCRIPT uses the UTF-16 character encoding for all of the data passed to and from the user-installed script, which is recommended when using CHAR or VARCHAR with CHARACTER SET UNICODE.

QUOTECHAR

An optional parameter that forces all input and output to the script to be quoted using the specified character.

Using QUOTECHAR also enables the database to distinguish between NULL fields and empty VARCHARs. A VARCHAR with length zero is quoted, while NULL fields are not.

If the character is found in the data, it will be escaped by a second quote character. For example:

He said, "Hey there!"

QUOTECHAR("") with double quotation marks becomes

"He said, ""Hey there!"""

AUTH

An optional clause that binds an operating system user to the script via authorization objects.

Use the optional AUTH clause with the SCRIPT table operator to specify the fully qualified name of an authorization object in single quotes. You can only specify one AUTH clause.

If the database name is not provided, the name is fully qualified using the current user or database.

The database user executing the script query must have an EXECUTE privilege for the following:

• EXECUTE privilege on the authorization object specified.
• EXECUTE FUNCTION on td_sysfnlib.script.

A database user may be associated with more than one authorization, but the authorization that is used is the one specified in the AUTH clause.

You can grant the execute privilege on an authorization to a role and then assign a user privileges using that role. Authorization objects belong to database users that create them, but also implicitly to any owner of that database user. The owners can GRANT or REVOKE the EXECUTE privilege on an authorization object on behalf of the database users.

An authorization object called DEFAULT_AUTH is created for the SYSUIF database. The authorization is used to control which database users can execute scripts under the default operating system user tdatuser. This authorization object is used when none is specified in the AUTH clause. Users must have the EXECUTE privilege granted on the SYSUIF.DEFAULT_AUTH object in order to execute successfully.

For more information, see "Authorization Statements for External Routines" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

AS

An optional introduction to alias_name or column_name.

alias_name

An alias for the table referenced by table_name.column_name_alias is also used to name expressions.

table_name

The name of the table being referenced.

view_name

The name of the view being referenced.

query_expression

The name of the view being referenced.

AS correlation_name

An optional alias for the ON clause input table.

HASH BY

The rows in the ON clause will be redistributed to AMPs based on the hash value of column_specification The user-installed script file then runs once on each AMP.

PARTITION BY or PARTITION BY ANY

In its column_specification, or comma-separated list of column specifications, the group, or groups, over which the function operates.

PARTITION BY is optional. If there is no PARTITION BY clause, then the entire result set, delivered by the FROM clause, constitutes a single group or partition.

PARTITION BY clause is also called the window partition clause.

ORDER BY

In its value_expression, the order in which the values in a group, or partition, are sorted.

LOCAL ORDER BY

Orders qualified rows on each AMP in preparation to be input to a table function.

COLUMN SPECIFICATION

A SQL expression comprising the following two expressions:

• column_name
• column_position

column_name, column_position

The name of the column or columns or their ordinal position within the join index definition.

sort_expression

The order in which the SQL expressions are sorted.

ASC

Results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

Results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

NULL results are to be listed first.

NULLS LAST

NULL results are to be listed last.