Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 135 of 212

123 C H A P T E R 6 | More analytics Batch mode invocation of a model The stored procedure in the sample database that invokes the model in batch mode is shown in Example 6-12. This stored procedure, PredictTipBatchMode, retrieves the stored model and stores it in a variable that becomes a parameter for the sp_execute_external_script stored procedure. You pass the data to score as a query string into PredictTipBatchMode. It becomes a data frame called InputDataSet used in the rxPredict function that sp_execute_external_script executes. The output of this stored procedure is a set of rows containing a score for each row in the input. Example 6-12: Creating a stored procedure to invoke a model in batch mode CREATE PROCEDURE [dbo].[PredictTipBatchMode] @inquery nvarchar(max) AS BEGIN DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model FROM nyc_taxi_models); EXEC sp_execute_external_script @language = N'R', @script = N' mod <- unserialize(as.raw(model)); print(summary(mod)) OutputDataSet<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL, predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE); str(OutputDataSet) print(OutputDataSet)', @input_data_1 = @inquery, @params = N'@model varbinary(max)', @model = @lmodel2 WITH RESULT SETS ((Score float)); END Individual scoring mode invocation of a model Rather than score a set of rows in batch mode, you can score a single case. Example 6-13 shows the PredictTipSingleMode stored procedure in the sample database, which illustrates this approach. It is similar to the previous example, except the PredictTipSingleMode stored procedure defines input parameters for each of the variables in your training data set. These parameters are then sent to a table-valued helper function in the sample database that computes the linear distance, and the result becomes the InputDataSet data frame. The output is a single value that represents the probability of a tip. Example 6-13: Creating a stored procedure to invoke a model in single mode CREATE PROCEDURE [dbo].[PredictTipSingleMode] @passenger_count int = 0, @trip_distance float = 0, @trip_time_in_secs int = 0, @pickup_latitude float = 0, @pickup_longitude float = 0, @dropoff_latitude float = 0, @dropoff_longitude float = 0 AS BEGIN DECLARE @inquery nvarchar(max) = N' SELECT * FROM [dbo].[fnEngineerFeatures]( @passenger_count, @trip_distance, @trip_time_in_secs, @pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude)' DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model FROM nyc_taxi_models); EXEC sp_execute_external_script @language = N'R', @script = N'

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016