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 written in C++, and is optimized specifically for scoring operations. sp_rxPredict is a wrapper for:

Function	Wrapper
rxPredict R function	- RevoScaleR - MicrosoftML
rx_predict Python function	- revoscalepy - microsoftml

The model must be created using R or Python. However, 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 isn't installed. For more information, see Real-time scoring with sp_rxPredict in SQL Server.

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.

Extra 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 PREDICT (Transact-SQL) function, if SQLCLR can't 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 also support native scoring with the PREDICT function.

R: MicrosoftML models

• rxFastTrees: Fast Tree
• rxFastForest: Fast Forest
• rxLogisticRegression: Logistic Regression
• rxOneClassSvm: OneClass SVM
• rxNeuralNet: Neural Net
• rxFastLinear: Fast Linear Model

R: Transformations supplied by MicrosoftML

• rxFastTrees: Fast Tree
• concat: Machine Learning Concat Transform
• categorical: Machine Learning Categorical Data Transform
• categoricalHash: Machine Learning Categorical HashData Transform
• selectFeatures: Machine Learning Feature Selection Transform

Python: revoscalepy models

• rx_lin_mod ¹
• rx_logit ¹
• rx_btrees ¹
• rx_dtree ¹
• rx_dforest ¹

¹ Models also support native scoring with the PREDICT function.

Python: microsoftml models

• microsoftml.rx_fast_trees: Boosted Trees
• microsoftml.rx_fast_forest: Random Forest
• microsoftml.rx_logistic_regression: Logistic Regression
• microsoftml.rx_oneclass_svm: Anomaly Detection
• microsoftml.rx_neural_network: Neural Network
• microsoftml.rx_fast_linear: Linear Model with Stochastic Dual Coordinate Ascent

Python: Transformations supplied by microsoftml

• microsoftml.rx_fast_trees: Boosted Trees
• microsoftml.concat: Concatenates multiple columns into a single vector
• microsoftml.categorical: Converts a text column into categories
• microsoftml.categorical_hash: Hashes and converts a text column into categories

Unsupported model types

The following model types aren't supported:

• Models using the rxGlm or rxNaiveBayes algorithms in RevoScaleR.

• PMML models in R.

• Models created using other non-Microsoft libraries.

• Models using a transformation function or formula containing a transformation, such as A ~ log(B) aren't 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 doesn't use an interpreter, so any functionality that might require an interpreter isn't 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 might 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.