執行查詢 (original) (raw)

執行查詢

本文件將說明如何在 BigQuery 中執行查詢，並透過模擬執行，瞭解查詢在執行前會處理多少資料。

查詢作業的類型

您可以使用下列任一查詢工作類型查詢 BigQuery 資料：

• **互動式查詢工作**。根據預設，BigQuery 會以互動式查詢工作執行查詢，這類工作旨在盡快開始執行。
• **批次查詢工作**。批次查詢的優先順序低於互動式查詢。如果專案或預留作業使用了所有可用的運算資源，批次查詢就更有可能排入佇列並保留在佇列中。批次查詢開始執行後，其運作方式與互動式查詢相同。詳情請參閱「查詢佇列」。
• 持續查詢工作 (預先發布版)。透過這些工作，查詢會持續執行，讓您能夠即時在 BigQuery 中分析傳入的資料，然後將結果寫入 BigQuery 資料表，或將結果匯出至 Bigtable 或 Pub/Sub。您可以使用這項功能執行時間敏感的工作，例如建立洞察並立即採取行動、套用即時機器學習 (ML) 推論，以及建構事件驅動資料管道。

您可以使用下列方法執行查詢工作：

• 在 Google Cloud 控制台中編寫並執行查詢。
• 在 bq 指令列工具中執行 bq query 指令。
• 透過程式呼叫 BigQuery REST API 中的 jobs.query 或 jobs.insert 方法。
• 使用 BigQuery 用戶端程式庫。

BigQuery 會將查詢結果儲存至臨時表格 (預設) 或永久表格。指定永久資料表做為結果的目標資料表時，您可以選擇要附加或覆寫現有資料表，或是建立具有專屬名稱的新資料表。

必要的角色

如要取得執行查詢作業所需的權限，請管理員授予您下列 IAM 角色：

• 專案中的 BigQuery 工作使用者 (roles/bigquery.jobUser)。
• 在查詢參照的所有資料表和檢視中，使用 BigQuery 資料檢視器 (roles/bigquery.dataViewer)。如要查詢檢視畫面，您也必須在所有基礎資料表和檢視畫面上具備這個角色。如果您使用已授權的檢視畫面或已授權的資料集，就不需要存取基礎來源資料。

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

這些預先定義的角色包含執行查詢工作的必要權限。如要查看確切的必要權限，請展開「必要權限」部分：

所需權限

您必須具備下列權限，才能執行查詢工作：

• bigquery.jobs.create 無論資料儲存位置為何，都會在執行查詢的專案上執行。
• bigquery.tables.getData 查詢參照的所有資料表和檢視表。如要查詢檢視表，您也必須在所有基礎資料表和檢視表上具備這項權限。如果您使用已授權的檢視畫面或已授權的資料集，就不需要存取基礎來源資料。

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

疑難排解

Access Denied: Project [project_id]: User does not have bigquery.jobs.create
permission in project [project_id].

當實體缺少在專案中建立查詢工作所需的權限時，就會發生這個錯誤。

解決方法：管理員必須授予您所查詢專案的 bigquery.jobs.create 權限。除了存取查詢資料所需的權限外，您還必須具備這個權限。

如要進一步瞭解 BigQuery 權限，請參閱「使用身分與存取權管理功能控管存取權」。

執行互動式查詢

如要執行互動式查詢，請選取下列其中一個選項：

主控台

1. 前往「BigQuery」頁面
   前往 BigQuery
2. 按一下 「SQL 查詢」。
3. 在查詢編輯器中輸入有效的 GoogleSQL 查詢。
   舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年到 2013 年之間美國最常見的姓名：

SELECT  
  name, gender,  
  SUM(number) AS total  
FROM  
  `bigquery-public-data.usa_names.usa_1910_2013`  
GROUP BY  
  name, gender  
ORDER BY  
  total DESC  
LIMIT  
  10;  

