R in SQL Server: $ operator is invalid for atomic vectors

0

I am trying to port some of my working R scripts to SQL Server. I have the following R code:

titanic <- read.csv(titanicUri, header = TRUE)
titanic <- titanic[complete.cases(titanic),]
titanic$AgeGroup <- cut(titanic$Age, c(0,13,100), labels=c("Young","Old"))

I created a table in SQL Server and imported the data. When I attempt to implement the 'cut' function like this:

EXECUTE sp_execute_external_script    
      @language = N'R'    
    , @script = N' 
        titanic <- InputDataSet;
        titanic <- titanic[complete.cases(titanic),];
        titanic$AgeGroup <- cut(titanic$Age, c(0,13,100), labels=c("Young","Old"));
        titanic <- data.frame(titanic);
        OutputDataSet <- titanic;
        '    
    , @input_data_1 = N' SELECT Name FROM Titanic;'    
    WITH RESULT SETS (([PassengerName] varchar(Max)));

I am getting this error (the complete.cases works fine)

Error in titanic$Age : $ operator is invalid for atomic vectors

Msg 39004, Level 16, State 20, Line 11
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

Msg 39019, Level 16, State 1, Line 11
An external script error occurred: Error in titanic$Age : $ operator is invalid for atomic vectors Calls: source -> withVisible -> eval -> eval -> cut

Error in ScaleR. Check the output for more information. Error in eval(expr, envir, enclos) : Error in ScaleR. Check the output for more information. Calls: source -> withVisible -> eval -> eval -> .Call Execution halted

Msg 11536, Level 16, State 1, Line 11
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Thanks in advance

sql-server
r
asked on Stack Overflow Feb 13, 2017 by Jamie Dixon • edited Jun 20, 2020 by Community

1 Answer

1

As @TimGoodman comments, your input dataset is one column due to specified SELECT query. Simply expand column listing to have Age available:

@input_data_1 = N'SELECT * FROM Titanic;'

Consider too specifying output dataset. See helpful mssqltips.com guide.

@output_data_1_name = N'titanic'
answered on Stack Overflow Feb 13, 2017 by Parfait

User contributions licensed under CC BY-SA 3.0