详解SQL中的机器学习

[[380168]]

这是SQL查询引擎试图解决的问题。它们使使用简单但功能强大的SQL命令来使用复杂的分布式系统世界成为可能。dask-sql是一个新的SQL查询引擎(免责声明：我是作者)，建立在仅python的Dask分布式库之上。这个新库可让您将Python与SQL和分布式可扩展计算结合起来!(在我的其他一篇文章中了解更多有关它的信息)。

dask-sql的最新版本0.3.0具有对机器学习的实验性支持，因此今天我们将了解如何使用它。当然，我们将为此使用著名的Iris数据集-数据科学的世界。即使这个特定的数据样本很小，只需将更多的计算节点添加到群集中，本文中显示的所有内容都可以轻松扩展到大量数据。

旁注：带有dask-sql的ML仍处于试验阶段:-)可以随意尝试，但请谨慎使用。

先决条件和准备

在本文中，我将假设您(或您的数据工程师)已经设置并运行了dask-sql。有关更多信息，请参考文档或此博客文章。我还将假设您通过其SQL Server接口连接dask-sql，例如通过诸如Apache Hue之类的BI工具，该工具甚至提供了最新版本对dask-sql的本机支持。

如果您正在使用dask-sql的Python接口，则仍然可以继续。只需使用以下单元格初始化Jupyter笔记本

from IPython.core.magic import register_line_cell_magic 
from dask_sql import Context 
# Create a context to store the tables and models 
c = Context() 
# Small helper function to make our life easier 
@register_line_cell_magic 
def sql(line, cell=None): 
    if cell is None: 
        cell = line 
        line = None 
     
    if not line: 
        line = {} 
    return c.sql(cell, return_futures=False, **line) 

在以下代码示例中，在每个SQL命令前加上%% sql，例如

%%sql 
SELECT 1 + 1 

加载和准备数据

在这些事情都解决了之后，让我们开始导入数据。由于dask-sql利用大型的Python和Dask生态系统，您可以从许多不同的位置读取许多不同格式的数据样本。在此示例中，我们将以相当标准的CSV格式从Internet提取数据：

CREATE OR REPLACE TABLE iris WITH ( 
    location = 'https://datahub.io/machine-learning/iris/r/iris.csv', 
    persist = True 
) 

数据集被加载并存储为名为“ iris”的表。persist = True使dask-sql将数据缓存在内存中。

现在，检查数据是否正确加载

DESCRIBE iris

如您所见，数据类型是自动分配的。我们可以使用以下标准SQL命令显示数据的前十行：

SELECT * FROM iris LIMIT 10 

鸢尾花数据集的简要介绍：如上面的输出所示，数据集描述了鸢尾花及其种类的测量结果。它是机器学习中的标准数据集之一，可以用作许多类型的ML方法的示例。在此示例中，我们将应用无监督的聚类算法。

在开始培训之前，让我们首先在每个ML管道中执行另一个重要步骤：特征提取。由于我不是生物学家，因此在此仅以一个非常简单且幼稚的新功能为例：将萼片长度乘以萼片宽度。还可以使用SQL语句和函数生成更复杂的功能。如果这还不够，dask-sql允许注册用Python编写的用户定义函数(UDF)。

SELECT  
    *,  
    sepallength * petallength AS new_feature  
FROM iris 
LIMIT 10 

为了让我们的生活更轻松，让我们为该增强型表引入一个别名

CREATE OR REPLACE TABLE transformed_data AS ( 
    SELECT  
        *,  
        sepallength * petallength AS new_feature 
    FROM iris 
) 

我们现在准备应用机器学习!

训练机器学习模型

机器学习模型的种类繁多，从简单的模型(如线性回归)到强大的Boosted Decision Trees，再到最先进的研究模型(如Transformers)。其中许多是在著名的scikit-learn Python软件包中实现的，因此(在许多其他库中)可在dask-sql中使用。

在此示例中，我们应用了k-Means聚类算法。简而言之，它将数据集分组为具有相似特征的行簇。如果一切顺利，我们希望它可以将相同物种的花朵聚在一起-而无需我们告诉算法。让我们看看该算法的性能如何(破坏者：不是很好)。如果您想了解有关与dask-sql兼容的模型及其设置的更多信息，建议您阅读一下文档。

因此，让我们将聚类算法应用于数据!

CREATE OR REPLACE MODEL clustering WITH ( 
    model_class = 'sklearn.cluster.KMeans', 
    wrap_predict = True, 
    n_clusters = 3 
) AS ( 
    SELECT sepallength, sepalwidth, petallength, petalwidth, new_feature 
    FROM transformed_data 
) 

如您所见，我们使用了一个新的SQL构造CREATE MODEL进行训练，它获得一些参数来指定要训练的模型。在我们的例子中，我们从scikit-learn中选择k-Means算法，并将我们期望的组或簇数设置为三个(因为我们有三个种类)。scikit-learn软件包中的算法在中等大小的数据上可以很好地工作，如果您需要超出此范围，请查看dask-ml。

培训应立即完成(因为总数据集很小)，因此我们可以继续检查预测。

检查性能

SELECT * FROM PREDICT ( 
    MODEL clustering, 
    SELECT * FROM transformed_data 
    LIMIT 10 
) 

该SQL语句将训练有素的模型应用于给定的数据，并向其中添加带有模型的预测目标的新列“目标”。从前十行来看，它看起来不错(所有“ setosa”都有相同的预测目标)。因此，我们再次引入别名以进行更多计算

CREATE OR REPLACE TABLE iris_results AS ( 
    SELECT class AS label, target AS predicted FROM PREDICT ( 
        MODEL clustering, 
        SELECT * FROM transformed_data 
    ) 
) 

为了简短起见，我们只快速浏览一下结果，并检查物种和预测簇的分布。

SELECT  
    label, predicted, COUNT(*) AS numbers 
FROM iris_results 
GROUP BY label, predicted 

一点都不完美，但是幸运的是，这不是关于ML的文章，所以我将跳过优化步骤:-)。您的BI工具可能能够自动绘制这些数字，并且作为python用户，您可以使用

df = c.sql(""" 
SELECT  
    label, predicted, COUNT(*) AS numbers 
FROM iris_results 
GROUP BY label, predicted 
""", return_futures=False) 
dfdf = df.set_index(["label", "predicted"]) 
df.numbers.unstack(0).plot.bar(ax=plt.gca()) 

概要

感谢您关注这篇文章直到最后!我们已经介绍了相当多的材料，所以这里有个简短的回顾：

• SQL查询引擎很酷，因为您可以使用它们使用具有简单SQL语法(以及很多魔术)的复杂分布式系统来查询复杂数据。
• dask-sql就是其中之一，它与Python生态系统的交互特别简单(因为它建立在Dask之上，Dask是可扩展Python应用程序的库)。
• 好处之一是可以轻松集成各种Python ML库，例如scikit-learn包中的k-Means算法，如本博文所示。另一个好处是，一切都可以通过使用SQL来完成!

如果您想了解更多信息，请转至文档，然后对数据进行SQL处理。

原文链接：https://towardsdatascience.com/machine-learning-in-sql-it-actually-works-56e8d91fc273