BigQuery教學，成本優化三大指南！ 教你讓企業花更少用更好

經營與管理數據倉儲令人傷神且耗時，尤其現在數據無處不在，更存在於我們的一言一行。調整系統規模以配合超高的數據成長，對維護日常的運作也成了日益艱鉅的挑戰。用最少的停機時間升級數據倉儲，並支援機器學習與人工智慧以滿足業務需求，更是繁瑣。企業選擇 Google Cloud 的 BigQuery -無伺服器的企業數據倉儲，是為了專心於分析並提高生產力，而不是管理基礎建設。

延伸閱讀：BigQuery 是什麼？與傳統倉儲不同，4 大優勢助你分析大數據資料！

一旦使用了 BigQuery，就可以用閃電般的速度進行查詢，透過串流取得即時的洞察，並開始使用先進的預測性分析。但這不表示您儲藏在 BigQuery 的數據沒有進一步的優化空間。在這雲端計算時代，成本是科技決策背後的主要動力之一，我們發現企業最在意自然是關於計價細節和如何持續優化成本。

因此我們歸納這份操作清單，協助企業用於優化 BigQuery 成本，不僅如何更可以進一步優化您的業務成果。在 BigQuery 中優化成本有一項好處，是因為其無伺服器的架構讓這些優化能產生更好的效能，您就不用為了成本跟效能妥協。

首先我們先了解 BigQuery 的訂價，再接著再深入探討各子項的計價，為您的 BigQuery 費用提供秘訣。不管任何資料中心，BigQuery 的訂價分為二大項如下：

儲存

• 動態儲存月費（Active storage）
• 長期儲存月費（Long-term storage）
• 串流插入（Streaming inserts）

查詢處理

• 以量計價（On-demand）
• 固定費率（Flat-rate）

在我們進一步探討這些項目之前，以下為 BigQuery 作業在任何資料中心皆免費提供的項目：

• 批次載入（Batch loading data into BigQuery）
• 自動重新分組：無須設定與維護（Automatic re-clustering which requires no setup and maintenance)
• 匯出數據作業（Exporting data operation）
• 刪除表格、視圖、分隔、功能、數據組（Deleting table, views, partitions, functions and datasets ）
• 元資料作業（Metadata operations）
• 暫存查詢（Cached queries）
• 導致錯誤的查詢（Queries that result in error）
• 每月前 10GB 儲存免費（Storage for first 10 GB of data per month）
• 每月前 1TB 查詢數據處理，對於選擇隨選訂價的使用者而言有（Query data processed for first 1 TB of data per month : advantageous to users on on-demand pricing）

一旦數據載入 BigQuery，費用就會按照每秒儲存到表格的數據量計算。以下是提供優化您的 BigQuery 儲存成本的小秘訣。

1.數據組期限：只有在需要的時候保留數據

預設中，以 BigQuery 縱向存儲格式（Capacitor columnar data format）儲存的數據都已經加密並壓縮。若在數據組中，無須永久保留的臨時數據，可以設定表格期限時間。

例如在這個例子中，我們只須查詢臨時天氣數據組，直到下游作業清除數據並推送到生產數據組為止。這裡，我們可以設定預設表格到期日為七天。

請注意，如果您更新數據組的預設表格到期日，這只會適用於新建立的表格。使用 DDL 語句則會更改現有表格。

BigQuery 也提供在同一數據組內設定不同表格到期日的彈性。所以在同一數據組中，如果名為 new_york 的表格需要更長的數據保留時間。

如上圖所示，new_york 的數據會保留六個月，因為我們尚未設定 california 的表格到期日，其到期日就是預設的七天。

專家建議：和資料組層級與表格層級相似，您也可以設定每一個分隔層級的到期日。查看 BigQuery 官方說明文件，瞭解預設行為。

2.注意編輯資料的方式

若您的表格或分區資料表在過去 90 天都沒有編輯過，儲存在該表格內的數據價格會自動下降約 50%。但是在效能、耐久度、可用性，或任何其他功能皆不會因為表格或分區資料表被視為長期儲存而有所衰減。

要充分運用長期儲存，所有編輯表格數據的操作都要注意，例如串流、複製、載入數據，包括所有 DML 或 DDL 操作。這會讓您的數據恢復成使用中儲存，並重設 90 天的期間。為避免這種狀況，您可以考慮將新一批次的數據載入新表格或表格分隔。

專家建議：查詢表格數據連同一些操作不會影響重設 90 天期間，您的價格依然被視為長期儲存。

在大部分狀況下，保留數據在 BigQuery 裡面是有好處的，除非您確定表格裡的數據最多一年存取一次，例如因法規或法令之故而儲存的檔案庫。在這種狀況下，可瞭解取得比 BigQuery 長期儲存更優惠的價格像是匯出表格數據到雲端儲存的 Coldline 類別。

