GSP416

概览

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

在本实验中，您将在 BigQuery 内深入处理半结构化数据（注入 JSON、数组数据类型）。对您的架构进行反规范化，将其制作成包含嵌套和重复字段的单个表，这样可以提升性能，但用于处理数组数据的 SQL 语法可能会很复杂。您将练习加载和查询各种半结构化数据集、排查相关问题，以及解除这些数据集的嵌套。

您将执行的操作

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

• 加载和查询半结构化数据，包括解除这些数据的嵌套。
• 对有关半结构化数据的查询进行问题排查。

设置和要求

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

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

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

2. 将新数据集命名为 fruit_store。将其他选项保留为各自的默认值（数据位置、默认到期时间）。

3. 点击创建数据集。

任务 2. 练习在 SQL 中使用数组

在 SQL 中，每一行通常会有一个值，如下方的水果列表所示：

Row	Fruit
1	raspberry
2	blackberry
3	strawberry
4	cherry

如果您想要店内每个人的水果列表，该怎么办？该列表可能如下所示：

Row	Fruit	Person
1	raspberry	sally
2	blackberry	sally
3	strawberry	sally
4	cherry	sally
5	orange	frederick
6	apple	frederick

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

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

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

Row	Fruit (array)	Person
1	raspberry	sally
	blackberry
	strawberry
	cherry
2	orange	frederick
	apple

上表有哪些地方看起来很奇怪？

• 只有两行。
• 一行中 Fruit 有多个字段值。
• 用户与所有字段值均相关。

关键的分析洞见是什么？那就是 array 数据类型！

您可以通过一种更简单的方式解读 Fruit 数组：

Row	Fruit (array)	Person
1	[raspberry, blackberry, strawberry, cherry]	sally
2	[orange, apple]	frederick

这两个表的内容是完全相同的。此处有两个重要知识点：

• 数组就是用方括号 [ ] 括起来的元素列表
• BigQuery 会以展平形式直观显示数组，它会纵向列出数组中的值（请注意，所有这些值仍属于同一行）

亲自尝试一下吧。

1. 在 BigQuery 查询编辑器中输入以下内容：

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array

2. 点击运行。

3. 现在尝试执行以下查询：

#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]

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

4. 以下是要查询的最终表：

#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;

5. 点击运行。

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

将半结构化 JSON 加载到 BigQuery 中

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

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

1. 点击 fruit_store 数据集。

您随即会看到创建表选项。

注意：您可能需要将浏览器窗口拉宽才能看到“创建表”选项。

2. 在表中添加以下详细信息：

• 来源：在基于以下数据源创建表下拉菜单中，选择 Google Cloud Storage。
• 从 Cloud Storage 存储桶中选择文件：cloud-training/data-insights-course/labs/optimizing-for-performance/shopping_cart.json
• 文件格式：JSONL（以换行符分隔的 JSON）

3. 将新表命名为 fruit_details。

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

5. 点击创建表。

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

回顾

• BigQuery 原生支持数组
• 数组的值必须采用同一种数据类型
• 在 BigQuery 中，数组称为 REPEATED 字段

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

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

您的表中还没有数组吗？您可以自行创建！

1. 复制并粘贴以下查询，探索此公共数据集：

SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date

2. 点击运行，然后查看结果。

现在，使用 ARRAY_AGG() 函数，将字符串值聚合到数组中。

3. 复制并粘贴以下查询，探索此公共数据集：

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

4. 点击运行，然后查看结果

5. 现在，使用 ARRAY_LENGTH() 函数，计算浏览过的页数和产品数量：

SELECT fullVisitorId, 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

6. 接下来，将 DISTINCT 添加到 ARRAY_AGG()，删除重复的页面和产品，以便您查看浏览过的非重复产品的数量：

SELECT fullVisitorId, 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。

1. 复制并粘贴以下查询，探索可用数据，看看能否找到包含重复值（数组）的字段：

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

2. 运行查询。

3. 在结果中向右滚动，直至您看到 hits.product.v2ProductName 字段（我们稍后会介绍多个字段别名）。

Google Analytics 架构中的可用字段数量可能极为庞大，难以进行分析。

4. 尝试像之前一样只查询访问和页面名称字段：

SELECT 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 的数据集

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

2. 点击加星标。

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

3. 打开 bigquery-public-data。

