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

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

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

The SCRIPT table operator is unavailable by default to users of Teradata Vantage delivered as-a-service, such as on AWS, Azure and Google Cloud. Contact your Teradata account representative to have it enabled.

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

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 [,...] )] ]
)

Syntax Elements

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