Teradata R Package Function Reference | 17.00 - 17.00 - Script - Teradata R Package

Teradata® R Package Function Reference

prodname
Teradata R Package
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B700-4007-090K

Description

This function constructs a ScriptTableOperator object, that can be used to execute a user-installed script or any Linux command inside database on Teradata Vantage.

Usage

Script(
  script.command = NULL,
  returns = NULL,
  data = NULL,
  data.hash.column = NULL,
  data.partition.column = NULL,
  data.order.column = NULL,
  is.local.order = FALSE,
  sort.ascending = TRUE,
  nulls.first = TRUE,
  delimiter = "\t",
  auth = NULL,
  charset = "LATIN",
  quotechar = NULL
)

Arguments

script.command

Required Argument.
Specifies the command/script to run.
Types: character

returns

Required Argument.
Specifies the output column definition. It contains a named list of key-value pairs where key contains the column name and value contains the Teradata SQL data type.
Types: named list of characters

data

Optional Argument.
Specifies the tbl_teradata containing the input for the script.
Default Value: NULL
Types: tbl_teradata

data.hash.column

Optional Argument.
Specifies the input tbl_teradata column to be used for hashing.
The rows in the data are redistributed to AMPs based on the hash value of the column specified. The user-installed script file then runs once on each AMP.
Note: This argument cannot be specified along with the arguments "data.partition.column" and "data.order.column".
Default Value: NULL
Types: character

data.partition.column

Optional Argument.
Specifies Partition By columns for "data".
Values to this argument can be provided as a vector, if multiple columns are used for partition.
Note:

  1. This argument cannot be specified along with "data.hash.column" argument.

  2. This argument cannot be specified when the argument "is.local.order" is set to TRUE.

  3. If this argument is not specified, then the entire result set, delivered by the function, constitutes a single group or partition.

Permitted Values: "ANY" OR one or more columns in "data" tbl_teradata.
Default Value: NULL
Types: character OR vector of characters

data.order.column

Optional Argument.
Specifies Order By columns for "data".
Values to this argument can be provided as a vector, if multiple columns are used for ordering.
This column(s) mentioned in the argument is/are used in ordering irrespective of whether the argument "is.local.order" is set to TRUE or not.
Note: This argument cannot be specified along with the argument "data.hash.column".
Default Value: NULL
Types: character OR vector of characters

is.local.order

Optional Argument.
Specifies whether the input data is to be ordered locally or not. When set to TRUE, the data is ordered locally.
Order by (Vs) Local Order by:

  • Order by: Specifies 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.

Note:

  1. This argument is ignored if the argument "data.order.column" is not specified or NULL.

  2. When this argument is specified, the argument "data.order.column" should be specified and the column(s) specified in "data.order.column" is/are used for local ordering.

Default Value: FALSE
Types: logical

sort.ascending

Optional Argument.
Specfies whether to sort the result set in ascending or descending order using columns specified in "data.order.column" argument.
The sorting is ascending when this argument is set to TRUE, descending otherwise.
Note: This argument is ignored if the argument "data.order.column" is not specified.
Default Value: TRUE
Types: logical

nulls.first

Optional Argument.
Specifies whether the result set containing NULL values are to be listed first during ordering.
NULLs are listed first when this argument is set to TRUE, otherwise NULLs are listed last.
Default Value: TRUE
Types: logical

delimiter

Optional Argument.
Specifies the delimiter to use when reading the columns from a row and writing result columns.
Note:

  1. This argument cannot be same as "delimiter" argument.

  2. This argument cannot be a NEWLINE character i.e., '\n'.

Default Value: "\t"
Types: character of length 1

auth

Optional Argument.
Specifies an authorization to use when running the script which binds an operating system user to the script via authorization objects.
Use this argument to specify the fully qualified name of an authorization object in single quotes.
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 the following EXECUTE privileges:

  1. EXECUTE privilege on the authorization object specified.

  2. EXECUTE FUNCTION on td_sysfnlib.script.

Default Value: NULL
Types: character

charset

Optional Argument.
Specifies the character encoding for all of the data passed to and from the user-installed script.
Note: Teradata recommends to use UTF16, when using CHAR or VARCHAR with CHARACTER SET UNICODE.
Permitted Values: "UTF-16", "LATIN"
Default Value: "LATIN"
Types: character

quotechar

Optional Argument.
Specifies a character that forces all input and output of the script to be quoted using this specified character.
Using this argument enables the Advanced SQL Engine to distinguish between NULL fields and empty VARCHARs. A VARCHAR with length zero is quoted, while NULL fields are not.
If this character is found in the data, it will be escaped by a second quote character.
Note:

  1. This argument cannot be same as "delimiter" argument.

  2. This argument cannot be a NEWLINE character i.e., '\n'.

Default Value: NULL
Types: character of length 1

Value

A named list of "ScriptTableOperator" class.

See Also

td_setup_test_env, td_test_script

Examples

# Replace "<schema_name>" with the name of the schema to search the file for.
schema_name <- "<schema_name>"

script_command <- gettextf("Rscript ./%s/mapper.R", schema_name)

# Get remote data source connection.
con <- td_get_context()$connection

# Load example data.
loadExampleData("script_example", "barrier")

# Create object(s) of class "tbl_teradata".
barrier <- tbl(con, "barrier")

# Create a ScriptTableOperator object that allows us to execute user script in Vantage.
script_obj <- Script(data = barrier %>% select(Name),
                     script.command = script_command,
                     returns = list("word"= "VARCHAR(15)", "count_input"= "VARCHAR(2)")
                     )