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 the
rxPredict R function in RevoScaleR and MicrosoftML, and the rx_predict Python function in revoscalepy and microsoftml. It is written in C++ and is optimized specifically for scoring operations.
Although the model must be created using R or Python, once it is 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.
sp_rxPredict ( @model, @input )
A pretrained model in a supported format.
A valid SQL query
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.
To enable use of the stored procedure, SQLCLR must be enabled on the instance.
There are security implications to enabing 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.
To create and train model, use one of the supported algorithms for R or Python, provided by SQL Server 2016 R Services, SQL Server 2016 R Server (Standalone), SQL Server 2017 Machine Learning Services (R or Python), or SQL Server 2017 Server (Standalone) (R or Python).
R: RevoScaleR models
R: MicrosoftML models
R: Transformations supplied by MicrosoftML
Python: revoscalepy models
Python: microsoftml models
Python: Transformations supplied by microsoftml
Unsupported model types
The following model types are not supported:
- Models using the
rxNaiveBayesalgorithms in RevoScaleR
- PMML models in R
- Models created using other third-party libraries
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.