GSP246

概览

BigQuery 是 Google 推出的全托管式、无需运维、费用低廉的分析数据库。借助 BigQuery，您可以查询 TB 级的数据，而不必管理任何基础设施，也无需数据库管理员。

BigQuery ML 让数据分析师只需编写极少的代码，即可创建、训练、评估机器学习模型，并用这些模型进行预测。

在本实验中，您将对 BigQuery 公共数据集中纽约市黄色出租车的数百万条行程记录进行处理。您将使用这些数据在 BigQuery 中创建机器学习模型，并根据模型输入预测出租车费用，然后评估模型的性能并使用此模型进行预测。

学习内容

在本实验中，您将学习如何执行以下任务：

• 使用 BigQuery 查找公开数据集
• 查询和探索出租车公共数据集
• 创建用于批量预测的训练和评估数据集
• 在 BigQuery ML 中创建预测（线性回归）模型
• 评估您的机器学习模型的性能

设置和要求

点击“开始实验”按钮前的注意事项

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 Google Cloud 资源可供您使用多长时间。

此实操实验可让您在真实的云环境中开展实验活动，免受模拟或演示环境的局限。我们会为您提供新的临时凭据，让您可以在实验规定的时间内用来登录和访问 Google Cloud。

为完成此实验，您需要：

• 能够使用标准的互联网浏览器（建议使用 Chrome 浏览器）。

注意：请使用无痕模式或无痕浏览器窗口运行此实验。这可以避免您的个人账号与学生账号之间发生冲突，这种冲突可能导致您的个人账号产生额外费用。

• 完成实验的时间 - 请注意，实验开始后无法暂停。

注意：如果您已有自己的个人 Google Cloud 账号或项目，请不要在此实验中使用，以避免您的账号产生额外的费用。

如何开始实验并登录 Google Cloud 控制台

1. 点击开始实验按钮。如果该实验需要付费，系统会打开一个弹出式窗口供您选择付款方式。左侧是实验详细信息面板，其中包含以下各项：

   • 打开 Google Cloud 控制台按钮
   • 剩余时间
   • 进行该实验时必须使用的临时凭据
   • 帮助您逐步完成本实验所需的其他信息（如果需要）

2. 点击打开 Google Cloud 控制台（如果您使用的是 Chrome 浏览器，请右键点击并选择在无痕式窗口中打开链接）。

   该实验会启动资源并打开另一个标签页，显示登录页面。

   提示：请将这些标签页安排在不同的窗口中，并将它们并排显示。

   注意：如果您看见选择账号对话框，请点击使用其他账号。

3. 如有必要，请复制下方的用户名，然后将其粘贴到登录对话框中。

   {{{user_0.username | "<用户名>"}}}

   您也可以在实验详细信息面板中找到用户名。

4. 点击下一步。

5. 复制下面的密码，然后将其粘贴到欢迎对话框中。

   {{{user_0.password | "<密码>"}}}

   您也可以在实验详细信息面板中找到密码。

6. 点击下一步。

   重要提示：您必须使用实验提供的凭据。请勿使用您的 Google Cloud 账号凭据。 注意：在本次实验中使用您自己的 Google Cloud 账号可能会产生额外费用。

7. 继续在后续页面中点击以完成相应操作：

   • 接受条款及条件。
   • 由于该账号为临时账号，请勿添加账号恢复选项或双重验证。
   • 请勿注册免费试用。

片刻之后，系统会在此标签页中打开 Google Cloud 控制台。

注意：如需查看列有 Google Cloud 产品和服务的菜单，请点击左上角的导航菜单。

打开 BigQuery 控制台

1. 在 Google Cloud 控制台中，选择导航菜单 > BigQuery。

您会看到欢迎在 Cloud 控制台中使用 BigQuery 消息框，其中提供了指向快速入门指南和版本说明的链接。

2. 点击完成。

BigQuery 控制台即会打开。

任务 1. 探索纽约市出租车数据

问题：在 2015 年的各个月份，黄色出租车有多少条行程记录？

1. 复制以下 SQL 代码并粘贴到查询编辑器中：

#standardSQL SELECT TIMESTAMP_TRUNC(pickup_datetime, MONTH) month, COUNT(*) trips FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015` GROUP BY 1 ORDER BY 1

2. 然后，点击运行。

您应该会看到包含以下两列的输出结果：

可以看到，2015 年的各个月份，纽约市出租车的行程记录数都超过了 1 千万条，这可不是一个小数目！

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

计算 2015 年每个月份黄色出租车的行程记录数

问题：在 2015 年，黄色出租车的行程平均车速是多少？

• 将之前的查询替换为以下查询，然后点击运行：

