【Reference】GCP Tips — BiqQuery useful tips

Kellen

15 min readSep 18, 2023

這個號稱地表最强資料分析工具除了速度快及易用性高之外，還有沒有其他須留意的地方，以下就易用性、省錢、視覺化的可能性提供一些 Tips！

架構你的易用性工具篇（以 Python 為例）

先必須理解相關 Python 操作及閱讀相關的 Reference 後

主要有幾個套件會引入

google-cloud-bigquery：Google Cloud Client Libraries 的 Python 程式庫
pyarrow：將 pandas DataFrame 轉換成 BigQuery Table 的套件

from google.cloud import bigquery


#  ----------- 讀取資料 -----------
client = bigquery.Client()
# Perform a query.
QUERY = (
    'SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` '
    'WHERE state = "TX" '
    'LIMIT 100')

# 或選用 job_config
# job_config = bigquery.QueryJobConfig(destination=table_id)

# API request 或選用 client.query(QUERY, job_config)
query_job = client.query(QUERY)  
rows = query_job.result()  # Waits for query to finish

for row in rows:
    print(row.name)

#  ----------- 建立資料表(table)並上傳至 BQ -----------
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # Make an API request.
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

每次都這麼寫，確實有點麻煩，來動手把常用的工作試著架構一個易用的工具

之後就可以專心在分析操作，異常處理就交給這支 backend tools

故意將來源指定錯誤

視覺化呈現篇

Google 將協助背後伺服器管理，讓 BigQuery 展現 High Availability (HA) 的優點，同時亦提供 Distributed 高效的搜尋效能。此外，不同於傳統 RDS， bigquery 的儲存方式以 欄位 劃分儲存 (columnar storage)，更適合用於數據分析。對於開發者而言，支持常見的 SQL 語法，方便軟體人員銜接學習這項新服務。

BigQuery 可搭配 Looker Studio 將資料以視覺化方式呈現，不過 Looker Studio 不在 GCP 頁面（建議將此網站加至書籤）。

點選 Blank Report 後選擇 BigQuery，也可以進行分享，對方會有 View 權限進入預覽模式。

BigQuery 省錢密技及勿踩陷井篇

BigQuery 是 Columnar 的 database，背後有大量的運算機器來處理資料調用後的運算處理，因此在計費上，主要以儲存量與查詢量來做計費。目前 BigQuery 目前提供給使用者查詢的免費額度 1TB，超過流量後 1TB 也才只有 $5 美元，且以使用量計費！看起來真不錯，但真的是這樣嗎？

實際上 BigQuery 在執行查詢時，透過調用的數量所估算的資料量大小來計費，例如查詢某個 Table 時，只有 Select 其中兩個欄位，則費用以該兩個欄位的「總大小」做計算，而非傳統的 RDB 用 row 的方式去處理：

例如：這個查詢⭐無料（$0 美元費）因為此查詢沒有任何 column

但是，同仁為了省錢有指定 LIMIT 參數，竟然花了 587 GB，一次把公司的免費額度用掉了 60%，這就是剛剛提到的這兩個 Column 總量來運算

在看這個例子，LIMIT=1 竟然還需要 2.54 GB 是不是很吐血呢

大家如果擔心每次 Query 整表成本太高，可以建立分割表 Partitioned Table，以日期做 Where 條件就不會 Query 整張表，有效節省大量成本！

避免使用 SELECT *，你可以使用 partitioned 或是 cluster 的方式去處理！並留意右上角的資料處理大小。舉例：在表格建置時記得帶入 PARTITION 以讓後續搜尋可以僅針對 PARTITION 作 Query
GCP 也有建議啟用 Require partition 過濾器：該過濾器會強制使用者在 SQL 中的 WHERE 子句中使用 partition，建表時可以參考官方文檔。啟用需要分區過濾條件選項來要求使用過濾條件，如果試圖在不指定WHERE子句的情況下查詢分區表，則會產生以下錯誤
Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination
使用有效期設置移除不必要的表和分區：若您本身為管理 infra 的工作，可以通過與使用者約定，在對創建的表設置有效期來達到控制存儲費用（成本考量，可以考慮強制性），也優化存儲空間使用情況。資料到期後，系統會刪除表包含的資料。如果只需訪問最新數據，則此選項會非常有用；若只是測試數據而無需保留，則此選項也會非常有效。

CREATE TABLE `xxx.ooo` 
PARTITION BY DATE(Data_Ym) AS
SELECT block_timestamp_truncated, message_type
FROM `bigquery-public-data.crypto_band.messages` 
WHERE block_timestamp_truncated  between '2020-11-01'and '2020-11-30';

-- Create an empty partitioned table
-- Create a time-unit column-partitioned table
-- Ref: https://cloud.google.com/bigquery/docs/creating-partitioned-tables
CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  transaction_date
  OPTIONS (
    partition_expiration_days = 3,
    require_partition_filter = TRUE);

BigQuery 有計算低消，雖然有努力優化 SQL 及設計 Partition 且只有查詢 2.75K 的量，但還是被收取了 10 MB 的查詢費用 ~

Clustered tables：BigQuery 中的 Clustered tables 是使用者定義的列排列順序的表， Clustered table 中資料會自動根據一個或多個指定欄位中的值來進行重新排序。當查詢中的過濾條件或聚合運算包含了指定欄位時，BigQuery 可以避免掃描不必要的數據，提高查詢效能並降低費用。

高基數(High Cardinality)或非時間欄位適合當作 Clustered Table 的指定欄位

