Python 教程：使用 T-SQL 定型和保存 Python 模型

项目
05/30/2023

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

在此系列教程的第四部分中（共五部分），你可了解如何使用 Python 包 scikit-learn 和 revoscalepy 来训练机器学习模型。这些 Python 库已随 SQL Server 机器学习安装。

请加载模块并调用必要的函数，以使用 SQL Server 存储过程创建和训练模型。该模型需要在此系列教程前面的部分中设计的数据功能。最后，将训练后的模型保存到 SQL Server 表中。

在本文中，你将：

使用 SQL 存储过程创建并训练模型
将训练后的模型保存到 SQL 表中

在第一部分中，你安装了必备条件并还原了示例数据库。

在第二部分中，你探索了示例数据，并生成了一些绘图。

在第三部分中，你学习了如何使用 Transact-SQL 函数根据原始数据创建特征。然后从存储过程调用了该函数，创建了包含该功能值的表。

在第五部分中，你将了解如何操作在第四部分中训练和保存的模型。

将示例数据拆分为定型集和测试集

创建名为 PyTrainTestSplit 的存储过程，以将 nyctaxi_sample 表中的数据划分为两部分：nyctaxi_sample_training 和 nyctaxi_sample_testing。

运行以下代码来创建它：

DROP PROCEDURE IF EXISTS PyTrainTestSplit;
GO

CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
AS

DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct

DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
GO

要使用自定义拆分来划分数据，请运行存储过程，并提供一个整数参数，该参数表示分配给定型集的数据的百分比。例如，以下语句会将 60% 的数据分配给定型集。
```
EXEC PyTrainTestSplit 60
GO
```

构建逻辑回归模型

数据准备就绪后，可以使用它来定型模型。为此，可以调用运行某些 Python 代码的存储过程，并将其作为定型数据表的输入。在本教程中，你将创建两个模型，并且这两个模型都是二元分类模型：

存储过程 PyTrainScikit 使用 scikit-learn 包创建小费预测模型。
存储过程 TrainTipPredictionModelRxPy 使用 revoscalepy 包创建小费预测模型。

每个存储过程都使用你提供的输入数据来创建和定型逻辑回归模型。所有 Python 代码包装在系统存储过程 sp_execute_external_script 中。

为了更轻松地基于新数据重新定型模型，可以将对 sp_execute_external_script 的调用包装在另一存储过程中，并将新的定型数据作为参数传入。本部分将引导你完成该过程。

PyTrainScikit

在 Management Studio 中，打开一个新“查询”窗口并运行以下语句以创建存储过程 PyTrainScikit。因为存储过程包含输入数据的定义，所以无需提供输入查询。

DROP PROCEDURE IF EXISTS PyTrainScikit;
GO

CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import numpy
import pickle
from sklearn.linear_model import LogisticRegression

##Create SciKit-Learn logistic regression model
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()
logitObj = SKLalgo.fit(X, y)

##Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

运行以下 SQL 语句，将定型后的模型插入到表 nyc_taxi_models 中。

DECLARE @model VARBINARY(MAX);
EXEC PyTrainScikit @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);

数据处理和模型调整可能需要几分钟时间。通过管道传递到 Python 的 stdout 流的消息会显示在 Management Studio 的“消息”窗口中。例如：

STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

打开表 nyc_taxi_models。可以看到已添加了一个新行，在列 model中包含序列化模型。
```
SciKit_model
0x800363736B6C6561726E2E6C696E6561....
```

TrainTipPredictionModelRxPy

此存储过程使用 revoscalepy Python 包。它所包含的对象、转换和算法与为 R 语言的 RevoScaleR 包提供的对象、转换和算法类似。

通过使用 revoscalepy，可以创建远程计算上下文、在计算上下文之间移动数据、转换数据并使用常见算法（如逻辑和线性回归、决策树等）定型预测模型。有关详细信息，请参阅 SQL Server 中的 revoscalepy 模块和 revoscalepy 函数参考。

在 Management Studio 中，打开新的“查询”窗口并运行以下语句以创建存储过程 TrainTipPredictionModelRxPy。因为存储过程已包含输入数据的定义，所以无需提供输入查询。

DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
GO

CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import numpy
import pickle
from revoscalepy.functions.RxLogit import rx_logit

## Create a logistic regression model using rx_logit function from revoscalepy package
logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);

## Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

此存储过程在模型定型过程中执行以下步骤：

SELECT 查询应用自定义标量函数 fnCalculateDistance 计算上车与下车位置之间的直接距离。查询的结果存储在默认 Python 输入变量 InputDataset 中。
二进制变量 tipped 用作标签或结果列，模型使用以下这些特征列进行调整：passenger_count、trip_distance、trip_time_in_secs 和 direct_distance。
定型后的模型将进行序列化并存储在 Python 变量 logitObj 中。通过添加 T-SQL 关键字 OUTPUT，可以将变量添加为存储过程的输出。在下一步中，该变量用于将模型的二进制代码插入到数据库表 nyc_taxi_models 中。借助此机制，可轻松存储和重新使用模型。

按如下所示运行存储过程，将定型后的 revoscalepy 模型插入到表 nyc_taxi_models 中。

DECLARE @model VARBINARY(MAX);
EXEC TrainTipPredictionModelRxPy @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);

数据处理和模型调整可能需要一些时间。通过管道传递到 Python 的 stdout 流的消息会显示在 Management Studio 的“消息”窗口中。例如：

STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

打开表 nyc_taxi_models。可以看到已添加了一个新行，在列 model中包含序列化模型。
```
revoscalepy_model
0x8003637265766F7363616c....
```

在此教程的下一部分中，你将使用已训练的模型来创建预测。

后续步骤

本文内容：

使用 SQL 存储过程创建并训练了模型
将训练后的模型保存到了 SQL 表中

Python 教程：使用存储过程中嵌入的 Python 来运行预测

通过