在 BigQuery 中查询维基百科数据集

1. 概览

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

在此 Codelab 中，您将使用 BigQuery 探索 Wikipedia 数据集。

学习内容

• 如何使用 BigQuery
• 如何将真实世界的数据集加载到 BigQuery 中
• 如何编写查询来深入了解大型数据集

所需条件

• Google Cloud 项目
• 一个浏览器，例如 Google Chrome 或 Firefox

调查问卷

您打算如何使用本教程？

仅阅读 阅读并完成练习

您如何评价自己在 Google Cloud 方面的经验水平？

新手水平 中等水平 熟练水平

2. 设置和要求

启用 BigQuery

如果您还没有 Google 账号，则必须先创建一个。

1. 登录 Google Cloud 控制台，然后前往 BigQuery。您还可以在浏览器中输入以下网址，直接打开 BigQuery 网页界面。

https://console.cloud.google.com/bigquery

2. 接受服务条款。
3. 您必须先创建一个项目，然后才能使用 BigQuery。按照提示创建新项目。

选择项目名称，并记下项目 ID。

项目 ID 是一个在所有 Google Cloud 项目中均保持唯一的名称。它稍后将在此 Codelab 中被称为 PROJECT_ID。

此 Codelab 使用的 BigQuery 资源未超出 BigQuery 沙盒限额。不需要结算账号。如果您日后想移除沙盒限制，可以注册 Google Cloud 免费试用版，从而添加结算账号。

您将在下一部分中加载 Wikipedia 数据集。

3. 创建数据集

首先，在项目中创建新数据集。一个数据集由多个表组成。

1. 如需创建数据集，请点击资源窗格中的项目名称，然后点击创建数据集：

2. 输入 lab 作为数据集 ID：

3. 点击创建数据集以创建空数据集。

4. 使用 bq 命令行程序加载数据

激活 Cloud Shell

1. 在 Cloud Console 中，点击激活 Cloud Shell。

如果您以前从未启动过 Cloud Shell，将看到一个中间屏幕（非首屏），描述它是什么。如果是这种情况，请点击继续（您将永远不会再看到它）。一次性屏幕如下所示：

预配和连接到 Cloud Shell 只需花几分钟时间。

这个虚拟机已加载了您需要的所有开发工具。它提供了一个持久的 5GB 主目录，并且在 Google Cloud 中运行，大大增强了网络性能和身份验证。只需使用一个浏览器或 Google Chromebook 即可完成本 Codelab 中的大部分（甚至全部）工作。

在连接到 Cloud Shell 后，您应该会看到自己已通过身份验证，并且相关项目已设置为您的项目 ID：

2. 在 Cloud Shell 中运行以下命令以确认您已通过身份验证：

gcloud auth list

命令输出

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

注意：gcloud 命令行工具是 Google Cloud 中一款强大的统一命令行工具。它预先安装在 Cloud Shell 中。您会注意到，它支持 Tab 键补全功能。如需了解详情，请参阅 gcloud 命令行工具概览。

3. 在 Cloud Shell 中运行以下命令，以确认 gcloud 命令了解您的项目：

gcloud config list project

命令输出

[core]
project = <PROJECT_ID>

如果不是上述结果，您可以使用以下命令进行设置：

gcloud config set project <PROJECT_ID>

命令输出

Updated property [core/project].

将数据加载到 BigQuery 中

为方便起见，我们已将 Wikimedia 网页浏览量数据集中 2019 年 4 月 10 日的部分数据放在 Google Cloud Storage 中，网址为 gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz。数据文件是经过 GZip 压缩的 CSV 文件。您可以使用 bq 命令行实用程序直接加载此文件。在加载命令中，您还需要描述文件的架构。

您可以通过 API 控制台或命令行找到项目 ID：“gcloud config list”，然后查找“project”的值。

此外，首次运行 bq 时，系统可能会要求您选择默认项目。选择后，您可以重新运行上述命令来加载数据。

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

数据加载可能需要几分钟的时间。等待该流水线完成。

您使用了以下几个高级选项来加载网页浏览文件：

• 将 --source_format CSV 设置为指示应将文件解析为 CSV 文件。此步骤是可选的，因为 CSV 是默认格式。
• 将 --field_delimiter " " 设置为表示使用单个空格来分隔字段。
• 将 --allow_jagged_rows 设置为包含列数较少的行，并在加载 CSV 文件时忽略错误。
• 设置 --quote "" 以指示字符串不带引号。
• 将 --max_bad_records 3 设置为在解析 CSV 文件时最多忽略 3 个错误。

如需详细了解 bq 命令行，请参阅文档。

5. 预览数据集

在 BigQuery 控制台中，打开您刚刚加载的其中一个表。

1. 展开项目。
2. 展开数据集。
3. 选择表格。

您可以在架构标签页中查看表架构。4. 如需了解表格中的数据量，请前往详细信息标签页：

5. 打开预览标签页，查看表格中的部分行。

6. 编写查询

1. 点击编写新查询：

系统随即会显示查询编辑器：

2. 如需查找 2019 年 4 月 10 日下午 2 点到 3 点之间的维基媒体观看总次数，请编写以下查询：

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. 点击运行：

几秒钟后，结果会显示在底部，其中还会显示处理的数据量：

虽然该表的大小为 691.4MB，但此查询仅处理了 123.9MB 的数据。BigQuery 仅处理来自查询中所用列的字节，因此处理的数据总量可能明显小于表的大小。通过聚簇和分区，处理的数据量可以进一步减少。

您每月可以免费处理/查询最多 1 TB 的数据。如需了解详情，请参阅 BigQuery 价格页面。

7. 更高级的查询

查找 Wikipedia 网页浏览量

Wikimedia 数据集包含所有 Wikimedia 项目（包括维基百科、维基词典、维基教科书和维基语录）的网页浏览量。添加 WHERE 语句，将查询范围缩小到仅限英文维基百科网页：

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

请注意，通过查询额外的列 wiki，处理的数据量从 124MB 增加到了 204MB。

BigQuery 支持许多熟悉的 SQL 子句，例如 CONTAINS、GROUP BY, ORDER BY 以及许多聚合函数。此外，您还可以使用正则表达式查询文本字段！不妨试试：

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

如需详细了解 BigQuery 语法，请参阅 BigQuery 查询参考文档。

跨多个表格进行查询

您可以使用通配符表选择一系列表来形成联合。

1. 首先，通过将下一小时的网页浏览量加载到新表中，创建第二个要查询的表：

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. 在查询编辑器中，通过查询以“pageviews_2019”为前缀的表，查询您加载的两个表：

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

您可以使用 _TABLE_SUFFIX 伪列更有选择性地过滤表。此查询将限制为与 4 月 10 日对应的表。

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. 清理

（可选）使用 bq rm 命令删除您创建的数据集。使用 -r 标志可移除其中包含的所有表格。

bq rm -r lab

9. 恭喜！

您使用 BigQuery 和 SQL 查询了真实的维基百科网页浏览量数据集。您可以查询 PB 级数据集！

了解详情

• 不妨访问 BigQuery subreddit，了解其他人目前如何使用 BigQuery。
• 查找 BigQuery 中提供的公共数据集。
• 了解如何将数据加载到 BigQuery 中。