4. 查找并打开 google_analytics_sample 数据集。

5. 点击 ga_sessions(366)_ 表。

6. 开始滚动浏览架构，并使用浏览器的查找功能回答下列问题。

正如您所想，现代电子商务网站会存储海量的网站会话数据。

在单个表中包含 32 个 STRUCT 的主要优势在于，您可以运行如下所示的查询，而无需执行任何 JOIN 操作：

SELECT visitId, 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 和数组

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

1. 利用此查询，试用 STRUCT 语法并注意结构体容器内的不同字段类型：

#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

Row	runner.name	runner.split
1	Rudisha	23.4

您从字段别名中发现了什么？由于字段嵌套在结构体内（name 和 split 是 runner 的子集），因此最终需要使用点表示法。

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

当然是使用数组！

2. 运行下面的查询进行确认：

#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

Row	runner.name	runner.splits
1	Rudisha	23.4
		26.3
		26.4
		26.1

总结如下：

• 结构体是容器，其中可以嵌套多个字段名称和数据类型。
• 数组可以是结构体内的字段类型之一（如上方的 splits 字段所示）。

练习注入 JSON 数据

1. 创建一个标题为 racing 的新数据集。

2. 依次点击 racing 数据集和“创建表”。

注意：您可能需要将浏览器窗口拉宽才能看到“创建表”选项。

• 来源：在基于以下数据源创建表下拉菜单下，选择 Google Cloud Storage。
• 从 Cloud Storage 存储桶中选择文件：cloud-training/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" } ] } ]

3. 将新表命名为 race_results。

4. 点击创建表。

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

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

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

哪个字段属于 ARRAY？

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

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

练习查询嵌套和重复字段

1. 我们来看看所有参赛者的 800 米比赛结果：

#standardSQL SELECT * FROM racing.race_results

系统返回了多少行？

回答：1 行

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

2. 运行以下架构，看看会发生什么情况：

#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 一样，以下列表中有两种不同的粒度级别。一行用于比赛类型，三行用于参赛者姓名。那么，如何将…

Row	race	participants.name
1	800M	Rudisha
2	???	Makhloufi
3	???	Murphy

更改为：

Row	race	participants.name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy

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

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

回答：CROSS JOIN

太棒了！

3. 现在，尝试运行以下内容：

#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 表中的字段。

4. 将数据集名称添加到查询中：

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name

5. 然后点击运行。

哇！您已成功列出每场比赛的所有参赛者！

Row	race	name
1	800M	Rudisha
2	800M	Makhloufi
3	800M	Murphy
4	800M	Bosse
5	800M	Rotich
6	800M	Lewandowski
7	800M	Kipketer
8	800M	Berian

6. 您可以通过以下方法简化最后一个查询：

• 为原始表添加别名
• 将文字“CROSS JOIN”替换为英文逗号（英文逗号在这里隐含进行交叉联接的意思）

这将为您提供相同的查询结果：

#standardSQL SELECT race, participants.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

Row	racer_count
1	8

回答：有 8 位参赛者参加了比赛。

点击“检查我的进度”以验证是否完成了以下目标： 执行查询来使用 COUNT 计算总参赛人数

任务 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;

Row	name	total_race_time
1	Rudisha	102.19999999999999
2	Rotich	103.6

点击“检查我的进度”以验证是否完成了以下目标： 执行查询，列出姓名以 R 开头的参赛者的总比赛时长

任务 9. 在数组值中进行过滤

您碰巧看到了 800 米比赛的最短单圈时间记录是 23.2 秒，但没有看到这一圈是谁跑的。请创建一项查询，返回该结果。

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

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_time WHERE split_time = ;

可能的解决方法：

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;

Row	name	split_time
1	Kipketer	23.2

点击“检查我的进度”以验证是否完成了以下目标： 执行查询来查看哪位跑步者的单圈时间最短

恭喜！

您已成功注入 JSON 数据集、创建 ARRAY 和 STRUCT，以及解除半结构化数据嵌套，从而获取相关分析洞见。

后续步骤/了解详情

• 如需了解详情，请参阅使用数组。
• 请参阅以下实验：
  • 在 BigQuery 中创建日期分区表
  • 使用 BigQuery ML 预测模型来预测出租车费
  • 将新数据集注入 BigQuery 中

Google Cloud 培训和认证

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

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

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

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