BigQuery 中的分区和聚类

1. 简介

BigQuery 是一种全托管式、PB 级、低成本企业数据仓库，用于进行数据分析。BigQuery 是无服务器的。您无需设置和管理集群。

BigQuery 数据集位于 GCP 项目中，包含一个或多个表。您可以使用 SQL 查询这些数据集。

在此 Codelab 中，您将使用 GCP Console 中的 BigQuery 网页界面来了解 BigQuery 中的分区和聚类。BigQuery 的表分区和聚类有助于根据常见的数据访问模式来构建数据。在查询特定数据范围时，分区和聚簇是充分发挥 BigQuery 性能和降低成本的关键。这样可以减少每次查询扫描的数据量，并且在查询开始时间之前确定剪枝。

注意：除了 BigQuery 网页界面之外，您还可以使用 bq 命令行工具对 BigQuery 数据集执行操作。如需详细了解 bq 命令行工具，请参阅 bq 命令行工具。

如需详细了解 BigQuery，请参阅 BigQuery 文档。

学习内容

• 如何创建和查询分区表和聚簇表
• 比较使用分区表和聚簇表时的查询性能

所需条件

为完成此实验，您需要：

• 最新版本的 Google Chrome
• Google Cloud Platform 结算账号

2. 准备工作

如需使用 BigQuery，您需要创建 GCP 项目或选择现有项目。

创建项目

如需创建新项目，请按以下步骤操作：

1. 如果您还没有 Google 账号（Gmail 或 Google 应用），请创建一个。
2. 登录 Google Cloud Platform Console ( console.cloud.google.com) 并创建一个新项目。
3. 如果您没有任何项目，请点击“创建项目”按钮：

否则，请从项目选择菜单中创建一个新项目：

4. 输入项目名称，然后选择创建。请注意，项目 ID 在所有 Google Cloud 项目中都是唯一的名称。

3. 使用公开数据集

借助 BigQuery，您可以处理公共数据集，包括 BBC News、GitHub 代码库、Stack Overflow 和美国国家海洋和大气管理局 (NOAA) 数据集。您无需将这些数据集加载到 BigQuery 中。您只需打开数据集，即可在 BigQuery 中浏览和查询这些数据集。在此 Codelab 中，您将使用 Stack Overflow 公共数据集。

浏览 Stack Overflow 数据集

Stack Overflow 数据集包含有关帖子、标记、徽章、评论、用户等的信息。如需在 BigQuery 网页界面中浏览 Stack Overflow 数据集，请按以下步骤操作：

1. 打开 Stack Overflow 数据集。GCP Console 中会打开 BigQuery 网页界面，并显示 Stackoverflow 数据集的相关信息。
2. 在导航面板中，选择 bigquery-public-data。菜单会展开，列出公共数据集。每个数据集包含一个或多个表。
3. 向下滚动，然后选择 stackoverflow。菜单会展开，列出 Stack Overflow 数据集中的表。
4. 选择 badges 以查看 badges 表的架构。记下表格中字段的名称。
5. 在“字段名称”上方，点击预览，查看“徽章”表的示例数据。

如需详细了解 BigQuery 中提供的所有公共数据集，请参阅 Google BigQuery 公共数据集。

查询 Stack Overflow 数据集

浏览数据集是了解所处理数据的好方法，但查询数据集才是 BigQuery 的真正优势所在。本部分将介绍如何运行 BigQuery 查询。您目前无需了解任何 SQL。您可以复制并粘贴以下查询。

如需运行查询，请完成以下步骤：

1. 在 GCP 控制台的右上角附近，选择编写新查询。
2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询。BigQuery 会验证查询，网页界面会在文本区域下方显示一个绿色对勾标记，表示语法有效。

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. 选择运行。该查询会返回每年发布的 Stack Overflow 帖子或问题的数量。

注意：此查询使用 posts_questions 表的完整表名称，包括项目名称、数据集名称和表名称：bigquery-public-data.stackoverflow.posts_questions。

4. 创建新表

在上一部分中，您查询了 BigQuery 向您提供的公共数据集。在本部分中，您将通过现有表格在 BigQuery 中创建新表格。您将创建一个新表，其中包含从 Stack Overflow 公共数据集 posts_questions 表中抽样获得的数据，然后查询该表。

创建新数据集

如需创建表并将数据加载到 BigQuery 中，请先按照以下步骤创建一个 BigQuery 数据集来存储数据：

