在 BigQuery 中使用 JSON、数组和结构体 | Google Cloud Skills Boost

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

您将执行的操作

在本实验中，您将学习如何完成以下操作：

加载和查询半结构化数据，包括解除这些数据的嵌套。

对有关半结构化数据的查询进行问题排查。

设置和要求

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

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

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

为完成此实验，您需要：

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

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

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

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

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

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

打开 Google Cloud 控制台 按钮

进行该实验时必须使用的临时凭据

帮助您逐步完成本实验所需的其他信息（如果需要）

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

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

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

注意：如果您看见 选择账号 对话框，请点击 使用其他账号 。
如有必要，请复制下方的 用户名 ，然后将其粘贴到登录对话框中。
{{{user_0.username | "<用户名>"}}}
您也可以在 实验详细信息 面板中找到 用户名 。

点击 下一步 。

复制下面的密码，然后将其粘贴到欢迎对话框中。
{{{user_0.password | "<密码>"}}}
您也可以在 实验详细信息 面板中找到密码。

点击 下一步 。
重要提示： 您必须使用实验提供的凭据。请勿使用您的 Google Cloud 账号凭据。注意：在本次实验中使用您自己的 Google Cloud 账号可能会产生额外费用。
继续在后续页面中点击以完成相应操作：

接受条款及条件。

由于该账号为临时账号，请勿添加账号恢复选项或双重验证。

请勿注册免费试用。

片刻之后，系统会在此标签页中打开 Google Cloud 控制台。
注意： 如需查看列有 Google Cloud 产品和服务的菜单，请点击左上角的 导航菜单 。
打开 BigQuery 控制台

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

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

点击完成。

BigQuery 控制台即会打开。

任务 1. 创建新数据集来存储表

在 BigQuery 中，点击您的项目 ID 旁边的三点状图标，然后选择 创建数据集 ：

在传统的关系型数据库 SQL 中，看到重复的姓名，您会立即想到将上表拆分为两个单独的表：“水果”和“用户”。该过程称为标准化（即一个表拆分为多个表）。对于 mySQL 等事务型数据库，这是一种常用方法。

对于数据仓储，数据分析师通常会采取相反的方法（即反规范化），将多个单独的表合并为一个大型报表。

现在，您将学习一种不同的方法，将不同粒度级别的数据全部存储在一个使用重复字段的表中：

Fruit (array)

Person
#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array
您应该会收到如下所示的错误消息：

Error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1]

数组中元素的数据类型必须相同（全都是字符串、全都是数字）。

以下是要查询的最终表：
#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;
点击运行。

查看结果后，点击 JSON 标签页即可查看结果的嵌套结构。

将半结构化 JSON 加载到 BigQuery 中

如果您有需要注入到 BigQuery 中的 JSON 文件，该怎么办？

在数据集内创建一个新表 fruit_details 。

点击 fruit_store 数据集。

您随即会看到 创建表 选项。
注意： 您可能需要将浏览器窗口拉宽才能看到“创建表”选项。
在表中添加以下详细信息：
来源：在 基于以下数据源创建表 下拉菜单中，选择 Google Cloud Storage 。 从 Cloud Storage 存储桶中选择文件 ： data-insights-course/labs/optimizing-for-performance/shopping_cart.json 文件格式 ：JSONL（以换行符分隔的 JSON）
将新表命名为 fruit_details 。

勾选 架构（自动检测） 对应的复选框。

点击 创建表 。

在架构中，请注意 fruit_array 已标记为 REPEATED，这表示它是数组。

BigQuery 原生支持数组

数组的值必须采用同一种数据类型

在 BigQuery 中，数组称为 REPEATED 字段

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

任务 3. 使用 ARRAY_AGG() 创建您自己的数组

您的表中还没有数组吗？您可以自行创建！
复制并粘贴 以下查询，探索此公共数据集： SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date
点击运行，然后查看结果。

现在，使用 ARRAY_AGG() 函数，将字符串值聚合到数组中。
复制并粘贴 以下查询，探索此公共数据集： SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date
点击运行，然后查看结果
date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date date, ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed, ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date
点击“检查我的进度”以验证是否完成了以下目标： 执行查询来查看浏览过的非重复产品的数量

您可以使用数组执行一些非常实用的操作，例如：

使用 ARRAY_LENGTH(<array>) 查找元素数量

使用 ARRAY_AGG(DISTINCT <field>) 删除重复的元素

使用 ARRAY_AGG(<field> ORDER BY <field>) 对元素进行排序

