使用 SQL 机器学习和 PREDICT T-SQL 函数进行本地评分

适用于： SQL Server 2017 （14.x）及更高版本 Azure SQL 托管实例 Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse是数据湖基础上的企业规模关系仓库，具有未来就绪的体系结构、内置 AI 和新功能。如果不熟悉数据仓库，请从Fabric Data Warehouse开始。现有的指定 SQL 池工作负荷可以升级到 Fabric，以跨数据科学、实时分析和报告访问新功能。

了解如何结合使用 PREDICT T-SQL 函数和本机评分功能，以近乎实时的方式为新数据输入生成预测值。本机评分要求你具有已定型的模型。

PREDICT 函数使用 SQL机器学习中的本机 C++ 扩展功能。此方法可提供最快的处理速度，适用于 Open Neural Network Exchange (ONNX) 格式的预测和工作负载以及支持模型（仅限 Azure Synapse Analytics），或使用 RevoScaleR 和 revoscalepy 包训练的模型。

本机评分的工作原理

本机评分使用可以读取 ONNX 或预定义二进制格式的模型的库，并为你提供的新数据输入生成分数。由于已对模型进行定型、部署和存储，因此它可用于评分，而无需调用 R 或 Python 解释器。这意味着减少了多个流程交互的开销，从而提高了预测性能。

若要使用本机评分，请调用 PREDICT T-SQL 函数并传递以下必需的输入：

基于受支持的模型和算法的兼容模型。
输入数据，通常定义为 T-SQL 查询。

此函数将返回输入数据的预测以及要传递的源数据的任何列。

先决条件

PREDICT 可在以下平台使用：

Windows 和 Linux 上的 SQL Server 2017 及更高版本的所有版本
Azure SQL 托管实例
Azure Synapse Analytics

默认启用该函数。无需安装 R 或 Python，也不需要启用其他功能。

支持的模型

PREDICT 函数支持的模型格式取决于执行本机评分的 SQL 平台。请参阅下表，了解各平台上支持的模型格式。

平台	ONNX 模型格式	RevoScale 模型格式
SQL Server	否	是
Azure SQL 托管实例	否	是
Azure Synapse Analytics	是	否

ONNX 模型

模型须为 Open Neural Network Exchange (ONNX) 模型格式。

RevoScale 模型

必须预先使用 RevoScaleR 或 revoscalepy 包，通过下列受支持的 rx 算法之一对模型进行训练。

使用适用于 R 的 rxSerialize 和适用于 Python 的 rx_serialize_model 对模型进行序列化。已对这些序列化函数进行了优化，以支持快速评分。

支持的 RevoScale 算法

Revoscalepy 和 RevoScaleR 支持以下算法。

revoscalepy 算法
- rx_lin_mod
- rx_logit
- rx_btrees
- rx_dtree
- rx_dforest
RevoScaleR 算法
- rxLinMod
- rxLogit
- rxBTrees
- rxDtree
- rxDForest

如果需要使用 MicrosoftML 或 microsoftml 中的算法，请通过 sp_rxPredict 进行实时评分。

不受支持的模型类型包括以下类型：

包含其他转换的模型
使用 RevoScaleR 或 revoscalepy 等效项中的 rxGlm 或 rxNaiveBayes 算法的模型
PMML 模型
使用其他开放源代码或第三方库创建的模型

示例

使用 ONNX 模型进行预测

此示例演示了如何使用存储在 dbo.models 表中的 ONNX 模型进行本机评分。

DECLARE @model VARBINARY(max) = (
        SELECT DATA
        FROM dbo.models
        WHERE id = 1
        );

WITH predict_input
AS (
    SELECT TOP (1000) [id]
        , CRIM
        , ZN
        , INDUS
        , CHAS
        , NOX
        , RM
        , AGE
        , DIS
        , RAD
        , TAX
        , PTRATIO
        , B
        , LSTAT
    FROM [dbo].[features]
    )
SELECT predict_input.id
    , p.variable1 AS MEDV
FROM PREDICT(MODEL = @model, DATA = predict_input, RUNTIME=ONNX) WITH (variable1 FLOAT) AS p;

注意

由于 PREDICT 返回的列和值可能因模型类型而有所不同，因此必须使用 WITH 子句定义返回数据的架构。

使用 RevoScale 模型进行预测

此示例使用 R 中的 RevoScaleR 创建一个模型，然后调用 T-SQL 中的实时预测函数。

步骤 1。准备并保存模型

运行以下代码以创建示例数据库和所需的表。

CREATE DATABASE NativeScoringTest;
GO
USE NativeScoringTest;
GO
DROP TABLE IF EXISTS iris_rx_data;
GO
CREATE TABLE iris_rx_data (
    "Sepal.Length" float not null, "Sepal.Width" float not null
  , "Petal.Length" float not null, "Petal.Width" float not null
  , "Species" varchar(100) null
);
GO

使用以下语句将 iris 数据集的数据填充到数据表中。

INSERT INTO iris_rx_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width" , "Species")
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'iris_data <- iris;'
  , @input_data_1 = N''
  , @output_data_1_name = N'iris_data';
GO

现在，创建用于存储模型的表。

DROP TABLE IF EXISTS ml_models;
GO
CREATE TABLE ml_models ( model_name nvarchar(100) not null primary key
  , model_version nvarchar(100) not null
  , native_model_object varbinary(max) not null);
GO

以下代码创建基于 iris 数据集的模型，并将其保存到名为“模型”的表中。

DECLARE @model varbinary(max);
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'
    iris.sub <- c(sample(1:50, 25), sample(51:100, 25), sample(101:150, 25))
    iris.dtree <- rxDTree(Species ~ Sepal.Length + Sepal.Width + Petal.Length + Petal.Width, data = iris[iris.sub, ])
    model <- rxSerializeModel(iris.dtree, realtimeScoringOnly = TRUE)
    '
  , @params = N'@model varbinary(max) OUTPUT'
  , @model = @model OUTPUT
  INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
  VALUES('iris.dtree','v1', @model) ;

注意

请确保使用 RevoScaleR 的 rxSerializeModel 函数保存模型。标准 R serialize 函数无法生成所需的格式。

可以运行如下语句，以二进制格式查看存储的模型：

SELECT *, datalength(native_model_object)/1024. as model_size_kb
FROM ml_models;

步骤 2. 在模型上运行 PREDICT

以下简单的 PREDICT 语句使用本地评分（native scoring）函数从决策树模型中获得分类结果。它根据提供的属性、花瓣长度和宽度预测 iris 种类。

DECLARE @model varbinary(max) = (
  SELECT native_model_object
  FROM ml_models
  WHERE model_name = 'iris.dtree'
  AND model_version = 'v1');
SELECT d.*, p.*
  FROM PREDICT(MODEL = @model, DATA = dbo.iris_rx_data as d)
  WITH(setosa_Pred float, versicolor_Pred float, virginica_Pred float) as p;
go

如果你收到错误信息：“执行函数 PREDICT 时发生错误。” 模型已损坏或无效”，这通常意味着查询未返回模型。检查键入的模型名称是否正确，或模型表是否为空。

注意