BigQuery 中的分区和聚类

1. 简介

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

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

在此 Codelab 中，您将使用 GCP Console 中的 BigQuery 网页界面了解 BigQuery 中的分区和聚类。BigQuery 的表分区和聚簇可帮助您设计数据结构，以匹配常见的数据访问模式。在对特定数据范围进行查询时，分区和聚类是充分提高 BigQuery 性能和费用的关键。这会减少每个查询扫描的数据，并且要在查询开始前确定剪枝。

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

学习内容

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

所需条件

为完成此实验，您需要：

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

2. 准备工作

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

创建项目

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

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

870a3cbd6541ee86.png

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

f6dff3437a20cf2.png

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

1884405a64ce5765

3. 使用公共数据集

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

浏览 Stack Overflow 数据集

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

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

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

查询 Stackoverflow 数据集

浏览数据集是了解您正在处理的数据的好方法，但查询数据集才是 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
  1. 选择运行。该查询会返回每年发布的 Stack Overflow 帖子或问题的数量。

4. 创建新表

在上一部分中，您查询了 BigQuery 为您提供的公共数据集。在本部分中，您将在 BigQuery 中基于现有表创建一个新表。您将使用从 Stack Overflow 公共数据集 posts_questions 表中采样的数据创建一个新表，然后查询该表。

创建新数据集

如需创建表数据并将其加载到 BigQuery 中，请先完成以下步骤，以创建一个用于保存数据的 BigQuery 数据集：

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

acc6378c49622323.png

  1. 在创建数据集对话框中，对于数据集 ID，输入 stackoverflow。保留所有其他默认设置，然后点击 OK。

7a2dfd8bcb8f259a

使用 2018 年 StackOverflow 帖子创建新表

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

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

9ca55f544e8da8bd

  1. 在查询编辑器文本区域中，复制并粘贴以下 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';
  1. 选择运行。该查询会在项目的 stackoverflow 数据集中创建一个新表 questions_2018，其中包含对 BigQuery Stack Overflow 数据集 bigquery-public-data.stackoverflow.posts_questions 运行查询后得到的数据。

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

f197b022b4276338.png

5. 创建和查询分区表

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

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

如需详细了解分区表，请点击此处。

在 2018 年发布的 StackOverflow 帖子上创建新的分区表

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

b9d0ca4df0881f58.png

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

9ca55f544e8da8bd

  1. 在查询编辑器文本区域中，复制并粘贴以下 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';
  1. 选择运行。该查询会在项目的 stackoverflow 数据集中创建一个新表 questions_2018_partitioned，其中包含对 BigQuery Stack Overflow 数据集 bigquery-public-data.stackoverflow.posts_questions 运行查询后得到的数据

使用 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';
  1. 选择运行，同时停用 BigQuery 缓存（请参阅上一部分了解如何停用 BigQuery 缓存）。该查询会返回在 2018 年 1 月创建的 Stack Overflow 问题（标记为 android）以及问题和其他一些统计信息。
  2. 在查询结果中，您应该会看到完成查询所花费的时间，以及获取结果所处理的数据量。

ef01144374069823.png

您应该会看到，使用分区表的查询的性能优于使用非分区表的查询，因为 BigQuery 会删减分区，即仅扫描所需分区，处理的数据较少，运行速度更快。这样可以优化查询费用和查询性能。

6. 创建和查询聚簇表

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

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

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

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

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

e7d9acc0dc3b9d79.png

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

9ca55f544e8da8bd

  1. 在查询编辑器文本区域中，复制并粘贴以下 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';
  1. 选择“运行”。该查询会在项目的 stackoverflow 数据集中创建一个新表 questions_2018_clustered，其中包含在 BigQuery Stack Overflow 表bigquery-public-data.stackoverflow.posts_questions上运行查询所得到的数据。新表按 create_date 进行分区，并按 tag 列进行聚簇。

使用 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';
  1. 选择运行，同时停用 BigQuery 缓存（请参阅上一部分了解如何停用 BigQuery 缓存）。该查询会返回在 2018 年 1 月创建的 Stack Overflow 问题（标记为 android）以及问题和其他一些统计信息。
  2. 在查询结果中，您应该会看到完成查询所花费的时间，以及获取结果所处理的数据量。

85e3c30d6fb3d547

使用分区表和聚簇表时，查询扫描的数据少于分区表或非分区表。通过分区和聚类来组织数据的方式最大限度地减少了槽工作器扫描的数据量，从而提高查询性能并优化费用。

7. 正在清理

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

删除 BigQuery 数据集

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

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

删除项目

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

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

恭喜！现在，您已经了解了

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

请注意，您不必设置或管理集群即可使用数据集。