SQL Server 2022 机器学习服务释放数据价值

本质上，您在存储过程中运行 SQL Server 中的 Python 和 R 脚本（sp_execute_external_script），该存储过程调用 Launchpad 服务到相应的库以执行。机器学习服务依赖于扩展框架来运行 Python 和 R 脚本。包含 R 和 Python 的基本发行版，您可以安装和使用开源框架和库，例如 TensorFlow、scikit-learn 和流行的 PyTorch。

在本教程中，您将学习如何在 Windows 上安装 SQL Server 机器学习服务，配置开发工具以及编写和运行 Python 或 R 脚本。所有 SQL 和 Python 代码都可以在此处找到。

安装 SQL Server 2022 机器学习服务

完成安装前检查清单。

如果您已部署 SQL Server 2022，则可以随意使用它。

如果没有，请安装以下其中一项，并确保也安装 Polybase

• 适用于 Docker 的 SQL Server 2022
• 适用于 Windows 的 SQL Server 2022
• 适用于 Linux 的 SQL Server 2022

运行 SQL Server 2022 安装向导。根据您的环境，您将安装 SQL Server 并添加 ML 功能，或者只是将 ML 功能添加到现有部署中。

在“安装”选项卡上，选择“新建 SQL Server 独立安装”或“将功能添加到现有安装”。

在下一页“功能选择”中，选择以下内容进行安装

• 数据库引擎服务
• 机器学习服务

下面的屏幕截图显示了要安装的最低限度功能

您还需要一种与 SQL Server 交互的方式

• Microsoft SQL Server Management Studio (SSMS)
• Microsoft sqlcmd 实用程序

确保 SQL Server 正在运行，并使用上述任一工具连接到它。

安装 R 的运行时和包

下载并安装最新版本的适用于 Windows 的 R 4.2。

安装依赖项。在 RStudio 或 RStudio 终端中，运行以下命令

# R Terminal
install.packages("iterators")
install.packages("foreach")
install.packages("R6")
install.packages("jsonlite")

下载并安装 CompatibilityAPI 和 RevoScaleR 包

install.packages("https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip", repos=NULL)

install.packages("https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip", repos=NULL)

使用 SQL Server 配置已安装的 R 运行时。使用 RegisterRext.exe 命令行实用程序配置 R。您可以在 R 库中的 RevoScaleR 目录中找到它（在我的情况下，这是 C:\Users\msarr\AppData\Local\R\win-library\4.3\RevoScaleR\rxLibs\x64）。使用管理员权限从此位置打开命令行并运行以下命令，但首先请确保使用Rhome 路径、用户名、密码和 SQL Server 实例名称来适配您的环境

.\RegisterRext.exe /configure /user:SA /password:0JK23mdfidf //rhome:"%ProgramFiles%\R\R-4.3.2" /instance:"MSSQLSERVER"
...
Stopping service MSSQLLaunchpad...
Copied RLauncher.dll from C:\Users\msarr\AppData\Local\R\win-library\4.3\RevoScaleR\rxLibs\x64\ to C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn.
Starting service MSSQLLaunchpad...
R extensibility installed successfully.

使用SSMS连接到之前安装了机器学习服务的 SQL Server 实例。打开一个新的查询窗口并运行以下命令以启用外部脚本

EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE

请注意，无论您要支持多少种语言，您只需启用一次外部脚本即可。

当您在下面看到 config_value 和 run_value 返回 1 时，验证命令是否已成功执行

EXEC sp_configure  'external scripts enabled';

重新启动 SQL Server 实例以应用更改。在 SSMS 中，右键单击服务器实例并选择“重新启动”。

使用一个简单的 T-SQL 命令验证安装，该命令返回 R 版本

EXEC sp_execute_external_script @script=N'print(R.version)',@language=N'R';
GO

不幸的是，在我的情况下，这返回了一个错误，克服该错误需要花费一些精力。我不得不承认，鉴于我之前与 R 的古怪经历，我并不感到意外。

