排解查詢相關問題

本文旨在協助您排解與執行查詢相關的常見問題,例如找出查詢速度緩慢的原因,或針對查詢失敗時傳回的常見錯誤提供解決步驟。

排解查詢速度緩慢的問題

排解查詢效能緩慢問題時,請考慮下列常見原因:

  1. 查看「Google Cloud 服務健康狀態」頁面,瞭解可能影響查詢效能的已知 BigQuery 服務中斷情形。

  2. 工作詳細資料頁面中查看查詢的工作時間軸,瞭解查詢各階段的執行時間。

    • 如果大部分時間都用於建立資源,請與 Cloud Customer Care 團隊聯絡尋求協助。

    • 如果大部分的經過時間都是因為執行時間過長,請查看查詢效能洞察。 查詢效能洞察可告知您查詢的執行時間是否超過平均執行時間,並提供可能原因。可能的原因包括查詢時段爭用或隨機播放配額不足。如要進一步瞭解各項查詢效能問題和可能的解決方法,請參閱「解讀查詢效能洞察資料」。

  3. 查看查詢工作的 JobStatistics 中的 finalExecutionDurationMs 欄位。查詢可能已重試。finalExecutionDurationMs 欄位包含這項工作最後一次嘗試執行的時間長度 (以毫秒為單位)。

  4. 查看查詢工作詳細資料頁面中處理的位元組數,確認是否高於預期。方法是比較目前查詢處理的位元組數,以及在可接受的時間內完成的另一個查詢作業。如果這兩項查詢處理的位元組有很大差異,可能是因為資料量過大,導致查詢速度緩慢。如要瞭解如何最佳化查詢來處理大量資料,請參閱「最佳化查詢運算」一文。

    您也可以使用 INFORMATION_SCHEMA.JOBS 檢視畫面搜尋費用最高的查詢,找出專案中處理大量資料的查詢。

如果還是找不到原因,請與 Cloud Customer Care 團隊聯絡,尋求協助。

Avro 結構定義解析

錯誤字串:Cannot skip stream

載入多個具有不同結構定義的 Avro 檔案時,可能會發生這個錯誤,導致結構定義解析問題,並造成匯入工作在隨機檔案中失敗。

如要解決這項錯誤,請確保載入作業中最後一個依字母順序排列的檔案,包含不同結構定義的超集 (聯集)。這是根據 Avro 處理結構定義解析的方式所做的規定。

並行查詢發生衝突

錯誤字串:Concurrent jobs in the same session are not allowed

如果工作階段中同時執行多項查詢 (不支援此做法),就會發生這項錯誤。請參閱工作階段限制

DML 陳述式衝突

錯誤字串:Could not serialize access to table due to concurrent update

如果對同一資料表並行執行的資料操縱語言 (DML) 陳述式發生衝突,或是資料表在 DML 陳述式變異期間遭到截斷,就可能發生這項錯誤。詳情請參閱「DML 陳述式衝突」。

如要解決這個錯誤,請執行影響單一資料表的 DML 作業,確保作業不會重疊。

相互關聯的子查詢

錯誤字串:Correlated subqueries that reference other tables are not supported unless they can be de-correlated

如果查詢包含參照子查詢外部資料欄的子查詢 (稱為「關聯」資料欄),就可能發生這個錯誤。系統會使用效率不彰的巢狀執行策略評估相關子查詢,其中會針對產生相關資料欄的外部查詢中每個資料列評估子查詢。有時 BigQuery 會在內部改寫含有相關子查詢的查詢,以便更有效率地執行查詢。如果 BigQuery 無法充分最佳化查詢,就會發生相關子查詢錯誤。

如要解決這項錯誤,請嘗試下列做法:

  • 從子查詢中移除任何 ORDER BYLIMITEXISTSNOT EXISTSIN 子句。
  • 使用多重陳述式查詢建立臨時資料表,以供子查詢參照。
  • 請重新撰寫查詢,改為使用 CROSS JOIN

資料欄層級存取權控管權限不足

錯誤字串:Requires fineGrainedGet permission on the read columns to execute the DML statements

如果您嘗試執行 DML DELETEUPDATEMERGE 陳述式,但掃描的資料欄使用資料欄層級存取權控管機制,且您沒有這些資料欄的精細讀取者權限,就會發生這項錯誤。詳情請參閱「欄層級存取權控管對寫入作業的影響」。

排定查詢的憑證無效

錯誤字串:

  • Error code: INVALID_USERID
  • Error code 5: Authentication failure: User Id not found
  • PERMISSION_DENIED: BigQuery: Permission denied while getting Drive credentials