#standardSQL SELECT EXTRACT(HOUR FROM pickup_datetime) hour, ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime, pickup_datetime, SECOND))*3600, 1) speed FROM `bigquery-public-data.new_york.tlc_yellow_trips_2015` WHERE trip_distance > 0 AND fare_amount/trip_distance BETWEEN 2 AND 10 AND dropoff_datetime > pickup_datetime GROUP BY 1 ORDER BY 1

您应该会看到包含以下两列的输出结果：

白天的平均车速约为 11-12 英里/小时，但凌晨 5 点的平均车速几乎翻倍，可以达到 21 英里/小时。凭直觉而言，这是有道理的，因为凌晨 5 点的道路上，车流比较少。

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

计算 2015 年黄色出租车的平均车速

任务 2. 确定目标

接下来，您将在 BigQuery 中创建机器学习模型，并根据不同行程和出行数据的历史数据集来预测纽约市的出租车价格。在乘车前预测车费对乘客和出租车公司的行程规划都大有帮助。

任务 3. 选择特征并创建您的训练数据集

纽约市黄色出租车数据集是该市提供的公共数据集，已加载到 BigQuery 中供您探索。

浏览字段的完整列表，然后预览数据集以找到有用的特征，这些特征将帮助机器学习模型了解出租车历史行程数据与车费之间的关系。

您的团队决定测试以下字段是否适合作为您的车费预测模型的输入：

• 通行费金额
• 车费金额
• 时段
• 上车地址
• 下车地址
• 乘客数量

1. 用以下代码替换查询：

#standardSQL WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, pickup_longitude AS pickuplon, pickup_latitude AS pickuplat, dropoff_longitude AS dropofflon, dropoff_latitude AS dropofflat, passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount > 0 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.TRAIN ) SELECT * FROM taxitrips

请注意有关此查询的几个事项：

• 底部的 (SELECT * from taxitrips) 是此查询的主要部分。
• 对纽约市数据集的大批量提取由 taxitrips 来执行，而 SELECT 则包含训练特征和标签。
• WHERE 会移除您不想在训练中使用的数据。
• WHERE 还包括一个取样子句，用以仅选取 1/1000 的数据。
• 定义名为 TRAIN 的变量，以便您能快速构建独立的 EVAL 集。

2. 现在您已充分了解了此查询的目的，请点击运行。

您应该会收到类似如下的结果：

标签是什么（正确答案）？

total_fare 是标签（即您将要预测的内容）。您是用 tolls_amount 和 fare_amount 创建的此字段，因此可以忽略客户小费部分，不将其提供给模型，因为小费是由乘客自行决定的。

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

测试字段是否适合作为车费预测模型的输入

任务 4. 创建 BigQuery 数据集来存储模型

在此部分，您将创建一个新的 BigQuery 数据集来存储您的机器学习模型。

1. 在左侧的“探索器”面板中，点击项目 ID 旁边的查看操作图标，然后点击创建数据集。

2. 在“创建数据集”对话框中，输入以下内容：

• 在数据集 ID中，输入 taxi。
• 将 us（美国的多个区域）选作位置类型。
• 将其他值保留为默认值。

3. 然后点击创建数据集。

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

创建 BigQuery 数据集来存储模型

任务 5. 选择 BigQuery ML 模型类型并指定选项

现在，您已选择初始特征，可以在 BigQuery 中创建您的首个机器学习模型了。

有多个模型类型可供选择：

• 线性回归 (linear_reg) 模型可用于预测下个月的销售额等数值。
• 逻辑回归 (logistic_reg) 模型可用于二元分类或多类别分类，如垃圾邮件或非垃圾邮件。
• k-Means 聚类可用于通过非监督式学习进行探索 (kmeans)。

注意：机器学习中还使用了许多其他模型类型（例如神经网络和决策树），可通过 TensorFlow 等库调用。目前，BQML 支持上面列出的三种模型。请参考 BQML 路线图了解详情。

1. 输入以下查询，创建新模型并指定模型选项。

CREATE or REPLACE MODEL taxi.taxifare_model OPTIONS (model_type='linear_reg', labels=['total_fare']) AS WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, pickup_longitude AS pickuplon, pickup_latitude AS pickuplat, dropoff_longitude AS dropofflon, dropoff_latitude AS dropofflat, passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount > 0 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.TRAIN ) SELECT * FROM taxitrips

2. 接下来，点击运行以训练模型。

3. 等待模型完成训练（5 - 10 分钟）。

模型训练完成后，您将看到消息“This statement will create a new model named qwiklabs-gcp-03-xxxxxxxx:taxi.taxifare_model”（本语句创建了名为 qwiklabs-gcp-03-xxxxxxxx:taxi.taxifare_model 的新模型），表明您的模型已成功完成训练。

