排解 BigQuery 連接器錯誤和限制

將數據分析連結至 BigQuery 時，可能會發生逾時、SQL 語法限制、配額限制或 VPC Service Controls 錯誤。本指南說明 BigQuery 連接器常見的問題，展開「解決步驟」，查看並修正問題。

查詢和 SQL 語法錯誤

Google 數據分析中的自訂 SQL 查詢有特定限制。如果查詢違反這些限制，可能會發生錯誤。

Field is ambiguous 加入錯誤

如果自訂查詢含有重複的資料欄名稱，圖表會顯示這項錯誤：

User Configuration Error: Field is ambiguous

錯誤訊息文字：使用者設定錯誤

原因：已聯結的資料表 (JOIN) 不能共用重複的資料欄名稱。舉例來說，如果您在 Criteria_ID 欄位中，聯結兩個具有相同結構定義的資料表，產生的虛擬資料表就會有重複的資料欄 (Criteria_ID、Parent_ID、Name)，進而觸發不明確錯誤。

解決步驟

使用 AS 關鍵字或 EXCEPT 子句，為所有資料欄名稱設定專屬名稱：

方法 1：使用別名明確重新命名重複的欄位

SELECT *
FROM (
  SELECT
    Criteria_ID AS Criteria_ID_1,
    Parent_ID AS Parent_ID_1,
    Name AS NAME_1
  FROM
    `project.dataset.table_1` ) AS table_1
LEFT JOIN (
  SELECT
    Criteria_ID AS Criteria_ID_2,
    Parent_ID AS Parent_ID_2,
    Name AS NAME_2
  FROM
    `project.dataset.table_2` ) AS table_2
ON
  table_1.Criteria_ID_1 = table_2.Criteria_ID_2;

方法 2：使用 EXCEPT 排除及重新命名特定欄位

如果只需要重新命名少量欄位，並保留其餘欄位，請使用 EXCEPT：

SELECT * EXCEPT (city), city AS city_1 FROM `project.dataset.table_1`

自訂 SQL 查詢語法錯誤 (多個陳述式)

如果自訂 SQL 查詢包含變數或多個陳述式 (DECLARE、SET)，就會失敗。

原因：數據分析 會在外部 SELECT 查詢 (SELECT * FROM (<your_custom_sql>)) 內執行 SQL，因此查詢必須是單一 SELECT 陳述式。

舉例來說，下列查詢會失敗，因為查詢在選取前宣告了變數：

DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;
SELECT total_bytes_billed / (1024 * 1024) * cost_per_tb_in_dollar / (1024 * 1024) FROM `billing_table`;

解決步驟

使用一般資料表運算式 (CTE 或 WITH 子句)，將計算合併為一個 SELECT 陳述式：

WITH constants AS (
  SELECT 4.2 AS cost_per_tb_in_dollar
)
SELECT
  total_bytes_billed / (1024 * 1024) * c.cost_per_tb_in_dollar / (1024 * 1024) AS cost
FROM `billing_table`, constants AS c;

效能和查詢逾時

如果查詢執行時間超過三到五分鐘，數據分析會在收到結果前逾時，並傳回 HTTP 504 Gateway timeout。

HTTP 504 Gateway timeout 或長時間執行的查詢錯誤

在數據分析中，自訂查詢或複雜的圖表彙整作業可能會在三到五分鐘後逾時，並傳回 HTTP 504 Gateway timeout 錯誤。

解決步驟

如果查詢經常逾時，請採取下列最佳化措施：

  • 啟用 BigQuery Storage Read API：啟用 BigQuery Storage Read API，提高資料輸送量。
  • 簡化查詢：移除不必要的 `JOIN` 指令、依較廣泛的時間範圍分組資料，以及只選取必要資料欄。
  • 使用 BigQuery BI Engine：透過 BigQuery BI Engine 預留容量，即可享有不到一秒的效能。
  • 使用資料庫檢視畫面：將自訂 SQL 儲存為 BigQuery 檢視畫面或具體化檢視畫面，然後直接將數據分析連結至該檢視畫面。
  • 預先匯總至報表資料表：在 BigQuery 中使用排程查詢，將摘要記錄寫入另一個資料表，然後查詢摘要資料表。

配額與資料表限制

如果資料集包含數千個資料表或傳回數百萬筆記錄，就可能發生限制錯誤：

資料集超過 5,000 個資料表時，使用者介面回應速度緩慢

嘗試使用 Google 數據分析中的表格選取清單連線至 BigQuery 資料集時，使用者介面會凍結或沒有回應。

原因：連接器每個資料集最多支援 5,000 個資料表。如果資料集超過 5,000 個資料表或檢視區塊，資料表選取清單就會逾時並凍結。

解決步驟

如要連線但不載入資料表清單，請使用下列其中一種替代方法：

  • 使用自訂查詢連線：選取「自訂查詢」，然後撰寫較不複雜的 `SELECT` 陳述式： 
    SELECT * FROM `your_project.your_dataset.your_table`
  • 直接從 BigQuery 連線：在 BigQuery 控制台中找出資料表，然後依序點選「匯出」或「探索資料」，並選取「使用 Looker Studio 開啟」。
  • 分割或重組資料集：將報表資料表移至較小的專屬報表資料集，這些資料集包含的資料表少於 5,000 個。

最多可傳回 200 萬列

如果資料集很大，圖表可能會顯示資料截斷警告，或省略超過 200 萬筆記錄的資料列。

原因：連接器每個圖表查詢最多會傳回 200 萬列。如果查詢超過 200 萬筆記錄，圖表會截斷資料並顯示警告。

