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'.
- 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;
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.
- 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
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.
bcdedit /set hypervisorlaunchtype auto
Sample Script Table Operator workflow
The following is a typical Script Table Operator workflow using tdplyr.