使用BigQuery ML预测天气（6.19）

导言

天气预报是一个复杂的过程，如果你住在墨尔本这种天气波动很大的地区，你应该能够发现天气和网站上看到的不同。

通常的操作是先收集大量关于大气、湿度风力等信息，然后依靠我们已有的大气知识和物理模型预测出不久将来的天气变化。但是，由于我们对物理模型的理解有限，以及大气性质变换莫测，可能通过这种方式得到的结果并不是非常可靠。

本文，我们将研究使用机器学习的方法预测天气。我们希望模型能够查看历史数据，并了解温度在不久的将来会如何变化。

NOAA天气数据是Google BigQuery中公开可用的数据集之一，由Google员工每天维护和更新。我们将使用这些数据以及最近的BigQuery ML来查看是否有可能提出预测。

我们将在BigQuery中准备和预处理数据，并在此处依赖BigQuery ML作为我们的机器学习模型。所有这些都是在BigQuery中进行的，这就是它的美妙之处。所以期望在BigQuery内部看到很多关于训练数据准备和预处理的查询，以及BigQuery ML的最终模型。

BigQuery维护一个数据集，其中包含更新的国家海洋和大气管理局（NOAA）天气数据和来自世界各地不同站点的大气数据。该数据包含了1929年至今的气候数据，并且每日进行更新。

数据集是“bigquery-public-data.noaa_gsod”，每年都有一个表。因此访问表很容易，通过BigQuery的语法，甚至可以使用正则表达式访问它们。在一项初步研究中，我们观察到，如果我们将训练数据集限制在2010年以后，仍有足够的数据供模型训练。因此，在创建数据集时，我们将通过将它们称为“bigquery-public-data.noaa_gsod.gsod201 *”来查询相应的表。

gsod表的每个记录对应于来自站的观察。“stn”字段显示站号，它将我们引用到站表，其中保存有关站的信息，包括其地理位置和海拔及其国家。

每个观测指的是一个特定的日期，包括温度（最小值，最大值，平均值），大气数据，包括风速，降水量，湿度等。我们将利用该站和观测数据作为我们的特征输入模型。

训练数据集准备包括一些步骤。首先，我们将训练数据限制在感兴趣的地理范围内；其次，我们将非浮点字段将值转换为浮点型；第三，我们将处理缺失值；第四，我们将准备过去7天的天气信息和明天的温度标签字段。你将会看到我们的模型可以查看过去7天的温度和大气数据，并预测即将到来的一天的温度。

如果你注意到所有内容都在SQL中，并且在BigQuery内部，我会更感激。只需闭上眼睛，考虑在一条记录中进行过去7天的查询。这是BigQuery中的一个反模式，你的查询内容永远在运行。

地理位置和时间范围

我们将实验限制在2010年以后的数据和我们感兴趣的地理位置上。这非常容易，一个连接和一组where子句。

1select lon, lat, elev, stn, temp, ...2from `bigquery-public-data.noaa_gsod.gsod201*` gsod ,`bigquery-public-data.noaa_gsod.stations` stns3where stns.usaf = gsod.stn and stns.country='AS'4and lat > LAT_MIN and lat < LAT_MAX5and lon > LON_MIN and lon < LON_MAXselect lon, lat, elev, stn, temp, ...
2from `bigquery-public-data.noaa_gsod.gsod201*` gsod ,`bigquery-public-data.noaa_gsod.stations` stns
3where stns.usaf = gsod.stn and stns.country='AS'
4and lat > LAT_MIN and lat < LAT_MAX
5and lon > LON_MIN and lon < LON_MAX

你可以在其中使用你设置的范围替换大写的变量。澳大利亚在数据集中以代码“AS”显示，我们通过*符号可以轻松的将表格限制在2010年以后。

另一点：如果你想讲地理范围限制的更复杂而不是像我们使用的方框，你需要查看BigQuery GIS，它在撰写文本时处于测试阶段。

Casting

BigQuery ML将字符串字段视为分类值，这不是我们想要的字段。示例如下：

1select cast(elev as FLOAT64) as elev , ...2from ...select cast(elev as FLOAT64) as elev , ...
2from ...

此外，我们可以通过以下方式从年、月和日字段创建日期时间字段：

1select datetime(year, mo , da , 00 , 00 , 00) as datetime , ...2from ...select datetime(year, mo , da , 00 , 00 , 00) as datetime , ...
2from ...

Missing Values

如果你查看gsod表的描述，你会注意到缺失值由特定值（如99.9或类似值）表示，我们将编写一个查询，用字段的平均值替换这些缺失值。如下：

