tdplyr offers the following functions to work with Script Table Operator of Analytics Database:
- Script(): This function constructs a ScriptTableOperator object, that can be used to execute an user-installed script or any Linux command inside database on Vantage.
- td_setup_test_env(): This function enables the user to load the downloaded Docker image and help setting up the Docker environment for testing R scripts.
- td_test_script(): This S3 function enables the user to run script in Docker container environment outside Vantage. Input data for user script is read from file or Vantage.
- td_set_data(): This S3 function enables the user to set data and data related arguments without having to re-create ScriptTableOperator object.
- td_execute_script(): This S3 function enables user to run R script on Vantage or execute any Linux command using Teradata's Script Table Operator without having to write the SQL query.
For more information on the functions' signature and their arguments, refer the documentation in Teradata Package for R Function Reference or in-line help using:
> ?td_execute_script.ScriptTableOperator
Required Docker and OS Versions
The functions td_setup_test_env() and td_test_script() are supported on the following platforms with Docker 19.03.5 or later versions:
- Ubuntu 16.04.2 LTS or later versions
- CentOS Linux 7 or later versions
- RHEL 7.1 or later versions
- Windows 10 or later versions
- macOS 13 or later versions
The td_setup_test_env() and td_test_script() functions cannot work on VMs that do not have proper Docker setup. You will encounter issues with Docker setup on VMs that do no support nested virtualization. For example, VM fails to start because one of the Hyper-V components is not running.
See https://docs.docker.com/desktop/vm-vdi/ for details about this issue.
Example Prerequisites
This section sets up the environment for examples in the following STO related functions. All examples assume that the context is created with the default database set to "TDAPUSERDB". And the user is "TDAPUSER", password is "TDAPPSWD".
- Create connection to default database "TDAPUSERDB".Using dbConnect on Teradata SQL Driver for R as td_create_context() does not support the database argument yet.
> con1 <- dbConnect(teradatasql::TeradataDriver(), host = <hostname>, user = "TDAPUSER", password = "TDAPPSWD", database = "TDAPUSERDB") > td_set_context(con1) > con <- td_get_context()$connection
> con <Teradata Native Driver Connection> TDAPUSER@<hostname> Database: TDAPUSERDB Teradata Version: 16.20.27.01 <TeradataConnection Driver=17.0.0.4 Database=16.20.27.01 Host=<hostname> uConnHandle=2>
- Load example data.
> loadExampleData("script_example", "barrier") Loading: TDAPUSERDB.barrier ...> barrier <- tbl(con, "barrier") > barrier # Source: table<barrier> [?? x 2] # Database: [Teradata 16.20.27.01] [Teradata Native Driver 17.0.0.4] # [TDAPUSERDB@<hostname>1025/TDAPUSERDB] Id Name <int> <chr> 1 1 Old Macdonald Had A Farm> db_name <- "TDAPUSERDB"
- Set Script command that is used in Script() function.
> script_command <- gettextf("Rscript ./%s/mapper.R", db_name)> script_command [1] "Rscript ./TDAPUSERDB/mapper.R"
- Set search path string. This is used to set search path while running td_execute_script() function.
> set_session <- gettextf('SET SESSION SEARCHUIFDBPATH = "%s";', db_name)> set_session [1] "SET SESSION SEARCHUIFDBPATH = \"TDAPUSERDB\";"
- .libPaths() gives a vector of locations where R packages are installed. Use the one where tdplyr is installed.
> tdplyr_install_location <- file.path(.libPaths()[1], "tdplyr")
- Set the Docker image location. Replace <path_to_docker_image> with the actual local path to the downloaded docker image file.
> docker_image_location <- "<path_to_docker_image>/sto_sandbox_R3.5.1_sles12sp3.0.5.2_docker_image.1.0.0.tar.gz"
The following sections introduce STO related functions which enable the execution of user installed scripts in Vantage.
See Teradata Package for R Function Reference for detailed description of the functions' arguments and their usage.