1. 選用步驟：如要選取其他查詢設定，請依序按一下 「更多」和「查詢設定」。
2. 按一下 「Run」。
   如未指定目的地資料表，查詢工作會將輸出寫入臨時 (快取) 資料表。
   您現在可以在「查詢結果」窗格的「結果」分頁中，查看查詢結果。
3. 選用步驟：如要按照資料欄排序查詢結果，請點選資料名稱欄旁的 「Open sort menu」(開啟排序選單)，然後選取排列順序。如果排序作業的預估處理位元組數大於 0，選單頂端就會顯示位元組數。
4. 選用步驟：如要查看查詢結果的圖表呈現，請前往「圖表」分頁。您可以放大或縮小圖表、將圖表下載為 PNG 檔案，或切換圖例顯示狀態。
   在「圖表設定」窗格中，您可以變更圖表類型 (折線、長條或散布圖)，並設定圖表的評量指標和維度。這個窗格中的欄位會預先填入從查詢的目的資料表架構推斷的初始設定。在同一個查詢編輯器中，後續執行的查詢會保留設定。維度支援 INT64、FLOAT64、NUMERIC、BIGNUMERIC、TIMESTAMP、DATE、DATETIME、TIME 和 STRING 資料類型，而度量項目支援 INT64、FLOAT64、NUMERIC 和 BIGNUMERIC 資料類型。
5. 選用步驟：在「JSON」JSON分頁中，您可以以 JSON 格式探索查詢結果，其中索引鍵是資料欄名稱，值則是該欄的結果。

bq

1. In the Google Cloud console, activate Cloud Shell.
   Activate Cloud Shell
   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
2. 使用 bq query 指令。在以下範例中，--use_legacy_sql=false 標記可讓您使用 GoogleSQL 語法。
   bq query \
       --use_legacy_sql=false \
       'QUERY'
   請將 QUERY 替換為有效的 GoogleSQL 查詢。舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年至 2013 年之間美國最常見的姓名：

bq query \  
    --use_legacy_sql=false \  
    'SELECT  
      name, gender,  
      SUM(number) AS total  
    FROM  
      `bigquery-public-data.usa_names.usa_1910_2013`  
    GROUP BY  
      name, gender  
    ORDER BY  
      total DESC  
    LIMIT  
      10;'  

查詢工作會將輸出寫入臨時 (快取) 資料表。
您可以選擇為查詢結果指定目的地資料表和位置。如要將結果寫入現有資料表，請加入適當的標記，以便附加 (--append_table=true) 或覆寫 (--replace=true) 資料表。
bq query \
    --location=LOCATION \
    --destination_table=TABLE \
    --use_legacy_sql=false \
    'QUERY'
更改下列內容：

• LOCATION：目的地資料表的區域或多區域，例如 US
  在這個範例中，usa_names 資料集儲存在美國多地區位置。如果您為這項查詢指定目的地資料表，則包含目的地資料表的資料集也必須位於美國多地區。您無法查詢位於某個位置的資料集，然後將結果寫入位於另一個位置的資料表。
  您可以使用 .bigqueryrc 檔案設定位置的預設值。
• TABLE：目標資料表的名稱，例如 myDataset.myTable
  如果目的地資料表是新資料表，BigQuery 會在您執行查詢時建立該資料表。不過，您必須指定現有的資料集。
  如果資料表不在目前專案中，請使用 PROJECT_ID:DATASET.TABLE 格式新增Google Cloud 專案 ID，例如 myProject:myDataset.myTable。如未指定 --destination_table，系統會產生將輸出寫入臨時資料表的查詢工作。

API

如要使用 API 執行查詢，請插入新工作並填入 query 工作設定屬性。您可以選擇在工作資源的 jobReference 區段中，於 location 屬性內指定您的位置。

呼叫 getQueryResults 來輪詢結果。持續輪詢，直到 jobComplete 等於 true 為止。檢查 errors 清單中的錯誤與警告。

