谷歌BigQuery ML正式上岗，只会用SQL也能玩转机器学习！

策划编辑 | Natalie

作者 | Umar Syed，Sergei Vassilvitskii

编译 | 无明

编辑 | Natalie

AI 前线导读： 前不久，我们在文章《SQL 足以解决你的问题，别动不动就机器学习》中跟大家探讨过 SQL 和机器学习的问题，想必很多人还有印象。ML/AI 当然有它们的用武之地，但在很多情况下，开发人员根本不需要机器学习，只用 SQL 就够了。文章作者希望更多开发者从实际角度出发，以解决问题为目标，减少对时髦技术的过度追求。现在，开发者可能又有了一条解决问题的新思路：谷歌今天正式发布 BigQuery ML，对 SQL 语言进行了扩展，数据科学家和分析师只用 SQL 语言就可以实现流行的机器学习功能并执行预测分析！

更多优质内容请关注微信公众号“AI 前线”（ID：ai-front）

谷歌 BigQuery 提供了基于大型数据集的交互式分析能力，让企业能够轻松分享有意义的见解并基于客户分析开发解决方案。很多使用 BigQuery 的企业都没有使用机器学习来更好地理解他们正在生成的数据，这是因为精通 SQL 的数据分析师可能不具备机器学习方面的数据科学背景。

谷歌近日正式发布 BigQuery ML（https://cloud.google.com/blog/big-data/2018/07/bridging-the-gap-between-data-and-insights ），作为 BigQuery 功能的一部分，可以让数据科学家和分析师在大型的结构化或半结构化的数据集上构建和部署机器学习模型。BigQuery ML 对 SQL 语言进行了扩展，让用户能够利用流行的 ML 功能和执行预测分析，如预测销售和在数据源头创建客户细分。 BigQuery ML 还会自动设置智能默认选项，并负责进行数据转换，从而带来易用的无缝式体验，同时生成非常好的结果。

在设计 BigQuery ML 后端时，该团队面临着两难的局面。将大量数据从 BigQuery 服务器传输到运行机器学习算法的专用服务器上将非常耗时，并且存在数据安全性和隐私方面的问题。不过，梯度下降的核心组件——一种用于优化机器学习算法的方法——可以使用常用的 SQL 操作来实现，因此我们能够让 BigQuery ML 利用现有的 BigQuery SQL 处理引擎。

BigQuery 引擎旨在高效地扫描大型数据集而不只是随机抽取小型样本，因此，BigQuery ML 是基于梯度下降的标准（批量）变体，而不是随机版本。虽然随机梯度下降在当今的大型机器学习系统中更为常见，但批量变体具有很多实践方面的优势。

例如，基于随机梯度下降过程的数据库嵌入式机器学习系统逐个处理样本，并且在无排序数据集上表现不佳。为了优化常规 SQL 的查询性能，BigQuery 的数据通常分布在磁盘上，并且这些数据被持续重新分布，以便支持随机机器学习算法，而这些操作是计算密集型的，相当昂贵。相反，批量梯度下降对磁盘上的数据是否排序和分区并不敏感，从而完全避免了这个问题。此外，批处理方法可以与经典的线性搜索技术相结合，可以让学习算法更加稳定并且需要更少的调整。我们还实现了对正规化和预处理的支持。

要体验 BigQuery ML，请访问 BigQuery 控制台（https://console.cloud.google.com/bigquery ）并按照用户指南（https://cloud.google.com/bigquery/docs/bigqueryml-intro ）进行操作。

创建模型非常简单：

CREATE MODEL dataset.model_name
  OPTIONS(model_type=’linear_reg’, input_label_cols=[‘input_label’])
AS SELECT * FROM input_table;

在未来，谷歌计划进一步将梯度下降实现与 BigQuery 基础架构集成在一起，以实现更高的性能提升。

以下是创建回归模型、使用模型进行预测、创建分类模型和评估模型的示例。

创建回归模型

#standardsql
CREATE OR REPLACE MODEL flights.arrdelay
OPTIONS
  (model_type='linear_reg', labels=['arr_delay']) AS
SELECT
  arr_delay,
  carrier,
  origin,
  dest,
  dep_delay,
  taxi_out,
  distance
FROM
  `cloud-training-demos.flights.tzcorr`
WHERE
  arr_delay IS NOT NULL

10 分钟后，模型就训练好了，并计算出每次迭代的结果。

这里的损失就是均方误差，因此模型在迭代 #6 上收敛，RMSE 约为 sqrt(97)=10 分钟。

使用模型进行预测

#standardsql
SELECT * FROM ML.PREDICT(MODEL flights.arrdelay,
(
SELECT
  carrier,
  origin,
  dest,
  dep_delay,
  taxi_out,
  distance,
  arr_delay AS actual_arr_delay
FROM
  `cloud-training-demos.flights.tzcorr`
WHERE
  arr_delay IS NOT NULL
LIMIT 10))

结果如下：

因为使用训练模型来预测名为“arr_delay”的变量，所以 ML.PREDICT 会创建一个名为 predict_arr_delay 的列。这个示例从原始表中拉出 10 行数据并预测这些航班的晚点情况。

创建分类模型

这次是预测航班延延误过 15 分钟的可能性。这是一个分类问题，所以需要对查询稍作修改：

#standardsql
CREATE OR REPLACE MODEL flights.ontime
OPTIONS
  (model_type='logistic_reg', labels=['on_time']) AS
SELECT
  IF(arr_delay < 15, 1, 0) AS on_time,
  carrier,
  origin,
  dest,
  dep_delay,
  taxi_out,
  distance
FROM
  `cloud-training-demos.flights.tzcorr`
WHERE
  arr_delay IS NOT NULL

计算结果：

以及另一个预测示例：

评估模型

我们可以在独立数据集上评估模型。为了方便，这个例子展示了如何在模型训练的同一数据集上进行评估：

#standardsql
SELECT * FROM ML.EVALUATE(MODEL flights.ontime,
(
SELECT
  IF(arr_delay < 15, 1, 0) AS on_time,
  carrier,
  origin,
  dest,
  dep_delay,
  taxi_out,
  distance
FROM
  `cloud-training-demos.flights.tzcorr`
WHERE
  arr_delay IS NOT NULL
))

结果：

英文原文：

https://ai.googleblog.com/2018/07/machine-learning-in-google-bigquery.html

https://towardsdatascience.com/how-to-train-and-predict-regression-and-classification-ml-models-using-only-sql-using-bigquery-ml-f219b180b947