你知道吗?SQL也能做机器学习!

你知道吗?SQL也能做机器学习!

是的,你没听错。我们可以用 SQL 来运行机器学习模型!当我听到这个消息时,我很好奇SQL 要如何在机器学习模型中使用,以及我们要如何使用 SQL 生成预测。

你可能会问,我们要用标准的 SQL 查询来做机器学习吗?没错,答案是YES!

多亏有Google 的BigQuery ML,只需要用标准 SQL 查询,我们就可以创建机器学习模型。

等等……那什么是BigQuery ML呢?

Google BigQuery是一个无服务、经济高效、且高度可扩展的数据仓库系统, BigQuery ML 让你只需用标准 SQL 查询,就可以在BigQuery中创建和执行机器学习模型。BigQuery ML 让数据科学家、机器学习工程师、和数据工程师可以直接用 SQL 快速构建和分析机器学习模型。如果你想了解更多数据分析相关内容,可以阅读以下这些文章:
手把手教你用Python创建SQL数据库!
六条鲜为人知的SQL技巧,帮你每月省下100小时!
数据分析新工具MindsDB–用SQL预测用户流失
DS数据科学家和DA数据分析师:要学习什么不同内容?

BigQuery ML 支持哪些类型的模型?

  • 回归模型

o 线性回归、二元逻辑回归、多类逻辑回归

  • 聚类

o K-means 聚类

  • 用于创建如推荐系统的矩阵分解
  • 用于预测的时间序列模型
  • Boosted Tree 模型 -> XGBoost (分类和回归)
  • 深度神经网络 (DNN) -> 分类和回归

更多可用模型参见官方文档:

https://cloud.google.com/bigquery-ml/docs/introduction

BigQuery在幕后进行什么工作?

  • 利用BigQuery 的处理能力构建模型
  • 自动调整学习率
  • 自动将数据拆分为训练集和测试集
  • L1/L2 正则化
  • 训练/测试拆分的 3 种策略:随机、顺序、自定义
  • 设置学习率

BigQuery ML创建模型的步骤

现在我们已经初步了解了BigQuery ML,是时候看看我们要如何加载和创建 ML ,来对数据集进行预测了。

接下来,我将向你展示我们如何使用著名的 Titanic 数据集来训练模型、模型评估、和测试模型。我将构建一个简单的基线模型来进行演示。

将数据加载到BigQuery 中:

登录到 GCP ,找到Big Query平台处。

Big Query平台

现在,创建一个新的数据库:

创建数据库

接下来,我们在数据集上创建一个表,选择上传并选择 test.csv 文件,设定文件格式为 CSV ,然后为你的表格命名。

创建表格

然后,单击 Create Table 按钮,你会看到你的表格布局,你还可以预览你的表格:

表格布局
表格预览

创建机器学习模型

现在可以编写一些 SQL 查询了,在编辑器中编写这个查询:

CREATE OR REPLACE MODEL
`qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`
OPTIONS
(
model_type='logistic_reg',
labels = ['Pclass']
)
AS
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data`
查询编辑器

点击 Run 按钮,并等待它训练和处理模型。处理完成后,你会在侧边栏中看到一个名为 model 的新文件夹,单击model文件夹,你可以看到有关模型的所有信息,包括损失(Loss)、持续时间(Duration)、F1 分数、ROC、AUC、学习率、混淆矩阵等等。

评估机器学习模型的 SQL 查询

我们将通过roc_auc分数比较我们的模型,单击查询编辑器并输入此查询:

SELECT
roc_auc,
CASE
WHEN roc_auc > .8 THEN 'good'
WHEN roc_auc > .7 THEN 'fair'
WHEN roc_auc > .6 THEN 'not great'
ELSE
'poor'
END
AS model_quality
FROM
ML.EVALUATE(MODEL `qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`,
(
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data` ) )

你可以看到,我们的roc_auc得分为 0.9,这非常好;但是,在这里,我使用的是一种非常简单的方法来训练模型,因此,我们的模型可能存在偏差和过度拟合。

模型预测

评估完模型后,我们可以进行预测了,输入这个查询到查询编辑器中。

SELECT
*
FROM
ml.PREDICT(MODEL `qwiklabs-gcp-04-905d44b8a3bb.titanic.credit_classification_model`,
(
SELECT
* EXCEPT(Survived)
FROM
`qwiklabs-gcp-04-905d44b8a3bb.titanic.data` ) );

我们可以通过 google sheet 和 Data Studio 看到我们的预测结果,让我们在 Sheets 上探索它。

BigQuery ML备忘单

下面,我列出了在BigQuery ML上常用的查询语句,你可以用它做查询参考。

回顾一下构建 ML 模型需要做的事情:

  • ETL 到BigQuery
  • 创建模型
  • 模型评估
  • 模型预测

BigQuery ML的优势

  • 无需使用 Python 编写代码/使用 Java 构建 ML 解决方案,可以直接用 SQL在BigQuery中训练和访问模型
  • BigQuery提高了速度和模型运行时间,因为我们不需要从任何其他源数据加载数据,这些数据已经存储在BigQuery表格中
  • 正如我们之前讨论过的, BigQuery ML 是无服务器的,因此,随着数据量的增加,我们无需担心需要去维护服务器,也无需在制作 ML 解决方案之前设置环境。

以上就是本文的全部内容,你可以下载Google的BigQuery ML,并创建自己的机器学习模型。感谢你的阅读!我们下一篇文章见。你还可以订阅我们的YouTube频道,观看大量数据科学相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/

原文作者:Himanshu Tripathi
翻译作者:Jiawei Tong
美工编辑:过儿
校对审稿:Jiawei Tong
原文链接:https://pub.towardsai.net/machine-learning-with-sql-30e942c75240