Script Table Operator | Teradata R Package - 17.00 - Script Table Operator - Teradata R Package

Teradata® R Package User Guide

prodname
Teradata R Package
vrm_release
17.00
created_date
November 2020
category
User Guide
featnum
B700-4005-090K

The tdplyr Script() function and its associated methods provide an interface to Script Table Operator (STO) in Advanced SQL Engine. See the Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210 for more information on Script Table Operator.

This function enables the user to run a user-installed script natively in Vantage against data in an Advanced SQL Engine table via the Script Table Operator. The Teradata In-nodes Interpreter and Add-ons language packages must be installed in advance in the target Advanced SQL Engine nodes.

Prior to running Script Table Operator functions, make sure the user has the necessary database permissions, and specify search path as listed here:

  • Teradata user should be given permission to execute Script Table Operator and the default authentication.

    For example, if the user is 'TDAPUSER', then:

    GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO TDAPUSER;
    GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH to TDAPUSER;
  • Teradata user should be given some additional permissions on the database.

    For example, if the user is 'TDAPUSER' and the database is 'TDAPUSERDB', then:

    GRANT SELECT ON TDAPUSERDB TO TDAPUSER;
  • You must set SEARCHUIFDBPATH prior to accessing any installed scripts in the target database, so that the Linux system user 'tdatuser' can find the scripts to run them through the STO.
    For example, if the scripts reside in 'TDAPUSERDB', then set search path in corresponding R script or R console as follows:
    DBI::dbExecute(con, dplyr::sql("SET SESSION SEARCHUIFDBPATH = TDAPUSERDB;"))

Example of Grant Permissions to User

In this example, a user "me" is using a database 'TDAPUSERDB' as default database, the user should have the execute permissions on the Script function to the default database.

  • Create the required users and databases as database administrator.
    -- Create a user 'sysdba' with default database as 'sysdba'.
    CREATE USER sysdba FROM DBC
    AS PERM = 14e9
    PASSWORD = sysdba
    DEFAULT DATABASE = sysdba
    NO BEFORE JOURNAL
    NO AFTER JOURNAL;
    
    -- Create a new database from the newly created user 'sysdba'.
    CREATE DATABASE TDAPUSERDB FROM sysdba
    AS PERM=1e9
    NO BEFORE JOURNAL
    NO AFTER JOURNAL;
    
    -- Create a user with default database as newly created database 'TDAPUSERDB'.
    CREATE USER me FROM sysdba
    AS PERM = 100e6
    PASSWORD = me
    DEFAULT DATABASE = TDAPUSERDB
    NO BEFORE JOURNAL
    NO AFTER JOURNAL;
  • Make sure the file 'mapper.R' is installed in the database 'TDAPUSERDB'.

    If the file is not present in Vantage, see File Management Functions section for how to use td_install_file() to install file in Vantage and the required permissions.

  • Make sure the database administrator provides the following permissions to the user 'me', if not provided already.
    -- Permissions to the user 'me'.
    GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO me;
    GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH to me;
    -- Additional permissions on the database 'TDAPUSERDB'.
    GRANT CREATE TABLE ON TDAPUSERDB TO me; -- Required if user does not have "create table" permission.
    GRANT CREATE VIEW ON TDAPUSERDB TO me; -- Required if user does not have "create view" permission.
    GRANT SELECT ON TDAPUSERDB TO me;
  • Set the search path and run the Script function as the user 'me'.
    -- Set the search path.
    SET SESSION SEARCHUIFDBPATH = TDAPUSERDB;
    -- Create view on top of Script function.
    -- Note that view creation is successful.
    create view vw3 as SELECT * FROM Script(
     ON "TDAPUSERDB"."usecase5" AS "input"
     PARTITION BY ANY
     SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
     delimiter(',')
     returns('oc1 VARCHAR(10), oc2 VARCHAR(10)')
    ) as sqlmr;
     *** View has been created.
     *** Total elapsed time was 1 second.
    -- Seeing the contents of the view is not possible as the user 'me' does not have 
    -- execute permissions on TD_SYSFNLIB.SCRIPT and default auth i.e., SYSUIF.DEFAULT_AUTH.
     
    select * from vw3;
     *** Failure 3523 An owner referenced by user does not have EXECUTE FUNCTION
      WITH GRANT OPTION access to TD_SYSFNLIB.SCRIPT.
  • Give the user "me" following permissions, for the above select on view 'vw3' to work.
    GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO TDAPUSERDB WITH GRANT OPTION;
    GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH TO TDAPUSERDB WITH GRANT OPTION;

Sandbox Environment

tdplyr provides user a sandbox environment that can be used to run user scripts outside Vantage. This helps in testing user scripts in a Vantage-like environment before they are uploaded for execution in the target Advanced SQL Engine.

The sandbox environment is based on a Docker image that has:
  • R Interpreter (Version 3.5.1)
  • Add on packages for R
  • A script that will enable user to run R script within the sandbox environment. Input to this R script can be:
    • A file with input data
    • Data read from all AMPs
The size of Docker image is around 2.96GB. Due to the large image size, Teradata recommends downloading it beforehand, and saving it into a local folder.

You can choose to setup the Docker environment and test R scripts by running them inside the Docker container, or you can directly execute your scripts on Vantage.

When configuring Docker on Windows 10, if you encounter the error Cannot enable Hyper-V service, run the following command from PowerShell window:
bcdedit /set hypervisorlaunchtype auto

Sample Script Table Operator workflow

The following is a typical Script Table Operator workflow using tdplyr.

tdplyr_Script_flow