none
How to load trained R models saved in .rd,.Rdata files into SQL Server R context? RRS feed

  • Question

  • Hi,

    I am trying to experiment with SQL Server 2016 R services and would like to be able to load trained R models in production to score data in the "sp_execute_external_script". For example do the below:

    # Targeting Random Forest -----------------------------------------------------------
    
    message(sprintf("... loading and fitting the RF model for the %s %s data ..."
                    , toupper(client)
                    , toupper(modeltype)
    ))
    
    load(file = sprintf("D:/cst/models/targeting/%s-rf-top-%s-%s-trees.Rd"
                        , toupper(client)
                        , gsub(".", "_", pct * 100, fixed = T)
                        , ntrees
    ))
    
    rf_vars <- sort(names(cst_rf$variable.importance))
    
    

    Is this even possible as I keep getting an error?

    I was able to retrain the model within SQL, store it in a table and then score from it.

    ERROR:

    Msg 39004, Level 16, State 20, Line 8
    A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
    Msg 39019, Level 16, State 1, Line 8
    An external script error occurred: 
    There are 55968 people before removing HCC 134 (ESRD).
    Now theres 55116 people after removing HCC 134 (ESRD).
    Thats a difference of 852 rows.
    ... loading and fitting the RF model for the RI-BCBSRI MONTHLY data ...

    Invalid BXL stream

    Tuesday, May 16, 2017 3:27 PM

Answers

  • Hi Arun,

    What you can do is to serialize your model in, for example, RStudio or Visual Studion and then insert the model in a table in the database:

    # set up the formula for the model
    mymodel <- lm(some_stuff)
    
    # serialize the model
    modelbin <- serialize(mymodel, NULL)
    modelbinstr = paste(modelbin, collapse = "")
    
    # setup a connection to the db
    conn <- odbcDriverConnect(connection = odbcConnString)
    
    # dbo.pr_CreateModel is a proc which stores the mode in a table
    q <- paste("EXEC dbo.pr_CreateModel @Model='", modelbinstr, "'", sep = "")
    sqlQuery(conn, q)

    Then you have some code in T-SQL that grabs the model ens executes sp_execute_external_script. Below is an example of a script, but you would probably have it in a procedure:

    BEGIN  
    
    -- get the model  
    DECLARE @model varbinary(max) = (SELECT TOP 1 model  FROM dbo.tb_RModel);  
      EXEC sp_execute_external_script @language = N'R',  
         @script = N'  
           #deserialize the model
           mod <- unserialize(as.raw(model));  
           
           OutputDataSet<-rxPredict(modelObject = mod, 
               data = InputDataSet, 
               outData = NULL, 
               predVarNames = "Salary", type = "response", 
               writeModelVars = FALSE, overwrite = TRUE);  
           str(OutputDataSet)  
           print(OutputDataSet)',  
      @input_data_1 = @input,  
      @params = N'@model varbinary(max)', --this defines the model as input param to the proc
      @model = @model  
      WITH RESULT SETS ((Salary bigint));  
    
    END

    Hope this helps!

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, May 16, 2017 6:42 PM

All replies

  • Hi Arun,

    What you can do is to serialize your model in, for example, RStudio or Visual Studion and then insert the model in a table in the database:

    # set up the formula for the model
    mymodel <- lm(some_stuff)
    
    # serialize the model
    modelbin <- serialize(mymodel, NULL)
    modelbinstr = paste(modelbin, collapse = "")
    
    # setup a connection to the db
    conn <- odbcDriverConnect(connection = odbcConnString)
    
    # dbo.pr_CreateModel is a proc which stores the mode in a table
    q <- paste("EXEC dbo.pr_CreateModel @Model='", modelbinstr, "'", sep = "")
    sqlQuery(conn, q)

    Then you have some code in T-SQL that grabs the model ens executes sp_execute_external_script. Below is an example of a script, but you would probably have it in a procedure:

    BEGIN  
    
    -- get the model  
    DECLARE @model varbinary(max) = (SELECT TOP 1 model  FROM dbo.tb_RModel);  
      EXEC sp_execute_external_script @language = N'R',  
         @script = N'  
           #deserialize the model
           mod <- unserialize(as.raw(model));  
           
           OutputDataSet<-rxPredict(modelObject = mod, 
               data = InputDataSet, 
               outData = NULL, 
               predVarNames = "Salary", type = "response", 
               writeModelVars = FALSE, overwrite = TRUE);  
           str(OutputDataSet)  
           print(OutputDataSet)',  
      @input_data_1 = @input,  
      @params = N'@model varbinary(max)', --this defines the model as input param to the proc
      @model = @model  
      WITH RESULT SETS ((Salary bigint));  
    
    END

    Hope this helps!

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Tuesday, May 16, 2017 6:42 PM
  • Thanks Niels,

    That definitely helps. I was wondering what is wrong with trying to read the saved model as .Rd or .Rdata into the context? Is it not supported by MSFT yet?

    Wednesday, May 17, 2017 5:17 PM
  • Hi Niels,

    I have tried your suggested method but the serialized models are large (~790Mb) and then while running the sql insert through a stored procedure the R session stops abruptly. Can you suggest a solution? Thanks.

    Wednesday, May 17, 2017 7:15 PM
  • Hi Arun,

    Hmm, any error messages from R? I wonder if it is a memory issue, or it times out. If you are using "normal" R ODBC methods, I'd try to use some of the the RevoScaleR methods to communicate with the database ('cause, they are kind of optimized for db access to SQL Server). It may also be an idea to save the model as a file stream - seeing it is large.

    Niels


    http://www.nielsberglund.com | @nielsberglund

    Thursday, May 18, 2017 3:06 AM
  • No error messages from R. R studio session gets aborted after like 3 minutes. I can give the RevoScaleR methods a try and file stream conversion. Hope one would work.

    Thanks Niels for helping. I greatly appreciate your responses.

    -Arun

    Thursday, May 18, 2017 2:46 PM