【Explanation】GCP FinOps— BigQuery Cost Optimisation Best Practices Summary
TL;DR
實施 FinOps 不是一次性活動，而是一項持續的紀律和旅程，每個組織都應該將其作為其雲端策略的一部分進行投資，以下提供幾種可以省下預算的方法
- 選擇正確的定價模型
- 設計 Partition 分區和 Clustering
- 查詢
- 避免執行查詢來探索表數據
- 查詢前使用工具進行估算與控制費用
- 建立意外錯誤的控制措施 — 設定查詢上限
- 使用 INT64 列進行 ORDER BY 或 JOIN
- 設定表過期時間
放置在第一點是覺得滿重要的，首要得要先了解、評估組織內的使用場景以才能選擇對的付費模型，其中需要留意的有儲存與運算需要留意。儲存的模式可以選擇 logical 或 physical 兩種不同的計價模型，而超過 90 天不對資料更改，也可以採用長期儲存方案。而 on-demand billing 或使用 Capacity billing 購置 Slots 也在 FinOps 白皮書列為 Low effort/High savings 首選。
2. 設計 Partition 分區和 Clustering
📙【Reference】GCP Tips — BiqQuery useful tips
📙【GCP Blog】ost optimization best practices for BigQuery
消除 BigQuery 浪費的最重要技能是設計資料庫結構並以最小化查詢掃描的資料量的方式設計查詢。我們首先使用時間對表進行分區，然後在查詢中添加一個僅掃描我們需要的分區的條件，從而顯著降低了 BigQuery 成本。
建議是盡可能對資料進行 Partition，這有助於降低處理查詢的成本並提高效能。可以根據時間、日期或任何時間戳列對資料進行 Partition。假設對 sales 包含過去 12 個月資料的表格進行分割。這會導致包含每天資料的較小Partition。Partition後，可以對資料再進行 Clustering。
3. 查詢
- 執行前檢查查詢成本
- 避免選擇 *
- 忘記掉地端的 limit
- 使用預覽功能取代 Select * FROM <table> 的習慣
執行前檢查查詢成本
實際的查詢成本取決於多個因素，如數據大小、查詢複雜性等。在實際執行查詢之前，建議可以多使用這種方法來估算其成本以便做出更好的判斷。這段程式碼使用 dry_run=True，表示僅模擬查詢而不真正執行它。通過調用query_job.total_bytes_processed，可以獲取模擬查詢所使用的總字節數。然後，將其轉換為GB來計算估計的查詢成本。
from google.cloud import bigquery
project_id = 'your-project-id'
query = 'SELECT * FROM your_dataset.your_table'
def check_query_cost():
client = bigquery.Client(project=project_id)
job_config = bigquery.QueryJobConfig(dry_run=True)
query_job = client.query(query, job_config=job_config)
query_cost = query_job.total_bytes_processed / (1024 * 1024 * 1024) # 轉換成GB
print(f"Estimated query cost: {query_cost:.2f} GB")
check_query_cost()避免選擇 * + 忘記掉地端的 limit
避免使用 “Select * FROM <Table>”，因為這樣 BigQuery 會讀取資料集中所有的數據，另外 limit 也忘了吧！它不會改變您的帳單，你 limit 查詢 100 行或 1,000,000 行，得到的費用仍然不變。
4. 避免執行查詢來探索表數據
可以改使用預覽功能取代 Select * FROM <table> 的習慣，避免僅為了預覽資料而使用「select * from <table>」。每當您這樣做時，您就需要向 Google 付費，您可以轉到表格並點擊「預覽」，就可以在那裡看到所有資料和所有列及其名稱
5. 查詢前使用工具進行估算與控制費用
將 QueryJobConfig.dry_run 屬性設定為True。如果提供了試運行查詢配置，Client.query() 將始終傳回已完成的 QueryJob。
# result
This query will process 65935918 bytes【GCP Docs.】📙 Query a public dataset with the BigQuery client libraries
6. 建立意外錯誤的控制措施 — 設定查詢上限
只是關鍵欄位下錯多查找幾 GB 大小，可能會花費你幾美分，這對大多數企業來說是可以接受的。但是，當您的資料集表的量級為 TB 或 PB，且由可能會有多個使用者存取時，錯誤的查找可能會導致巨大的查詢成本。建議設定要收費最大位元組數，如果即將超出限制，查詢將失敗並且不會收費
使用 bq
bq query --maximum_bytes_billed=1000000 \
--use_legacy_sql=false \
'SELECT
word
FROM
`bigquery-public-data`.samples.shakespeare'使用 Python 並可以用成 framework tool 加以包裝後提供 N users
【GCP Docs.】📙 Python Class QueryJobConfig (3.17.0)
from google.cloud import bigquery
first_query = ____ # Your code goes here
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
first_query_job = client.query(first_query, job_config=safe_config)
# API request - run the query, and return a pandas DataFrame
first_results = first_query_job.to_dataframe()
# View top few rows of results
print(first_results.head())
# 若超出查詢的上限話
google.api_core.exceptions.InternalServerError: 500 Query exceeded limit
for bytes billed: 1000. 66060288 or higher required
7. 使用 INT64 列進行 ORDER BY 或 JOIN
每當您在 BigQuery 中連接或按表排序時，請嘗試使用 INT64 格式。如果列是 INT64，因為是只有 8 bytes 相較 string 會小很多，因此每個資料都會花費較少的執行時間，因為以這種方式連接表的效率要高得多，例如包括若有顧客主檔 Cust_Id，可以改由 Index 或 serial_num 的 INT64 來取代原先為 Cust_Id string type。
8. 設定表過期時間
也可以使用 project.dataset_id.INFORMATION_SCHEMA.TABLE_OPTIONS 查詢
SELECT *
FROM fubar.demo.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
option_name = 'expiration_timestamp';
Reference
- BigQuery Cost Optimisation Best Practices and Techniques
- How to estimate BigQuery costs
- 15 Solutions to Decrease BigQuery costs in 2023
- A guide to BigQuery INFORMATION_SCHEMA
- 【GCP Docs】Query a public dataset with the BigQuery client libraries
- 【GCP Docs】Python Class QueryJobConfig (3.17.0)
- 【GCP Blog】Cost optimization best practices for BigQuery
- 【GCP Blog】Learning more about FinOps on Google Cloud
