Possible Invalid Ways of using Script() | Teradata R Package - Possible Invalid Ways of using Script() - Teradata Package for R

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