本頁面由 Cloud Translation API 翻譯而成。

在 JupyterLab 內查詢 BigQuery 中的資料

本頁面說明如何在 Vertex AI Workbench 執行個體的 JupyterLab 介面中，查詢儲存在 BigQuery 中的資料。

在 Notebook (IPYNB) 檔案中查詢 BigQuery 資料的方法

如要在 JupyterLab 筆記本檔案中查詢 BigQuery 資料，您可以使用 %%bigquery magic 指令和 Python 專用的 BigQuery 用戶端程式庫。

Vertex AI Workbench 執行個體也包含 BigQuery 整合功能，可讓您在 JupyterLab 介面中瀏覽及查詢資料。

本頁面說明如何使用這些方法。

事前準備

如果您尚未建立 Vertex AI Workbench 執行個體，請建立。

必要的角色

為確保您的執行個體服務帳戶具備查詢 BigQuery 資料的必要權限，請要求管理員授予執行個體服務帳戶專案中的「服務使用情形個人使用者」(roles/serviceusage.serviceUsageConsumer) 角色。

如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

管理員也可能會透過自訂角色或其他預先定義的角色，為執行個體的服務帳戶授予必要權限。

開啟 JupyterLab

前往 Google Cloud 控制台的「Instances」頁面。

前往「Instances」(執行個體)
按一下 Vertex AI Workbench 執行個體名稱旁的「Open JupyterLab」。

Vertex AI Workbench 執行個體會開啟 JupyterLab。

瀏覽 BigQuery 資源

BigQuery 整合功能提供可瀏覽您有權存取的 BigQuery 資源的窗格。

在 JupyterLab 導覽選單中，按一下「Notebook 中的 BigQuery」。

「BigQuery」BigQuery窗格會列出可用的專案和資料集，您可以在其中執行下列工作：
- 如要查看資料集的說明，請按兩下資料集名稱。
- 如要顯示資料集的資料表、檢視畫面和模型，請展開資料集。
- 如要在 JupyterLab 中以分頁開啟摘要說明，請按兩下資料表、檢視畫面或模型。
注意：在表格的摘要說明中，按一下「預覽」分頁標籤，即可預覽表格資料。下圖為 bigquery-public-data 專案中 google_trends 資料集中的 international_top_terms 資料表預覽畫面：

使用 %%bigquery 魔法指令查詢資料

在本節中，您將直接在筆記本儲存格中編寫 SQL，並從 BigQuery 讀取資料至 Python 筆記本。

使用單一或雙百分比字元 (% 或 %%) 的神奇指令，可讓您使用最少的語法，在 Notebook 中與 BigQuery 互動。Vertex AI Workbench 執行個體會自動安裝 Python 專用的 BigQuery 用戶端程式庫。在幕後，%%bigquery 神奇指令會使用 Python 專用的 BigQuery 用戶端程式庫執行指定的查詢，將結果轉換為 pandas DataFrame，並視需要將結果儲存到變數，然後顯示結果。

注意：自 google-cloud-bigquery Python 套件 1.26.0 版本起，系統預設會使用 BigQuery Storage API 從 %%bigquery magics 下載結果。

如要開啟筆記本檔案，請依序選取「File」>「New」>「Notebook」。
在「Select Kernel」對話方塊中，選取「Python 3」，然後按一下「Select」。

新的 IPYNB 檔案會隨即開啟。

如要取得 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;

按一下「Run cell」(執行儲存格)。

輸出結果會與下列內容相似：

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
...

在下一個儲存格 (上一個儲存格的輸出內容下方) 中輸入下列指令，執行相同的查詢，但這次將結果儲存至名為 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 指令可用的引數，請參閱用戶端程式庫魔法說明文件。
按一下「Run cell」(執行儲存格)。
在下一個儲存格中輸入下列指令，查看剛才讀取的查詢結果的前幾列：
```
regions_by_country.head()
```
按一下「Run cell」(執行儲存格)。

pandas DataFrame regions_by_country 已準備好繪製。

直接使用 BigQuery 用戶端程式庫查詢資料

在本節中，您將直接使用 Python 專用的 BigQuery 用戶端程式庫，將資料讀取至 Python 筆記本。

用戶端程式庫可讓您進一步掌控查詢，並使用更複雜的設定來處理查詢和工作。程式庫與 pandas 的整合可讓您將宣告式 SQL 與命令式程式碼 (Python) 的功能結合在一起，以便分析、視覺化及轉換資料。

注意：您可以使用多種 Python 資料分析、資料疊加及視覺化程式庫，例如 numpy、pandas、matplotlib 等。其中有數種程式庫會以 DataFrame 物件為建構基礎。