限制 ARRAY_AGG(<field> LIMIT 5)
任务 4. 查询包含数组的表

与我们的课程数据集 data-to-insights.ecommerce.all_sessions 相比，用于 Google Analytics 的 BigQuery 公共数据集 bigquery-public-data.google_analytics_sample 包含更多字段和行。更重要的是，该数据集已经以原生方式将字段值（例如产品、页面和交易）存储为 ARRAY。
复制并粘贴 以下查询，探索可用数据，看看能否找到包含重复值（数组）的字段：
SELECT FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398
运行查询。

在结果中 向右滚动 ，直至您看到 hits.product.v2ProductName 字段（我们稍后会介绍多个字段别名）。
visitId, hits.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398
您会收到以下错误消息： Error: Cannot access field page on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [3:8]

您必须先将数组重新拆分为多行，然后才能正常查询 REPEATED 字段（数组）。

例如， hits.page.pageTitle 数组目前以单行形式存储，如下所示：
['homepage','product page','checkout']
该数组需要拆分为多行：
['homepage', 'product page', 'checkout']
如何使用 SQL 实现这一点？

回答： 对数组字段使用 UNNEST() 函数：
SELECT DISTINCT visitId, h.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h WHERE visitId = 1501570398 LIMIT 10
我们稍后会更详细地介绍 UNNEST()，但现在您只需知道：

您需要对数组使用 UNNEST() 函数，才能将数组元素重新拆分为多行

UNNEST() 始终跟在 FROM 子句中表名称的后面（概念上可视为预联接的表）

点击“检查我的进度”以验证是否完成了以下目标： 执行查询来对数组字段使用 UNNEST()

任务 5. STRUCT 简介

您可能想过，为什么字段别名 hit.page.pageTitle 看起来像是三个字段合而为一，并以英文句点分隔。就像 ARRAY 值可让您灵活地深入了解字段粒度一样，您可以使用另一种数据类型，将相关字段归为一组，从而扩充您的架构。 该 SQL 数据类型就是 STRUCT 数据类型。

理解 STRUCT 的最简单方法是，从概念上将其视为已经预联接到主表的单独表。

STRUCT 的特点如下：

包含一个或多个字段

每个字段可以具有相同或不同的数据类型

拥有自己的别名

听起来很像表，对吧？

探索包含 STRUCT 的数据集

若要打开 bigquery-public-data 数据集，请点击 +添加 ，选择 按名称为项目加星标 ，然后输入名称 bigquery-public-data

点击 加星标 。

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

打开 bigquery-public-data 。

查找并打开 google_analytics_sample 数据集。

点击 ga_sessions(366)_ 表。

开始滚动浏览架构，并使用浏览器的查找功能回答下列问题。
totals.*, device.* FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398 LIMIT 10 注意： .* 语法会指示 BigQuery 返回该 STRUCT 的所有字段（就像我们联接单独的表 totals.* 时一样）。
通过将大型报表存储为 STRUCT（预联接的“表”）和 ARRAY（粒度更深），您可以：

避免对 32 个表执行 JOIN 操作，获得显著的性能优势

在需要时从 ARRAY 获取粒度数据，但如果您不需要，也不会受到处罚（BigQuery 会将各列单独存储在磁盘上）

将所有业务情境汇总在一个表中，而不必担心 JOIN 键以及哪些表包含您需要的数据

任务 6. 练习使用 STRUCT 和数组

下一个数据集是跑步者绕着跑道跑一圈的时间。每一圈称为一个“分段”。

利用此查询，试用 STRUCT 语法并注意结构体容器内的不同字段类型：
#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner
您从字段别名中发现了什么？由于字段嵌套在结构体内（name 和 split 是 runner 的子集），因此最终需要使用点表示法。

如果跑步者在一场比赛中有多个分段时间（例如每圈时间），该怎么办？

当然是使用数组！

