从 JupyterLab 查询 BigQuery 表中的数据

从 JupyterLab 查询 BigQuery 表中的数据

本页面介绍如何从 Vertex AI Workbench 实例的 JupyterLab 界面查询存储在 BigQuery 中的数据。

在笔记本 (IPYNB) 文件中查询 BigQuery 数据的方法

如需从 JupyterLab 笔记本文件中查询 BigQuery 数据，您可以使用 %%bigquery 魔法命令和 Python 版 BigQuery 客户端库。

Vertex AI Workbench 实例还包含 BigQuery 集成，可让您从 JupyterLab 界面浏览和查询数据。

本页面介绍如何使用这些方法。

准备工作

创建 Vertex AI Workbench 实例（如果您尚未创建）。

所需的角色

为了确保实例的服务账号拥有查询 BigQuery 中的数据所需的权限，请让管理员向您实例的服务账号授予项目的 Service Usage Consumer (roles/serviceusage.serviceUsageConsumer) IAM 角色。 如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

您的管理员也可以通过自定义角色或其他预定义角色向实例的服务账号授予所需的权限。

打开 JupyterLab

1. 在 Google Cloud 控制台中，进入实例页面。

   转到实例

2. 在 Vertex AI Workbench 实例名称旁边，点击打开 JupyterLab。

   您的 Vertex AI Workbench 实例会打开 JupyterLab。

浏览 BigQuery 资源

BigQuery 集成提供了一个窗格，供您浏览您有权访问的 BigQuery 资源。

1. 在 JupyterLab 导航菜单中，点击  笔记本中的 BigQuery。

   BigQuery 窗格列出了可用的项目和数据集，您可以在其中执行以下任务：

   • 如需查看数据集的说明，请双击数据集名称。
   • 要显示数据集的表、视图和模型，请展开数据集。
   • 如需在 JupyterLab 中以标签页的形式打开摘要说明，请双击表、视图或模型。

   注意：在表的摘要说明上，点击预览标签页以预览表的数据。下图显示了在 bigquery-public-data 项目的 google_trends 数据集中找到的 international_top_terms 表的预览：

   

使用 %%bigquery 魔法命令查询数据

在本部分中，您可以直接在笔记本单元中写入 SQL，并将 BigQuery 中的数据读取到 Python 笔记本。

使用一个或两个百分比字符（% 或 %%）的魔法命令可让您使用最少的语法在笔记本中与 BigQuery 进行交互。Python 版 BigQuery 客户端库会自动安装在 Vertex AI Workbench 实例中。在后台，%%bigquery 魔法命令使用 Python 版 BigQuery 客户端库运行给定查询，将结果转换为 Pandas DataFrame，以及视需要将结果保存到变量中，然后显示结果。

注意：从 google-cloud-bigquery Python 软件包 1.26.0 版开始，默认使用 BigQuery Storage API 从 %%bigquery 魔法命令下载结果。

1. 如需打开笔记本文件，请选择文件 > 新建 > 笔记本。

2. 在选择内核对话框中，选择 Python 3，然后点击选择。

   系统会打开您的新 IPYNB 文件。

3. 如需获取 international_top_terms 数据集中国家/地区的区域数，请输入以下语句：

   %%bigquery
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code,
     country_name
   ORDER BY
     num_regions DESC;

4. 点击  运行单元。

   输出类似于以下内容：

   Query complete after 0.07s: 100%|██████████| 4/4 [00:00<00:00, 1440.60query/s]
   Downloading: 100%|██████████| 41/41 [00:02<00:00, 20.21rows/s]
   country_code      country_name    num_regions
   0   TR  Turkey         81
   1   TH  Thailand       77
   2   VN  Vietnam        63
   3   JP  Japan          47
   4   RO  Romania        42
   5   NG  Nigeria        37
   6   IN  India          36
   7   ID  Indonesia      34
   8   CO  Colombia       33
   9   MX  Mexico         32
   10  BR  Brazil         27
   11  EG  Egypt          27
   12  UA  Ukraine        27
   13  CH  Switzerland    26
   14  AR  Argentina      24
   15  FR  France         22
   16  SE  Sweden         21
   17  HU  Hungary        20
   18  IT  Italy          20
   19  PT  Portugal       20
   20  NO  Norway         19
   21  FI  Finland        18
   22  NZ  New Zealand    17
   23  PH  Philippines    17
   ...
   

   注意：您的结果可能与上面的结果不同，因为正在查询的 google_trends 数据集会不断随着新数据而刷新。

