本頁面說明如何從 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
前往 Google Cloud 控制台的「Instances」(執行個體) 頁面。
按一下 Agent Platform Workbench 執行個體名稱旁的「Open JupyterLab」(開啟 JupyterLab)。
Agent Platform Workbench 執行個體會開啟 JupyterLab。
瀏覽 BigQuery 資源
BigQuery 整合功能提供一個窗格,可瀏覽您有權存取的 BigQuery 資源。
在 JupyterLab 導覽選單中,按一下
「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 的結果。
如要開啟筆記本檔案,請依序選取「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。您可以使用%%bigquerymagic 指令的引數提供該名稱。%%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 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 資料表中的資料,請完成下列步驟:
在 JupyterLab 中,開啟筆記本 (IPYNB) 檔案或建立新檔案。
如要建立儲存格內查詢編輯器,請按一下儲存格,然後按一下儲存格右側的 「BigQuery Integration」(BigQuery 整合) 按鈕。或在 Markdown 儲存格中輸入
#@BigQuery。BigQuery 整合功能會將儲存格轉換為儲存格內查詢編輯器。
在
#@BigQuery下方的新行中,使用 BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果查詢中偵測到錯誤,查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效,系統會顯示預估要處理的位元組數。按一下「Submit Query」(提交查詢)。查詢結果會隨即顯示。根據預設,查詢結果會分頁顯示,每頁 100 列,總共最多 1,000 列,但您可以在結果表格底部變更這些設定。在查詢編輯器中,請僅查詢驗證所需資料。您會在筆記本儲存格中再次執行這項查詢,並可視需要調整限制,擷取完整的結果集。
您可以點選「Query and load as DataFrame」(查詢並載入為 DataFrame),自動新增包含程式碼片段的新儲存格,用於匯入 Python 適用的 BigQuery 用戶端程式庫、在筆記本儲存格中執行查詢,並將結果儲存在名為
df的 pandas DataFrame。
獨立
如要使用獨立查詢編輯器查詢 BigQuery 資料表中的資料,請完成下列步驟:
在 JupyterLab 的「BigQuery in Notebooks」窗格中,對資料表按一下滑鼠右鍵,然後選取「Query table」(查詢資料表),或按兩下資料表,在另一個分頁中開啟說明,然後按一下「Query table」(查詢資料表) 連結。
使用 BigQuery 支援的陳述式和 SQL 方言編寫查詢。如果查詢中偵測到錯誤,查詢編輯器的右上角會顯示錯誤訊息。如果查詢有效,系統會顯示預估要處理的位元組數。
按一下「Submit Query」(提交查詢)。查詢結果會隨即顯示。根據預設,查詢結果會分頁顯示,每頁 100 列,總共最多 1,000 列,但您可以在結果表格底部變更這些設定。在查詢編輯器中,請僅查詢驗證所需資料。您會在筆記本儲存格中再次執行這項查詢,並可視需要調整限制,擷取完整的結果集。
您可以點選「Copy code for DataFrame」(複製 DataFrame 的程式碼),複製匯入 Python 專用 BigQuery 用戶端程式庫的程式碼片段,在筆記本儲存格中執行查詢,並將結果儲存在名為
df的 pandas DataFrame。將這段程式碼貼到要執行的筆記本儲存格中。
查看查詢記錄及重複使用查詢
如要在 JupyterLab 中以分頁形式查看查詢記錄,請按照下列步驟操作:
在 JupyterLab 導覽選單中,按一下
「BigQuery in Notebooks」(Notebooks 中的 BigQuery),開啟「BigQuery」窗格。在「BigQuery」窗格中向下捲動,然後點選「查詢記錄」。
系統會在新的分頁中開啟查詢清單,您可以在其中執行下列工作:
- 如要查看查詢的詳細資料,例如工作 ID、查詢執行時間和所需時間,請按一下查詢。
- 如要修改查詢、再次執行查詢,或將查詢複製到筆記本以供日後使用,請按一下「Open query in editor」(在編輯器中開啟查詢)。
後續步驟
如要查看如何以圖表呈現 BigQuery 資料表中的資料,請參閱「在 JupyterLab 中探索及圖表呈現 BigQuery 資料」。
如要進一步瞭解如何編寫 BigQuery 查詢,請參閱「執行互動式和批次查詢工作」。
瞭解如何控管 BigQuery 資料集的存取權。