在 JupyterLab 內查詢 BigQuery 中的資料

本頁面說明如何從 Gemini Enterprise Agent Platform Workbench 執行個體的 JupyterLab 介面,查詢儲存在 BigQuery 的資料。

在筆記本 (IPYNB) 檔案中查詢 BigQuery 資料的方法

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

Agent Platform Workbench 執行個體也整合 BigQuery,可讓您在 JupyterLab 介面中瀏覽及查詢資料。

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

事前準備

如果您尚未建立 Agent Platform Workbench 執行個體,請先完成這項作業。

必要的角色

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

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

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

開啟 JupyterLab

  1. 前往 Google Cloud 控制台的「Instances」(執行個體) 頁面。

    前往「Instances」(執行個體) 頁面

  2. 按一下 Agent Platform Workbench 執行個體名稱旁的「Open JupyterLab」(開啟 JupyterLab)。

    Agent Platform Workbench 執行個體會開啟 JupyterLab。

瀏覽 BigQuery 資源

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

  1. 在 JupyterLab 導覽選單中,按一下BigQuery「BigQuery in Notebooks」(Notebooks 中的 BigQuery)。

    「BigQuery」BigQuery窗格會列出可用的專案和資料集,您可以在其中執行下列工作:

    • 如要查看資料集說明,請按兩下資料集名稱。
    • 如要顯示資料集的資料表、檢視表和模型,請展開資料集。
    • 如要在 JupyterLab 中以分頁形式開啟摘要說明,請按兩下資料表、檢視表或模型。

    注意:在資料表的摘要說明中,按一下「Preview」(預覽) 分頁標籤,即可預覽資料表的資料。下圖顯示 bigquery-public-data 專案中 google_trends 資料集內的 international_top_terms 資料表預覽畫面:

    國際熱門字詞清單。

使用 %%bigquery magic 指令查詢資料

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

使用單一或雙百分比字元 (% 或 %%) 的魔術指令,可讓您在筆記本中使用最少的語法與 BigQuery 互動。Python 適用的 BigQuery 用戶端程式庫會自動安裝在 Agent Platform Workbench 執行個體中。在幕後,%%bigquery 魔法指令會使用 BigQuery Python 用戶端程式庫執行指定查詢、將結果轉換為 pandas DataFrame、視需要將結果儲存至變數,然後顯示結果。

注意:自 google-cloud-bigquery Python 套件 1.26.0 版起,系統預設會使用 BigQuery Storage API 下載 %%bigquery magic 的結果。

  1. 如要開啟筆記本檔案,請依序選取「File」(檔案) >「New」(新增) > 「Notebook」(筆記本)。

  2. 在「Select Kernel」對話方塊中,選取「Python 3」,然後點選「Select」。

    新的 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. 按一下 「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
    ...
    
  5. 在下一個儲存格 (位於上一個儲存格的輸出內容下方),輸入下列指令來執行相同的查詢,但這次將結果儲存至名為 regions_by_country 的新 pandas DataFrame。您可以使用 %%bigquery magic 指令的引數提供該名稱。

    %%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. 按一下 「Run cell」(執行儲存格)。

  7. 在下一個儲存格中輸入下列指令,查看剛才讀取的查詢結果前幾列:

    regions_by_country.head()
    
  8. 按一下 「Run cell」(執行儲存格)。

    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. 按一下 「Run cell」(執行儲存格)。

  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. 按一下「 Run cell」(執行儲存格)。

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

       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 用戶端程式庫,請參閱「使用用戶端程式庫」快速入門導覽課程。

在 Agent Platform Workbench 中使用 BigQuery 整合功能查詢資料

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

  • 儲存格內查詢編輯器是一種儲存格類型,可在筆記本檔案中使用。

  • 獨立查詢編輯器會在 JupyterLab 中以獨立分頁開啟。

儲存格內

如要使用儲存格內查詢編輯器,查詢 BigQuery 資料表中的資料,請完成下列步驟:

  1. 在 JupyterLab 中,開啟筆記本 (IPYNB) 檔案或建立新檔案。

  2. 如要建立儲存格內查詢編輯器,請按一下儲存格,然後按一下儲存格右側的 「BigQuery Integration」(BigQuery 整合) 按鈕。或在 Markdown 儲存格中輸入 #@BigQuery。

    BigQuery 整合功能會將儲存格轉換為儲存格內查詢編輯器。

  3. 在 #@BigQuery 下方的新行中,使用 BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果查詢中偵測到錯誤,查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效,系統會顯示預估要處理的位元組數。

  4. 按一下「Submit Query」(提交查詢)。查詢結果會隨即顯示。根據預設,查詢結果會分頁顯示,每頁 100 列,總共最多 1,000 列,但您可以在結果表格底部變更這些設定。在查詢編輯器中,請僅查詢驗證所需資料。您會在筆記本儲存格中再次執行這項查詢,並可視需要調整限制,擷取完整的結果集。

  5. 您可以點選「Query and load as DataFrame」(查詢並載入為 DataFrame),自動新增包含程式碼片段的新儲存格,用於匯入 Python 適用的 BigQuery 用戶端程式庫、在筆記本儲存格中執行查詢,並將結果儲存在名為 df 的 pandas DataFrame。

獨立

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

  1. 在 JupyterLab 的「BigQuery in Notebooks」窗格中,對資料表按一下滑鼠右鍵,然後選取「Query table」(查詢資料表),或按兩下資料表,在另一個分頁中開啟說明,然後按一下「Query table」(查詢資料表) 連結。

  2. 使用 BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果查詢中偵測到錯誤,查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效,系統會顯示預估要處理的位元組數。

  3. 按一下「Submit Query」(提交查詢)。查詢結果會隨即顯示。根據預設,查詢結果會分頁顯示,每頁 100 列,總共最多 1,000 列,但您可以在結果表格底部變更這些設定。在查詢編輯器中,請僅查詢驗證所需資料。您會在筆記本儲存格中再次執行這項查詢,並可視需要調整限制,擷取完整的結果集。

  4. 您可以點選「Copy code for DataFrame」(複製 DataFrame 的程式碼),複製匯入 Python 專用 BigQuery 用戶端程式庫的程式碼片段,在筆記本儲存格中執行查詢,並將結果儲存在名為 df 的 pandas DataFrame。將這段程式碼貼到要執行的筆記本儲存格中。

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

如要在 JupyterLab 中以分頁形式查看查詢記錄,請按照下列步驟操作:

  1. 在 JupyterLab 導覽選單中,按一下 BigQuery「BigQuery in Notebooks」(Notebooks 中的 BigQuery),開啟「BigQuery」窗格。

  2. 在「BigQuery」窗格中向下捲動,然後點選「查詢記錄」。

    左側導覽列底部醒目顯示的查詢記錄

    系統會在新的分頁中開啟查詢清單,您可以在其中執行下列工作:

    • 如要查看查詢的詳細資料,例如工作 ID、查詢執行時間和所需時間,請按一下查詢。
    • 如要修改查詢、再次執行查詢,或將查詢複製到筆記本以供日後使用,請按一下「Open query in editor」(在編輯器中開啟查詢)。

後續步驟