5. 在下一个单元（在上一个单元的输出下方）中，输入以下命令以运行相同查询，但这次会将结果保存到名为 regions_by_country 的新 Pandas DataFrame 中。您通过在 %%bigquery 魔法命令中使用参数来提供该名称。

   %%bigquery regions_by_country
   SELECT
     country_code,
     country_name,
     COUNT(DISTINCT region_code) AS num_regions
   FROM
     `bigquery-public-data.google_trends.international_top_terms`
   WHERE
     refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
   GROUP BY
     country_code, country_name
   ORDER BY
     num_regions DESC;

   注意：如需详细了解 %%bigquery 命令的可用参数，请参阅客户端库魔法命令文档。

6. 点击  运行单元。

7. 在下一个单元中，输入以下命令以查看您刚刚读取的查询结果的前几行：

   regions_by_country.head()
   

8. 点击  运行单元。

   Pandas DataFrame regions_by_country 已准备好绘制图表。

直接使用 BigQuery 客户端库查询数据

在本部分中，您将使用 Python 版 BigQuery 客户端库直接读取数据到 Python 笔记本中。

该客户端库可让您更好地控制查询，并可让您使用更复杂的查询和作业配置。借助该库与 Pandas 的集成，您可以结合声明性 SQL 与命令式代码 (Python) 的强大功能，来分析、直观呈现和转换数据。

注意：有许多 Python 数据分析、数据整理和可视化库（例如 numpy、pandas、matplotlib 等）可供您使用。其中一些库是以 DataFrame 对象为基础而构建的。

1. 在下一个单元中，输入以下 Python 代码以导入 Python 版 BigQuery 客户端库并初始化客户端：

   from google.cloud import bigquery
   
   client = bigquery.Client()
   

   BigQuery 客户端用于通过 BigQuery API 收发消息。

2. 点击  运行单元。

3. 在下一个单元中，输入以下代码以检索美国 top_terms 在某个时间段内以某个天数为时间单位而重叠的每日热门字词的百分比。此处的思路是查看每天的热门字词，并查看它们与前一天、前 2 天、前 3 天等时间单位（大概一个月范围内所有日期对）中热门字词的重叠百分比。

   sql = """
   WITH
     TopTermsByDate AS (
       SELECT DISTINCT refresh_date AS date, term
       FROM `bigquery-public-data.google_trends.top_terms`
     ),
     DistinctDates AS (
       SELECT DISTINCT date
       FROM TopTermsByDate
     )
   SELECT
     DATE_DIFF(Dates2.date, Date1Terms.date, DAY)
       AS days_apart,
     COUNT(DISTINCT (Dates2.date || Date1Terms.date))
       AS num_date_pairs,
     COUNT(Date1Terms.term) AS num_date1_terms,
     SUM(IF(Date2Terms.term IS NOT NULL, 1, 0))
       AS overlap_terms,
     SAFE_DIVIDE(
       SUM(IF(Date2Terms.term IS NOT NULL, 1, 0)),
       COUNT(Date1Terms.term)
       ) AS pct_overlap_terms
   FROM
     TopTermsByDate AS Date1Terms
   CROSS JOIN
     DistinctDates AS Dates2
   LEFT JOIN
     TopTermsByDate AS Date2Terms
     ON
       Dates2.date = Date2Terms.date
       AND Date1Terms.term = Date2Terms.term
   WHERE
     Date1Terms.date <= Dates2.date
   GROUP BY
     days_apart
   
   ORDER BY
     days_apart;
   """
   pct_overlap_terms_by_days_apart = client.query(sql).to_dataframe()
   
   pct_overlap_terms_by_days_apart.head()

   正在使用的 SQL 被封装在 Python 字符串中，然后传递给 query() 方法以运行查询。to_dataframe 方法等待查询完成，并使用 BigQuery Storage API 将结果下载到 Pandas DataFrame。

4. 点击  运行单元。

   前几行查询结果会显示在代码单元下方。

      days_apart   num_date_pairs  num_date1_terms overlap_terms   pct_overlap_terms
    0          0             32               800            800            1.000000
    1          1             31               775            203            0.261935
    2          2             30               750             73            0.097333
    3          3             29               725             31            0.042759
    4          4             28               700             23            0.032857
   

   注意：您的结果可能与上面的结果不同，因为正在查询的 google_trends 数据集会不断随着新数据而刷新。