C#

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 C# 設定說明進行操作。詳情請參閱 BigQuery C# API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Java

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要透過 Proxy 執行查詢，請參閱「設定 Proxy」。

Node.js

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Node.js 設定說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

PHP

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 PHP 設定說明進行操作。詳情請參閱 BigQuery PHP API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Python

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Ruby

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Ruby 設定說明進行操作。詳情請參閱 BigQuery Ruby API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

執行批次查詢

如要執行批次查詢，請選取下列任一選項：

主控台

1. 前往「BigQuery」頁面
   前往 BigQuery
2. 按一下 「SQL 查詢」。
3. 在查詢編輯器中輸入有效的 GoogleSQL 查詢。
   舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年到 2013 年之間美國最常見的姓名：

SELECT  
  name, gender,  
  SUM(number) AS total  
FROM  
  `bigquery-public-data.usa_names.usa_1910_2013`  
GROUP BY  
  name, gender  
ORDER BY  
  total DESC  
LIMIT  
  10;  

1. 依序按一下 「More」和「Query settings」。
2. 在「資源管理」部分中，選取「批次」。
3. 選用步驟：調整查詢設定。
4. 按一下 [儲存]。
5. 按一下 「Run」。
   如未指定目的地資料表，查詢工作會將輸出寫入臨時 (快取) 資料表。

bq

1. In the Google Cloud console, activate Cloud Shell.
   Activate Cloud Shell
   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
2. 使用 bq query 指令並指定 --batch 標記。在以下範例中，--use_legacy_sql=false 旗標可讓您使用 GoogleSQL 語法。
   bq query \
       --batch \
       --use_legacy_sql=false \
       'QUERY'
   請將 QUERY 替換為有效的 GoogleSQL 查詢。舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年至 2013 年之間美國最常見的姓名：

bq query \  
    --batch \  
    --use_legacy_sql=false \  
    'SELECT  
      name, gender,  
      SUM(number) AS total  
    FROM  
      `bigquery-public-data.usa_names.usa_1910_2013`  
    GROUP BY  
      name, gender  
    ORDER BY  
      total DESC  
    LIMIT  
      10;'  

查詢工作會將輸出寫入臨時 (快取) 資料表。
您可以選擇為查詢結果指定目的地資料表和位置。如要將結果寫入現有資料表，請加入適當的標記，以便附加 (--append_table=true) 或覆寫 (--replace=true) 資料表。
bq query \
    --batch \
    --location=LOCATION \
    --destination_table=TABLE \
    --use_legacy_sql=false \
    'QUERY'
更改下列內容：

• LOCATION：目的地資料表的區域或多區域，例如 US
  在這個範例中，usa_names 資料集儲存在美國多地區位置。如果您為這項查詢指定目的地資料表，則包含目的地資料表的資料集也必須位於美國多地區。您無法查詢位於某個位置的資料集，然後將結果寫入位於另一個位置的資料表。
  您可以使用 .bigqueryrc 檔案設定位置的預設值。
• TABLE：目標資料表的名稱，例如 myDataset.myTable
  如果目的地資料表是新資料表，BigQuery 會在您執行查詢時建立該資料表。不過，您必須指定現有的資料集。
  如果資料表不在目前專案中，請使用 PROJECT_ID:DATASET.TABLE 格式新增Google Cloud 專案 ID，例如 myProject:myDataset.myTable。如未指定 --destination_table，系統會產生將輸出寫入臨時資料表的查詢工作。

API

如要使用 API 執行查詢，請插入新工作並填入 query 工作設定屬性。您可以選擇在工作資源的 jobReference 區段中，於 location 屬性內指定您的位置。

填入查詢工作屬性時，請加入 configuration.query.priority 屬性，並將值設為 BATCH。

呼叫 getQueryResults 來輪詢結果。持續輪詢，直到 jobComplete 等於 true 為止。檢查 errors 清單中的錯誤與警告。