如果排定查詢作業因憑證過時而失敗,就可能發生這項錯誤,尤其是查詢 Google 雲端硬碟資料時。

如要解決這項錯誤,請按照下列步驟操作:

服務帳戶憑證無效

錯誤字串:HttpError 403 when requesting returned: The caller does not have permission

如果您嘗試使用服務帳戶設定排程查詢,可能會看到這則錯誤訊息。如要解決這項錯誤,請參閱「授權和權限問題」一文中的疑難排解步驟。

快照時間無效

錯誤字串:Invalid snapshot time

嘗試查詢資料集時間旅行視窗外的歷來資料時,可能會發生這項錯誤。如要修正這項錯誤,請變更查詢,在資料集的時間旅行視窗中存取歷來資料。

如果查詢開始後,查詢中使用的其中一個資料表遭到捨棄並重新建立,也可能會出現這項錯誤。檢查是否有排定的查詢或應用程式執行這項作業,且執行時間與失敗的查詢相同。如果有的話,請嘗試將執行捨棄和重新建立作業的程序移至不會與讀取該資料表的查詢衝突的時間執行。

工作已存在

錯誤字串:Already Exists: Job <job name>

如果查詢工作必須評估大型陣列,導致建立查詢工作所需的時間比平均值長,就可能發生這項錯誤。舉例來說,具有 WHERE 子句的查詢 (例如 WHERE column IN (<2000+ elements array>))。

如要解決這項錯誤,請按照下列步驟操作:

找不到所需工作

錯誤字串:Job not found

如果對 getQueryResults 呼叫的回應中,location 欄位未指定任何值,就會發生這項錯誤。如果是這種情況,請再次呼叫並提供 location 值。

詳情請參閱避免多次評估相同的通用資料表運算式 (CTE)

找不到所在位置

錯誤字串:Dataset [project_id]:[dataset_id] was not found in location [region]

當您參照不存在的資料集資源,或要求中的位置與資料集的位置不符時,系統就會傳回這個錯誤。

如要解決這個問題,請在查詢中指定資料集位置,或確認資料集位於相同位置。

查詢超過執行時間限制

錯誤字串:Query fails due to reaching the execution time limit

如果查詢達到查詢執行時間限制,請查詢 INFORMATION_SCHEMA.JOBS 檢視區塊,瞭解先前執行查詢的時間,查詢內容類似於下列範例:

SELECT TIMESTAMP_DIFF(end_time, start_time, SECOND) AS runtime_in_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE statement_type = 'QUERY'
AND query = "my query string";

如果先前執行查詢的時間明顯較短,請使用查詢效能深入分析找出並解決根本問題。

查詢回應過大

錯誤字串:responseTooLarge

當查詢的結果超出回應大小上限時,系統就會傳回這個錯誤。

如要解決這個錯誤,請按照responseTooLarge 錯誤訊息的相關指引操作。

DML 陳述式過多

錯誤字串:Too many DML statements outstanding against <table-name>, limit is 20

如果單一資料表佇列中處於 PENDING 狀態的 DML 陳述式超過 20 個,就會發生這項錯誤。如果您提交 DML 工作至單一資料表的速度,比 BigQuery 的處理速度快,通常就會發生這個錯誤。

其中一個可能的解決方法是將多個較小的 DML 作業分組,形成較大但數量較少的工作,例如批次更新和插入。將較小的工作分組為較大的工作時,執行較大工作的成本會攤提,執行速度也會更快。合併影響相同資料的 DML 陳述式通常可提高 DML 工作效率,且較不可能超出佇列大小配額限制。如要進一步瞭解如何最佳化 DML 作業,請參閱「避免使用更新或插入單列的 DML 陳述式」。

如要提高 DML 效率,也可以為資料表設定分區或叢集。詳情請參閱「最佳做法」。

使用者沒有權限

錯誤字串:

  • Access Denied: Project [project_id]: User does not have bigquery.jobs.create permission in project [project_id].
  • User does not have permission to query table project-id:dataset.table.
  • Access Denied: User does not have permission to query table or perhaps it does not exist.

如果您在執行查詢的專案中沒有 bigquery.jobs.create 權限,無論您在含有資料的專案中擁有何種權限,都可能發生這些錯誤。

如果服務帳戶、使用者或群組在查詢參照的所有表格和檢視區塊中,沒有 bigquery.tables.getData 權限,也可能會收到這些錯誤。如要進一步瞭解執行查詢所需的權限,請參閱「必要角色」。

如果查詢的區域中沒有資料表 (例如 asia-south1),也可能會發生這類錯誤。您可以檢查資料集位置來驗證區域。

