将 BigQuery 与 Python 搭配使用

1. 概览

BigQuery 是 Google 推出的全托管式低成本分析数据仓库，可支持 PB 级数据规模。BigQuery 无需运维：没有需要管理的基础架构，也无需指派数据库管理员，因此您可以专注于分析数据，使用熟悉的 SQL 发掘有意义的数据洞见，并利用我们的随用随付模式。

在此 Codelab 中，您将使用 Python 版 Google Cloud 客户端库通过 Python 查询 BigQuery 公共数据集。

学习内容

  • 如何使用 Cloud Shell
  • 如何启用 BigQuery API
  • 如何对 API 请求进行身份验证
  • 如何安装 Python 客户端库
  • 如何查询莎士比亚的作品
  • 如何查询 GitHub 数据集
  • 如何调整缓存和显示统计信息

所需条件

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

调查问卷

您将如何使用本教程？

仅阅读教程内容 阅读并完成练习

您如何评价使用 Python 的体验？

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

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

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

2. 设置和要求

自定进度的环境设置

  1. 登录 Google Cloud 控制台，然后创建一个新项目或重复使用现有项目。如果您还没有 Gmail 或 Google Workspace 账号，则必须创建一个。

b35bf95b8bf3d5d8.png

a99b7ace416376c4.png

bd84a6d3004737c5.png

  • 项目名称是此项目参与者的显示名称。它是 Google API 尚未使用的字符串，您可以随时对其进行更新。
  • 项目 ID 在所有 Google Cloud 项目中必须是唯一的，并且不可变（一经设置便无法更改）。Cloud Console 会自动生成一个唯一字符串；通常情况下，您无需关注该字符串。在大多数 Codelab 中，您都需要引用项目 ID（它通常标识为 PROJECT_ID），因此如果您不喜欢某个 ID，请再生成一个随机 ID，还可以尝试自己创建一个，并确认是否可用。然后，项目创建后，ID 会处于“冻结”状态。
  • 第三个值是一些 API 使用的项目编号。如需详细了解所有这三个值，请参阅文档。
  1. 接下来，您需要在 Cloud Console 中启用结算功能，才能使用 Cloud 资源/API。运行此 Codelab 应该不会产生太多的费用（如果有费用的话）。要关闭资源以避免产生超出本教程范围的费用，请按照此 Codelab 末尾提供的任何“清理”说明操作。Google Cloud 的新用户符合参与 $300 USD 免费试用计划的条件。

启动 Cloud Shell

虽然可以通过笔记本电脑对 Google Cloud 进行远程操作，但在此 Codelab 中，您将使用 Google Cloud Shell，这是一个在云端运行的命令行环境。

激活 Cloud Shell

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

55efc1aaa7a4d3ad.png

如果您以前从未启动过 Cloud Shell，系统会显示一个中间屏幕（非首屏）来介绍 Cloud Shell。如果是这种情况，请点击继续（此后您将不会再看到此通知）。一次性屏幕如下所示：

9c92662c6a846a5c

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

