sp_rxPredict

APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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.

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 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.

Supported algorithms

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 rxGlm or rxNaiveBayes algorithms in RevoScaleR
  • PMML models in R
  • Models created using other third-party libraries

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.