I've created a model in R, published into SQL Server table and validated the model by calling it into SQL Server. However, I'm failing in an attempt to use the model for prediction over new data. Here's my script:
DROP PROCEDURE IF EXISTS predict_risk_new_data;
GO
CREATE OR ALTER PROCEDURE predict_risk_new_data (@q nvarchar(max))
AS
BEGIN
DECLARE @model varchar(30) = 'risk_rxLogit'
DECLARE @rx_model varbinary(max) = (SELECT model FROM rx_models WHERE model_name = @model);
EXEC sp_execute_external_script
@language = N'R'
,@script = N'
require("RevoScaleR");
input_data = InputDataSet;
model <- unserialize(rx_model);
prediction <- rxPredict(model, input_data, writeModelVars = TRUE);
OutputDataSet <- cbind(predictions[1], predictions[2]);'
,@input_data_1 = @q
,@parallel = 1
,@params = N'@rx_model varbinary(max), @r_rowsPerRead int'
,@input_data_1_name = N'InputDataSet'
,@rx_model = @rx_model
,@r_rowsPerRead = 100
WITH result sets (("Risk_Pred" float, "ZIP" int));
END
GO;
/*
EXEC predict_risk 'SELECT TOP 100 [ZIP], [Week], [Age], [Risk] FROM dbo.Risk'
*/
Here's the error output:
Msg 39004, Level 16, State 20, Line 223 A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 223 An external script error occurred: Error in unserialize(rx_model) : read error Calls: source -> withVisible -> eval -> eval -> unserialize
Error in execution. Check the output for more information. Error in eval(expr, envir, enclos) : Error in execution. Check the output for more information. Calls: source -> withVisible -> eval -> eval -> .Call Execution halted
New to R/ML in SQL Server, help would be aprreciated. Thanks in advance.
When I did something like this I had to add as.raw to the model. Try this
model <- unserialize(as.raw(rx_model));
User contributions licensed under CC BY-SA 3.0