#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩

精选原创

wangboyang 2021-12-16 21:03:59 博主文章分类：SqlServer系列 ©著作权

文章标签 sql python 存储过程机器学习 SqlServer 文章分类 SQL Server 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者wangboyang的原创作品，请联系作者获取转载授权，否则将追究法律责任

随着技术的发展，微软也在逐步的更新自己的产品线，通过开放、包容的态度在逐步提升自己产品能力的同时，也通过开放的原则逐步拥抱一些主流的语言与技术，在SqlServer中，典型的就是SQL Server 2017 (14.x)及更高版本中引入了使用关系数据运行 Python 和 R 脚本的功能，叫做SqlServer机器学习服务,它默认支持Python以及R语言，同时支持Microsoft Python 包和 R 包进行预测分析和机器学习，通过SqlServer扩展性框架,在 SQLServer 管理的安全框架内执行受信任的脚本语言，这样对于我们熟悉SqlServer的开发人员来说，使用熟练的工具结合外部语言脚本可以有效快速的通过SqlServer实现机器学习场景。

关于SqlServer扩展性框架相关内容，我会在后续文章中进行展示，现在我要补充一下，SqlServer机器学习服务不光可以扩展Python 和 R 脚本的功能，同时也执行Java与C#感兴趣的朋友能否在评论区给我展示一下你们的应用成果。

在数据库中如何执行Python？

目前Python作为很多领域主流的语言，包括：网络爬虫、web、大数据、机器学习等都拥有着很大的应用群体，那么我们就看下如何利用SqlServer机器学习服务在数据库中使用Python，对于Python语言使用开发包，SqlServer不仅支持了Microsoft 包之外，还可以安装和使用开源包和框架，例如 PyTorch、TensorFlow 和 scikit-learn。微软提供的Python开发包如下，有兴趣的Python开发者可以体验一下，内容如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_SqlServer_02

1、开启机器学习服务中外部脚本执行功能

在确保SqlServer为2017版本以上的同时，最好是2019，通过SMSS（SQL Server Management Studio）联接到数据库新建查询执行sp_configure存储过程我们会发现返回的结果中external scriptsenabled 的值目前应为 0。我们继续通过该存储过程将外部脚本执行设置为1[启用]，代码如下：

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

2、利用Python输出Hello World

在SqlServer机器学习服务中使用存储过程 sp_execute_external_script 在 SQL Server 实例上执行 Python脚本，后面会对该存储过程进行详细展示，目前我们了解该存储过程用于执行Python脚本，并且按国际惯例，利用Python进行HelloWorld输出，代码如下：

EXECUTE sp_execute_external_script 
@language = N'Python',
@script = N'print("Hello World!!!")'

我们可以得到如下结果：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_机器学习_03

可见，我们通过存储过程 sp_execute_external_script 在 SQL Server 实例上执行了内容为print("Hello World!!!")的Python脚本，并且可以得到一个来自外部脚本的结果消息。当然，存储过程sp_execute_external_script可不只是这一点点能耐，下面我们来看看存储过程 sp_execute_external_script的具体用法。

3、sp_execute_external_script说明

SqlServer为我们预制好了sp_execute_external_script存储过程，我们可以通过调用存储过程，并对其传递响应的参数来实现我们对Python的调用，下表是官方给出的该存储过程参数的作用与说明，如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_存储过程_04

了解完参数之后我们就可以理解sp_execute_external_script存储过程的具体用法，但我们发现我们可以通过参数对外部脚本进行数据传递，供外部脚本在执行期间使用，也可以接受外部脚本的返回的结果集供TSQL使用，那么在我们传递参数过程中，鉴于TSQL与外部脚本的独立性，有些参数类型并不适用，一般情况下，无法映射到数据类型的任何结果集都输出为 Transact-SQL NULL。不支持的类型如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_存储过程_05

4、TSQL与Python的交互

那我们上一篇《SqlServer-数据旋转-知其然也指其所以然》中数据旋转示例中的表结构为例，为了减少翻阅文章所带来的麻烦，我在本章节继续列出创建该表的代码如下：

CREATE TABLE skills
(
    name NVARCHAR(6),
    skills NVARCHAR(20),
    score int
)
 