Go

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Java

如要執行批次查詢，請將查詢優先順序設定為 QueryJobConfiguration.Priority.BATCH (當建立 QueryJobConfiguration 時)。

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Node.js

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Node.js 設定說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Python

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

執行持續查詢

如要執行持續查詢工作，您必須進行額外的設定。詳情請參閱「建立持續查詢」。

查詢設定

執行查詢時，您可以指定下列設定：

• 查詢結果的目的地資料表。
• 工作優先順序。
• 是否使用快取的查詢結果。
• 工作逾時時間，以毫秒為單位。
• 是否使用工作階段模式。
• 要使用的加密類型。
• 這項查詢的計費位元組數上限。
• 要使用的 SQL 方言。
• 執行查詢的位置。查詢必須與查詢中參照的任何資料表位於相同位置。
• 預留空間 (預覽)，用於執行查詢。

選用的工作建立模式

選用的建立工作模式 (先前稱為「短查詢最佳化模式」) 可改善短時間內執行的查詢整體延遲時間，例如資訊主頁或資料探索工作負載的查詢。這個模式會執行查詢，並針對 SELECT 陳述式傳回內嵌的結果，不必使用 jobs.getQueryResults 擷取結果。使用選用工作建立模式的查詢在執行時不會建立工作，除非 BigQuery 判斷需要建立工作才能完成查詢。

如要啟用選用的工作建立模式，請在 jobs.query 要求主體中，將 QueryRequest 例項的 jobCreationMode 欄位設為 JOB_CREATION_OPTIONAL。

當這個欄位的值設為 JOB_CREATION_OPTIONAL 時，BigQuery 會判斷查詢是否可以使用選用的作業建立模式。如果是，BigQuery 會執行查詢，並在回應的 rows 欄位中傳回所有結果。由於未為這項查詢建立工作，因此 BigQuery 不會在回應本文中傳回 jobReference。而是傳回 queryId 欄位，您可以使用該欄位透過 INFORMATION_SCHEMA.JOBS 檢視畫面取得查詢相關的洞察資料。由於未建立工作，因此沒有 jobReference 可傳遞至 jobs.get 和 jobs.getQueryResults API 來查詢這些查詢。

如果 BigQuery 判斷需要工作才能完成查詢，就會傳回 jobReference。您可以檢查 INFORMATION_SCHEMA.JOBS 檢視畫面中的 job_creation_reason 欄位，判斷為何為該查詢建立工作。在這種情況下，您應在查詢完成後使用 jobs.getQueryResults 擷取結果。

使用 JOB_CREATION_OPTIONAL 值時，回應中可能不會出現 jobReference 欄位。請先檢查欄位是否存在，再存取該欄位。

如果為多語句查詢 (指令碼) 指定 JOB_CREATION_OPTIONAL，BigQuery 可能會最佳化執行程序。在進行這項最佳化作業時，BigQuery 可能會判斷自己可以透過建立的工作資源數量少於個別陳述式數量來完成指令碼，甚至可能不必建立任何工作就能執行整個指令碼。這項最佳化功能取決於 BigQuery 對指令碼的評估結果，且不一定會在所有情況下套用。系統會自動完成最佳化調整。使用者不必進行任何控制或操作。

如要使用選用的工作建立模式執行查詢，請選取下列任一選項：

主控台

1. 前往「BigQuery」頁面
   前往 BigQuery
2. 按一下 「SQL 查詢」。
3. 在查詢編輯器中輸入有效的 GoogleSQL 查詢。
   舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年到 2013 年之間美國最常見的姓名：

SELECT  
  name, gender,  
  SUM(number) AS total  
FROM  
  `bigquery-public-data.usa_names.usa_1910_2013`  
GROUP BY  
  name, gender  
ORDER BY  
  total DESC  
LIMIT  
  10;  

