sp_rxPredict

Applies to: SQL Server 2016 (13.x) and later - Windows only

Generates a predicted value for a given input consisting of a machine learning model stored in a binary format in a SQL Server database.

Provides scoring on R and Python machine learning models in near real time. sp_rxPredict is a stored procedure provided as a wrapper for

• rxPredict R function in RevoScaleR and MicrosoftML, and the rx_predict Python function in revoscalepy and microsoftml. It's written in C++ and is optimized specifically for scoring operations.

Although the model must be created using R or Python, once it's serialized and stored in a binary format on a target database engine instance, it can be consumed from that database engine instance even when R or Python integration is not installed. For more information, see Real-time scoring with sp_rxPredict.

Syntax

sp_rxPredict  ( @model, @input )

Arguments

model

A pretrained model in a supported format.

input

A valid SQL query

Return values

A score column is returned, as well as any pass-through columns from the input data source. Additional score columns, such as confidence interval, can be returned if the algorithm supports generation of such values.

Remarks

To enable use of the stored procedure, SQLCLR must be enabled on the instance.

Note

There are security implications to enabling this option. Use an alternative implementation, such as the Transact-SQL PREDICT function, if SQLCLR cannot be enabled on your server.

The user needs EXECUTE permission on the database.

Supported algorithms

To create and train model, use one of the supported algorithms for R or Python, provided by SQL Server Machine Learning Services (R or Python), SQL Server 2016 R Services, SQL Server Machine Learning Server (Standalone) (R or Python), or SQL Server 2016 R Server (Standalone).

R: RevoScaleR models

• rxLinMod *
• rxLogit *
• rxBTrees *
• rxDtree *
• rxdForest *

Models marked with * also support native scoring with the PREDICT function.

R: MicrosoftML models

• rxFastTrees
• rxFastForest
• rxLogisticRegression
• rxOneClassSvm
• rxNeuralNet
• rxFastLinear

R: Transformations supplied by MicrosoftML

• featurizeText
• concat
• categorical
• categoricalHash
• selectFeatures

Python: revoscalepy models

• rx_lin_mod *
• rx_logit *
• rx_btrees *
• rx_dtree *
• rx_dforest *

Models marked with * also support native scoring with the PREDICT function.

Python: microsoftml models

• rx_fast_trees
• rx_fast_forest
• rx_logistic_regression
• rx_oneclass_svm
• rx_neural_network
• rx_fast_linear

Python: Transformations supplied by microsoftml

• featurize_text
• concat
• categorical
• categorical_hash

Unsupported model types

The following model types are not supported:

• Models using the rxGlm or rxNaiveBayes algorithms in RevoScaleR.
• PMML models in R.
• Models created using other third-party libraries.
• Models using a transformation function or formula containing a transformation, such as A ~ log(B are not supported in real-time scoring. To use a model of this type, we recommend that you perform the transformation on input data before passing the data to real-time scoring.

Real-time scoring does not use an interpreter, so any functionality that might require an interpreter is not supported during the scoring step.

Examples

DECLARE @model = SELECT @model 
FROM model_table 
WHERE model_name = 'rxLogit trained';

EXEC sp_rxPredict @model = @model,
@inputData = N'SELECT * FROM data';

In addition to being a valid SQL query, the input data in @inputData must include columns compatible with the columns in the stored model.

sp_rxPredict supports only the following .NET column types: double, float, short, ushort, long, ulong and string. You may need to filter out unsupported types in your input data before using it for real-time scoring.

For information about corresponding SQL types, see SQL-CLR Type Mapping or Mapping CLR Parameter Data.