3.避免重複複製數據

BigQuery 使用聯合數據(federated data)存取模型讓您直接從外部數據來源查詢資料，例如 Cloud Bigtable、Cloud Storage﹑Google Drive、Cloud SQL。這對於避免重複複製數據很有用，進而減少儲存成本。這對於從外部來源一次讀取數據，或是存取少量經常變更但不需要每次改變時都載入到 BigQuery 的數據很有用。

專家建議：依據最為佳的使用技術指南。通常，用外部來源執行的查詢，效能比在 BigQuery 上查詢相同數據的還差，這是因為儲存在 BigQuery 的數據是列儲存格式，產生 query 的效能更佳。

4.查看您是否使用串流插入載入數據

檢查您上個月的 BigQuery 帳單，查看是否更改為串流插入。若是如此，請問問自己：「我的 BigQuery 是否需要 即時性的數據（幾秒鐘，而非幾小時）？」或「是否只要數據一出現在 BigQuery，我就會即時使用它？」如果有答案是否定的，建議您切換為批次載入數據，因為這是完全免費的。

專家建議：只有在 BigQuery 中的數據會立即被下一個管線處理 (pipeline) 的狀況下，才使用串流插入。

5.瞭解 BigQuery 的備份與災難復原（DR）流程。

BigQuery 保留表格過去七日內的更改，讓您可以查詢數據當時的截圖。這表示您可以不用重新載入復原備份，就能還原過往數據。如果表格被刪除，兩天後會刷新其歷史紀錄。

要從一小時前的表格截圖找到行數，使用以下查詢：

瞭解更多關於資料表修飾符（Table decorators ）的範例。

專家建議：如果是關鍵的業務數據，尤其是如果您在區域性地點使用 BigQuery 於區域性地點的話，請依照數據災難恢復原方案指引進行數據備份。

您可能會查詢 BigQuery 數據用來分析並滿足商業用途，例如預測性分析、即時庫存管理，或是公司財務數據的單一事實來源。

隨選訂價是大部分使用者和企業選擇使用 BigQuery 時的原因。收費是按照處理的bytes(位元數)計算，不管是儲存在 BigQuery 上的數據還是涉及的外部數據來源。有幾種方式可以減少處理的位元數。讓我們一步步透過的實作說明如何減少查詢成本，例如 SQL 指令、作業、使用者定義功能等等。

因為採取列儲存結構儲存數據，BigQuery 可以提供驚人的效能。這也表示SELECT * 是最昂貴的數據查詢方式。這是因為執行SELECT *會橫跨表格內存在的所有欄位的完整查詢掃描，也包括您可能不需要的資料。（我們懂您可能在上個月使用多次 SELECT * 所帶來的罪惡感 XD）

讓我們看看以下這個例子，了解提出一次查詢會處理多少數據。這是我們查詢一個 BigQuery 裡面有的公開氣候資料集：

可以看到，藉由選擇必要的欄位，我們可以減少大約八倍的處理數據，這是優化成本的快速方式。同時請注意，在查詢中使用限制條文不會影響成本。

專家建議：若您在下 Query 查詢之前，必須探索數據並瞭解其語意，您隨時可以使用免費的數據預覽選項。

也請記得，在查詢執行的第一階段，所處理的數據位元就會向您收費。避免只是為了優化中途階段處理的位元組，而建立複雜的多階段查詢，因為這不會影響成本（雖然可能會提升效能）。

專家建議：越早重視經常使用的查詢語法，因為這可以降低成本並提高在 BigQuery 的效能。

上述查詢是以 GB 的量計算，一個失誤就可能花費幾美分，這對大部分公司而言還可接受。但是如果您的數據組表格是以 TB 或 PB 計算，而且有很多人可以存取時，在不知不覺中查詢所有欄位可能造成龐大的查詢成本。

在這種狀況下，可透過設定最大位元計費來限制查詢成本。超過此一限制就會造成查詢失敗，而不會產生查詢成本，如下圖所示。

為什麼客製化的控制這麼重要？來看這個例子。假設您在美國多個區域共有 10 TB 的數據，每個月的儲存費用約 200 美元。如果一個月有 10 位使用者使用 [ SELECT * ..] 掃描所有數據 10 次，您的 BigQuery 帳單大約會變成 5,000 美元，因為總計您每個月都掃描 1 PB 的數據。使用考慮過的限制額度可幫助您避免這類的意外。最後請注意，即使您中途取消已執行中的查詢，此時還是會產生完整的查詢成本。

專家建議：除了在查詢層級上自訂成本管控，您也可以應用類似的邏輯到使用者和專案層級(gcp project)。

快取能夠提高查詢效能，且您不會因為從快取表格中取得的結果被額外的費用。快取功能預設是開啟的，您可以的 GCP 機台的查詢編輯器中點選更多 -> 查詢設定進行檢查，如下所示。