解決這些錯誤時,請注意下列事項:

  • 服務帳戶:服務帳戶必須在執行所在專案中具備 bigquery.jobs.create 權限,且在查詢參照的所有資料表和檢視區塊中具備 bigquery.tables.getData 權限。

  • 自訂角色:自訂 IAM 角色必須在相關角色中明確包含 bigquery.jobs.create 權限,且必須對查詢參照的所有資料表和檢視區塊具有 bigquery.tables.getData 權限。

  • 共用資料集:在其他專案中使用共用資料集時,您可能仍需具備專案的 bigquery.jobs.create 權限,才能在該資料集中執行查詢或工作。

如要授予資料表或檢視表的存取權,請參閱「授予資料表或檢視表的存取權」。

超出資源上限問題

如果 BigQuery 的資源不足以完成查詢,就會發生下列問題。

查詢超出 CPU 資源

錯誤字串:Query exceeded resource limits

如果隨選查詢使用的 CPU 相對掃描的資料量過多,就會發生這個錯誤。如要瞭解如何解決這些問題,請參閱「排解資源超出上限的問題」。

查詢超出記憶體資源

錯誤字串:Resources exceeded during query execution: The query could not be executed in the allotted memory

如果是 SELECT 陳述式,當查詢使用的資源過多時,就會發生這個錯誤。如要解決這個錯誤,請參閱「排解資源超出上限的問題」。

堆疊空間不足

錯誤字串:Out of stack space due to deeply nested query expression during query resolution.

如果查詢包含過多巢狀函式呼叫,就可能發生這個錯誤。有時,剖析期間會將查詢的部分內容轉換為函式呼叫。舉例來說,如果運算式含有重複的串連運算子,例如 A || B || C || ...,就會變成 CONCAT(A, CONCAT(B, CONCAT(C, ...)))

如要修正這項錯誤,請重新編寫查詢,減少巢狀結構的數量。

查詢執行期間資源超出上限

錯誤字串:Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: [percentage]% of limit. Top memory consumer(s): ORDER BY operations.

這類情況可能會發生在 ORDER BY ... LIMIT ... OFFSET ... 查詢中。由於實作細節,排序作業可能會在單一運算單元上進行,如果需要處理的資料列過多,可能會導致記憶體不足,特別是使用大型 OFFSET 時。LIMITOFFSET

如要解決這項錯誤,請避免在 ORDER BY ... LIMIT 查詢中使用較大的 OFFSET 值。或者,您也可以使用可擴充的 ROW_NUMBER() 視窗函式,根據所選順序指派排名,然後在 WHERE 子句中篩選這些排名。例如:

SELECT ...
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ...) AS rn
  FROM ...
)
WHERE rn > @start_index AND rn <= @page_size + @start_index  -- note that row_number() starts with 1

查詢超出隨機播放資源

錯誤字串:Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

當查詢無法存取足夠的重組資源時,就會發生這項錯誤。

如要解決這個錯誤,請佈建更多時段,或減少查詢處理的資料量。如要進一步瞭解如何解決這個問題,請參閱「洗牌配額不足」。

如要進一步瞭解如何解決這些問題,請參閱「排解資源超出上限的問題」。

查詢過於複雜

錯誤字串:Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

如果查詢過於複雜,就會發生這項錯誤。造成複雜度的主要原因如下:

  • WITH 深度巢狀或重複使用的子句。
  • 深度巢狀結構或重複使用的檢視區塊。
  • 重複使用 UNION ALL 運算子

如要解決這個錯誤,請嘗試下列做法:

  • 將查詢分割為多個查詢,然後使用程序語言,依序執行這些查詢並共用狀態。
  • 請改用暫時性資料表,而非 WITH 子句。
  • 改寫查詢,減少參照的物件和比較次數。

您可以使用 INFORMATION_SCHEMA.JOBS 檢視表中的 query_info.resource_warning 欄位,主動監控即將達到複雜度上限的查詢。下列範例會傳回過去三天內資源用量高的查詢:

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM
  <your_project_id>.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY
  query_info.query_hashes.normalized_literals
LIMIT
  1000

如要進一步瞭解如何解決這些問題,請參閱「排解資源超出上限的問題」。

排解超出資源上限的問題

查詢工作

