在 Jupyter 笔记本中直观呈现 BigQuery 数据

BigQuery 是一个 PB 级的分析数据仓库,可用于对大量数据近乎实时地运行 SQL 查询。

数据可视化工具可帮助您理解 BigQuery 数据,还可帮助您以交互方式分析数据。您可以借助可视化工具来识别趋势,对其进行响应,并使用数据进行预测。在本教程中,您会在 Jupyter 笔记本中使用 Python 版 BigQuery 客户端库和 Pandas,以直观呈现 BigQuery natality 示例表中的数据。

目标

在本教程中,您将执行以下操作:

  • 设置运行 Jupyter 笔记本的环境
  • 使用 BigQuery Python 客户端库和 Pandas 查询并直观呈现 BigQuery 数据

费用

BigQuery 是一种付费产品,访问 BigQuery 时会产生 BigQuery 使用费。BigQuery 提供每月 1 TB 的免费查询额度。如需了解详情,请参阅 BigQuery 价格页面。

准备工作

开始学习本教程前,请先使用 Google Cloud Console 创建或选择项目。

  1. 登录您的 Google Cloud 帐号。如果您是 Google Cloud 新手,请创建一个帐号来评估我们的产品在实际场景中的表现。新客户还可获享 $300 赠金,用于运行、测试和部署工作负载。
  2. 在 Google Cloud Console 的项目选择器页面上,选择或创建一个 Google Cloud 项目。

    转到“项目选择器”

  3. 新项目会自动启用 BigQuery。如需在预先存在的项目中激活 BigQuery,请转到 启用 BigQuery API。

    启用 API

  4. 如果您不想提供信用卡或为项目启用结算功能,BigQuery 会提供沙盒。无论您的项目是否启用了结算功能,本主题中的步骤都适用。如果您想启用结算功能,请参阅了解如何启用结算功能

设置本地 Jupyter 环境

在本教程中,您将使用本地托管的 Jupyter 笔记本。请按照以下步骤安装 Jupyter、设置身份验证并安装所需的 Python 库。

  1. 在您的终端,运行以下命令来安装最新版本的 Python 版 BigQuery 客户端库,包括 Pandas 库(使用 Pandas 的函数需要此库):

    pip install --upgrade 'google-cloud-bigquery[pandas]'
    
  2. 按照 Jupyter 文档中的安装说明安装 Jupyter。

  3. 按照身份验证使用入门页面中的说明设置应用默认凭据。您需要创建一个服务帐号并设置相应的环境变量,来设置身份验证功能。

概览:Jupyter 笔记本

笔记本提供了编写和执行代码的环境。笔记本实质上是一种源工件,以 .ipynb 文件的形式进行保存。它可包含描述性文本内容、可执行代码块和关联的结果(以交互式的 HTML 形式呈现)。从结构上来说,笔记本是一系列的单元。

单元是一个输入文本块,系统会对其进行评估并生成结果。单元可分成两种类型:

  • 代码单元 - 包含要评估的代码。执行代码产生的任何输出或结果会显示在输入代码下方。

  • Markdown 单元 - 包含 Markdown 文本,该文本可转换为 HTML 以生成标头、列表和带格式的文本

下面的屏幕截图显示了一个 Markdown 单元,后跟 Python 代码单元。请注意,Python 单元的输出显示在代码正下方。

Jupyter Markdown 单元和代码单元

每个打开的笔记本都与某个正在运行的会话相关联。在 Python 中,该会话也称为内核。此会话执行笔记本中输入的所有代码,并管理状态(变量及其值、函数和类,以及您加载的任何现有 Python 模块)。

查询并直观呈现 BigQuery 数据

在本教程的这一部分中,您将创建一个 Datalab 笔记本以用于查询和直观呈现 BigQuery 中的数据。您将使用 natality 示例表中的数据来创建可视化效果。本教程中的所有查询均使用标准 SQL 语法。