還有要記得快取是按照使用者和專案區分的。

一個實際的例子，如果您的 BigQuery 支援 Data Studio dashboard ，而且成千上萬的使用者可以存取，就有必要啟用快取功能，快取的內容將可提供多位使用者的查詢。

專家建議：要大幅增加橫跨多位使用者的快取 hit ，使用單一服務帳戶查詢 BigQuery，或使用社區連結器，如 Next ‘19 的影片所示。

BigQuery 中盡可能使用分割表格，這有助於減少處理查詢的成本並提高效能。現在，您可以依據攝取時間、日期，或任何時間戳記欄位分割表格。假設您分割了一個包含過去 12 個月的數據的銷售表格。這會導致較小的分隔含有每天的數據，如下所示。

現在若您查詢分析 8 月份的銷售數據，您只須支付在這 31 個分隔（partition）裡處理的數據，而不是整份表格。

另一個好處是，如之前提到的，每一個分隔（partition）都被視為不同的長期儲存（long-term）。以上面的例子而言，銷售數據在過去幾個月內經常被載入和修改。但剩下的分隔（partition）在過去 90 天內沒有修，如此可以為您節省了的儲存成本。要真正獲得分割表格的好處，您應該充份使用分隔欄位篩選表格。

專家建議：在建立或更新分割表格時，您可以啟動「要求分割篩選」功能強制使用者納入指定分割欄位的 WHERE 子句，否則查詢結果會出現錯誤。

資料進入分割區後，您現在可以將表格叢集編組按照最多四個欄位的內容組織數據。BigQuery 接著會依據指定的欄位順序排序數據，並組成一個區塊。您使用這些欄位進行查詢篩選時，BigQuery 會聰明地使用稱為區塊修整的功能，僅掃描相關區塊。

例如以下，業務部的管理階層需要一個 dashboard 來呈現某一位業務的相關指標。在 sales_rep 欄位啟動叢集編組順序是個好策略，因為這會一直被當成篩選器使用。如下所示，您可以看到 BigQuery 僅掃描了一個分隔（2019 年 9 月 1 日），還有兩個可以找到業務 Bob 和 Tom 的區塊。其他在這個分隔裡的區塊都被刪減，這會減少處理的位元數，與相關的查詢成本。

您可以在叢集編組找到更多資訊。

專家建議：叢集編組僅能使用在已分割的數據上。您隨時都可以按照攝取的數據進行分割，或是加入虛擬日期或時間戳欄位以利啟動表格的叢集編組。

一旦您的 BigQuery 月費超過每月 10,000 美元，請檢查您的 BigQuery 查詢處理成本，看看是否單一費率訂價更具成本效益。單一費率讓您能以固定的每月成本處理無限的數據查詢，而不是像隨選計價，按照所處理資料量的位元計費。在註冊期間，您可以購買查詢處理容量，按照 BigQuery 運算單元計算。最低單一費率訂價從 500 個運算單元起。決定要購買多少運算單元的一個好決策參考點，是使用 Stackdriver 將上個月的運算單元視覺化變可知道完整的使用量。

註：若您的查詢超過單一費率用量，BigQuery 執行將會按照比例減緩，直到這些運算單元再次釋出。

您可能會認為採用單一費率，就完全不用擔心優化查詢成本的問題了，但實際上效能還是會受到影響。您的查詢（作業）在相同時間內執行得愈快，意謂者您能夠在固定運算單元中完成的作業量就愈多。想通了這一點的話，你就會明白這就算是就是成本優化！

再購買單一費率用後，你可能會遇到有些特定的情況，首先買的運算單元太少可能影響效能，或者買太多則會出現多餘容量而影響成本。為了找到您的最佳比例，建議您可以從單一月費計畫開始，更為靈活使用，可以在 30 天後降級或取消費率，一旦您對於自己需要的運算單元量有了完整的掌握估算，再轉為單一年費計畫進一步節省費用。

專家建議：您隨時都可以在 GCP 組織中選用隨選費率和單一費率，如此的混合費率做法有助於節費最大化。

想要有效使用 BigQuery，完整得到現代數據倉儲的所有好處。如果您不結合工作監控進度並將您的成果視覺化，一切都將徒勞無功。在您採取任何行動前，先快速產出一份上個月的 BigQuery 使用量報告，以快速瞭解成本。然後您就可以做出決策，將未來幾天、幾個月要採取的成本優化行動排出一個優先順序，並使用 Data Studio dashboard 分析這會如何影響不同的策略指標。

一旦執行成本優化，您應該可以看到 BigQuery 帳單支出明顯減少，想了解更多關於 BigQuery 的資訊，請參考延伸閱讀，它將協助您使用新一代的雲端倉儲系統。

本文編譯自：《Cost optimization best practices for BigQuery》