td_execute_script() | Teradata R Package - 17.00 - td_execute_script() - 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 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.