4. 查看您的出租车数据集，确认现在已出现 taxifare_model。

接下来，您将评估模型针对未曾见过的评估数据的性能。

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

创建出租车费模型

任务 6. 评估分类模型性能

选择您的性能标准

对于线性回归模型，您需要使用损失指标，例如均方根误差 (RMSE)。您需要不断训练和改进模型，直到 RMSE 降至最低。

在 BQML 中，mean_squared_error 是评估经训练的机器学习模型时可查询的一个字段。添加 SQRT() 以获取 RMSE。

完成训练后，您可以使用 ML.EVALUATE 运行此查询，以评估模型的性能。

1. 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

#standardSQL SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL taxi.taxifare_model, ( WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, pickup_longitude AS pickuplon, pickup_latitude AS pickuplat, dropoff_longitude AS dropofflon, dropoff_latitude AS dropofflat, passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount > 0 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.EVAL ) SELECT * FROM taxitrips ))

现在，您将使用 params.EVAL 过滤器，根据不同的出租车行程集对模型进行评估。

2. 模型运行后，请检查您的模型结果（您的模型 RMSE 值会略有不同）。

行	rmse
1	9.477056435999074

经评估，您的模型 RMSE 为 9.47。由于我们取了均方根误差 (RMSE)，因此可以用与 total_fare 相同的单位来评估 9.47 这个误差，所以它是 +-$9.47。

要知道这种损失指标是否可用于模型的产品化，完全取决于您在模型训练开始前设定的基准标准。基准测试是指确定模型性能和准确率的最低可接受水平。

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

评估分类模型性能

任务 7. 预测出租车费金额

接下来，编写查询以使用新模型进行预测。

• 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

#standardSQL SELECT * FROM ml.PREDICT(MODEL `taxi.taxifare_model`, ( WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, pickup_longitude AS pickuplon, pickup_latitude AS pickuplat, dropoff_longitude AS dropofflon, dropoff_latitude AS dropofflat, passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount > 0 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.EVAL ) SELECT * FROM taxitrips ));

现在，您将看到模型对出租车费用的预测，以及这些行程的实际费用和其他特征。您应该会得到类似如下的结果：

验证您已完成的任务

点击检查我的进度可验证您已完成的任务。如果您成功完成了任务，系统会给出评分。

预测出租车费金额

任务 8. 利用特征工程改进模型

构建机器学习模型是一个迭代过程。我们评估完初始模型的性能后，通常会回过头来对特征和行进行删减，看看是否可以得到更好的模型。

过滤训练数据集

现在，请查看出租车费用的常用统计数据。

1. 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

SELECT COUNT(fare_amount) AS num_fares, MIN(fare_amount) AS low_fare, MAX(fare_amount) AS high_fare, AVG(fare_amount) AS avg_fare, STDDEV(fare_amount) AS stddev FROM `nyc-tlc.yellow.trips` # 1,108,779,463 fares

您应该会收到类似如下输出结果：

可以看到，数据集中有一些异常离群值（车费为负数或超过 $50,000 的值）。可应用一些主题专业知识，帮助模型避免学习异常离群值。

限制数据范围，仅显示 $$6 至 $$200 之间的车费。

2. 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

SELECT COUNT(fare_amount) AS num_fares, MIN(fare_amount) AS low_fare, MAX(fare_amount) AS high_fare, AVG(fare_amount) AS avg_fare, STDDEV(fare_amount) AS stddev FROM `nyc-tlc.yellow.trips` WHERE trip_distance > 0 AND fare_amount BETWEEN 6 and 200 # 843,834,902 fares

您应该会收到类似如下输出结果：

这样就会好一些。与此同时，对行程距离也做一些限制，从而真正专注于纽约市的情形。

3. 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

SELECT COUNT(fare_amount) AS num_fares, MIN(fare_amount) AS low_fare, MAX(fare_amount) AS high_fare, AVG(fare_amount) AS avg_fare, STDDEV(fare_amount) AS stddev FROM `nyc-tlc.yellow.trips` WHERE trip_distance > 0 AND fare_amount BETWEEN 6 and 200 AND pickup_longitude > -75 #limiting of the distance the taxis travel out AND pickup_longitude < -73 AND dropoff_longitude > -75 AND dropoff_longitude < -73 AND pickup_latitude > 40 AND pickup_latitude < 42 AND dropoff_latitude > 40 AND dropoff_latitude < 42 # 827,365,869 fares

您应该会收到类似如下输出结果：

我们的新模型仍有超过 8 亿条行程记录的大型训练数据集可供学习。使用这些新的限制条件重新训练模型，看看其性能表现如何。

重新训练模型