1. 在 GCP 控制台导航面板中，选择在设置过程中创建的项目名称。
2. 在右侧的详细信息面板中，选择创建数据集。

3. 在创建数据集对话框中，在数据集 ID 字段中输入 stackoverflow。保留所有其他默认设置，然后点击确定。

创建包含 2018 年 StackOverflow 信息帖的新表

现在，您已创建 BigQuery 数据集，接下来可以在 BigQuery 中创建新表。如需创建包含现有表中的数据的表，您将查询 2018 年 Stack Overflow 帖子数据集并将结果写入新表，具体步骤如下：

1. 在 GCP 控制台的右上角附近，选择编写新查询。

2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询以创建新表，该查询是 DDL 语句。

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. 选择运行。该查询会在您项目的 stackoverflow 数据集中创建一个新表 questions_2018，其中包含对 BigQuery Stack Overflow 数据集 bigquery-public-data.stackoverflow.posts_questions 运行查询后得到的数据。

注意：此步骤大约需要 2 分钟才能创建新表格。请等待，然后再继续执行下一步。

查询包含 2018 年 Stack Overflow 帖子信息帖的新表

现在您已经创建了 BigQuery 表，接下来运行一个查询，以返回 Stack Overflow 帖子，其中包含问题和标题，以及一些其他统计信息，例如回答数、评论数、查看次数和收藏数。请完成以下步骤：

1. 在 GCP 控制台的右上角附近，选择编写新查询。
2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. 选择运行。该查询会返回 2018 年 1 月创建的、标记为 android 的 Stack Overflow 问题，以及相应问题和其他一些统计信息。
4. 默认情况下，BigQuery 会缓存查询结果。运行相同的查询，您会发现 BigQuery 花费的时间要少得多，因为它从缓存中返回结果。
5. 再次运行相同的查询，但这次停用 BigQuery 缓存。在实验的剩余部分，我们将停用缓存，以便在与分区表和聚簇表进行性能比较时保持公平，这些比较将在后续部分中进行。在查询编辑器中，点击更多，然后选择查询设置。
6. 在缓存偏好设置下，取消选中使用缓存的结果。
7. 在查询结果中，您应该会看到查询完成所用的时间以及处理的数据量。

5. 创建和查询分区表

在上一部分中，您使用 Stack Overflow 公共数据集，通过 posts_questions 表中的数据在 BigQuery 中创建了一个新表。我们查询了此数据集，但停用了缓存，并观察了查询性能。在本部分中，您将基于同一 Stack Overflow 公共数据集的 posts_questions 表创建一个新的分区表，并观察查询性能。

分区表是一种特殊的表，分成多个区段（称为分区），可让您更轻松地管理和查询数据。您通常可以使用数据注入时间、TIMESTAMP/DATE 列或 INTEGER 列将大型表拆分为许多较小的分区。我们将创建一个 DATE 分区表。

点击此处详细了解分区表。

创建包含 2018 年 StackOverflow 帖子的新分区表

如需使用现有表或查询中的数据创建分区表，您需要查询 2018 年 Stackoverflow 帖子数据集并将结果写入新表中，请完成以下步骤：

1. 在 GCP 控制台的右上角附近，选择编写新查询。

2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询以创建新表，该查询是 DDL 语句。

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. 选择运行。该查询会在您项目的 stackoverflow 数据集中创建一个新表 questions_2018_partitioned，其中包含对 BigQuery Stack Overflow 数据集 bigquery-public-data.stackoverflow.posts_questions 运行查询后得到的数据

注意：此步骤大约需要 2 分钟才能创建新表格。请等待，然后再继续执行下一步。

查询包含 2018 年 Stack Overflow 帖子的分区表

现在，您已经创建了一个 BigQuery 分区表，接下来让我们运行相同的查询，这次是对分区表运行，以返回包含问题和标题的 Stack Overflow 帖子，以及一些其他统计信息，例如回答数、评论数、浏览次数和收藏数。请完成以下步骤：

1. 在 GCP 控制台的右上角附近，选择编写新查询。
2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. 选择 Run，并停用 BigQuery 缓存（如需了解如何停用 BigQuery 缓存，请参阅上一部分）。该查询会返回 2018 年 1 月创建的、标记为 android 的 Stack Overflow 问题，以及相应问题和其他一些统计信息。
4. 在查询结果中，您应该会看到查询完成所用的时间以及处理的数据量。