9f0e51b578fecce5

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

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

  1. 在 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`
  1. 在 Cloud Shell 中运行以下命令，以确认 gcloud 命令了解您的项目：
gcloud config list project

命令输出

[core]
project = <PROJECT_ID>

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

gcloud config set project <PROJECT_ID>

命令输出

Updated property [core/project].

3. 启用 API

默认情况下，BigQuery API 应在所有 Google Cloud 项目中启用。您可以在 Cloud Shell 中使用以下命令检查是否属实：BigQuery 应该会列出：

gcloud services list

您应该会看到 BigQuery 列出：

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

如果 BigQuery API 未启用，您可以在 Cloud Shell 中使用以下命令启用它：

gcloud services enable bigquery.googleapis.com

4. 对 API 请求进行身份验证

要向 BigQuery API 发出请求，您需要使用服务账号。服务账号属于您的项目，供 Google Cloud Python 客户端库用于发出 BigQuery API 请求。与任何其他用户账号一样，服务账号由电子邮件地址表示。在本部分中，您将使用 Cloud SDK 创建服务账号，然后创建以该服务账号的身份进行身份验证所需的凭据。

首先，设置一个 PROJECT_ID 环境变量：

export PROJECT_ID=$(gcloud config get-value core/project)

接下来，使用以下命令创建一个新的服务账号来访问 BigQuery API：

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

接下来，创建 Python 代码将用于以新服务账号身份登录的凭据。使用以下命令创建这些凭据并将其保存为 JSON 文件 ~/key.json：

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

最后，设置 BigQuery Python 客户端库使用的 GOOGLE_APPLICATION_CREDENTIALS 环境变量来查找凭据，该变量将在下一步中介绍。应使用以下命令将环境变量设置为您创建的凭据 JSON 文件的完整路径：

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

您可以详细了解如何对 BigQuery API 进行身份验证。

5. 设置访问权限控制

BigQuery 使用 Identity and Access Management (IAM) 来管理对资源的访问权限。BigQuery 具有许多预定义角色（user、dataOwner、dataViewer 等），您可以将这些角色分配给您在上一步中创建的服务账号。如需详细了解访问权限控制，请参阅 BigQuery 文档。

在查询公共数据集之前，您需要确保服务账号至少具有 roles/bigquery.user 角色。在 Cloud Shell 中运行以下命令，将用户角色分配给服务账号：

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

您可以运行以下命令来验证服务账号是否具有用户角色：

gcloud projects get-iam-policy $PROJECT_ID

您应该会看到以下内容：

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. 安装客户端库

安装 BigQuery Python 客户端库：

pip3 install --user --upgrade google-cloud-bigquery

现在，您就可以使用 BigQuery API 编写代码了！

7. 查询莎士比亚的作品

公共数据集是存储在 BigQuery 中且可供公众使用的任何数据集。还有许多其他公共数据集可供您查询。虽然有些数据集是由 Google 托管，但大多数数据集是由第三方托管。如需了解详情，请参阅公共数据集页面。

除公共数据集外，BigQuery 还提供了有限数量的示例表供您查询。这些表包含在 bigquery-public-data:samples 数据集中。samples 数据集中的 shakespeare 表包含莎士比亚作品的字词索引。它给出了每个字词在每个语料库中出现的次数。

在此步骤中，您将查询 shakespeare 表。

首先，在 Cloud Shell 中创建一个简单的 Python 应用，您将用来运行 Translation API 示例。

mkdir bigquery-demo
cd bigquery-demo
touch app.py

从 Cloud Shell 的右上角打开代码编辑器：

b648141af44811a3.png

找到 bigquery-demo 文件夹内的 app.py 文件，并将代码替换为以下代码。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

花一两分钟时间研究一下代码，看看如何查询该表。

返回 Cloud Shell，运行应用：

python3 app.py

您应该会看到一个字词列表及其出现次数：

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

8. 查询 GitHub 数据集

为了更加熟悉 BigQuery，您现在需要针对 GitHub 公开数据集发出查询。您可以在 GitHub 上找到最常见的提交消息。您还将使用 BigQuery 的 Web 控制台来预览和运行临时查询。

如需查看数据，请在 BigQuery 网页界面中打开 GitHub 数据集：

打开“github_repos”表格

点击“预览”按钮查看数据：

d3f0dc7400fbe678.png

找到 bigquery_demo 文件夹内的 app.py 文件，并将代码替换为以下代码。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

花一两分钟时间研究一下代码，看看如何查询表中最常见的提交消息。

返回 Cloud Shell，运行应用：

python3 app.py

您应该会看到提交消息及其发生实例的列表：

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

9. 缓存和统计信息

BigQuery 会缓存查询结果。因此，后续查询所需的时间会更短。您可以使用查询选项停用缓存。BigQuery 还会跟踪有关查询的统计信息，例如创建时间、结束时间、处理的总字节数。

在此步骤中，您将停用缓存，并显示有关查询的统计信息。

找到 bigquery_demo 文件夹内的 app.py 文件，并将代码替换为以下代码。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

关于代码的几点注意事项。首先，通过引入 QueryJobConfig 并将 use_query_cache 设置为 false 来停用缓存。其次，您从作业对象访问了有关查询的统计信息。

返回 Cloud Shell，运行应用：

python3 app.py

与之前一样，您应该会看到提交消息及其发生实例的列表。此外，您还应该在最后看到有关该查询的一些统计信息：

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. 将数据加载到 BigQuery 中

如果您想查询自己的数据，则需要将数据加载到 BigQuery 中。BigQuery 支持从多种来源加载数据，包括 Cloud Storage、其他 Google 服务和其他可读来源。您甚至可以使用流式插入来流式传输数据。如需了解详情，请参阅将数据加载到 BigQuery 中页面。

在此步骤中，您需要将存储在 Cloud Storage 中的 JSON 文件加载到 BigQuery 表中。JSON 文件位于 gs://cloud-samples-data/bigquery/us-states/us-states.json

如果您想了解 JSON 文件的内容，可以使用 gsutil 命令行工具在 Cloud Shell 中下载该文件：

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

您可以看到，该文件包含美国各州的列表，每个州都是一个 JSON 文档，并单独占一行：

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

如需将此 JSON 文件加载到 BigQuery 中，请找到 bigquery_demo 文件夹内的 app.py 文件，并将代码替换为以下代码。

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

花两分钟时间研究一下代码如何加载 JSON 文件以及如何在数据集下创建具有架构的表。

返回 Cloud Shell，运行应用：

python3 app.py

在 BigQuery 中创建一个数据集和一个表。

如需验证数据集是否已创建，请转到 BigQuery 控制台。您应该会看到一个新的数据集和表。切换到表格的“预览”标签页查看您的数据：

8c7d2621820a5ac4

11. 恭喜！

您已了解如何在 Python 中使用 BigQuery！

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生费用，请执行以下操作：

  • 在 Cloud Console 中，转到管理资源页面。
  • 在项目列表中，选择您的项目，然后点击删除。
  • 在对话框中输入项目 ID，然后点击关停以删除项目。

了解详情

许可

此作品已获得 Creative Commons Attribution 2.0 通用许可授权。