如需使用 Jupyter 笔记本查询并直观呈现 BigQuery 数据,请执行以下操作:

  1. 如果您尚未启动 Jupyter,请在终端运行以下命令:

    jupyter notebook
    
  2. Jupyter 随即运行并在浏览器窗口中打开。在 Jupyter 窗口中,点击新建 (New) 按钮,然后选择 Python 3 以创建 Python 笔记本。

    Jupyter 新增的 Python 3 笔记本。

  3. 点击页面顶部的 Untitled

  4. 重命名笔记本 (Rename notebook) 对话框中,输入新名称(如 BigQuery tutorial),然后点击 重命名 (Rename)。

  5. Python 版 BigQuery 客户端库提供了一个魔法命令,可让您使用最少的代码运行查询。如需从客户端库加载这个魔法命令,请将以下代码粘贴到笔记本的第一个单元中。

    %load_ext google.cloud.bigquery
  6. 点击运行 (Run) 按钮,或使用 SHIFT + ENTER 运行该命令。

  7. Python 版 BigQuery 客户端库提供了一个单元魔法命令 %%bigquery,可用于运行 SQL 查询并以 Pandas DataFrame 形式返回结果。在下一个单元中输入以下内容,以按年份返回出生人口总数:

    %%bigquery
    SELECT
        source_year AS year,
        COUNT(is_male) AS birth_count
    FROM `bigquery-public-data.samples.natality`
    GROUP BY year
    ORDER BY year DESC
    LIMIT 15
  8. 点击运行 (Run)。

  9. 查询结果会显示在代码单元下方。

    出生人数(按年份)。

  10. 在下一个单元块中,输入以下命令以运行相同查询,但这次会将结果保存到新的 total_births 变量中,而该变量作为 %%bigquery 的参数提供。这些结果随后可用于进一步的分析和直观呈现。

    %%bigquery total_births
    SELECT
        source_year AS year,
        COUNT(is_male) AS birth_count
    FROM `bigquery-public-data.samples.natality`
    GROUP BY year
    ORDER BY year DESC
    LIMIT 15
  11. 点击运行

  12. 现在,您已将 Pandas DataFrame 保存到变量 total_births 中,可随时开始绘制结果图表了。为了做好准备以绘制查询结果图表,请将以下内置魔法命令粘贴到下一个单元中以激活 matplotlib。Mattplotlib 是 Pandas 用于绘图的库。

    %matplotlib inline
  13. 点击运行

  14. 在下一个单元中输入以下代码,以便使用 Pandas DataFrame.plot() 方法以条形图形式直观呈现查询结果。 如需详细了解如何使用 Pandas 直观呈现数据,请参阅 Pandas 文档

    total_births.plot(kind="bar", x="year", y="birth_count")
  15. 点击运行 (Run)。

  16. 该图表显示在代码块下方。

    出生人数(按年份)条形图。

  17. 接下来,将以下查询粘贴到下一个单元中,以按工作日检索出生人数。

    %%bigquery births_by_weekday
    SELECT
        wday,
        SUM(CASE WHEN is_male THEN 1 ELSE 0 END) AS male_births,
        SUM(CASE WHEN is_male THEN 0 ELSE 1 END) AS female_births
    FROM `bigquery-public-data.samples.natality`
    WHERE wday IS NOT NULL
    GROUP BY wday
    ORDER BY wday ASC

    由于 wday(工作日)字段允许 null 值,因此查询会排除 wday 为 null 的记录。

  18. 点击运行 (Run)。

  19. 在下一个单元中,输入以下代码以使用折线图直观呈现查询结果。

    births_by_weekday.plot(x="wday")
  20. 点击运行 (Run)。

  21. 该图表会显示在代码块下方。请注意,星期日 (1) 和星期六 (7) 的出生人数大幅减少。

    出生人数(按工作日)折线图

  22. 依次点击 文件 (File) > 保存和检查点 (Save and Checkpoint),或点击工具栏中的保存图标。创建检查点可让您将笔记本回滚到以前的状态。