在下一個儲存格中輸入下列 Python 程式碼，以匯入 Python 專用的 BigQuery 用戶端程式庫並初始化用戶端：
```
from google.cloud import bigquery

client = bigquery.Client()
```
BigQuery 用戶端是用來與 BigQuery API 收發訊息。
按一下「Run cell」(執行儲存格)。

在下一個儲存格中輸入下列程式碼，即可擷取美國 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。

按一下「Run」單元格。

程式碼儲存格下方會顯示前幾列的查詢結果。

   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

如要進一步瞭解如何使用 BigQuery 用戶端程式庫，請參閱「使用用戶端程式庫」快速入門指南。

使用 Vertex AI Workbench 中的 BigQuery 整合功能查詢資料

BigQuery 整合功能提供兩種額外的資料查詢方法。這些方法與使用 %%bigquery 魔法指令的方式不同。

單元格查詢編輯器是可在筆記本檔案中使用的單元格類型。
獨立查詢編輯器會在 JupyterLab 中以另一個分頁開啟。

儲存格內

如要使用單元格內的查詢編輯器查詢 BigQuery 資料表中的資料，請完成下列步驟：

在 JupyterLab 中開啟筆記本 (IPYNB) 檔案，或建立新的筆記本。
如要建立儲存格內查詢編輯器，請按一下儲存格，然後在儲存格的右側按一下「BigQuery 整合」按鈕。或者，在 Markdown 儲存格中輸入 #@BigQuery。

BigQuery 整合功能會將儲存格轉換為儲存格內的查詢編輯器。
在 #@BigQuery 下方的新行中，使用 BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果系統偵測到查詢中有錯誤，查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效，系統會顯示預估的位元組數。
按一下「提交查詢」。查詢結果會隨即顯示。根據預設，查詢結果會分頁顯示，每頁 100 列，總列數上限為 1,000 列，但您可以在結果資料表底部變更這些設定。在查詢編輯器中，請只將查詢限制在驗證查詢所需的資料。您將在筆記本儲存格中再次執行這項查詢，並視需要調整限制，以便擷取完整的結果集。

注意： 關閉及重新開啟 Notebook 檔案後，查詢文字和結果會保留。
您可以按一下「查詢並載入為 DataFrame」，自動新增儲存格，其中包含會匯入 Python 專用 BigQuery 用戶端程式庫的程式碼片段，並在筆記本儲存格中執行查詢，以及將結果儲存在名為 df 的 pandas 資料框中。

獨立

如要使用獨立查詢編輯器查詢 BigQuery 資料表中的資料，請完成下列步驟：

在 JupyterLab 的「BigQuery in Notebooks」窗格中，請按一下資料表右鍵，然後選取「Query table」，或雙擊資料表以在個別分頁中開啟說明，然後按一下「Query table」連結。
使用BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果系統偵測到查詢中有錯誤，查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效，系統會顯示預估的位元組數。
按一下「提交查詢」。查詢結果會隨即顯示。根據預設，查詢結果會分頁顯示，每頁 100 列，總列數上限為 1,000 列，但您可以在結果資料表底部變更這些設定。在查詢編輯器中，請只將查詢限制在驗證查詢所需的資料。您將在筆記本儲存格中再次執行這項查詢，並視需要調整限制，以便擷取完整的結果集。
您可以按一下「Copy code for DataFrame」，複製程式碼片段，該片段會匯入 Python 專用的 BigQuery 用戶端程式庫、在筆記本儲存格中執行查詢，並將結果儲存在名為 df 的 pandas 資料框中。將這段程式碼貼到要執行程式碼的筆記本儲存格中。

查看查詢記錄和重複使用查詢

如要在 JupyterLab 中以分頁的形式查看查詢記錄，請執行下列步驟：

在 JupyterLab 導覽選單中，按一下「BigQuery in Notebooks」，開啟「BigQuery」窗格。
在「BigQuery」窗格中，向下捲動並點選「查詢記錄」。

系統會在新分頁中開啟查詢清單，讓您執行下列工作：
- 如要查看查詢的詳細資料，例如工作 ID、查詢執行時間和所需時間，請按一下查詢。
- 如要修改查詢、再次執行查詢，或是將查詢複製到筆記本以供日後使用，請按一下「在編輯器中開啟查詢」。

後續步驟

如需 BigQuery 資料表資料視覺化範例，請參閱「在 JupyterLab 中探索及以視覺化方式呈現 BigQuery 資料」一文。
如要進一步瞭解如何撰寫 BigQuery 查詢，請參閱「執行互動式與批次查詢工作」一文。
瞭解如何控管 BigQuery 資料集的存取權。