td_execute_script() | Teradata R Package - td_execute_script() - Teradata Package for R

Teradata® Package for R User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
Product Category
Teradata Vantage

The td_execute_script() function enables a user to run script using Script Table Operator on Vantage.

To use the td_execute_script() function:
  • The script file (in this case "mapper.R") should be installed in Vantage.
  • Search path should be set to appropriate database.
If these prerequisites are not met, then the function execution fails in Vantage with error message like:
Error in obtainRows(res, FALSE, params) :
  [Version 17.0.0.4] [Session 8006] [Teradata Database] [Error 9134] Error in function SCRIPT: The output of SCRIPT_COMMAND for column 1 could not be converted to the result data type.
To avoid the need to debug error messages from Vantage for Script Table Operator, it is highly recommended to pay utmost attention while creating ScriptTableOperator object using Script() function call.

See examples in Possible Invalid Ways of using Script() for known issues when running Script() function on Vantage.

Example

  • Install the script file in Vantage.

    This step installs the file 'mapper.R' found in the 'scripts' directory of tdplyr installation location.

    > td_install_file(file.path.loc = file.path(tdplyr_install_location, "scripts", "mapper.R"), file.identifier = 'mapper')
    File 'mapper' installed in Vantage.
  • Run td_execute_script() without setting search path.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON ( SELECT "Name"
    FROM "barrier" ) as "input"
        PARTITION BY ANY 
        SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
        DELIMITER(' ')
        RETURNS('word VARCHAR(15)','count_input VARCHAR(2)')
        CHARSET('LATIN')
    )  as sqlmr
    $result
     Error in obtainRows(res, FALSE, params) :
      [Version 17.0.0.4] [Session 8006] [Teradata Database] [Error 9134] Error in function SCRIPT: The output of SCRIPT_COMMAND for column 1 could not be converted to the result data type.
     at gosqldriver/teradatasql.(*teradataConnection).formatDatabaseError TeradataConnection.go:1138
     at gosqldriver/teradatasql.(*teradataConnection).makeChainedDatabaseError TeradataConnection.go:1154
     at gosqldriver/teradatasql.(*teradataConnection).processErrorParcel TeradataConnection.go:1217
     at gosqldriver/teradatasql.(*TeradataRows).processResponseBundle TeradataRows.go:1716
     at gosqldriver/teradatasql.(*TeradataRows).executeSQLRequest TeradataRows.go:552
     at gosqldriver/teradatasql.newTeradataRows TeradataRows.go:418
     at gosqldriver/teradatasql.(*teradataStatement).QueryContext TeradataStatement.go:122
     at gosqldriver/teradatasql.(*teradataConnection).QueryContext TeradataConnection.go:2083
     at database/sql.ctxDriverQuery ctxutil.go:48
     at database/sql.(*DB).queryDC.func1 sql.go:1579
     at dat
    As shown in the output, without setting search path, the function fails with error message.
  • Set the search path to the database "TDAPUSERDB".
    > dbExecute(con, dplyr::sql(set_session))
    [1] 1
  • Run the function td_execute_script() again after setting search path.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON ( SELECT "Name"
    FROM "barrier" ) as "input"
        PARTITION BY ANY 
        SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
        DELIMITER(' ')
        RETURNS('word VARCHAR(15)','count_input VARCHAR(2)')
        CHARSET('LATIN')
    )  as sqlmr
    $result
    # Source:   SQL [?? x 1]
    # Database: [Teradata 16.20.50.01] [Teradata Native Driver 17.0.0.4]
    #   [TDAPUSER@<hostname>1025/TDAPUSERDB]
    # Groups: 
      word      count_input
      <chr>     <chr>     
    1 Had       1         
    2 Farm      1         
    3 A         1         
    4 Macdonald 1         
    5 Old       1
    After the search path is set, the function runs correctly with desired output.