Msg 39021, Level 16, State 13, Line 0
Unable to launch runtime for 'R' script for request id: A06D71E3-B436-4834-B8C3-15153E906CAC. Please check the configuration of the 'R' runtime. See 'https://docs.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16' for setup instructions.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred: 
Unable to launch the runtime. ErrorCode 0x80004005: .

搜索这些错误将我带到了Python 和 R 脚本的 Launchpad 疑难解答 - SQL Server 机器学习服务。浏览这篇文章让我了解到可能存在配置错误，可能是 SQL Server Launchpad 服务或 R 工作目录的权限问题，或者两者兼而有之。

如果您遇到此错误，第一步是确定 Launchpad 是否正在运行。转到 Windows 菜单并运行 SQLServerManager16.msc。确保 Launchpad 正在运行；如果未运行，请重新启动它。记下 Launchpad 正在运行的的服务帐户的名称。

如果 Launchpad 未运行，请检查系统事件日志。打开事件查看器，切换打开“Windows 日志”并单击“系统”。检查系统事件日志以获取有关服务停止原因的详细信息。

通过检查 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ExtensibilityLog 下的 rlauncher.log，我能够收集到最多的故障排除信息。向下滚动到底部并查找错误。在我的情况下，我可以看到 rterm.exe 无法启动，并且我得到了可怕的“会话启动失败，错误代码：F”，无法与 SQL Server 中的 'R' 脚本的运行时通信和SQL Server 2016 R Services (In-Database) 无法正常工作中发布了解决方法。

在我编辑了位于 C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn 目录中的 rlauncher.config 文件中的工作目录后，我重新启动了 SQL Server 和 Launchpad 并成功运行了外部 R 脚本。我仍在努力原谅自己忘记了 R 需要旧的 8.3 格式的文件名。

安装 Python 的运行时和包

下载适用于 Windows 的 Python 3.10并使用以下设置进行安装

• 您可能需要以管理员身份运行 Python 安装程序才能为所有用户安装
• 选中“为所有用户安装启动程序（推荐）”。
• 从 Python 安装程序中，选择“自定义安装”。
• 选择您要安装的可选功能，然后单击“下一步”。
• 在“高级选项”页面上，启用“为所有用户安装”，保留其他默认设置，然后单击“安装”按钮。

以管理员身份打开命令提示符。下载并安装最新版本的 revoscalepy 包及其依赖项。

cd "C:\Program Files\Python310\"
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" dill numpy==1.22.0 pandas patsy python-dateutil
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl

使用以下 icacls 命令授予 SQL Server Launchpad 服务和 SID S-1-15-2-1（All_Application_Packages）读取和执行访问权限。

icacls "C:\Program Files\Python310\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T
icacls "C:\Program Files\Python310\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T

使用 SQL Server 配置 Python 运行时

使用 RegisterRext.exe 命令行实用程序在 SQL Server 2022 中配置已安装的 Python 运行时。如有必要，请更改实例名称。

cd "C:\Program Files\Python310\Lib\site-packages\revoscalepy\rxLibs"
.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python310" /instance:"MSSQLSERVER"

返回 SSMS 并连接到您刚刚安装了 SQL Server 机器学习服务的 SQL Server 实例。如果您尚未启用外部脚本，请单击“新建查询”并运行以下命令

EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE

要进行验证，请确认以下查询是否为 config_value 和 run_value 返回 1

EXEC sp_configure  'external scripts enabled';

重新启动 SQL Server 服务。这也会重新启动 SQL Server Launchpad 服务。您可以通过在 SSMS 对象资源管理器中右键单击 SQL Server 实例并选择“重新启动”，或使用 SQL Server 配置管理器来执行此操作。

使用请求 Python 版本的命令验证 SQL Server 是否已针对 Python 外部脚本正确配置

EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python'
GO

这应该返回以下内容

STDOUT message(s) from external script: 
3.10.0 (tags/v3.10.0:b494f59, Oct  4 2021, 19:00:18) [MSC v.1929 64 bit (AMD64)]