如要最佳化查詢,請嘗試下列步驟:

  • 請嘗試移除 ORDER BY 子句。
  • 如果您的查詢使用 JOIN,請確保較大的資料表位於子句的左側。此外,請確認資料中沒有重複的聯結鍵。
  • 如果您的查詢使用 FLATTEN,請判斷這是否有必要。詳情請參閱巢狀與重複的資料
  • 如果您的查詢使用 EXACT_COUNT_DISTINCT,請考慮改用 COUNT(DISTINCT)
  • 如果您的查詢使用 COUNT(DISTINCT <value>, <n>),並搭配較大的 <n> 值,請考慮改用 GROUP BY。詳情請參閱 COUNT(DISTINCT)
  • 如果您的查詢使用 UNIQUE,請考慮改用 GROUP BY,或是位於 subselect 內部的窗型函式
  • 如果查詢使用 LIMIT 子句具現化許多資料列,請考慮篩選其他資料欄 (例如 ROW_NUMBER()),或完全移除 LIMIT 子句,以允許寫入平行化。
  • 如果查詢使用深度巢狀檢視區塊和 WITH 子句,複雜度可能會呈指數成長,進而達到限制。
  • 請勿使用 WITH 子句取代臨時資料表。子句可能需要重新計算多次,這會使查詢變得複雜,因此速度較慢。改為將中繼結果保留在臨時資料表中,有助於解決複雜度問題
  • 避免使用 UNION ALL 查詢。
  • 如果查詢使用 MATCH_RECOGNIZE,請修改 PARTITION BY 子句來縮減分割區大小,或新增 PARTITION BY 子句 (如果沒有的話)。

詳情請參閱下列資源:

載入工作

如果您載入 Avro 或 Parquet 檔案,請縮減檔案中的列大小。檢查要載入的檔案格式是否有特定大小限制:

如果載入 ORC 檔案時出現這則錯誤訊息,請與支援團隊聯絡

Storage API:

錯誤字串:Stream memory usage exceeded

在 Storage Read API ReadRows 呼叫期間,部分記憶體用量高的串流可能會收到 RESOURCE_EXHAUSTED 錯誤,並顯示這則訊息。如果讀取的表格很寬,或是結構定義複雜,就可能發生這種情況。為解決這個問題,請選取較少的資料欄來讀取 (使用 selected_fields 參數),或是簡化資料表結構定義,以縮小結果列的大小。

排解連線問題

下列各節說明如何排解嘗試與 BigQuery 互動時的連線問題:

將 Google DNS 加入允許清單

使用 Google IP Dig 工具,將 BigQuery DNS 端點 bigquery.googleapis.com 解析為單一「A」記錄 IP。請確認防火牆設定未封鎖這個 IP。

一般來說,我們建議將 Google DNS 名稱加入允許清單。https://www.gstatic.com/ipranges/goog.jsonhttps://www.gstatic.com/ipranges/cloud.json 檔案中分享的 IP 範圍經常變更,因此建議改為將 Google DNS 名稱加入允許清單。以下是建議加入許可清單的常見 DNS 名稱:

  • *.1e100.net
  • *.google.com
  • *.gstatic.com
  • *.googleapis.com
  • *.googleusercontent.com
  • *.appspot.com
  • *.gvt1.com

找出捨棄封包的 Proxy 或防火牆

如要找出用戶端和 Google Front End (GFE) 之間的所有封包躍點,請在用戶端電腦上執行 traceroute 指令,這項指令會醒目顯示捨棄導向 GFE 封包的伺服器。以下是 traceroute 指令範例:

traceroute -T -p 443 bigquery.googleapis.com

如果問題與特定 IP 位址有關,您也可以找出特定 GFE IP 位址的封包躍點:

traceroute -T -p 443 142.250.178.138

如果發生 Google 端逾時問題,您會看到要求一路傳送至 GFE。

如果發現封包從未抵達 GFE,請與網路管理員聯絡,以解決這個問題。

產生 PCAP 檔案並分析防火牆或 Proxy

產生封包擷取檔案 (PCAP),並分析該檔案,確保防火牆或 Proxy 不會篩除傳送至 Google IP 的封包,且允許封包傳送至 GFE。

以下是可使用 tcpdump 工具執行的指令範例:

tcpdump -s 0 -w debug.pcap -K -n host bigquery.googleapis.com

針對間歇性連線問題設定重試機制

在某些情況下,GFE 負載平衡器可能會捨棄來自用戶端 IP 的連線,例如偵測到 DDOS 流量模式,或是負載平衡器執行個體縮減規模,導致端點 IP 遭到回收。如果 GFE 負載平衡器中斷連線,用戶端必須擷取逾時要求,然後對 DNS 端點重試要求。請務必不要使用相同的 IP 位址,直到要求最終成功為止,因為 IP 位址可能已變更。

如果您發現 Google 端持續發生逾時問題,且重試無效,請與 Cloud Customer Care 團隊聯絡,並附上執行 tcpdump 等封包擷取工具後產生的最新 PCAP 檔案。

後續步驟