结合利用 SQL 机器学习和 PREDICT T-SQL 函数的本机评分功能

  • 项目

适用于： SQL Server 2017 (14.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics

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

PREDICT 函数使用 SQL机器学习中的本机 C++ 扩展功能。 该方法可以使预测工作负载的处理速度达到可能的最快速度，并支持 Open Neural Network Exchange (ONNX) 格式的模型或使用 RevoScaleR 和 revoscalepy 包定型的模型。

本机评分的工作原理

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

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

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

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

先决条件

PREDICT 适用于：

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

默认启用该函数。 无需安装 R、Python 或启用其他功能。

支持的模型

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

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

ONNX 模型

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

RevoScale 模型

必须使用 RevoScaleR 或 revoscalepy 包以及下面列出的某个支持的 rx 算法对该模型进行预定型。

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

支持的 RevoScale 算法

Revoscalepy 和 RevoScaleR 支持以下算法。

如果需要使用 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 执行期间出错。 模型已损坏或无效”，这通常意味着查询未返回模型。 检查键入的模型名称是否正确，或模型表是否为空。

注意

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

后续步骤