Completion time: 2024-01-26T14:01:18.4833562-08:00

SQL Server 中的 Python 操作

让我们浏览一下您可以使用 Python 和 SQL Server 执行的一些操作。您可以在快速入门：运行 Python 脚本 - SQL 机器学习中找到更多简单的示例。

您可以使用 SSMS **导入其他 Python 库**。在下面，我们导入 Numpy 并将 2 和 5 的三次方。

EXEC sp_execute_external_script
@language =N'Python',
@script = N'
import numpy as np
 
cube2 = np.power(2,3)
cube5 = np.power(5,3)
 
print(cube2)
print(cube5)
		
'
GO

输出将是 2 的立方和 5 的立方。

Python 在 SQL Server 中最常见的用途之一是进行**基本算术运算**。以下示例显示了加法、减法、乘法和模数。

EXEC sp_execute_external_script
@language =N'Python',
@script = N'
number1 = 50
number2 = 10
result = number1 + number2
print(result)
result = number1 - number2
print(result)
result = number1 / number2
print(result)
result = number1 * number2
print(result)
result = number1 % number2
print(result)
'
		
GO

输出

STDOUT message(s) from external script: 
60
40
5.0
500
0


Completion time: 2024-01-30T09:22:28.1117766-08:00

**赋值运算**将常量或变量的值赋给另一个变量。SQL Server 支持所有 Python 赋值运算符。

EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE
 
EXEC sp_execute_external_script
@language =N'Python',
@script = N'
 
number1 = 50
number2 = 10
number1 += number2
print(number1)
 
number1 = 50
number2 = 10
number1 -= number2
print(number1)
 
number1 = 50
number2 = 10
number1 *= number2
print(number1)
 
 
number1 = 50
number2 = 10
number1 /= number2
print(number1)
 
number1 = 50
number2 = 10
number1 %= number2
print(number1)
 
'
		
GO

输出

STDOUT message(s) from external script: 
60
40
500
5.0
0


Completion time: 2024-01-30T09:28:26.1454821-08:00

您可以从 SQL Server 调用**内置和自定义 Python 函数**。这将返回 5 的立方和 2 的立方。

EXEC sp_execute_external_script
@language =N'Python',
@script = N'
 
def printCube (num):
	print(num ** 3)
 
printCube(5)
printCube(2)
		
'
GO

SQL Server 支持 Python 中的**比较运算**。

EXEC sp_execute_external_script
@language =N'Python',
@script = N'
 
number1 = 50
number2 = 10
 
print(number1 == number2)
print(number1 != number2)
print(number1 > number2)
print(number1 < number2)
print(number1 >= number2)
print(number1 <= number2)
 
'
		
GO

Output:

STDOUT message(s) from external script: 
False
True
True
False
True
False


Completion time: 2024-01-30T09:38:10.6732599-08:00

SQL Server 支持逻辑 Python 运算符 AND、OR、NOT。

EXEC sp_execute_external_script
@language =N'Python',
@script = N'
 
print(True and True)
print(True or False)
print(not(True))
 
'		
GO

Output:

STDOUT message(s) from external script: 
True
True
False


Completion time: 2024-01-30T09:40:18.7572690-08:00

有关其他示例，请参阅SQL Server 中的 Python：基础知识。

Python SQL 机器学习服务教程

在 Python 外部脚本成功运行后，让我们逐步了解在 SQL Server 中准备数据，然后使用 Python 运行线性回归模型的过程。本教程基于Python 教程：使用 SQL 机器学习预测滑雪租赁的线性回归。

我们将

• 下载并还原示例数据库
• 将数据从数据库加载到 Python 数据框中
• 在 Python 中准备数据
• 在 Python 中训练线性回归模型
• 创建一个生成 ML 模型的存储过程
• 将模型存储在数据库表中
• 创建一个使用模型进行预测的存储过程
• 对新数据执行模型

如果您已遵循本博文的第一部分，那么您已安装了大部分先决条件。

