Using ExecR to Run R Scripts and Table Operators | Teradata Vantage - Using ExecR to Run R Scripts and Table Operators - Analytics Database - Teradata Vantage

Support of languages for table operators can be classified as first and second class. First class support of a language includes registration of table operators in the database. For example, C is supported as a first class language. C operators are registered in the database using DDL statements such as CREATE FUNCTION.

R is supported as a second class language. R table operators are not registered in the database and are run by the ExecR system table operator in Teradata. ExecR is created by the DipRTblOp DIP script, and it resides in the td_sysgpl database. ExecR can be run only in protected mode because R is not thread safe.

The R code for the contract function and the table operator is passed to ExecR in USING clauses. During execution of ExecR, the contract and operator are interpreted by the R interpreter.

If the R code for the contract function and operator is less than 64 KB, you can pass the contract and operator as a string in the USING clauses:

SELECT * FROM TD_SYSGPL.ExecR (
  ON (SELECT * FROM tab1)
  [ HASH_BY_clause ]
  [ PARTITION_BY_clause ]
  [ LOCAL_ORDER_BY_clause ]
  [ DIMENSION_clause ]
  [ ON tab2 ]...
  USING
  Contract ('R_contract_function')
  Operator ('R_operator')
  [ other_USING_clause ]...
) AS D;

If the R code for the contract function and operator is greater than 64 KB, but smaller than 4 MB, you can pass the code as LOBs in the USING clauses. For example, consider a table named progtable with attributes id (int) and rcode (clob). The following query interprets rcode with id 1 as the contract and rcode with id 2 as the operator:

SELECT * FROM TD_SYSGPL.ExecR (
  ON (SELECT * FROM tab1)
  [ HASH_BY_clause ]
  [ PARTITION_BY_clause ]
  [ LOCAL_ORDER_BY_clause ]
  [ DIMENSION_clause ]
  [ ON tab2 ]...
  USING
  Contract (SELECT rcode FROM progtable WHERE id=1)
  Operator (SELECT rcode FROM progtable WHERE id=2)
  [ other_USING_clause ]...
) AS D;

You can omit the contract function if you are using a RETURNS clause to define the output column definitions. The RETURNS clause can specify an output table or the column names and their corresponding types. You must specify either the contract function or a RETURNS clause. If you specify a RETURNS clause, it must come before the USING clause inside the ExecR call. The following shows an example of specifying a RETURNS clause instead of the contract function:

SELECT * FROM TD_SYSGPL.ExecR (
  ON (SELECT * FROM t1)
  RETURNS (a INTEGER, b SMALLINT, c BIGINT, d BYTEINT, e FLOAT, f REAL)
  USING
  Operator ('library(tdr); ...')
) AS D1;