Pandas DataFrame

魔法命令可让您使用最少的语法与 BigQuery 进行交互。在后台,%%bigquery 使用 Python 版 BigQuery 客户端库运行给定查询,将结果转换为 Pandas Dataframe,视需要将结果保存到变量中,并最终显示结果。如果直接使用 Python 版 BigQuery 客户端库(而不是通过魔法命令),您可以更好地控制查询并使用更复杂的配置。借助该库与 Pandas 的集成,您可以结合声明性 SQL 与命令式代码 (Python) 的强大功能,以执行有用的数据分析、可视化和转换任务。

使用 Pandas DataFrame 查询并直观呈现 BigQuery 数据

在本教程的这一部分,您将使用 Pandas DataFrame 查询并直观呈现 BigQuery 中的数据。您使用 Python 版 BigQuery 客户端库来查询 BigQuery 数据,并使用 Pandas 库通过 DataFrame 分析数据。

  1. 在下一个单元中输入以下 Python 代码,以导入 Python 版 BigQuery 客户端库并初始化客户端。BigQuery 客户端用于向 BigQuery API 发送和接收消息。

    from google.cloud import bigquery
    
    client = bigquery.Client()
  2. 点击运行 (Run)。

  3. 使用 Client.query() 方法运行查询。在下一个单元中,输入以下代码运行查询,以按多胞胎(2 表示双胞胎,3 表示三胞胎,以此类推)检索年度多胞胎出生人数。

    sql = """
    SELECT
        plurality,
        COUNT(1) AS count,
        year
    FROM
        `bigquery-public-data.samples.natality`
    WHERE
        NOT IS_NAN(plurality) AND plurality > 1
    GROUP BY
        plurality, year
    ORDER BY
        count DESC
    """
    df = client.query(sql).to_dataframe()
    df.head()
  4. 点击运行 (Run)。

  5. 如需在 DataFrame 中绘制查询结果图表,请将以下代码插入到下一个单元中以对数据进行透视分析,并创建一个堆叠条形图来显示多胞胎出生人数随时间变化的情况。

    pivot_table = df.pivot(index="year", columns="plurality", values="count")
    pivot_table.plot(kind="bar", stacked=True, figsize=(15, 7))
  6. 点击运行 (Run)。

  7. 该图表显示在代码块下方。

    多胞胎出生人数(按年份)堆叠条形图

  8. 在下一个单元中,输入以下查询以按妊娠周数检索出生人数。

    sql = """
    SELECT
        gestation_weeks,
        COUNT(1) AS count
    FROM
        `bigquery-public-data.samples.natality`
    WHERE
        NOT IS_NAN(gestation_weeks) AND gestation_weeks <> 99
    GROUP BY
        gestation_weeks
    ORDER BY
        gestation_weeks
    """
    df = client.query(sql).to_dataframe()
  9. 点击运行 (Run)。

  10. 要在 DataFrame 中绘制查询结果图表,请将以下代码粘贴到下一个单元中。

    ax = df.plot(kind="bar", x="gestation_weeks", y="count", figsize=(15, 7))
    ax.set_title("Count of Births by Gestation Weeks")
    ax.set_xlabel("Gestation Weeks")
    ax.set_ylabel("Count")
  11. 点击运行 (Run)。

  12. 该条形图会显示在代码块下方。

    平均体重(按妊娠周数)。

后续步骤

  • 详细了解如何为 BigQuery 编写查询:BigQuery 文档中的 查询数据 部分介绍了如何运行查询以及如何创建用户定义的函数 (UDF) 等方面的内容。

  • 探索 BigQuery 语法:在 BigQuery 中,SQL 查询的首选方言是标准 SQL,详情请参阅 SQL 参考文档。如需了解 BigQuery 的旧版 SQL 语法,请参阅查询参考(旧版 SQL)