您可以在博客资产 - SQL ML 代码中找到本教程的代码。

入门

如果尚未安装，请安装这些其他 Python 包

• Pandas
• Pyodbc

• Scikit-learn

下载并还原 TutorialDB.bak。如果您需要说明，请参阅 备份和还原数据库 - Azure Data Studio | Microsoft Learn。

验证已还原的数据库是否存在

USE TutorialDB;
SELECT * FROM [dbo].[rental_data];

探索和准备数据

为了在 Python 中使用数据，您必须首先将其从数据库加载到 pandas 数据框中。然后，您可以准备数据以进行分析。

在您选择的编辑器中创建一个新的 Python 笔记本。我正在使用 VS Code。将下面的脚本复制并粘贴到您的笔记本中。在运行它之前，请使用您的服务器、用户名和密码更新连接字符串。要使用 Windows 身份验证，请指定 Trusted_Connection=Yes；而不是连接字符串中的 UID 和 PWD 参数。

import pyodbc
import pandas
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error

# Connection string to your SQL Server instance
conn_str = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=<server>; DATABASE=TutorialDB;UID=<username>;PWD=<password>')

query_str = 'SELECT Year, Month, Day, Rentalcount, Weekday, Holiday, Snow FROM dbo.rental_data'

df = pandas.read_sql(sql=query_str, con=conn_str)
print("Data frame:", df)

结果将类似于以下内容。

Data frame:      Year  Month  Day  Rentalcount  WeekDay  Holiday  Snow
0    2014      1   20          445        2        1     0
1    2014      2   13           40        5        0     0
2    2013      3   10          456        1        0     0
3    2014      3   31           38        2        0     0
4    2014      4   24           23        5        0     0
..    ...    ...  ...          ...      ...      ...   ...
448  2013      2   19           57        3        0     1
449  2015      3   18           26        4        0     0
450  2015      3   24           29        3        0     1
451  2014      3   26           50        4        0     1
452  2015     12    6          377        1        0     1

[453 rows x 7 columns]

像所有 优秀的 数据工程师一样，您将删除您不打算分析的列。您必须删除 RentalCount，因为它将用作预测目标。

columns = df.columns.tolist()
columns = [c for c in columns if c not in ["Year", "Rentalcount"]]

print("Training set:", df[columns])

精简后的数据集类似于

Training set:      Month  Day  Weekday  Holiday  Snow
0        1   20        2        1     0
1        2   13        5        0     0
2        3   10        1        0     0
3        3   31        2        0     0
4        4   24        5        0     0
..     ...  ...      ...      ...   ...
448      2   19        3        0     1
449      3   18        4        0     0
450      3   24        3        0     1
451      3   26        4        0     1
452     12    6        1        0     1

[453 rows x 5 columns]

训练线性回归模型

训练是查找最能描述数据集中变量之间依赖关系的模型的过程。您即将使用线性回归算法训练模型 lin_model。

# Store the variable we'll be predicting on.
target = "Rentalcount"

# Generate the training set.  Set random_state to be able to replicate results.
train = df.sample(frac=0.8, random_state=1)

# Select anything not in the training set and put it in the testing set.
test = df.loc[~df.index.isin(train.index)]

# Print the shapes of both sets.
print("Training set shape:", train.shape)
print("Testing set shape:", test.shape)

# Initialize the model class.
lin_model = LinearRegression()

# Fit the model to the training data.
lin_model.fit(train[columns], train[target])

运行上述代码后，您将看到类似以下的结果

Training set shape: (362, 7)
Testing set shape: (91, 7)

预测

使用预测函数使用 lin_model 预测租金数量。

# Generate our predictions for the test set.
lin_predictions = lin_model.predict(test[columns])
print("Predictions:", lin_predictions)

# Compute error between our test predictions and the actual values.
lin_mse = mean_squared_error(lin_predictions, test[target])
print("Computed error:", lin_mse)

结果

