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

1. 概览

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

在此 Codelab 中，您将使用 BigQuery 探索维基百科数据集。

学习内容

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

所需条件

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

调查问卷

您打算如何使用本教程？

<ph type="x-smartling-placeholder"></ph> 只读 阅读并完成练习

如何评价您的 Google Cloud 使用体验？

<ph type="x-smartling-placeholder"></ph> 新手 中级 熟练

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 免费试用来添加结算账号。

在下一部分中，您将加载维基百科数据集。

3. 创建数据集

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

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

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

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

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

激活 Cloud Shell

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

如果您以前从未启动过 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 中

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

您可以通过 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. 点击 Run：

几秒钟后，结果将列在底部，并显示处理的数据量：

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

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

7. 更多高级查询

查找维基百科页面浏览量

维基媒体数据集包含所有维基媒体项目（包括维基百科、维基百科、维基图书和维基引文）的网页浏览量。添加 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 查询了真实的 Wikipedia 网页浏览数据集。您有权查询 PB 级数据集！

了解详情

• 查看 BigQuery 子版块，了解目前其他人如何使用 BigQuery。
• 查找 BigQuery 中提供的公共数据集。
• 了解如何将数据加载到 BigQuery 中。