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)

unnamed_38.max-1400x1400.png

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;

BigQuery Partitioned Table與Clustered Table