运行下面的查询进行确认：
#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner 来源：在 基于以下数据源创建表 下拉菜单下，选择 Google Cloud Storage 。 从 Cloud Storage 存储桶中选择文件 ： data-insights-course/labs/optimizing-for-performance/race_results.json 文件格式 ：JSONL（以换行符分隔的 JSON）
在架构中，点击 以文本形式修改 滑块，然后添加以下内容：
"name": "race", "type": "STRING", "mode": "NULLABLE" "name": "participants", "type": "RECORD", "mode": "REPEATED", "fields": [ "name": "name", "type": "STRING", "mode": "NULLABLE" "name": "splits", "type": "FLOAT", "mode": "REPEATED"
将新表命名为 race_results 。

点击 创建表 。

加载作业成功后，预览新创建的表的架构：

哪个字段属于 STRUCT？您是怎么知道的？

participants 字段属于 STRUCT，因为其类型为 RECORD。

哪个字段属于 ARRAY？

participants.splits 字段是父级 participants 结构体内的浮点数数组。该字段的模式为 REPEATED，这表示它是数组。该数组的值是单个字段内的多个值，因此称为嵌套值。

点击“检查我的进度”以验证是否完成了以下目标： 创建数据集和表来注入 JSON 数据

练习查询嵌套和重复字段

我们来看看所有参赛者的 800 米比赛结果：
#standardSQL SELECT * FROM racing.race_results
系统返回了多少行？

回答： 1 行

如果您想要列出每位跑步者的姓名和比赛类型，该怎么办？

运行以下架构，看看会发生什么情况：
#standardSQL SELECT race, participants.name FROM racing.race_results
Error: Cannot access field name on a value with type ARRAY<STRUCT<name STRING, splits ARRAY<FLOAT64>>>> at [2:27]

就像使用聚合函数时忘记添加 GROUP BY 一样，以下列表中有两种不同的粒度级别。一行用于比赛类型，三行用于参赛者姓名。那么，如何将…

participants.name

在传统的关系型 SQL 中，如果您有一个比赛类型表和一个参赛者表，该如何从这两个表中获取信息？您会使用 JOIN 将两者联接起来。在此示例中，参赛者 STRUCT（在概念上与表非常相似）已经成为比赛类型表的一部分，但尚未与您的非 STRUCT 字段“race”正确关联。

您可以使用 SQL 命令将 800 米比赛与第一个表中的各位参赛者关联起来，能想到是哪两个单词组成的命令吗？

回答： CROSS JOIN

现在，尝试运行以下内容：
#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN participants # this is the STRUCT (it is like a table within a table)
Table name "participants" missing dataset while no default dataset is set in the request .

虽然参赛者 STRUCT 与表类似，但从技术上讲仍然是 racing.race_results 表中的字段。

将数据集名称添加到查询中：
#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name
然后点击运行。

哇！您已成功列出每场比赛的所有参赛者！
FROM racing.race_results AS r, r.participants
如果有多个比赛类型（800 米、100 米、200 米），CROSS JOIN 是否会将每位参赛者的姓名与每场可能的比赛关联起来，就像笛卡尔积一样？

回答：不会。这属于相互关联的交叉联接，只能拆分与单个行相关联的元素。 如需了解更多讨论内容，请参阅使用 ARRAY 和 STRUCT

STRUCT 要点回顾：

SQL STRUCT 只是一个用来容纳其他数据字段的容器，这些字段可以采用不同的数据类型。结构体一词是指数据结构。回想一下之前的示例： STRUCT(``"Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits``)`` AS runner

结构体会被赋予别名（例如上面的 runner），并且在概念上可视为主表内的表。

您必须先拆分 STRUCT（和 ARRAY），然后才能对其元素进行操作。使用 UNNEST() 将结构体本身的名称或属于数组的结构体字段括起来，以便将其拆分和展平。

任务 7. 实验问题：STRUCT()

请使用您之前创建的 racing.race_results 表回答下面的问题。

任务： 编写查询，使用 COUNT 计算总参赛人数。

首先，使用下面未编写完全的查询：
#standardSQL SELECT COUNT(participants.name) AS racer_count FROM racing.race_results 注意： 请记住，您需要对结构体名称进行交叉联接，并将其放在 FROM 后面，作为额外的数据源。
可能的解决方法：
#standardSQL SELECT COUNT(p.name) AS racer_count FROM racing.race_results AS r, UNNEST(r.participants) AS p
任务 8. 实验问题：使用 UNNEST( ) 拆分数组

您可以编写查询，用于列出姓名以 R 开头的参赛者的总比赛时长。将总时长最短的结果排在最前面。使用 UNNEST() 运算符，然后从下面未编写完全的查询着手。

将查询补充完整：
#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_times WHERE GROUP BY ORDER BY
您需要拆分结构体及其中的数组，并将其放在 FROM 子句后面，作为数据源。

在适当的情况下，请务必使用别名。

可能的解决方法：
#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_times WHERE p.name LIKE 'R%' GROUP BY p.name ORDER BY total_race_time ASC; FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;
Google Cloud 培训和认证

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

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

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