如需详细了解如何使用 BigQuery 客户端库，请参阅使用客户端库快速入门。

在 Vertex AI Workbench 中使用 BigQuery 集成来查询数据

BigQuery 集成提供了另外两种查询数据的方法。这些方法与使用 %%bigquery 魔法命令不同。

• 单元内查询编辑器是可在笔记本文件中使用的单元类型。

• 独立查询编辑器会在 JupyterLab 中作为单独的标签页打开。

单元内

如需使用单元内查询编辑器查询 BigQuery 表中的数据，请完成以下步骤：

1. 在 JupyterLab 中，打开笔记本 (IPYNB) 文件或新建笔记本文件。

2. 如需创建单元内查询编辑器，请点击单元，然后点击单元右侧的 BigQuery 集成按钮。或者，在 Markdown 单元格中，输入 #@BigQuery。

   BigQuery 集成会将该单元转换为单元内查询编辑器。

3. 在 #@BigQuery 下的新行中，使用 BigQuery 支持的语句和 SQL 方言编写查询。如果在查询中检测到错误，查询编辑器的右上角会显示错误消息。如果查询有效，则显示预估的待处理字节数。

4. 点击提交查询后，查询结果将会显示。默认情况下，查询结果按每页 100 行分页，并且总共不超过 1000 行，但您可以在结果表的底部更改这些设置。在查询编辑器中，将查询限制为验证查询所需的数据。您将在笔记本单元中再次运行此查询，您可以根据需要调整该限制以检索完整结果集。

   注意：查询文本和结果在关闭并重新打开笔记本文件后仍然存在。

5. 您可以点击 Query and load as DataFrame（查询并加载为 DataFrame）以自动添加一个包含代码段的新单元，该代码段会导入 Python 版 BigQuery 客户端库，在笔记本单元中运行查询，并将结果存储在名为 df 的 Pandas DataFrame 中。

独立

如需使用独立查询编辑器查询 BigQuery 表中的数据，请完成以下步骤：

1. 在 JupyterLab 的 BigQuery in Notebooks（笔记本中的 BigQuery）窗格中，右键点击某个表，然后选择Query table（查询表），或者双击表以在新的标签页中打开说明，然后点击 Query table（查询表）链接。

2. 使用 BigQuery 支持的语句和 SQL 方言编写查询。如果在查询中检测到错误，查询编辑器的右上角会显示错误消息。如果查询有效，则显示预估的待处理字节数。

3. 点击提交查询后，查询结果将会显示。默认情况下，查询结果按每页 100 行分页，并且总共不超过 1000 行，但您可以在结果表的底部更改这些设置。在查询编辑器中，将查询限制为验证查询所需的数据。您将在笔记本单元中再次运行此查询，您可以根据需要调整该限制以检索完整结果集。

4. 您可以点击 Copy code for DataFrame（为 DataFrame 复制代码）以复制代码段，该代码段会导入 Python 版 BigQuery 客户端库，在笔记本单元中运行查询，并将结果存储在名为 df 的 Pandas DataFrame 中。将此代码粘贴到要运行它的笔记本单元中。

查看查询历史记录并重复使用查询

如需在 JupyterLab 的标签页中查看查询历史记录，请执行以下步骤：

1. 在 JupyterLab 导航菜单中，点击  笔记本中的 BigQuery 以打开 BigQuery 窗格。

2. 在 BigQuery 窗格中，向下滚动，然后点击查询记录。

   

   系统会在新标签页中打开您的查询列表，您可以在其中执行如下任务：

   • 如需查看查询的详细信息，例如其作业 ID、查询运行时间以及时长，请点击该查询。
   • 如需修改查询，请再次运行查询，或将其复制到笔记本中以备将来使用，然后点击在编辑器中打开查询。

注意：如果您在打开此标签页后运行查询，请点击  刷新以显示最新查询。

后续步骤

• 如需查看有关如何直观呈现 BigQuery 表中的数据的示例，请参阅在 JupyterLab 中探索并直观呈现 BigQuery 中的数据。

• 如需详细了解如何为 BigQuery 编写查询，请参阅运行交互式查询作业和批量查询作业。

• 了解如何控制对 BigQuery 数据集的访问权限。