PyCaret SQL 中的机器学习第一部分 | 文档

使用 PyCaret 在 SQL 中进行机器学习

通过在 SQL Server 中集成 PyCaret，将你的 ML 代码部署到数据

作者：Umar Farooque

本文将涵盖以下内容

如何免费下载和安装 SQL Server
如何创建新数据库并将数据导入数据库
如何在数据库中启用和使用 Python 脚本
如何训练聚类算法，以便为数据集中的每个观测值分配聚类标签

一、将代码带到数据 — 使用数据库进行机器学习的理由

进行 ML 实验的首选工具/环境是命令行、IDE 或 Notebook。然而，当数据量变得非常大，或者需要将 ML 模型投入生产时，这些工具/环境可能会带来限制。迫切需要具备在数据所在地编程和训练模型的能力。微软 SQL Server 在其 2019 版本中引入了这一能力。使用 SQL Server 进行机器学习的明显优势在于：

i. 从系统中提取大量数据既繁琐又耗时。在服务器上进行 ML 实验是将代码带到数据，而不是将数据带到代码

ii. ML 实验主要在计算机/CPU 内存中执行。大多数机器在大型数据集上训练 ML 算法时会达到性能上限。在 SQL Server 数据库中进行 ML 可以避免这种情况

iii. 将 ML Pipelines 与其他 ETL 流程集成和部署起来很容易

二、SQL Server

三、下载软件

四、设置环境

在将 PyCaret 功能用于 SQL Server 之前，你需要安装 SQL Server 和 PyCaret。这是一个多步骤过程

步骤 1 — 安装 SQL Server

下载 SQL Server 2019 Developer Edition 文件 “SQL2019-SSEI-Dev.exe”

打开文件并按照说明进行安装（建议使用自定义安装选项）

选择新的 SQL Server 独立安装

在“实例功能”选项中，选择功能，包括“Python”（位于机器学习服务和语言扩展下）以及机器学习服务器（独立）

点击“接受”以同意安装 Python

安装可能需要 15-20 分钟

步骤 2 — 安装 Microsoft SQL Server Management Studio (SSMS)

打开文件 “SSMS-Setup-ENU.exe” 以开始安装

安装可能需要 5-10 分钟

步骤 3 — 为机器学习创建数据库

安装完所有内容后，你需要启动一个服务器实例。为此，启动 SSMS。在登录阶段，系统会要求你输入 SQL Server 的名称，你可以从下拉菜单中选择。连接建立后，你可以看到服务器上的所有对象。如果你是第一次下载 SQL Server 且没有可用的数据库，你需要先创建一个新数据库。

在对象资源管理器面板中，右键单击“数据库”，然后选择“新建数据库”

输入数据库名称及其他信息

设置可能需要 2-3 分钟，包括创建数据库、用户和设置所有权

步骤 4 — 导入 CSV 文件

现在你需要使用 SQL Server Management Studio 将 CSV 文件导入数据库。

在数据库中创建一个表 “jewellery”

右键单击数据库，选择任务->导入数据

对于数据源，选择平面文件源。然后使用浏览按钮选择 CSV 文件。在点击“下一步”按钮之前，花一些时间配置数据导入。

对于目标，选择正确的数据库提供程序（例如 SQL Server Native Client 11.0）。输入服务器名称；勾选使用 SQL Server 身份验证，输入用户名、密码和数据库，然后点击“下一步”按钮。

在“选择源表和视图”窗口中，你可以在点击“下一步”按钮之前编辑映射。

勾选“立即运行”，然后点击“下一步”按钮

点击“完成”按钮运行包

步骤 5 — 为 SQL Server 启用 Python 脚本

我们将使用系统存储过程 **sp_execute_external_script** 在 SQL Server “内部”运行 Python。首先，你需要打开一个“新建查询”。在你的实例中执行以下查询，以启用该过程进行远程脚本执行

EXEC sp_configure ‘external scripts enabled’, 1

RECONFIGURE WITH OVERRIDE

