详解SQL中的机器学习

时间：2021-08-11 23:55:55

有时很难相信ML之前的世界已经存在。如此多的现代数据分析都建立在ML技术的基础之上，并且在可预见的将来将继续这样做。但是，并非所有人都能从这些巨大的进步中受益，因为使用ML技术主要涉及使用Python，开发代码和理解许多新技术。尤其是当大数据和分布式系统进入游戏时，事情变得一团糟。

详解SQL中的机器学习

这是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笔记本

  1. from IPython.core.magic import register_line_cell_magic 
  2. from dask_sql import Context 
  3. # Create a context to store the tables and models 
  4. c = Context() 
  5. # Small helper function to make our life easier 
  6. @register_line_cell_magic 
  7. def sql(line, cell=None): 
  8.     if cell is None: 
  9.         cell = line 
  10.         line = None 
  11.      
  12.     if not line: 
  13.         line = {} 
  14.     return c.sql(cell, return_futures=False, **line) 

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

  1. %%sql 
  2. SELECT 1 + 1 

详解SQL中的机器学习

加载和准备数据

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

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

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

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

DESCRIBE iris

详解SQL中的机器学习

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

  1. SELECT * FROM iris LIMIT 10 

详解SQL中的机器学习

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

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

  1. SELECT  
  2.     *,  
  3.     sepallength * petallength AS new_feature  
  4. FROM iris 
  5. LIMIT 10 

详解SQL中的机器学习

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

  1. CREATE OR REPLACE TABLE transformed_data AS ( 
  2.     SELECT  
  3.         *,  
  4.         sepallength * petallength AS new_feature 
  5.     FROM iris 
  6. ) 

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

训练机器学习模型

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

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

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

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

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

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

检查性能

  1. SELECT * FROM PREDICT ( 
  2.     MODEL clustering, 
  3.     SELECT * FROM transformed_data 
  4.     LIMIT 10 
  5. ) 

详解SQL中的机器学习

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

  1. CREATE OR REPLACE TABLE iris_results AS ( 
  2.     SELECT class AS label, target AS predicted FROM PREDICT ( 
  3.         MODEL clustering, 
  4.         SELECT * FROM transformed_data 
  5.     ) 
  6. ) 

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

  1. SELECT  
  2.     label, predicted, COUNT(*) AS numbers 
  3. FROM iris_results 
  4. GROUP BY label, predicted 

详解SQL中的机器学习

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

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

详解SQL中的机器学习

概要

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

  • 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

标签：

相关文章