解決步驟

如要避免資料遭到截斷，請採取下列做法：

  • 套用報表層級的日期篩選器，縮小查詢量。
  • 查詢需要分區篩選條件的日期分區資料表 (`DATE`、`DATETIME`、`TIMESTAMP`) (瞭解詳情)。
  • 在數據分析中以視覺化方式呈現資料前，請先在 BigQuery 中將高基數維度分組。

MEDIAN 和 PERCENTILE 變異數

在連結至 BigQuery 的圖表中計算確切中位數 (MEDIAN) 或百分位數 (PERCENTILE) 時，輸出結果可能與在其他 SQL 資料庫或 CSV 匯出項目中執行的相同計算略有不同。

原因：在 BigQuery 查詢中，MEDIAN 和 PERCENTILE 使用 APPROX_QUANTILES 近似匯總函式。這項功能可快速處理 PB 級資料集，但近似結果可能與 CSV 匯出或其他 SQL 資料庫執行的精確計算略有差異。

資料類型和加密錯誤

瞭解如何處理不支援的資料欄類型，以及機構強制執行的金鑰加密政策。

CONDITION_NOT_MET 加密錯誤 (CMEK)

查詢資料集時，圖表會失敗並傳回下列錯誤：

User Configuration Error: CONDITION_NOT_MET

原因：連接器不支援客戶自行管理的加密金鑰 (CMEK)。如果機構政策要求查詢或暫時儲存空間必須使用 CMEK 加密 (組織政策服務)，圖表會顯示 User Configuration Error: CONDITION_NOT_MET。

解決步驟

請與機構管理員合作，將報表專案從 CMEK 政策中排除，或將報表資料匯出至受標準 Google-owned and Google-managed encryption keys控管的資料集。

不支援 TIME 資料類型

如果連線的 BigQuery 資料表含有 TIME 資料類型 (例如 23:59:59) 的資料欄，Google 數據分析會將欄位轉換為 TEXT，導致無法依時間排序或彙整資料。

原因：Google 數據分析原生不支援 BigQuery 的 TIME 資料類型 (例如 23:59:59)。連接器會在擷取時將 TIME 資料欄轉換為 TEXT 字串，導致無法依時間排序。

解決步驟

請使用下列其中一種解決方法，將 `TIME` 資料欄轉換為 DATETIME 物件：

解決方法 1：使用自訂 SQL 查詢

直接在 SQL 中將 `TIME` 欄位與基準日期 (`1970-01-01`) 合併：

SELECT
  *,
  -- Combine a dummy date (1970-01-01) with your TIME field
  DATETIME(DATE "1970-01-01", your_time_field) AS time_as_datetime
FROM
  `your_project.your_dataset.your_table`
  • 結果：數據分析會將 `time_as_datetime` 擷取為「日期和時間」**欄位。
  • 格式設定：在報表圖表屬性中，將欄位的「顯示格式」變更為「小時」、「分鐘」或自訂時間格式 (`h:mm:ss`)，即可只顯示時間部分 (瞭解詳情)。

解決方法 2：在數據分析中建立計算欄位

如果沒有修改 SQL 查詢，請在資料來源中建立計算欄位：

PARSE_DATETIME("%H:%M:%S", CAST(your_time_field AS TEXT))
  • 結果： `PARSE_DATETIME` 函式會將文字字串解析為「日期和時間」物件，並將預設的日曆日期設為 1970 年 1 月 1 日 (瞭解詳情)。

VPC Service Controls 錯誤

在 service perimeter 中工作時，排解存取遭拒錯誤和背景程序限制。

Service Control Failure (透過非 VPN 連線查看報表時)

在貴機構的 VPN 或公司網路以外查看報表時，部分或所有圖表都會失敗，並顯示下列錯誤：

Service Control Failure

原因：連結器會將報表檢視者的 IP 位址傳遞至 BigQuery，以驗證 VPC Service Controls 的 IP 位址存取層級。複製報表時，副本中的舊版或「虛擬」自訂 SQL 資料來源可能會參照服務範圍內受保護的帳單專案，即使主要資料集位於範圍外也是如此。

解決步驟

找出並移除或重新指派報表中任何受隱藏的邊界限制帳單專案：

  1. 請複製受影響的報表，安全地排解問題。
  2. 在副本的報表編輯器中，前往「資源」>「管理已新增的資料來源」。
  3. 檢查附加至報表的每個內嵌 **BigQuery** 或 **自訂 SQL** 資料來源。
  4. 編輯每個自訂 SQL 連線，確認已設定 **帳單專案**。如果任何資料來源指向受 VPC Service Controls 範圍保護的帳單專案，請更新資料來源，改用未受保護的帳單專案，或刪除不再使用的資料來源。

VPC Service Controls 導致電子郵件排程傳送或圖表快訊失敗

如果連結至受 VPC Service Controls 保護的 BigQuery 資料集的圖表執行自動背景功能 (例如排定電子郵件傳送時間或圖表快訊)，系統會傳送排定的電子郵件，但不會附上報表內容或附件，或是無法觸發快訊 (VPC Service Controls unexpected field in error map)。

原因：自動化背景功能 (例如排定電子郵件傳送時間或圖表快訊) 會以背景工作形式執行，沒有使用者 IP 位址，因此 VPC Service Controls (VPC-SC) 在評估以 IP 為依據的存取層級 (VPC Service Controls unexpected field in error map) 時，會封鎖這些功能。

解決步驟

如要在 VPC Service Controls 範圍後方使用自動化背景功能，請設定資料來源以使用服務帳戶憑證，或建立以身分為依據的存取層級：