BigQuery 最佳化完美實踐：Partitioned and Clustered Table

BigQuery 是 Google 提供完全託管、企業級的資料倉儲（Dataware house）服務。當您使用 BigQuery 一段時間後，會發現隨著您的業務拓展，越來越多資料將會被儲存在 BigQuery 內。而無可避免地，由於在運算上 BigQuery 會需要掃描更多資料，造成了更長的等待時間和更多的成本支出。

如何有效將 BigQuery 使用到最佳化？

為了幫助企業解決這個時間成本的問題及花費，Google 也提供了一系列的方法來幫助企業最佳化BigQuery 的使用，其中包含了使用 preview 功能取代 Query 來檢視資料表、避免使用 selcet *、使用 Caching results、設定 Query 資料量上限等等的解決方法。

但是若您發現不論您已經最佳化您的 Query 但仍然無法有效降地 BigQuery 的等待時間並控制成本的支出，不仿可以重新設計您的資料表，也就是使用 BigQuery 的 Partitioned Table 和 Clustered Table。

這篇文章將會帶您認識這兩個特殊的資料表，並比較兩者間的不同，並且在最後實際測試其帶來的效果。

BigQuery 特殊資料表- Partitioned Table

Partitioned Table 是在 BigQuery 中一種將資進行分區的特殊資料表。將大型資料表切割成不同分區不僅可以增進 Query 的效能，同時可以減少每次 Query 讀取的資料量來減少相關成本支出。而當新資料寫入分區資料表時也會自動根據分區規則來寫入適合的分區。

在 BigQuery 中可以根據以下資料欄位將資料表進行分區：

1. 時間欄位
2. 資料匯入時間
3. 整數欄位

一、以時間欄位進行分區

資料表根據 TIMESTAMP, DATE, or DATETIME 欄位來進行分區。對於 TIMESTAMP, DATE 的欄位可以使用年、月、日、小時的方式來將資料表進行分區，而 DATETIME 欄位可以使用年、月、日的方式來將資料表進行分區。下圖利用 DATETIME 欄位和 Monthly 的方式來切割資料表：

此外，BigQuery 也會自動創建兩個額外的分區：

1. __NULL__：包含了時間欄位為null的資料。
2. __UNPARTITIONED__：包含了欄位時間點在 1960-01-01 之前或是 2159-12-31 之後的資料。

二、以資料匯入時間進行分區

資料表根據資料匯入 BigQuery 的 timestamp 來進行分區。同樣地，可以根據年、月、日、小時的方式來將資料表進行分區。

對於以資料匯入時間近習分區的資料表，BigQuery 會自動新增一個 pseudocolumn：_PARTITIONTIME 來將匯入時間點轉換為分區時間點，如下圖所示：

由於這個 partition table 是根據每小時來進行分區，所以在 _PARTITIONTIM E欄位顯示的是每小時的分區時間點。

對於時間單位分區表或是匯入時間分區表該如選擇用什麼樣的時間單位（小時、日、月、年）進行切割，可以參考以下建議：

1. 以天為單位（Daily Partitioning）是預設的切割方法，適用於當資料時間點橫跨許多不同日期。
2. 以小時為單位（Hourly Partitioning）的切割適合在短期的時間內（e.g., 6個月內）有大量的資料點。但必須注意的是一個分區資料表最多只能有4000個分區。
3. 以月或是年為單位（Monthly or Yearly Partitioning）的切割適合資料時間點範圍很廣（e.g., 超過500天）但每日的時間點相對少的資料表。

三、以整數欄位進行分區

資料表根據整數欄位來進行分區。以這種方式進行分區時，必須提供以下資訊：

1. 整數列的名稱
2. 開始數值
3. 結束數值
4. 間隔

舉例來說，若給定 user_id 為整數列、開始數值為1、結束數值為100、間隔為10，則第一個分區為 user_id 1-10、第二個分區為 user_id 11-20、第三個分區為 user_id 21-30，以此類推。若某個資料點的 user_id 不在 1-100 的區間內，則其會被分派到 __UNPARTITIONED__ 的分區；若某資料點 user_id 為 null，則其會被分派到 __NULL__ 的分區。

如何在 BigQuery 中建立Partitioned Table?

在 BigQuery 中 Partitioned Table 必須從一開始就建立，建立 Partitioned Table 和一般 Table 在語法上大同小異。Partitioned Table 必須在 PARTITION BY 加入以哪個 column 為基準來進行分區，並且在 OPTIONS 選擇是否設定分區失效時間和是否設定 partition filter requirements（也就是對table 進行查詢都必許使用 “where” 來過濾 partition 的欄位）。

1.以時間欄位進行分區：

CREATE TABLE

  mydataset.newtable (transaction_id INT64, transaction_date DATE)

PARTITION BY

  transaction_date

OPTIONS

  (partition_expiration_days=3,

  require_partition_filter=true)

2.以匯入時間進行分區：

CREATE TABLE

  mydataset.newtable (transaction_id INT64)

PARTITION BY

  _PARTITIONDATE

OPTIONS(

  partition_expiration_days=3,

  require_partition_filter=true)

3.以整數欄位進行分區：

CREATE TABLE

 mydataset.newtable (customer_id INT64, date1 DATE)

PARTITION BY

  RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))

OPTIONS(

  require_partition_filter=true)