Predictions: [124.41293228 123.8095075  117.67253182 209.39332151 135.46159387
 199.50603805 472.14918499  90.15781602 216.61319499 120.30710327
  89.47591091 127.71290441 207.44065517 125.68466139 201.38119194
 204.29377218 127.4494643  113.42721447 127.37388762  94.66754136
  90.21979191 173.86647615 130.34747586 111.81550069 118.88131715
 124.74028405 211.95038051 202.06309706 123.53053083 167.06313191
 206.24643852 122.64812937 179.98791527 125.1558454  168.00847713
 120.2305587  196.60802649 117.00616326 173.20010759  89.9563518
  92.11048236 120.91052805 175.47818992 129.65196995 120.97443971
 175.95863082 127.24800008 135.05866542 206.49627783  91.63004147
 115.78280925 208.92841718 213.5137192  212.83278197  96.74415948
  95.1324457  199.9089665  206.10791806 126.16510228 120.0281266
 209.08150631 132.88996619 178.84110582 128.85971386 124.67637239
 115.58134503  96.82167192 514.61789505 125.48319717 207.50359894
 121.64080826 201.9381774  113.22575025 202.46505762  90.7002328
  92.31194658 201.25627228 516.97252195  91.36660136 599.27093251
 199.6445585  123.66905128 117.4710676  173.12259514 129.60359486
 209.59478573 206.29481361 210.69322009 205.50255751 210.88011563
 207.65572019]
Computed error: 35003.54030828391

使用 SQL 机器学习部署线性回归模型

这是行动发生的地方——您将使用机器学习服务将您刚刚在 Python 中创建的模型部署到 SQL Server 数据库中。您将创建一个存储过程来训练和生成 ML 模型，然后将该模型存储在表中。然后，您将创建一个存储过程来应用该模型进行预测并执行它。

使用 Python 脚本创建一个存储过程 generate_rental_py_model，该过程使用 scikit-learn LinearRegression 训练并生成模型。

注意！SQL Server 对 Python 代码中的格式非常敏感。

复制粘贴我的代码并运行它给了我外部脚本错误、SqlSatelliteCall 错误——这真是浪费时间。避免这种痛苦，将下面的代码复制并粘贴到像 VS Code 这样的编辑器中，它将为您格式化代码，然后将其另存为 SQL 查询并提交。此外，本教程中的所有代码都可以在 此处找到。不要直接复制粘贴到 SSMS 或 ADS 中。

