管理輸入資料和資料來源

評估您的輸入資料時,請考量所需 I/O。您的查詢會讀取多少位元組?您是否有妥善限制輸入資料量?您的資料是位於原生的 BigQuery 儲存空間或外部資料來源?查詢作業讀取的資料量,以及影響查詢效能與成本的資料來源為何?

您可以使用查詢計劃說明檢驗查詢讀取輸入資料的方式。

以下最佳做法提供控管輸入資料與選擇資料來源的相關指引。

投影控管 - 避免使用 SELECT *

最佳做法:投影控管 - 僅查詢所需欄位。

投影指的是查詢作業讀取的欄位數。投影過多欄位會導致額外 (無謂浪費的) I/O 與實質化作業 (寫入結果)。

使用 SELECT * 是查詢資料最昂貴的方法。當您使用 SELECT * 時,BigQuery 會完整掃描資料表中的每個資料欄。

如要進行資料實驗或是探索資料,請使用其中一個資料預覽選項,而非 SELECT *

LIMIT 子句套用至 SELECT * 查詢不會影響讀取的資料量。系統不僅會收取您讀取整個資料表中所有位元組的費用,也會收取超過免費版配額的查詢數費用。

相反地,只要查詢需要的資料欄即可。例如,使用 SELECT * EXCEPT 將一或多個資料欄排除在結果之外。

如果您需要查詢資料表中的每個資料欄,但僅要查詢資料子集,請考慮:

  • 在目的地資料表中將結果具體化並改為查詢該資料表
  • 依照日期將資料表分區並查詢相關分區;例如,WHERE _PARTITIONDATE="2017-01-01" 只掃描 2017 年 1 月 1 日的分區

查詢資料子集或使用 SELECT * EXCEPT 可大幅降低查詢作業讀取的資料量。除了節省成本外,降低資料 I/O 量與查詢結果所需的實質化作業量還可提升效能。

修整分區查詢

最佳做法:查詢分區資料表時,請使用 _PARTITIONTIME 虛擬資料欄來篩選分區。

查詢分區資料表時,使用 _PARTITIONTIME 虛擬資料欄。使用 _PARTITIONTIME 篩選資料即可指定單一日期或日期範圍。例如,下列 WHERE 子句使用 _PARTITIONTIME 虛擬欄位指定 2016 年 1 月 1 日至 2016 年 1 月 31 日之間的分區:

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
    AND TIMESTAMP("20160131")

查詢作業僅會處理日期範圍指定的分區資料,進而降低輸入資料量。篩選分區可提升查詢效能並降低成本。

盡可能將資料去標準化

最佳做法:BigQuery 在資料去標準化時效能最好。其中不保留星狀或雪花狀結構定義等關聯結構定義,改為將資料去標準化並善用巢狀與重複的資料欄。巢狀與重複的欄位可維持資料間的關係,但不會因為保留關聯式 (標準化) 結構定義而犧牲效能。

將資料標準化以節省儲存空間並非現今系統的關注焦點。將資料去標準化雖會提高儲存空間成本,但用來換取效能的提升絕對划算。Join 作業需要資料協調 (通訊頻寬),去標準化作業則可將資料本地化至個別運算單元,因此二者可平行執行。

如果您需要在將資料去標準化的同時維持關係,請使用巢狀與重複的欄位而非完全整併您的資料。將關聯資料完全整併後,網路通訊 (隨機排列) 可能會對查詢效能造成負面影響。

例如,若要將訂單結構定義去標準化而不使用巢狀與重複的欄位,您可能需要按照 order_id 之類的欄位進行分組 (存在一對多關係時)。由於資料分組作業涉及隨機排列,因此效能將不如使用巢狀與重複欄位將資料去標準化。

在部分情況下,將資料去標準化並使用巢狀與重複的欄位可能無法帶來效能的提升。請避免在下列用途中進行去標準化:

  • 您的結構定義為星狀且維度變更頻繁。
  • BigQuery 會進行列層級的異動以搭配線上交易處理 (OLTP) 系統,但無法加以取代。

使用巢狀和重複的欄位

BigQuery 不需要完全整平的去標準化作業。您可以使用巢狀與重複欄位來保留關聯性。

  • 巢狀資料 (STRUCT)

    • 巢狀資料可用於以內嵌方式呈現外部實體。
    • 查詢巢狀資料時使用 dot 語法參照 Leaf 欄位,這與使用 Join 的語法類似。
    • 巢狀資料會在標準 SQL 中以 STRUCT 類型呈現。
  • 重複資料 (ARRAY)

    • 建立類型為 RECORD 的欄位並將模式設為 REPEATED 以保留內嵌的一對多關係 (只要關係並非高基數)。
    • 如果使用重複資料,資料重組就非必要。
    • 重複資料會以 ARRAY 形式呈現。您可以在標準 SQL 中使用 ARRAY 函式查詢重複資料。
  • 巢狀和重複的資料 (STRUCTARRAY)。

    • 巢狀和重複資料彼此互補。
    • 例如,在交易記錄資料表中,您可以納入明細項目 STRUCT 的陣列。

妥善使用外部資料來源

最佳做法:如果要以查詢效能為優先,建議不要使用外部資料來源。

查詢 BigQuery 管理的儲存空間中的資料表通常比查詢 Cloud Storage、Google 雲端硬碟或 Cloud Bigtable 中的外部資料表快。

請針對下列用途使用外部資料來源:

  • 載入資料時執行擷取、轉換及載入 (ETL) 作業
  • 變更頻繁的資料
  • 定期載入作業,例如週期性地從 Cloud Bigtable 擷取資料

避免使用過多萬用字元資料表

最佳做法:查詢萬用字元資料表時,儘可能使用最詳細的前置字串。

透過精簡的 SQL 陳述式使用萬用字元查詢多個資料表。萬用字元資料表是指符合萬用字元運算式的資料表集合。如果資料集包含下列項目,萬用字元資料表就相當實用:

  • 多個具備相容結構定義且命名類似的資料表
  • 資料分區資料表

查詢萬用字元資料表時,請在通用資料表前置字串後指定萬用字元 (*)。例如,FROM bigquery-public-data.noaa_gsod.gsod194* 會查詢自 1940 年代以來的所有資料表。

相對於較短的前置字串,較詳細的前置字串效果較好。例如,FROM bigquery-public-data.noaa_gsod.gsod194* 的效果比 FROM bigquery-public-data.noaa_gsod.* 來得好,因為符合萬用字元的資料表較少。

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