Using ExecR to Execute R Scripts and Table Operators - Advanced SQL Engine - Teradata Database

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 executed 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 executed 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  /  Partition By  /  Local Order By  /  DIMENSION   clauses]
   [ON ...]
   USING Contract('<R contract function>')
         Operator('<R operator>')
         [Other USING clauses]
         ...
) 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  /  Partition By  /  Local Order By  /  DIMENSION clauses]
   [ON ...]
   USING Contract(select rcode from progtable where id=1)
         Operator(select rcode from progtable where id=2)
         [Other USING clauses]
         ...
) 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.

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;