-- Stored procedure that trains and generates a Python model using the rental_data and a linear regression algorithm
DROP PROCEDURE IF EXISTS generate_rental_py_model;
go
CREATE PROCEDURE generate_rental_py_model (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script
      @language = N'Python'
    , @script = N'
from sklearn.linear_model import LinearRegression
import pickle

df = rental_train_data

# Get all the columns from the dataframe.
columns = df.columns.tolist()

# Store the variable well be predicting on.
target = "RentalCount"

# Initialize the model class.
lin_model = LinearRegression()

# Fit the model to the training data.
lin_model.fit(df[columns], df[target])

# Before saving the model to the DB table, convert it to a binary object
trained_model = pickle.dumps(lin_model)'

, @input_data_1 = N'select "RentalCount", "Year", "Month", "Day", "WeekDay", "Snow", "Holiday" from dbo.rental_data where Year < 2015'
, @input_data_1_name = N'rental_train_data'
, @params = N'@trained_model varbinary(max) OUTPUT'
, @trained_model = @trained_model OUTPUT;
END;
GO

创建一个表以保存模型。

USE TutorialDB;
DROP TABLE IF EXISTS dbo.rental_py_models;
GO
CREATE TABLE dbo.rental_py_models (
    model_name VARCHAR(30) NOT NULL DEFAULT('default model') PRIMARY KEY,
    model VARBINARY(MAX) NOT NULL
);
GO

Save the model, linear_model as a binary object in the table.

DECLARE @model VARBINARY(MAX);
EXECUTE generate_rental_py_model @model OUTPUT;

INSERT INTO rental_py_models (model_name, model) VALUES('linear_model', @model);

创建预测存储过程

存储过程 py_predict_rentalcount 使用模型来预测滑雪租赁数量。同样，在格式化包含 Python 的 SQL 时要非常小心。

DROP PROCEDURE IF EXISTS py_predict_rentalcount;
GO
CREATE PROCEDURE py_predict_rentalcount (@model varchar(100))
AS
BEGIN
    DECLARE @py_model varbinary(max) = (select model from rental_py_models where model_name = @model);

    EXECUTE sp_execute_external_script
                @language = N'Python',
                @script = N'

# Import the scikit-learn function to compute error.
from sklearn.metrics import mean_squared_error
import pickle
import pandas

rental_model = pickle.loads(py_model)

df = rental_score_data

# Get all the columns from the dataframe.
columns = df.columns.tolist()

# Variable you will be predicting on.
target = "RentalCount"

# Generate the predictions for the test set.
lin_predictions = rental_model.predict(df[columns])
print(lin_predictions)

# Compute error between the test predictions and the actual values.
lin_mse = mean_squared_error(lin_predictions, df[target])
#print(lin_mse)

predictions_df = pandas.DataFrame(lin_predictions)

OutputDataSet = pandas.concat([predictions_df, df["RentalCount"], df["Month"], df["Day"], df["WeekDay"], df["Snow"], df["Holiday"], df["Year"]], axis=1)
'
, @input_data_1 = N'Select "RentalCount", "Year" ,"Month", "Day", "WeekDay", "Snow", "Holiday"  from rental_data where Year = 2015'
, @input_data_1_name = N'rental_score_data'
, @params = N'@py_model varbinary(max)'
, @py_model = @py_model
with result sets (("RentalCount_Predicted" float, "RentalCount" float, "Month" float,"Day" float,"WeekDay" float,"Snow" float,"Holiday" float, "Year" float));

END;
GO

创建一个表来存储存储过程生成的预测。

DROP TABLE IF EXISTS [dbo].[py_rental_predictions];
GO

CREATE TABLE [dbo].[py_rental_predictions](
 [RentalCount_Predicted] [int] NULL,
 [RentalCount_Actual] [int] NULL,
 [Month] [int] NULL,
 [Day] [int] NULL,
 [WeekDay] [int] NULL,
 [Snow] [int] NULL,
 [Holiday] [int] NULL,
 [Year] [int] NULL
) ON [PRIMARY]
GO

最后，运行存储过程以预测租赁数量。

--Insert the results of the predictions for test set into a table
INSERT INTO py_rental_predictions
EXEC py_predict_rentalcount 'linear_model';

-- Select contents of the table
SELECT * FROM py_rental_predictions;

瞧！结果将类似于下面所示。

以上教程向您展示了一个工作流程，用于在 SQL Server 2022 上创建、训练、部署和运行 ML 模型作为存储过程。

想了解更多？请参阅 Python 教程 - SQL 机器学习，以及另一个很棒的教程，可以在 使用 SQL Server 2017 中的 Python 进行数据插值和转换找到。

Python、R 和 SQL Server 用于无限分析和 AI

SQL Server 这个名称可能会让人联想到一个古板的 RDBMS 数据孤岛，但在 SQL Server 2022 中，事实并非如此。能够将 C、Java、Python 和 R 代码作为存储过程运行，将 SQL Server 的功能扩展到其内置功能之外。能够 查询外部表 并 备份到与 S3-API 兼容的对象存储。

MinIO 是与 SQL Server 2022 配对的最佳对象存储选择。这在本地尤其如此，在本地，MinIO 是您唯一 高性能、可扩展、完全兼容 S3 API 且 企业级对象存储 解决方案。数据可以位于 MinIO 运行的任何位置，并可用于 AI——公有云、私有云、本地、边缘。

下载 MinIO 并立即开始构建机器学习应用程序。有任何问题或意见？请通过我们的 社区 Slack 频道 与我们联系。