1select if( temp = 99.9 , avg_temp, temp) as temp2from ... ,3(  SELECT4AVG(temp) AS avg_temp5FROM6`bigquery-public-data.noaa_gsod.gsod201*`7WHERE8temp  99.9 )select if( temp = 99.9 , avg_temp, temp) as temp
2from ... ,
3(  SELECT
4AVG(temp) AS avg_temp
5FROM
6`bigquery-public-data.noaa_gsod.gsod201*`
7WHERE
8temp  99.9 )

OLAP 

到目前为止，我们已经将数据现在在感兴趣的时间和地理范围内，投射字段，并用平均值替换缺失值。查询中最重要的部分是将7天的数据保存在一行中。self-join就是传统数据库的解决方案。但是，self-join是BigQuery中的反模式之一，并且引擎会遇到一些难以运行的内容，特别是如果它们不止一个。相反，解决方案是使用online analytical processing功能。

1select temp ,2LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_1,3LEAD(temp, 2 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_2 ,4... ,5LEAD(temp, 7 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_7 ,6LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime ASC) as LABEL ,7...8from ...9where ...select temp ,
2LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_1,
3LEAD(temp, 2 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_2 ,
4... ,
5LEAD(temp, 7 ) OVER (PARTITION BY stn ORDER BY datetime DESC) AS temp_7 ,
6LEAD(temp, 1 ) OVER (PARTITION BY stn ORDER BY datetime ASC) as LABEL ,
7...
8from ...
9where ...

lead函数返回后续行的值表达式（temp here）的值。第二个参数是偏移量。作为示例，偏移值1将返回前一天的温度，偏移值2表示两天前的温度值。同样，我们可以按升序对分区进行排序来访问明天的天气（LABEL）。

stn的分区将告诉BigQuery拥有与一个集群相同值的元素并迭代它们。因此（按日期时间desc的stn顺序）将告诉BigQuery与一个集群表示同一站的行，然后按时间按降序对集群进行排序，并从连续行返回温度。这是BigQuery中的方法。

这里，我们讨论了查询中使用不同代码模式。显然，最终查询是一个非常长的查询，它重复利用上面的代码片段获得特征视图。特征视图的每个记录包括连续7天的大气和温度特征以及标记，即第8天的温度。所有字段都很清晰，可以通过BigQuery ML将其摄入线性回归模型。

BigQuery ML是BigQuery中最新的机器学习模块。目前，它只包括分类和线性回归的逻辑回归。可以看出BigQuery ML的目标是提供一种简单快捷的方法来实现你的第一个模型。这是非常有价值的功能，因为训练和验证机器学习网络被抽象为单个查询。

线性回归的训练就像运行以下命令一样简单。

 1CREATE or REPLACE MODEL `weather-prediction-dataset.linear_regression` 2OPTIONS ( 3model_type="linear_reg" , 4input_label_cols=["LABEL"] , 5l2_reg=0.001 , 6max_iterations=20 , 7ls_init_learn_rate=0.01 , 8data_split_method="seq" , 9data_split_eval_fraction=0.2 ,10data_split_col="ID" )11select ...12from ...13where ...CREATE or REPLACE MODEL `weather-prediction-dataset.linear_regression`
 2OPTIONS (
 3model_type="linear_reg" ,
 4input_label_cols=["LABEL"] ,
 5l2_reg=0.001 ,
 6max_iterations=20 ,
 7ls_init_learn_rate=0.01 ,
 8data_split_method="seq" ,
 9data_split_eval_fraction=0.2 ,
10data_split_col="ID" )
11select ...
12from ...
13where ...

这告诉BigQuery训练线性回归模型。除了字段ID和LABEL之外，查询中的所有字段都将被视为模型的输入要素。ID用于训练数据集记录进行排序，data_split_eval_fraction将告诉BigQuery将最后20%的数据集作为评估集。标签字段是模型预测的内容，该查询在前80%的数据上训练线性回归模型。其余20%的数据用于评估。

我们还可以通过运行来展示另一个子集的性能指标：

1select * from ML.EVALUATE(2MODEL `weather-prediction-dataset.linear_regression` , (3select ...4from ...5where ... ))select * from ML.EVALUATE(
2MODEL `weather-prediction-dataset.linear_regression` , (
3select ...
4from ...
5where ... ))

从模型中获得预测就像以下一样容易：

1select * from ML.PREDICT (2MODEL `weather-prediction=dataset.linear_regression` , (3select ...4from ...5where ...))select * from ML.PREDICT (
2MODEL `weather-prediction=dataset.linear_regression` , (
3select ...
4from ...
5where ...))

结语

在本文中，我们展示了如何处理天气预报数据并在其上训练线性回归模型。天气模型显示6.9华氏度的RMSE，可以推断最简单的线性ML模型能够平均产生约7度的温度预测。

长按二维码 ▲

订阅「架构师小秘圈」公众号

如有启发，帮我点个在看，谢谢↓