INSERT INTO skills VALUES('张三','Java',91);
INSERT INTO skills VALUES('张三','HDFS',75);
INSERT INTO skills VALUES('张三','HIVE',85);
INSERT INTO skills VALUES('张三','Spark',85);
INSERT INTO skills VALUES('李四','Java',98);
INSERT INTO skills VALUES('王五','.Net',99);
INSERT INTO skills VALUES('王五','Java',75);
INSERT INTO skills VALUES('王五','HDFS',85);
INSERT INTO skills VALUES('王五','HIVE',85);
INSERT INTO skills VALUES('王五','Spark',85);

我们通过将该表的数据传递与外部脚本Python中，并且原封不动返回该表数据，用于展示sp_execute_external_script存储过程的参数传入与数据返回，同时我们使用WITH RESULT SETS 子句为 SQL 定义返回的数据表的架构，将返回的数据列名以中文的形式返回输出，代码如下：

EXECUTE sp_execute_external_script @language = N'Python'
    , @script = N'SQL_out = SQL_in;'
    , @input_data_1 = N'SELECT * FROM skills;'
    , @input_data_1_name  = N'SQL_in'
    , @output_data_1_name = N'SQL_out'
WITH RESULT SETS(([姓名] NVARCHAR(6) NOT NULL,[技能] NVARCHAR(20) NOT NULL,[成绩] INT NOT NULL));

我们可以看到我们通过@language参数制定了外部脚本为Python，并且制定了输出和输出参数，在Python中将结果原本返回给TSQL，同时通过WITH RESULT SETS将结果集进行了重命名，参数解读如下：

@language参数制定了外部脚本为Python

@ script为Python的具体执行内容

@ input_data_1传入给Python代码块的数据，这里为查询skills表的所有内容

@ input_data_1_name指定了在Python中使用传入数据的变量名

@output_data_1_name制定了在Python中存储返回数据的变量名

执行结果如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_sql_06

5、利用SqlServer机器学习服务通过Python实现数据旋转

上一篇文章中我们分析了数据旋转的执行原理与TSQL特有的数据旋转方式，结合上面的例子，我们会发现Python中仅仅用了两个变量的赋值操作，过于简单，若执行复杂操作并依赖于Python的开发包情况下，我们如何写？其实理解了sp_execute_external_script存储过程的用法后，我们可以有效的结合Python实现我们机器学习以及SQL查询运算的需求，接下来我将使用sp_execute_external_script对数据进行旋转来达到展示如下格式输出的应用，预期结果如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_python_07

代码如下：

CREATE TABLE skillsResults
(
     [姓名] NVARCHAR(6),
     [.Net] INT,
     Java INT,
     HDFS INT,
     HIVE INT,
     Spark INT
);
EXECUTE sp_execute_external_script @language = N'Python'
    , @script = N'
import pandas as pd
import numpy as np
df = pd.DataFrame(SQL_in)
SQL_out = pd.pivot_table(df,index="name",columns="skills",values="score")
    '
    , @input_data_1 = N'SELECT name,skills,score FROM skills;'
    , @input_data_1_name  = N'SQL_in'
    , @output_data_1_name = N'SQL_out'
WITH RESULT SETS(AS OBJECT [skillsResults]);

因为WITHRESULT SETS用于指定输出的数据表的列明，因为我们数据旋转的列明是不明确的，我在上方创建了一张表定义了返回类型的字段，通过AS OBJECT指定为skillsResults，表示使用该表的列作为输出列。这样就实现了我们的数据旋转，结果如下：

#yyds干货盘点#SqlServer-机器学习服务-Python有你更精彩_sql_08

最后我们通过官方经典案例，通过 SQL 机器学习服务利用 Python创建预测模型并对其进行评分，我们通过使用 Python 创建并训练预测模型。将此模型保存到 SQL Server 实例中的表，然后在 SQL Server 机器学习服务上使用此模型基于新数据来预测值。接下来，我们创建两个存储过程，第一个存储过程使用经典 Iris 花卉数据集，并生成 Naïve Bayes 模型，用于根据花卉特征预测 Iris 种类。第二个存储过程用于评分，它调用第一个过程中生成的模型，从而根据新数据输出一组预测。通过将 Python 代码用于 SQL 存储过程，操作会包含在 SQL 中，可重复使用，并且可以由其他存储过程和客户端应用程序进行调用。

1、创建数据库与表