1. 按一下 「More」，然後選擇「Optional job creation」查詢模式。如要確認這個選項，請按一下「確認」。
2. 按一下 「Run」。

bq

1. In the Google Cloud console, activate Cloud Shell.
   Activate Cloud Shell
   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
2. 使用 bq query 指令並指定 --job_creation_mode=JOB_CREATION_OPTIONAL 標記。在以下範例中，--use_legacy_sql=false 旗標可讓您使用 GoogleSQL 語法。
   bq query \
       --rpc=true \
       --use_legacy_sql=false \
       --job_creation_mode=JOB_CREATION_OPTIONAL \
       --location=LOCATION \
       'QUERY'
   請將 QUERY 替換為有效的 GoogleSQL 查詢，並將 LOCATION 替換為資料集所在的有效區域。舉例來說，您可以查詢 BigQuery 公開資料集 usa_names，找出 1910 年至 2013 年之間美國最常見的姓名：

bq query \  
    --rpc=true \  
    --use_legacy_sql=false \  
    --job_creation_mode=JOB_CREATION_OPTIONAL \  
    --location=us \  
    'SELECT  
      name, gender,  
      SUM(number) AS total  
    FROM  
      `bigquery-public-data.usa_names.usa_1910_2013`  
    GROUP BY  
      name, gender  
    ORDER BY  
      total DESC  
    LIMIT  
      10;'  

查詢工作會在回應中傳回內嵌的輸出內容。

API

如要使用 API 在選用的工作建立模式下執行查詢，請同步執行查詢，並填入 QueryRequest 屬性。加入 jobCreationMode 屬性，並將其值設為 JOB_CREATION_OPTIONAL。

查看回覆。如果 jobComplete 等於 true，且 jobReference 為空白，請從 rows 欄位讀取結果。您也可以從回應中取得 queryId。

如果有 jobReference，您可以查看 jobCreationReason，瞭解 BigQuery 為何建立工作。呼叫 getQueryResults 來輪詢結果。持續輪詢，直到 jobComplete 等於 true 為止。檢查 errors 清單中的錯誤與警告。

Java

適用版本：2.51.0 以上

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

如要透過 Proxy 執行查詢，請參閱「設定 Proxy」。

Python

適用版本：3.34.0 以上

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

節點

適用版本：8.1.0 以上

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Node.js 設定說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

Go

適用版本：1.69.0 以上

在嘗試這個範例之前，請先按照 BigQuery 快速入門：使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

JDBC 驅動程式

適用版本：JDBC 1.6.1 以上版本

必須在連線字串中設定 JobCreationMode=2。

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;JobCreationMode=2;Location=US;

ODBC 驅動程式

適用版本：ODBC 3.0.7.1016 以上版本

需要在 .ini 檔案中設定 JobCreationMode=2。

[ODBC Data Sources]
Sample DSN=Simba Google BigQuery ODBC Connector 64-bit
[Sample DSN]
JobCreationMode=2

配額

如要瞭解互動式和批次查詢的配額，請參閱「查詢工作」一文。

監控查詢

您可以使用工作檢視器或查詢 INFORMATION_SCHEMA.JOBS_BY_PROJECT 檢視畫面，取得執行中的查詢相關資訊。

模擬測試

BigQuery 的模擬執行作業會提供以下資訊：

• 以量計價模式的預估費用
• 查詢驗證
• 容量模式中查詢作業處理的近似位元組數

模擬測試不會使用查詢運算單元，而且不會產生費用。您可以利用模擬測試傳回的估算值，在 Pricing Calculator 中計算查詢費用。

執行模擬測試

如要執行模擬測試，請按照下列步驟操作：

後續步驟

• 瞭解如何管理查詢工作。
• 瞭解如何查看查詢記錄。
• 瞭解如何儲存及共用查詢。
• 瞭解查詢佇列。
• 瞭解如何寫入查詢結果。