Possible Invalid Ways of using Script() | Teradata R Package - 17.00 - Possible Invalid Ways of using 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
This section lists some of the invalid ways of using Script() function and corresponding error messages from Vantage, which helps the users to check and revisit arguments passed to the Script() function and fix the user script, if needed.

Example 1: Using invalid database

In this example invalid database 'invalid_db' is used in the argument "script.command".

  • Create a ScriptTableOperator object that allows you to execute user script in Vantage.
    > script_obj <- Script(data = barrier %>% select(Name),
                           script.command = "Rscript ./invalid_db/mapper.R",
                           returns = list("word" = "VARCHAR(15)", "count_input" = "VARCHAR(2)"),
                           data.order.column = "Name"
      )
  • Run user script on Vantage.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON ( SELECT "Name"
    FROM "barrier" ) as "input"
        PARTITION BY ANY
        ORDER BY "Name" ASC NULLS FIRST
        SCRIPT_COMMAND('Rscript ./invalid_db/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

Example 2: Using invalid script command

In this example, invalid script command is used in the argument "script.command".

  • Create a ScriptTableOperator object that allows you to execute user script in Vantage.
    > script_obj <- Script(data = barrier %>% select(Name),
                           script.command = "R ./TDAPUSERDB/mapper.R",
                           returns = list("word" = "VARCHAR(15)", "count_input" = "VARCHAR(2)"),
                           data.order.column = "Name"
      )
  • Run user script on Vantage.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON ( SELECT "Name"
    FROM "barrier" ) as "input"
        PARTITION BY ANY
        ORDER BY "Name" ASC NULLS FIRST
        SCRIPT_COMMAND('R ./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
    >

Example 3: Using invalid data type in "returns" argument

In this example, invalid data type is used in "returns" argument.

Note that VARCHAR(7) is used for the first column, where as one of the values for the column is 'Macdonald' which has 9 characters.

  • Create a ScriptTableOperator object that allows you to execute user script in Vantage.
    > script_obj <- Script(data = barrier %>% select(Name),
                           script.command = script_command,
                           returns = list("word" = "VARCHAR(7)", "count_input" = "VARCHAR(2)"),
                           data.order.column = "Name"
      )
    >
  • Run user script on Vantage.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON ( SELECT "Name"
    FROM "barrier" ) as "input"
        PARTITION BY ANY
        ORDER BY "Name" ASC NULLS FIRST
        SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
        DELIMITER('    ')
        RETURNS('word VARCHAR(7)','count_input VARCHAR(2)')
        CHARSET('LATIN')
    )  as sqlmr
    $result
     Error in obtainRows(res, FALSE, params) :
      [Version 17.0.0.4] [Session 8010] [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

Example 4: Using "barrier" instead of "barrier %>% select(Name)"

In this example, `barrier` is used instead of `barrier %>% select(Name)`.

Since the 'barrier' tbl_teradata has two columns, one of the output row generates three columns (1, old, 1) as per the script 'mapper.R' but the Script() function call mentions that the return tbl_teradata should have two columns. Note that the error message is different.

  • Create a ScriptTableOperator object that allows you to execute user script in Vantage.
    > script_obj <- Script(data = barrier,
                           script.command = script_command,
                           returns = list("word" = "VARCHAR(15)", "count_input" = "VARCHAR(20)"),
                           data.order.column = "Name"
      )
  • Run user script on Vantage.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON "TDAPUSERDB"."barrier" as "input"
        PARTITION BY ANY
        ORDER BY "Name" ASC NULLS FIRST
        SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
        DELIMITER('    ')
        RETURNS('word VARCHAR(15)','count_input VARCHAR(20)')
        CHARSET('LATIN')
    )  as sqlmr
    $result
     Error in obtainRows(res, FALSE, params) :
      [Version 17.0.0.4] [Session 8010] [Teradata Database] [Error 9134] Error in function SCRIPT: The output of SCRIPT_COMMAND returned more than the expected 2 columns.
     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 database/sql.withLock

Example 5: Using "barrier" instead of "barrier %>% select(Name)"

In this example, `barrier` is used instead of `barrier %>% select(Name)`.

Since barrier tbl_teradata has two columns, all the rows that are generated have two columns except one row which has three columns (1, old, 1) as per the script 'mapper.R' but the Script() function call mentions that the return tbl_teradata should have three columns. Note that the error message is different now as expected.

  • Create a ScriptTableOperator object that allows you to execute user script in Vantage.
    > script_obj <- Script(data = barrier,
                           script.command = script_command,
                           returns = list("word" = "VARCHAR(15)", "word1" = "VARCHAR(20)", "word2" = "VARCHAR(20)"),
                           data.order.column = "Name"
      )
    >
  • Run user script on Vantage.
    > td_execute_script(script_obj)
    SQL-MR Query :
     SELECT * FROM Script (
        ON "TDAPUSERDB"."barrier" as "input"
        PARTITION BY ANY
        ORDER BY "Name" ASC NULLS FIRST
        SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
        DELIMITER('    ')
        RETURNS('word VARCHAR(15)','word1 VARCHAR(20)','word2 VARCHAR(20)')
        CHARSET('LATIN')
    )  as sqlmr
    $result
     Error in obtainRows(res, FALSE, params) :
      [Version 17.0.0.4] [Session 8010] [Teradata Database] [Error 9134] Error in function SCRIPT: The output of SCRIPT_COMMAND returned less than the expected 3 columns.
     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 database/sql.withLock