调用新模型 taxi.taxifare_model_2，重新训练线性回归模型来预测总车费。您会发现模型中还添加了一些计算特征，用于计算上车点与下车点之间的欧几里得距离（直线距离）。

• 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

CREATE OR REPLACE MODEL taxi.taxifare_model_2 OPTIONS (model_type='linear_reg', labels=['total_fare']) AS WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, SQRT(POW((pickup_longitude - dropoff_longitude),2) + POW(( pickup_latitude - dropoff_latitude), 2)) as dist, #Euclidean distance between pickup and drop off SQRT(POW((pickup_longitude - dropoff_longitude),2)) as longitude, #Euclidean distance between pickup and drop off in longitude SQRT(POW((pickup_latitude - dropoff_latitude), 2)) as latitude, #Euclidean distance between pickup and drop off in latitude passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount BETWEEN 6 and 200 AND pickup_longitude > -75 #limiting of the distance the taxis travel out AND pickup_longitude < -73 AND dropoff_longitude > -75 AND dropoff_longitude < -73 AND pickup_latitude > 40 AND pickup_latitude < 42 AND dropoff_latitude > 40 AND dropoff_latitude < 42 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.TRAIN ) SELECT * FROM taxitrips

重新训练模型可能需要几分钟时间。当您在控制台中收到以下消息时，即可进入下一步：

评估新模型

现在线性回归模型已经得到了优化，可以使用数据集来评估此模型并了解其性能。

• 复制以下代码并将其粘贴到查询编辑器中，然后点击运行：

SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL taxi.taxifare_model_2, ( WITH params AS ( SELECT 1 AS TRAIN, 2 AS EVAL ), daynames AS (SELECT ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek), taxitrips AS ( SELECT (tolls_amount + fare_amount) AS total_fare, daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM pickup_datetime))] AS dayofweek, EXTRACT(HOUR FROM pickup_datetime) AS hourofday, SQRT(POW((pickup_longitude - dropoff_longitude),2) + POW(( pickup_latitude - dropoff_latitude), 2)) as dist, #Euclidean distance between pickup and drop off SQRT(POW((pickup_longitude - dropoff_longitude),2)) as longitude, #Euclidean distance between pickup and drop off in longitude SQRT(POW((pickup_latitude - dropoff_latitude), 2)) as latitude, #Euclidean distance between pickup and drop off in latitude passenger_count AS passengers FROM `nyc-tlc.yellow.trips`, daynames, params WHERE trip_distance > 0 AND fare_amount BETWEEN 6 and 200 AND pickup_longitude > -75 #limiting of the distance the taxis travel out AND pickup_longitude < -73 AND dropoff_longitude > -75 AND dropoff_longitude < -73 AND pickup_latitude > 40 AND pickup_latitude < 42 AND dropoff_latitude > 40 AND dropoff_latitude < 42 AND MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))),1000) = params.EVAL ) SELECT * FROM taxitrips ))

您应该会收到类似如下输出结果：

可以看到，您已将 RMSE 降至 +-$$5.12，明显优于第一个模型的 +-$$9.47。

由于 RMSE 定义了预测误差的标准差，我们看到重新训练的线性回归大幅提升了模型的准确率。

任务 9. 检验您的掌握情况

下面的选择题可加强您对本实验所涉概念的理解。请尽您所能回答。

任务 10. 其他可探索的数据集

如果您想了解使用其他数据集建模的情况（例如预测芝加哥的出租车行程费用），则可以使用 bigquery-public-data 项目。

1. 如需打开数据集 bigquery-public-data，请依次点击 +添加 > 按名称为项目加星标 > 输入项目名称，然后输入名称 bigquery-public-data。

2. 点击加星标。

“探索器”部分会显示 bigquery-public-data 项目。

恭喜！

您已在 BigQuery 中成功构建用于预测纽约市出租车费的机器学习模型。

后续步骤/了解详情

• 如需详细了解 BiqQuery ML，请查阅BigQuery ML 文档。
• 如需详细了解如何在 Vertex AI 中管理 BigQuery ML 模型，请查阅有关使用 Model Registry 注册 BigQuery ML 模型的文档。

Google Cloud 培训和认证

…可帮助您充分利用 Google Cloud 技术。我们的课程会讲解各项技能与最佳实践，可帮助您迅速上手使用并继续学习更深入的知识。我们提供从基础到高级的全方位培训，并有点播、直播和虚拟三种方式选择，让您可以按照自己的日程安排学习时间。各项认证可以帮助您核实并证明您在 Google Cloud 技术方面的技能与专业知识。

上次更新手册的时间：2024 年 2 月 7 日

上次测试实验的时间：2023 年 8 月 24 日

版权所有 2024 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。