BigQuery支援不同方法建立Clustered Table，SQL 語法可參考以下

CREATE TABLE mydataset.myclusteredtable
(
  customer_id STRING,
  transaction_amount NUMERIC
)
CLUSTER BY
  customer_id
OPTIONS (
  description=”a table clustered by customer_id”)

繫上安全帶，保持冷靜，繼續查詢：建議直接加入硬設定，直接設定查詢上限，預設情況下，查詢可以掃描的資料量沒有限制，因此錯誤的查詢可能會導致完全掃描（新手?晃神?）。相反，您可以設定要收費的最大位元組數，如果即將超出限制，則查詢將失敗並且不會向您收費

在查詢編輯器中，點擊更多，點擊查詢設置，然後點擊進階選項。在結算位元組數上限欄位中，輸入一個整數

API request 費用：舉凡 BigQuery 上所有的操作都需要透過 BigQuery RESTful API 進行處理，而 API 需要負擔每次呼叫費用。
儲存量費用：BigQuery 儲存之資料以 GCS Nearline 相等的價格做計算（0.02 USD per GB per month）。

主動監控篇 — 構建成本儀表板

BigQuery 以是流量計價的服務，費用與處理字節數成線性關係，這些字節數記錄在每個查詢的 INFORMATION_SCHEMA.JOBS 中，則可以使用相同的源表和方法來識別使用最多的用戶和查詢。而 INFORMATION_SCHEMA 是按區域劃分的，因此您必須指定所需數據的區域。

在有了根源性與成本分析的依據後，就可以深入研究對 SQL 查詢本身或其相依性工作探討來降低成本。這不像構建儀表板或匯總每個用戶的成本那麼簡單；會需要查看查詢本身及上下游關係，以找出成本的根本原因，並期待開支應該會像以下這位大神分享內部改善的 Bar Chart 這麼神奇。

How we cut BigQuery costs 80% by hunting down costly queries

INFORMATION_SCHEMA.TABLES是一個特殊的系統表，它記錄了有關 BigQuery 數據集中表的元數據資訊

-- 實際上用於檢索指定地區的所有表的資訊，包括表的名稱、模式、類型、創建時間、修改時間等。
SELECT * FROM esun-cncf.`region-asia-east1`.INFORMATION_SCHEMA.TABLES;

（MVP）識別重度用戶及重新組織日常作業

這邊我們使用系統表 INFORMATION_SCHEMA.JOBS 來測試一下

SELECT 
  -- 這些列包含可用於識別昂貴查詢的功能和/或來源的信息：
  cache_hit,
  creation_time,
  end_time,
  job_id,
  job_type,
  parent_job_id, 
  query,
  referenced_tables,
  state,
  statement_type,
  total_bytes_billed,
  total_bytes_processed,
  total_slot_ms,
  user_email 
FROM
   `region-asia-east1`.INFORMATION_SCHEMA.JOBS 
WHERE
   creation_time BETWEEN TIMESTAMP_SUB( CURRENT_TIMESTAMP (), INTERVAL  7  DAY ) AND CURRENT_TIMESTAMP () 
AND
   job_type = "QUERY" 
AND
   statement_type != "SCRIPT" 
AND
   cache_hit !=  true

我們可以按我們關心的成本字段（`total_bytes_billed` 用於按需或 `total_slot_ms` 定價）對這些結果進行排序，以找到最昂貴的單次查詢

（MVP）BigQuery Budget-Savvy Savior

SELECT
  user_email,
  TIMESTAMP_TRUNC(end_time, HOUR) AS query_time_hour,
  SUM(total_bytes_billed) / POW(2, 40) AS tib_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
  AND job_type = "QUERY"
  AND statement_type != "SCRIPT"
  AND cache_hit != true
GROUP BY user_email, query_time_hour
ORDER BY query_time_hour DESC, tib_billed DESC

或甚至是可以每天抓取相關資料後，開始查詢並制定成本上限，檢查是否超過指定的限制，然後輸出相應的消息！

（MVP）實現強制執行每日支出限制的告警

from google.cloud import bigquery

client = bigquery.Client()
# 24HR total cost
query = """
    SELECT SUM(dollars_billed) as total_cost
    FROM `your_project.your_dataset.bq_spend_per_hour`
    WHERE query_hour >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
"""

query_job = client.query(query)
results = query_job.result()
for row in results:
    total_cost = row.total_cost
# 成本限制
spend_limit = 1000  
# 檢查是否超過限制
if total_cost > spend_limit:
    print(f"Total cost in the last 24 hours ({total_cost} USD) exceeds the spend limit ({spend_limit} USD).")
else:
    print(f"Total cost in the last 24 hours ({total_cost} USD) is within the spend limit ({spend_limit} USD).")

其他降低成本的策略篇

參考這篇 Strategies for cost reduction 有不少 SQL 合併、簡化的想法

Minimize outputs
Combine multiple queries
兩個獨立（相似）的查詢為支持 Dashboard 或分析上的使用，則可以通過設計與合併來減少查詢總量
Store results from a query in a table
Store results from multiple queries in a table
Minimize the amount of materialized data
降低成本的一種方法是預先計算昂貴查詢的結果
Add clustering

Reference

BigQuery 文檔對分區和集群的精彩介紹
【Python教學】Python 使用 BigQuery 的操作與安裝
BigQuery Admin reference guide: Storage internals
Strategies for cost reduction
How we cut BigQuery costs 80% by hunting down costly queries