我们通过SQLServer Management Studio，创建名称为irissql的数据库，并使用它，在该库中创建iris_data与iris_models表，分别用于存放花卉信息数据与训练模型的存储表，代码如下：

CREATE DATABASE irissql
GO
USE irissql
GO
CREATE TABLE iris_data (
  id INT NOT NULL IDENTITY PRIMARY KEY
  , "Sepal.Length" FLOAT NOT NULL, "Sepal.Width" FLOAT NOT NULL
  , "Petal.Length" FLOAT NOT NULL, "Petal.Width" FLOAT NOT NULL
  , "Species" VARCHAR(100) NOT NULL, "SpeciesId" INT NOT NULL
);
CREATE TABLE iris_models (
  model_name VARCHAR(50) NOT NULL DEFAULT('default model') PRIMARY KEY,
  model VARBINARY(MAX) NOT NULL
);

2、通过Python填充数据

定义一个存储过程，通过SqlServer机器学习服务执行Python脚本，获取内置的 Iris 数据,将数据返回给SqlServer，代码如下：

CREATE PROCEDURE get_iris_dataset
AS
BEGIN
EXEC sp_execute_external_script @language = N'Python', 
@script = N'
from sklearn import datasets
iris = datasets.load_iris()
iris_data = pandas.DataFrame(iris.data)
iris_data["Species"] = pandas.Categorical.from_codes(iris.target, iris.target_names)
iris_data["SpeciesId"] = iris.target
', 
@input_data_1 = N'', 
@output_data_1_name = N'iris_data'
WITH RESULT SETS (("Sepal.Length" float not null, "Sepal.Width" float not null, "Petal.Length" float not null, "Petal.Width" float not null, "Species" varchar(100) not null, "SpeciesId" int not null));
END;

并通过调用存储过程，将数据插入表中，代码如下：

INSERT INTO iris_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", "SpeciesId")
EXEC dbo.get_iris_dataset;

3、通过存储过程创建生成模型

Python 代码所需的输入将作为输入参数在此存储过程中进行传递。输出将是基于适用于机器学习算法的 Python scikit-learn 库的定型模型。

CREATE PROCEDURE generate_iris_model (@trained_model VARBINARY(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
from sklearn.naive_bayes import GaussianNB
GNB = GaussianNB()
trained_model = pickle.dumps(GNB.fit(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]], iris_data[["SpeciesId"]].values.ravel()))
'
        , @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@trained_model varbinary(max) OUTPUT'
        , @trained_model = @trained_model OUTPUT;
END;

从上述代码我们可以看出在调用sp_execute_external_script存储过程时，通过input_data_1指明了传入的数据为iris_data表中的内容，并通过input_data_1_name告诉Python使用该参数的参数名，并制定了一个输出变量用于存放二进制序列化的模型，通过python的pickle包进行序列化，同时将数据填充模型。

4、将模型保存入模型表

通过调用上一步存储过程，获得到一个经过序列化的模型数据，将该序列化数据存入模型表中，用于后面可以随时读取模型，而不是每次都需单独创建模型，代码如下：

DECLARE @model varbinary(max);
DECLARE @new_model_name varchar(50)
SET @new_model_name = 'helloworld'
EXECUTE generate_iris_model @model OUTPUT;
DELETE iris_models WHERE model_name = @new_model_name;
INSERT INTO iris_models (model_name, model) values(@new_model_name, @model);

5、执行模型

现已创建、定型并保存了模型，接下来继续进行下一步：创建存储过程以执行预测并生成结果，将调用 sp_execute_external_script 来运行Python 脚本，该脚本加载序列化模型并提供要进行评分的新数据输入。

CREATE PROCEDURE predict_species (@model VARCHAR(100))
AS
BEGIN
    DECLARE @nb_model VARBINARY(max) = (
            SELECT model
            FROM iris_models
            WHERE model_name = @model
            );
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
irismodel = pickle.loads(nb_model)
species_pred = irismodel.predict(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]])
iris_data["PredictedSpecies"] = species_pred
OutputDataSet = iris_data[["id","SpeciesId","PredictedSpecies"]] 
print(OutputDataSet)
'
        , @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@nb_model varbinary(max)'
        , @nb_model = @nb_model
    WITH RESULT SETS((
                "id" INT
              , "SpeciesId" INT
              , "SpeciesId.Predicted" INT
                ));
END;