此外，BigQuery 並不支援將一般 Table 轉換為 Partitioned Table，必須重新建立一個新的Partitioned Table：

CREATE TABLE

  mydataset.newtable (transaction_id INT64, transaction_date DATE)

PARTITION BY

  transaction_date

AS 

SELECT

  transaction_id, transaction_date 

FROM 

  mydataset.mytable

BigQuery 特殊資料表- Clustered Table

除了 Partitioned Table 之外，BigQuery 中的 Clustered Table 同樣地可以提升查詢速度並減少成本花費。Clustered table 中資料會自動根據一個或多個指定欄位中的值來進行重新排序。當查詢中的過濾條件或聚合運算包含了指定欄位時，BigQuery 可以避免掃描不必要的數據。若使用多個指定欄位來創建分群資料表時指定欄位的順序決定了數據的排序順序，因此必須特別注意指定欄位的順序。若有新的資料寫入 Clustered Table， BigQuery 將會自動根據其指定欄位的值進行重新排序 (Auto re-clustering)。

一般來說，高基數(High Cardinality)或非時間欄位適合當作 Clustered Table 的指定欄位。請參考下圖，除了使用時間欄位進行分區也同時使用Tags欄位來為來進行 cluster。可以看到資料表在進行 cluster 後，在 Tags 欄位中相近的值會被排序再一起。

此外，若查詢一個小於 1GB 資料表或分區時，Clustered Table 在效能上並不會有太顯著的進步。

如何在 Big Query 中建立 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”)

比較 Partitioned Table 與 Clustered Table

一般來說，不論是 Partitioned Table 或是 Clustered Table 都可以增進資料查詢的效率並減少相對應成本費用的方法。但 Partitioned Table 和 Clustered Table 分別有其適用的情境：

Partitioned Table：

1. 您想在查詢運行之前了解查詢成本。Partitioned Table 在進行 Query 前會先有 Dry Run 來進行 Partition pruning，因此可以事先知道查詢成本。而 Clustered pruning 則是在查詢運行當下才會進行，所以無法事前掌握想關成本。
2. 您需要 partition-level 的管理，如設定分區失效時間、將資料寫入特定分區等。
3. 您想要選擇資料切割方法，如以時間欄位或是整數欄位進行分區。

Clustered Table：

1. 沒有嚴格的成本限制，可以接受再查詢前的成本預測誤差。
2. 您需要比單獨使用 partitioned table 更細緻的來切割資料。在同一欄位可以同時進行partitioning 和 clustering。
3. 您的查詢常常會使用某些特定的欄位。
4. 指定的欄位擁有 High Cardinality，也就是在指定欄位中的值基本上不重複。

除了以上各自適用的情境，您也可以於同一張資料表中同時使用 partitioning 和 clustering 來達到更精細的資料排序。資料首先會進行 partitioning 然後在每一區中進行 clustering。另一方面，由於在查詢前只會顯示在 partition pruning 之後的成本，所以實際查詢成本可能會比預先顯示的更低。

BigQuery 實戰測試

我們利用 BigQuery 公開資料集中的 iowa_liquor_sales 來進行測試。這是一個酒商的銷售資料，其中包含了銷售日期（YYYY/MM/DD）、店家id、店家名稱、城市、商品名稱、數量等欄位。Table 大小為 6.28GB。

在衡量的指標方面，主要會以 Elpased time 和 Slot time 作為比較指標。

• Elapsed Time：  BigQuery 執行查詢所花費的總時間。

• Slot Time：vCPU 執行查詢所用的總時間。

BigQuery 是基於一個分布式平行運算的架構，所以基本上來說 slot time 會大於 elapsed time。可以把 slot time 的關係想成是「人年」或是「人天」的概念。但對於規模較小的查詢，偶爾elapsed time會大於 slot time，是因為 BigQuery 需要一些時間來整合運算結果。

不論是 Elpased time 或是 Slot time，值越大就代表著查詢需要更多時間。

請注意：在進行測試前，請先在Query Settings中將快取的功能關閉才能正確衡量效能的差異。

首先，我們針對iowa_liquor_sales進行一個簡單的 Query，查看在 2014-01–01 至 2020-01-01間且城市欄位為 “Dubuque” 的資料：

可以看到在 Execution Details 中看到 Elapsed time 為4秒、slot time 為3秒，且需要掃描整個 table（6.28GB）。

接著我們建立一個新的 table：iowaliquor.sales_partitioned_clustered 並針對原本 table 中的 date 欄位進行 partitioning 和 city 欄位進行 clustering：

建立完成後，針對新的 table 在進行相同的查詢：

可以看到 Elapsed time 和 slot time 已經降至為2秒，且只需掃描 3.54GB 的資料。

雖然這樣的結果差異並不大，但證實了 table partitioning 和 clustering 確實可以加速 query 的速度，並減少 BigQuery 掃描的數據量。若今天是使用資料量更大的 table，其中差距將會更明顯。

看完這篇文章相信您已經了解了 Partitioned table 和 Clustered table 可以為您帶來的優勢。那快去試試看吧！

參考資料：

https://cloud.google.com/bigquery/docs/partitioned-tables

https://cloud.google.com/bigquery/docs/clustered-tables

https://cloud.google.com/blog/topics/developers-practitioners/bigquery-explained-storage-overview

Randy Hsiao