Executes a user-installed script or any LINUX command inside the database.
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.
- *
- 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.
- DESC
- Results are to be ordered in descending sort order.
- NULLS FIRST
- NULL results are to be listed first.
- NULLS LAST
- NULL results are to be listed last.