注意：在继续后续步骤之前，请重启实例。

可以执行以下 SQL 语句来检查 Python 路径并列出已安装的包。

检查 Python 路径

EXECUTE sp_execute_external_script

@language =N’Python’,

@script=N’import sys; print(“\n”.join(sys.path))’

列出已安装的包

EXECUTE sp_execute_external_script

@language = N’Python’,

@script = N’

import pkg_resources

import pandas as pd

installed_packages = pkg_resources.working_set

installed_packages_list = sorted([“%s==%s” % (i.key, i.version) for i in installed_packages])

df = pd.DataFrame(installed_packages_list)

OutputDataSet = df’

WITH RESULT SETS (( PackageVersion nvarchar (150) ))

步骤 6 — 将 PyCaret Python 包添加到 SQL Server

要安装 PyCaret 包，请打开命令提示符，并导航到安装了 SQL Server 的 Python 包位置。默认位置是

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES

导航到“Scripts” 目录，并使用 pip 命令安装 PyCaret 包

pip.exe install pycaret

注意：请确保你有权访问 SQL Server 目录以安装包和/或更改配置。否则，包安装将失败。

安装可能需要 5-10 分钟

注意：如果在运行 SQL 脚本时遇到关于缺少 “lightgbm” 模块的问题。请遵循以下说明

i. 卸载 “lightgbm”

pip.exe uninstall lightgbm

ii. 重新安装 “lightgbm”

pip.exe install lightgbm

执行以下 SQL 语句，从 SQL Server 验证 PyCaret 安装

EXECUTE sp_execute_external_script

@language = N’Python’,

@script = N’

import pkg_resources

pckg_name = “pycaret”

pckgs = pandas.DataFrame([(i.key) for i in pkg_resources.working_set], columns = [“key”])

installed_pckg = pckgs.query(‘’key == @pckg_name’’)

print(“Package”, pckg_name, “is”, “not” if installed_pckg.empty else “”, “installed”) ’

五、ML 实验示例 — SQL Server 中的聚类

聚类是一种机器学习技术，它将具有相似特征的数据点进行分组。这些分组对于数据探索、模式识别和分析数据子集非常有用。聚类的一些常见业务用例包括：

✔ 用于营销目的的客户细分。

✔ 用于促销和折扣的客户购买行为分析。

✔ 在流行病（如 COVID-19）爆发中识别地理聚类。

1. K均值聚类

在 SQL Server 中运行以下 SQL 代码

EXECUTE sp_execute_external_script

@language = N’Python’,

@script = N’dataset = InputDataSet

import pycaret.clustering as pc

dataset = pc.get_clusters(data = dataset)

OutputDataSet = dataset’,

@input_data_1 = N’SELECT [Age], [Income], [SpendingScore], [Savings] FROM [jewellery]’

WITH RESULT SETS(([Age] INT, [Income] INT, [SpendingScore] FLOAT, [Savings] FLOAT, [Cluster] varchar(15)));

2. 输出

原始表会附带一个包含标签的新列 ‘Cluster’。

默认情况下，PyCaret 训练一个包含 4 个聚类的 K均值 聚类模型（即表中的所有数据点被分为 4 组）。默认值可以轻松更改

要更改聚类数量，可以在 get_clusters() 函数中使用 num_clusters 参数。

要更改模型类型，可以在 get_clusters() 函数中使用 model 参数。

3. K众数

请参阅以下代码以训练包含6 个聚类的 K众数模型

EXECUTE sp_execute_external_script

@language = N’Python’,

@script = N’dataset = InputDataSet

import pycaret.clustering as pc

dataset = pc.get_clusters(data = dataset, model=”kmodes”, num_clusters = 6)

OutputDataSet = dataset’,

@input_data_1 = N’SELECT [Age], [Income], [SpendingScore], [Savings] FROM [jewellery]’

WITH RESULT SETS(([Age] INT, [Income] INT, [SpendingScore] FLOAT, [Savings] FLOAT, [Cluster] varchar(15)));