GSP413

概览

BigQuery 是 Google 推出的全托管式、无需运维、费用低廉的分析数据库。借助 BigQuery，您可以查询 TB 级的数据，而不必管理任何基础设施，也无需数据库管理员。BigQuery 使用 SQL，并且支持随用随付模式。BigQuery 让您可以专心分析数据，发掘有意义的数据洞见。

您将会用到一个电子商务数据集，其中包含 Google Merchandise Store 的上百万条 Google Analytics 记录。另外还需要研究其中提供的字段和记录以获取数据洞见。

本实验重点介绍如何使用 SQL JOIN 和 UNION 语法来创建新报表。

场景：您的营销团队向您和数据科学团队提供了公司电子商务网站的所有商品评价。您正在与他们一同在 BigQuery 中创建数据仓库，为此需要联接三个来源的数据：

• 网站电子商务数据
• 商品库存存货水平和交货期
• 商品评价情感分析

您将执行的操作

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

• 探索新的电子商务数据并执行情感分析。
• 联接数据集并创建新表。
• 使用 unions 和表通配符附加历史数据。

设置和要求

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

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 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. 创建新数据集来存储表

首先，在 BigQuery 中创建一个名为 ecommerce 的新数据集来存储表。

1. 在左侧窗格中，点击您的 BigQuery 项目名称 (qwiklabs-gcp-xxxx)。

2. 点击项目名称旁边的三个点，然后选择创建数据集。

创建数据集对话框将打开。

3. 将数据集 ID 设置为 ecommerce，将所有其他选项保留为各自的默认值。

4. 点击创建数据集。

点击“检查我的进度”，验证已完成以下目标： 创建新数据集来存储表

任务 2. 研究商品情感数据集

数据科学团队已经通过 API 处理了所有商品评价，并向您提交了每款商品的平均情感分数和量级。

包含营销团队数据集的项目是 data-to-insights。默认情况下，BigQuery 中不会显示 BigQuery 公共数据集。尽管您看不到，但本实验执行的查询将使用 data-to-insights 数据集。

1. 首先为数据科学团队制作的表创建一个副本，以便您读取其中的数据：

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` 注意：此副本仅供您查看，本实验中的查询将使用 data-to-insights 项目。

2. 点击 ecommerce 数据集以显示 products 表。

通过“预览”和“架构”标签页检查数据

1. 前往 ecommerce > products 数据集，然后点击预览标签页以查看数据。

2. 点击架构标签页。

创建一个查询来显示情感最积极的前 5 种商品

1. 在查询编辑器中编写 SQL 查询。

可能的解决方法：

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. 修改查询以显示情感最消极的前 5 种商品，并滤除 NULL 值。

可能的解决方法：

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

情感最消极的商品是什么？

点击“检查我的进度”，验证已完成以下目标： 研究商品情感数据集

任务 3. 联接数据集以发掘数据洞见

场景：在本月的第一天，库存团队已经告知您，商品库存数据集中的 orderedQuantity 字段已过时。他们需要您帮助查询一下各款商品在 2017 年 8 月 1 日的总销量，然后与库存中的当前存货水平进行对比，以确定需要先给哪些商品补货。

按 productSKU 计算日销量

1. 在 ecommerce 数据集中创建一个满足以下要求的新表：

• 命名为 sales_by_sku_20170801
• 数据来自 data-to-insights.ecommerce.all_sessions_raw
• 仅包含不同的结果
• 返回 productSKU
• 返回总订购数量 (productQuantity). 提示：使用 SUM() with a IFNULL 条件
• 仅过滤 20170801 的销量
• ORDER BY 将订单数量最多的 SKU 排在最前面

可能的解决方法：

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. 点击 sales_by_sku 表，然后点击预览标签页。

售出了多少不同的商品 SKU？

答案：462

下面，联接两个数据集，以便用商品库存信息充实销售数据。

联接销售数据和库存数据

1. 通过 JOIN，使用商品库存数据集中的以下字段来充实网站电子商务数据：

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. 将未编写完全的查询补充完整：

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

可能的解决方法：

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. 修改所编写的查询，在其中添加以下内容：

• 计算字段 (total_ordered / stockLevel) 并将其别名设为“ratio”。提示：使用 SAFE_DIVIDE(field1,field2)，以免在存货水平为 0 时出现除式分母为 0 的错误。
• 过滤结果，以仅包含在本月初库存售出水平就已经达到 50% 或更高的商品

可能的解决方法：

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

点击“检查我的进度”，验证已完成以下目标： 联接数据集以发掘数据洞见

任务 4. 附加其他记录

您的国际团队已经生成了 2017 年 8 月 2 日的店内销量，您希望将其记录到日销量表中。

创建一个新的空表，以按 productSKU 存储 2017 年 8 月 2 日的销量

1. 对于架构，请指定以下字段：

• 表名设为 ecommerce.sales_by_sku_20170802
• productSKU STRING
• 将 total_ordered 指定为 INT64 字段

可能的解决方法：

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. 确认您现在已经有两个共享日期的销量表，点击表结果中 Sales_by_sku 表名旁边的下拉菜单，或者刷新浏览器确认它已在列在左侧菜单中：

3. 插入销售团队提供给您的销量记录：

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. 预览表以确认记录包含在其中，点击表名查看结果。

附加整合历史数据

有多种方法可以附加具有相同架构的整合数据。其中两种常见的方法是使用 UNION 和表通配符。

• Union 是一个 SQL 运算符，可用于附加来自不同结果集的整合行。
• 表通配符可用于使用简洁的 SQL 语句查询多个表。只有标准 SQL 才提供通配符表。

1. 编写一个 UNION 查询，以返回下面两个表中的所有记录：

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 注意：UNION 与 UNION ALL 的区别是，UNION 不包括重复的记录。

使用多个日销量表的隐患是什么？您不得不编写多个 UNION 语句并将它们链接起来。

更好的解决方法是使用表通配符过滤条件和 _TABLE_SUFFIX 过滤条件。

2. 编写一个包含表通配符 (*) 的查询，以返回 2017 年 ecommerce.sales_by_sku_ 中的所有记录。

可能的解决方法：

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. 修改上一个查询，在其中添加一个过滤条件以限制结果中仅显示 2017 年 8 月 2 日的记录。

可能的解决方法：

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' 注意：您还可以考虑创建一个分区表，通过此表自动提取日销量数据并注入到正确的分区。

点击“检查我的进度”，验证已完成以下目标： 附加其他记录

恭喜！

您已创建了报表，并使用 SQL JOIN 和 UNION 处理了相关视图，之后研究了示例电子商务数据。

后续步骤/了解详情

• 已拥有 Google Analytics 账号，想要在 BigQuery 中查询您自己的数据集？请按照此导出指南中的说明操作。
• 如果您想详细了解 SQL JOIN 语法，请查阅 BigQuery JOIN 文档。
• 利用 Google 数据集搜索作为资源！
• 查看下面的其他实验：
  • 使用 BigQuery 分析 NCAA 数据
  • Cloud Composer：跨不同位置复制 BigQuery 表

Google Cloud 培训和认证

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

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

上次测试实验的时间：2023 年 10 月 31 日

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