BigQuery Partitioned Table與Clustered Table
Partitioned Table與Clustered Table
當BigQuery的資料量多到一個程度
執行時間與成本將會越來越大
如果發現已經最佳化Query
但還想增加效能、降低成本的時候
就可以使用Partitioned Table與Clustered Table
這個官方介紹影片我覺得講得很清楚淺顯易懂
很適合一開始不清楚Partitioned Table與Clustered Table的人快速了解
Partitioned Table
將table分為多個區塊
並有以下三種類型的分法
- Time-unit column: 基於TIMESTAMP、DATE、DATETIME類型的欄位
- Ingestion time: 依照BigQuery資料擷取的使間
- Integer range: 基於Integer類型的欄位
可參考以下範例圖片(圖片來為Google Cloud Blog的文章BigQuery explained: Storage overview, and how to partition and cluster your data for optimal performance)
Time-unit
Partitioning type可By 小時/日/月/年
Ingestion time
Partitioning type一樣可以By 小時/日/月/年
並且會自動產生一個名稱為_PARTITIONTIME的虛擬欄位(pseudocolumn)
這個欄位將會紀錄Ingestion time的截斷(truncated)時間
截斷的單位可能至小時或日
例如"2021-05-07 17:22:00"截斷至小時後為"2021-05-07 17:00:00"
BigQuery將透過這個虛擬欄位_PARTITIONTIME來將資料做分區
Integer range
必須做以下相關設定
- 作為分區依據的Integer欄位
- 分區起始值(start)
- 分區結束(end)
- 級距(interval)
當該欄位的值在以上設定範圍外的時候
該筆資料將會被分進一個名稱為的__UNPARTITIONED__分區(partition)
若此欄位為NULL的時候
該筆資料將會被分進一個名稱為__NULL__的分區(partition)
Partitioned Table限制
針對partitioned table的操作
或是partition數量都有一些限制
詳見官方文件
Clustered Table
Clustering可增加幾種類型Query的效能
例如Filter或聚合(Aggregation)
當資料被寫入或查詢Clustered Table的時候
BigQuery會利用Cluster欄位的值來做排序
將資料存入至不同的區塊(Block)中
在查詢的時候透過已排序的區塊來避免掉不必要的資料掃描
此外，如果查詢一個小於1GB的Table或是Partition
無論是否使用Clustered Table
效能差異都不會太大
可參考以下範例圖片(圖片來為Google Cloud Blog的文章BigQuery explained: Storage overview, and how to partition and cluster your data for optimal performance)
Clustered Table限制
- 最多使用4個Cluster欄位
- Cluster欄位僅能為top-level, non-repeated類型(例如非STRUCT或非Array)
- 當Cluster欄位為STRING的時候，BigQuery僅使用前1024個字元來做Clustering(但該欄位可存超過1024個字元)
更多限制詳見官方文件
Partitioned Table vs Clustered Table
這兩種類型都可以增加效能並減少成本
需要使用Clustered Table的情形
- 不需要在執行Query前事先知道成本(dry run功能)
- 需要比partition使用更細的粒度，並可同時將一個欄位設定為partition column及cluster column來同時獲得兩種類型的好處
- Query通常對多個特定欄位做filter或或聚合(aggregation)
- 高基數(High Cardinality)欄位(一個欄位的資料重複性越低，基數越高，例如Email/電話為高基數、性別為低基數)
需要使用Partitioned Table的情形
- 需要在執行Query前事先知道成本(dry run)，因為Partition pruning在執行Query前已經先完成，而Cluster pruning則是在Query執行的時候才做，因此Clustered Table無法使用dry run
- 需要partition-level的管理，例如讓某些partition自動過期、資料載入指定partition、刪除某個partition
- 需要指定每筆資料的分區方式，例如By小時分區或是By Integer值範圍做分區
以下情況比起使用Partitioned Table，Clustered Table比較適合
- Partitioned Table造成每個Partition資料量很少(小於1GB)
- Partitioned Table造成的Partition數量過多超過限制
- Partitioned Table造成原本對Table的異動會跨很多個Partition(一直跨多個Partition就失去用Partition的意義了)
也可以同時使用Partitioned Table及Clustered Table
資料先分Partition(例如By day)
再針對每個Partition中的指定欄位做cluster
Partitioned Table vs Sharded Table
Shared Table用意跟Partitioned Table類似
不過Shared Table是將Table加個suffix來區分table(例如: [PREFIX]_YYYYMMDD)
但這樣實務上是維護多個table
維護、操作上比起Partition麻煩多
因此官方建議若原本使用Sharded Table
可以考慮將它們全部轉為Partitioned Table
成本
在Table做Partitioning或Clustering之後
除了因為Query只掃描某幾個分區讓成本降低之外
儲存成本也會因為某些分區不常查詢變為Long-term storage而降低
查詢比較
未做Partitioning及Clustering之前
實際測試使用一個31.61MB的Table
搜尋指定日期的資料
會發現Dry Run的Bytes Processed同為Table Size 31.61MB
實做Partitioning及Clustering之後
在使用timestamp作為Partition及Clsuter欄位後
會發現Dry Run Bytes Processed已經顯示為八十幾KB
而且這還只是因為用Partition的情話下預估成本
前面有提到Cluster無法預估成本
因此如果在Partition大於1GB的情況下實際執行Query會比這個預估更低
大量資料Table測試
為了能夠更明顯的比較出使用Partition或Cluster後的差異
我用了一個Table Size為89.36GB的Table來做測試
並使用了下方的SQL做測試
SELECT * FROM `project_id.some_dataset.large_table` WHERE DATE(timestamp) = "2022-10-01" LIMIT 1000
以下為測試的情形
- 只用Partition的Bytes Processed: 80.49 MB
- 只用Cluster的Bytes Processed: 60.27 MB
- 同時使用Partition/Cluster: 55.31 MB
現有Table Migrate
針對已建立的Table(Non-partitioned或Non-Clustered)
可透過以下方式來設定成Partitioned Table或Clustered Table
Partitioning
由於BigQuery無法直接將Non-Partitioned Table改為Partitioned Table
因此只能透過複製的方式(CREATE TABLE ... AS SELECT...)
複製出一個新的Table並設定Partition欄位
CREATE TABLE `my_project.some_dataset.foobar_partition`
PARTITION BY DATE(timestamp)
AS SELECT * FROM `my_project.some_dataset.foobar`;
接著刪除原本的Table(foobar)
再將新的Table(foobar_partition_cluster)重新命名為原始Table名稱(foobar)
DROP TABLE `my_project.some_dataset.foobar`;
ALTER TABLE `my_project.some_dataset.foobar_partition` RENAME TO `foobar`;
Clustering
Clustering比較簡單
除了用上面Partitioning複製的方式之外(將PARTITION BY DATE(timestamp) 改為CLUSTER BY timestampSQL)
也可以透過bq update command
使用clustering_fields選項直接指定Cluster欄位
bq update --clustering_fields timestamp some_dataset.foobar_table
同時改為Partitioning、Clusering
因為改為Partition只能用複製
因此就Clustering動作就直接一起在複製的時候設定
CREATE TABLE `my_project.some_dataset.foobar_partition_cluster`
PARTITION BY DATE(timestamp)
CLUSTER BY timestamp
AS SELECT * FROM `my_project.some_dataset.foobar`;
DROP TABLE `my_project.some_dataset.foobar`;
ALTER TABLE `my_project.some_dataset.foobar_partition_cluster` RENAME TO `foobar`;
若要將整個dataset底下的table全部都改為Partitioned、Clustered Table
可使用以下SQL直接做調整(要調整前兩行的Project ID跟Dataset名稱)
DECLARE project_id STRING DEFAULT "my_project"; -- 設定Project ID
DECLARE dataset_name STRING DEFAULT "some_datatset"; -- 設定dataset名稱
DECLARE table_list ARRAY<STRING>;
DECLARE iter INT64 DEFAULT 0;
DECLARE create_table_query_string STRING;
DECLARE current_table_name STRING;
-- 取得所有table name存入table_list
EXECUTE IMMEDIATE "SELECT ARRAY_AGG(DISTINCT(table_name)) FROM `" || project_id || "." || dataset_name || ".INFORMATION_SCHEMA.TABLES`" INTO table_list ;
WHILE
iter < ARRAY_LENGTH(table_list) DO
SET current_table_name = table_list[OFFSET(iter)];
-- 複製原有table至新的table並設定partition及cluster
SET create_table_query_string = "CREATE TABLE `"|| project_id || "." || dataset_name || "." || current_table_name || "_partition_cluster` " ||
"PARTITION BY DATE(timestamp) " ||
"CLUSTER BY timestamp " ||
"AS SELECT * FROM `" || project_id || "." || dataset_name || "." || current_table_name || "`; ";
EXECUTE IMMEDIATE create_table_query_string;
-- 刪除原始table
EXECUTE IMMEDIATE "DROP TABLE `"|| project_id || "." || dataset_name || "." || current_table_name ||"`";
-- 把新table名稱改為原始table名稱
EXECUTE IMMEDIATE "ALTER TABLE `"|| project_id || "." || dataset_name || "." || current_table_name || "_partition_cluster` RENAME TO `" || current_table_name || "`";
SET iter = iter + 1;
END WHILE;
相關參考資料
- Google Cloud Blog: BigQuery explained: Storage overview, and how to partition and cluster your data for optimal performance