How to unserialize R model in SQL Server for predicting?

0

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.

r
sql-server
asked on Stack Overflow Oct 11, 2018 by Stanislav Jirak • edited Oct 11, 2018 by desertnaut

1 Answer

0

When I did something like this I had to add as.raw to the model. Try this

    model <- unserialize(as.raw(rx_model));
answered on Stack Overflow May 22, 2019 by Daniel Bourguet

User contributions licensed under CC BY-SA 3.0