過往在估算 Google BigQuery 整體成本時，主要會考量三個因素，即資料儲存、長期儲存和查詢資料使用，近期 Google Cloud Platform 在 2023 年 7 月正式推出 Physical Bytes Storage Billing（PBSB）與 Logical Bytes Storage Billing（LBSB），供使用者可以再細化不同的場景需求選擇自己的預算模式，有很大的機會可以再將 BQ 的成本往下降低！
首先，先了解資料儲存、長期儲存和查詢資料使用這三項的基本資訊
儲存資料成本（轉換 GB）
BigQuery 定價最簡單的組成部分，因為 BigQuery 目前 $0.02 per GB（per month）對所有儲存的資料按統一費率收費
通過查看 BigQuery 數據集中各表的表大小，可以簡單估算儲存資料的使用情況，例如，如果數據集總共有 50TB 的數據，費用估算為每月會花上個 $1,000 USD，算法如下：50TB * 1000 = 50,000 GB
50,000 GB * $0.02 = $1,000 per month
長期儲存資料成本
在進行 BigQuery 儲存成本估算時，除了考慮資料的總大小外，還應考慮Long Term Storage 的應用，該設定將自動適用於過去 90 天未更新的任何表格。但一旦表格被更新，將恢復到正常的儲存定價，並且 90 天⏰計時器將被重，省錢的關鍵因素是要考慮數據的更新頻率
- 超過
90天未更新的表被標記為長期儲存，費用從$0.02每 GB 下降到$0.01每 GB - 只是查詢表或手動導出/複製數據不會重置長期儲存計時器
- 如果估計有 30％ 數據集是靜態並且只會被查詢，30% 可以在經過 90 天後享受 50% 的儲存費用減免，可以調整先前的費用從 $10,000 調整為 $8,500，每月節省 💰 $1,500
查詢資料使用成本
實際使用者的使用量，估算上先加入一些實務的參數來方便估算
- 每天的用戶數
# of Users (per day) - 用戶每天的查詢數
# of Queries (per User, per day) - 估算每月的查詢資料使用
# Average Data Usage (per Query)
通過一些預估的情境，得到每月查詢量為
每天的用戶 ✖ 用戶每天的查詢數 ✖ 每日查詢量 ✖ 天數30
150 * 50 * 5 GB * 30 = 1,125,000 GB = 1,125 TB，我們可以簡單地將其乘以$5 per TB 每月費用為 💰 $5,625 USD 左右
總結，以上三個估算，得到 BQ 總成本為每月 $5,625 + $8,500 = $14,125 USD
新的 Storage 計價選擇 — Logical Billing & Physical Billing
先前 Google Cloud Platform 在 2023 年 7 月正式推出 Physical Bytes Storage Billing（PBSB）來進一步降低 BigQuery 儲存成本，預設情況下，在BigQuery中創建數據集時，儲存計費的消耗單位是 Logical Bytes Storage Billing（LBSB）。隨著 Physical Bytes Storage Billing（PBSB）計費的引入，客戶可以利用 Physical Bytes 的壓縮能力實現成本節省，同時也不會影響資料的可訪問性或查詢效能。
邏輯計費（Logical Billing）
基於查詢處理的資料的邏輯大小進行計費。這裡的「邏輯大小」指的是查詢所操作的數據的邏輯結構，而不僅僅是實際的儲存大小，假設您有一個包含以下欄位的表格 users，假設執行以下查詢
| user_id | username | email | age |
|---------|----------|----------------|-----|
| 1 | Alice | alice@email.com| 25 |
| 2 | Bob | bob@email.com | 30 |
| 3 | Charlie | clie@email.com | 35 |SELECT username, age
FROM `your_project.your_dataset.users`
WHERE age > 30;這個查詢將檢索 users 表格中 age 大於 30 的用戶的 username 和 age 欄位。在邏輯計費中，您會被計費的是您查詢的「邏輯大小」，即選擇的列（username 和 age）和滿足條件的行（age > 30）。
邏輯計費的關鍵點在於，即使實際表格可能包含更多列和更多數據，您只支付您實際查詢的那部分數據的成本。在這個例子中，只支付了 username 和 age 兩個欄位，以及符合條件的那些行的成本。這使得邏輯計費成為一種相對彈性的計費方式。
物理計費（Physical Billing）
基於查詢實際處理的數據的物理大小進行計費，包括實際的儲存大小，即查詢資料佔用的實際空間
SELECT username, age
FROM `your_project.your_dataset.users`
WHERE age > 30;物理計費支付的是實際查詢處理的所有數據的成本，而不僅僅是您選擇的邏輯，因此支付的將是 users 表格中包含的所有列（user_id, username, email, age）和符合條件的那些行的實際儲存大小。
適用場景探討
Logical Billing 強調的是您查詢的邏輯結構，而 Physical Billing 強調的是實際儲存和處理的物理數據。好處是用戶可以依自己的場景選擇支付。撇開這些不談，至少多了新的計費模式對於高壓縮比重度使用者來說仍然多了個絕佳節省成本的機會。
Logical Billing 適用場景：適用於涉及大量資料行，而您的查詢通常僅涉及資料集的一小部分。例如，選擇查找特定列或分析篩選，減少實際處理的資料量，並且您能夠有效地使用篩選條件來減少處理的資料量，那麼邏輯計費可能更經濟。
Physical Billing 適用場景：如果需要處理整個資料集，或者涉及多個資料表的複雜聯接操作，物理計費可能更為適合，可能常見是在進行一些帶有目的的資料市集建置或是 Feature Engineering （ML 特徵工程）工作。
還要留意什麼嗎？
Logical Billing 和 Physical Billing 的大小和成本之間的差異，一般來說 physical bytes 會比 logical bytes 還要小，但是要再多考量壓縮的性價比再進行 Physical Billing 切換判斷，一般壓縮比沒有達到 2 以上的話，也根本不用考慮要切換的問題了，因為換算下來反而會支付更多費用。
什麼會影響壓縮比？
- Cardinality（基數）：資料中不同值的數量越高，資料的可壓縮性就越差，因為必須儲存每個不同的值
註：BigQuery 壓縮使用 Capacitor 儲存格式 進行 - 資料類型或資料分佈： 不同的資料類型在壓縮方面的效果可能不同。例如，壓縮文本類型的數據相對於壓縮數值類型的數據可能會產生不同的效果。另外， 如果數據中存在大量的 Null 值，這些 Null 值可能不需要被實際存儲，因此也可能影響壓縮比
- 有無賦予資料的正確類型：替您的資料使用正確的資料類型吧！如果資料都只使用 STRING，那麼您可能會浪費大量空間。例如像是日期通常可以使用如 TIMESTAMP 或 DATE 來儲存。如果將日期存儲為 STRING
1990–05–15並佔用了 12 個邏輯字節，而 TIMESTAMP 或 DATE 則以更緊湊的二進制形式儲存並僅佔 8 個邏輯字節
註：總邏輯大小 = 固定開銷 + UTF-8 編碼的實際字符串大小 = 2（固定開銷） + 10（每個字符佔用 1 個字節） = 12 邏輯字節
有什麼技術或是工具可以預測存儲空間的費用
我們實際上可以透過 GCP 的 📙公開指導 來處理，相關資訊都可以在INFORMATION_SCHEMA.TABLE_STORAGE 中找到，先取得所有專案中每個資料集的資訊進一步作出成本差異，
對於使用 Logical Billing 或 Physical Billing 結算模式的資料集，您可以按如下方預測未來的每月儲存費用
以下範例示範如何傳回指定「專案中的資料儲存資訊」與傳回「指定區域中的表格的儲存資訊」
# 專案中的資料儲存
SELECT * FROM `myProject`.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;
# 指定區域中的表格的儲存資訊
SELECT * FROM `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT;⚠️注意：須指定位置以查詢INFORMATION_SCHEMA view，且查詢執行的位置必須與INFORMATION_SCHEMA view 的區域相符。
依 GCP 的 📙公開指導 取得所有專案中每個資料集的成本差異，以下看起來很複雜但實則上只是一些把計價因子進一步進行資料整理而已 ~ 別怕！
DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;
WITH
storage_sizes AS (
SELECT
table_schema AS dataset_name,
-- Logical
SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3) AS active_logical_gib,
SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3) AS long_term_logical_gib,
-- Physical
SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gib,
SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_tt_physical_gib,
SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,
-- Restorable previously deleted physical
SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,
FROM
`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
WHERE total_physical_bytes > 0
-- Base the forecast on base tables only for highest precision results
AND table_type = 'BASE TABLE'
GROUP BY 1
)
SELECT
dataset_name,
-- Logical
ROUND(active_logical_gib, 2) AS active_logical_gib,
ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
-- Physical
ROUND(active_physical_gib, 2) AS active_physical_gib,
ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
-- Compression ratio
ROUND(SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,
ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
-- Forecast costs logical
ROUND(active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,
ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,
-- Forecast costs physical
ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price, 2) AS forecast_active_physical_cost,
ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,
-- Forecast costs total
ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -
(((active_physical_gib + fail_safe_physical_gib) * active_physical_gib_price) + (long_term_physical_gib * long_term_physical_gib_price)), 2) AS forecast_total_cost_difference
FROM
storage_sizes
ORDER BY
(forecast_active_logical_cost + forecast_active_physical_cost) DESC;⚠️ 值得留意是 time_travel time_travel_physical_bytes 是指在 BigQuery 的 Time Travel 機制中，這個數量代表了您查詢了過去某個時間點的資料版本，因此需要額外的存儲成本來保留這些歷史數據。
在給定的查詢中，time_travel_physical_bytes 的計算涉及以下部分：
SUM(active_physical_bytes)：物理儲存總數據量，包括當前有效數據SUM(active_physical_bytes - time_travel_physical_bytes)： 這是在扣除因時間旅行而額外保留的歷史版本數據後的物理儲存數據量。這是為了計算不包括 Time Travel 版本實際物理儲存
實際可以再依這兩個的計價模式進行一些費用估算，產生 compression_ratio 和 cost_difference
| dataset_name | compression_ratio | cost_difference |
|--------------|-------------------|-----------------|
| 1 | 12 | 100 |
| 2 | 1.56 | -20 |
| 3 | 2.24 | 0.05 |
| 4 | 25.2 | 300.5 |
| 5 | 12 | -50.2 |還有什麼要考量嗎？
- 最終的選擇還是取決於具體使用情境、查詢模式和資料本身的設計。可能需要在不同的情況下評估兩種計費模式，以確定哪一種對您的使用情境更為成本效益。建議根據實際使用情境進行測試和監控，以了解不同計費模式的效果。
註：實際成本計算還涉及其他因素，例如查詢的複雜性和處理時間 - 實體儲存計費只能在資料集層級啟用。如果資料集中有多個表，則需要考慮整個資料集的總儲存大小。雖然您可能有許多表壓縮得很好，但可能還有其他表壓縮得不好，這會降低比率。有鑑於實體儲存的定價幾乎是邏輯儲存的兩倍，因此可能不值得進行更改，建議還是先驗證一下
如何置換我的計價模式
或者
# SQL
ALTER SCHEMA DATASET_NAME
SET OPTIONS(
storage_billing_model = 'BILLING_MODEL'); # 'physical' or 'logical'
# BQ
bq update -d --storage_billing_model=BILLING_MODEL PROJECT_ID:DATASET_NAME
# PROJECT_ID: your project ID
# DATASET_NAME: the name of the dataset that you're updating
# BILLING_MODEL: the type of storage you want to use, either LOGICAL or PHYSICAL
# API
curl -H "Authorization: Bearer $(gcloud auth print-access-token)" -H \
"Content-Type: application/json" -L -X \
PUT https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT_ID/datasets/DATASET_ID -d \
'{"datasetReference": {"projectId": "PROJECT_ID", "datasetId": "DATASET_NAME"}, "storageBillingModel": "BILLING_MODEL"}- 如果改變主意，或者實體計費模式會花費更多話，那麼須等待 14 天才能恢復更改（這原本是不可逆切換，後來 Google 有調整此選項）
當然也可以更進一步，設定一個 Looker Studio 儀表板來監控並一目了然地查看這些資訊。而本篇 FinOps 的重點，在 Big Query 可以積極探索 LBSB 與 PBSB 適用的場景以優化組織儲存成本，多了這個選項可以有再進一步費用優化的機會。