您应该会看到，使用分区表的查询的性能优于使用未分区表的查询，因为 BigQuery 会剪除分区，即仅扫描所需的分区，从而处理更少的数据并更快地运行。这样可以优化查询费用和查询性能。

6. 创建和查询聚簇表

在上一部分中，您在 BigQuery 中创建了一个分区表，其中包含 Stack Overflow 公共数据集中的 posts_questions 表的数据。我们查询了此表，并停用了缓存，然后观察了非分区表和分区表的查询性能。在本部分中，您将基于同一 Stack Overflow 公共数据集的 posts_questions 表创建一个新的聚簇表，并观察查询性能。

在 BigQuery 中对表进行聚簇时，系统会根据表架构中一个或多个列的内容自动整理表数据。您指定的列用于共置相关数据。当数据写入聚簇表时，BigQuery 会使用聚簇列中的值对数据进行排序。这些值用于将数据整理到 BigQuery 存储的多个块中。聚簇列的顺序决定了数据的排序顺序。当新数据添加到表或特定分区时，BigQuery 会在后台执行自动重新聚簇以恢复表或分区的排序属性。

如需详细了解如何使用聚簇表，请点击此处。

使用 2018 年的 Stack Overflow 帖子创建新的聚簇表

在本部分中，您将根据查询访问模式创建一个新表，该表按 creation_date 进行分区，并按 tags 列进行聚类。如需使用现有表或查询中的数据创建聚簇表，您需要查询 2018 年 Stack Overflow 帖子表并将结果写入新表中，具体步骤如下：

1. 在 GCP 控制台的右上角附近，选择编写新查询。

2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询以创建新表，该查询是 DDL 语句。

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. 选择“运行”。该查询会在您项目的 stackoverflow 数据集中创建一个新表 questions_2018_clustered，其中包含对 BigQuery Stack Overflow 表 bigquery-public-data.stackoverflow.posts_questions 运行查询所得到的数据。新表按 creation_date 进行分区，并按 tags 列进行聚簇。

注意：此步骤大约需要 2 分钟才能创建新表格。请等待，然后再继续执行下一步。

查询包含 2018 年 Stack Overflow 帖子的聚簇表

现在，您已经创建了一个 BigQuery 聚簇表，接下来让我们再次运行相同的查询，这次是在分区表和聚簇表上运行，以返回包含问题和标题的 Stack Overflow 帖子，以及一些其他统计信息，例如回答数、评论数、浏览次数和收藏数。请完成以下步骤：

1. 在 GCP 控制台的右上角附近，选择编写新查询。
2. 在查询编辑器文本区域中，复制并粘贴以下 SQL 查询

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. 选择 Run，并停用 BigQuery 缓存（如需了解如何停用 BigQuery 缓存，请参阅上一部分）。该查询会返回 2018 年 1 月创建的、标记为 android 的 Stack Overflow 问题，以及相应问题和其他一些统计信息。
4. 在查询结果中，您应该会看到查询完成所用的时间以及处理的数据量。

对于分区聚簇表，查询扫描的数据量比分区表或非分区表少。通过分区和聚簇整理数据的方式可最大限度地减少 slot 工作器扫描的数据量，从而提高查询性能并优化费用。

7. 清理

除非您打算继续使用 stackoverflow 数据集，否则应将其删除，并删除您为此 Codelab 创建的项目。

删除 BigQuery 数据集

如需删除 BigQuery 数据集，请执行以下步骤：

1. 在 BigQuery 中，从左侧导航面板中选择 stackoverflow 数据集。
2. 在详细信息面板中，选择删除数据集。
3. 在删除数据集对话框中，输入 stackoverflow，然后选择删除以确认要删除该数据集。

删除项目

如需删除您为此 Codelab 创建的 GCP 项目，请执行以下步骤：

1. 在 GCP 导航菜单中，选择 IAM 和管理。
2. 在导航面板中，选择设置。
3. 在详情面板中，确认当前项目是您为此 Codelab 创建的项目，然后选择关停。
4. 在关停项目对话框中，输入项目的项目 ID（而非项目名称），然后选择关停以确认。

恭喜！您现在已经了解了

• 如何使用 BigQuery 网页界面从现有表创建新表
• 如何创建和查询分区表和聚簇表
• 分区和聚簇如何优化查询性能和费用

请注意，您无需设置或管理集群即可处理数据集。