【51CTO.com快譯】作為一款分析平臺，Snowflake數(shù)據(jù)倉庫(Data Warehouse)以其超快的查詢性能蜚聲于業(yè)界。不過，我們對Snowflake既無法建立索引，又不可捕獲統(tǒng)計信息，更無法管理分區(qū)。那么，您該如何優(yōu)化Snowflake數(shù)據(jù)庫，以達(dá)到更好的查詢性能呢?本文將介紹有關(guān)如何將系統(tǒng)調(diào)整到最大吞吐量的三個主要方面，即：數(shù)據(jù)提取、數(shù)據(jù)轉(zhuǎn)換和最終用戶的查詢。

影響Snowflake查詢性能的因素

作為技術(shù)人員，我們經(jīng)常需要在對問題不甚了了的情況下，提出并實(shí)施解決方案。那么總的說來，我們在分析平臺上的性能問題時，通常會從如下三個方面入手：

i. 數(shù)據(jù)的加載速度：應(yīng)具有能夠快速加載大量數(shù)據(jù)的能力。

ii. 數(shù)據(jù)的轉(zhuǎn)換：應(yīng)具有最大化吞吐量，并將原始數(shù)據(jù)快速地轉(zhuǎn)換為適合查詢格式的能力。

iii. 數(shù)據(jù)的查詢速度：能夠最大程度地減少每次查詢的延遲，并盡快將結(jié)果提供給商業(yè)智能用戶。

1.Snowflake的數(shù)據(jù)加載

避免掃描文件

下圖展示了將數(shù)據(jù)批量加載到Snowflake處的最常見方法。該方法主要是將數(shù)據(jù)從本地(on-premise)系統(tǒng)傳輸?shù)皆贫舜鎯?，然后使用COPY命令加載到Snowflake中。

那么在復(fù)制數(shù)據(jù)之前，Snowflake會檢查文件是否已被加載。這是通過限制針對某個特定目錄的COPY，來實(shí)現(xiàn)最大化加載性能的第一種、也是最簡單的方法。如下代碼段展示了一系列COPY操作。

SQL

-- Slowest method:  Scan entire stage 
copy into sales_table 
        from @landing_data 
 pattern='.*[.]csv'; 
-- Most Flexible method:  Limit within directory 
copy into sales_table 
from @landing_data/sales/transactions/2020/05 
       pattern='.*[.]csv'; 
-- Fastest method:  A named file 
copy into sales_table 
from @landing_data/sales/transactions/2020/05/sales_050.csv; 

可見，最快捷的方法是：命名一個特定的文件，并用通配符來體現(xiàn)其靈活性。當(dāng)然，我們也可以在加載完畢后立即刪除目標(biāo)文件。

調(diào)整虛擬倉庫和文件的大小

下圖展示了：在將大型數(shù)據(jù)文件加載到Snowflake中時，設(shè)計人員往往趨向于擴(kuò)展出更大的虛擬倉庫，以加快整個加載過程。這是一個常見的誤區(qū)。實(shí)際上，在這種情況下，給倉庫擴(kuò)容并不會帶來任何性能上的優(yōu)勢。

也就是說，上面的COPY語句將打開一個10 Gb的數(shù)據(jù)文件，并使用某個線程在一個節(jié)點(diǎn)上順次加載數(shù)據(jù)，而其余的服務(wù)器則保持為空閑的狀態(tài)。通過基準(zhǔn)測試，我們發(fā)現(xiàn)：通常情況下，加載的速率約為每分鐘9 Gb。我們可以設(shè)法提高該速度。

下圖給出了一種更好的方法--將單個10Gb文件分解為100個100 Mb的文件，以充分利用Snowflake的自動化并行處理功能。

2.Snowflake的轉(zhuǎn)換性能

延遲與吞吐量

雖然優(yōu)化SQL是減少時間開銷的最有效方法，但是設(shè)計人員通常不太好把握時機(jī)。除了減少單個查詢的延遲，最大化吞吐量(即：在盡可能短的時間內(nèi)實(shí)現(xiàn)數(shù)據(jù)交付的最大化)也是非常重要的。

下圖展示了典型的數(shù)據(jù)轉(zhuǎn)換模式，該模式會在虛擬倉庫中執(zhí)行一系列的批處理作業(yè)。只有在前一項(xiàng)任務(wù)完成時，后一項(xiàng)任務(wù)才會開始：

我們很容易想到的解決方案是：將其擴(kuò)展到更大的虛擬倉庫中，以更快地完成作業(yè)任務(wù)。不過，該方案往往會受到硬件資源的極限限制。此外，雖然此舉能夠提高查詢的性能，但是也會造成大量倉庫資源未被充分利用。

如上圖所示，Apache Airflow可被用于執(zhí)行與Snowflake的多個獨(dú)立連接。其中，每個線程會針對同一虛擬倉庫去執(zhí)行單個任務(wù)。隨著工作量的增加，如果可用資源出現(xiàn)不足的情況，作業(yè)任務(wù)就會開始排隊。為了分擔(dān)負(fù)載，我們可以將Snowflake的多集群功能，配置為能夠自動創(chuàng)建另一個相同大小的虛擬倉庫。

完成任務(wù)后，上述解決方案還會自動縮小為單個群集，并且能夠在完成了最長的作業(yè)后，將群集掛起。目前為止，這是獲取自動擴(kuò)展與收縮能力的最有效方法。

如下SQL代碼段展示了創(chuàng)建多集群倉庫所需的命令，該倉庫將在60秒鐘的空閑時間后自動掛起。我們通過ECONOMYE擴(kuò)展策略，來提高吞吐量，并節(jié)省單個查詢的等待時間。

SQL

  -- Create a multi-cluster warehouse for batch processing 
  create or replace warehouse batch_vwh with 
  warehouse_size      = SMALL 
 min_cluster_count   = 1 
  max_cluster_count   = 10 
 scaling_policy.     = economy 
 auto_suspend.       = 60 
 initially_suspended = true; 

3.調(diào)整Snowflake的查詢性能

選擇必要列

與許多其他數(shù)據(jù)分析平臺類似，Snowflake也用到了列式數(shù)據(jù)存儲。如下圖所示，該存儲被優(yōu)化為僅獲取那些特定查詢所需的屬性，而非所有列：

在上圖中，該查詢只是在上百個列的表中獲取了其中的兩列。而傳統(tǒng)的行存儲則需要從磁盤中讀取所有列的數(shù)據(jù)。顯然，前者的效率要高出許多。

最大化緩存使用率

下圖展示了Snowflake內(nèi)部架構(gòu)的重要組成部分，它能夠在虛擬倉庫和云端服務(wù)層之間緩存數(shù)據(jù)。

商業(yè)智能儀表盤可以通過對同一查詢的重新執(zhí)行，以刷新并顯示被更改以后的數(shù)據(jù)值。Snowflake通過返回最近24小時內(nèi)查詢到的結(jié)果緩存(Results Cache)中的內(nèi)容，來實(shí)現(xiàn)對此類查詢的自動化調(diào)優(yōu)。

雖然數(shù)據(jù)也會被緩存到快速SSD(固態(tài)硬盤)上的虛擬倉庫中，但是不同于上述提到的結(jié)果緩存，虛擬倉庫是基于最近、最少使用原則，來保存原始數(shù)據(jù)，因此此類數(shù)據(jù)很可能已經(jīng)過期了。不過，我們雖然無法直接調(diào)整虛擬倉庫中的緩存內(nèi)容，但是可以通過如下步驟進(jìn)行優(yōu)化：

• 獲取所需的屬性：避免在查詢中使用SELECT *，畢竟這會將所有數(shù)據(jù)的屬性，從數(shù)據(jù)庫存儲(Database Storage)中全量獲取到倉庫緩存(Warehouse Cache)中。此舉不僅速度緩慢，而且還可能導(dǎo)致那些不需要的數(shù)據(jù)也被填充到了倉庫緩存中。
• 擴(kuò)容：我們雖然應(yīng)該避免通過擴(kuò)容的方式，來應(yīng)對特定的查詢，但是我們需要通過調(diào)整倉庫本身的大小，以提高整體的查詢性能。那些新增的服務(wù)器既可以分散突發(fā)任務(wù)的負(fù)擔(dān)，又能夠有效地增加倉庫緩存的大小。
• 考慮數(shù)據(jù)集群：對于大小超過TB的數(shù)據(jù)表而言，請考慮通過創(chuàng)建集群鍵(cluster key，請參見--https://www.analytics.today/blog/tuning-snowflake-performance-with-clustering)的方式，最大程度地消除分區(qū)(partition)。此舉既可以提高單個查詢的性能，又可以返回較少的微分區(qū)(micro-partitions)，從而充分地使用到倉庫緩存。

SQL

  -- Identify potential performance issues 
  select query_id                      as query_id 
  ,      round(bytes_scanned/1024/1024)     as mb_scanned 
 ,    total_elapsed_time / 1000          as elapsed_seconds 
  ,      (partitions_scanned /  
       nullif(partitions_total,0)) * 100 as pct_table_scan 
 ,      percent_scanned_from_cache * 100   as pct_from cache 
 ,    bytes_spilled_to_local_storage     as spill_to_local 
 ,      bytes_spilled_to_remote_storage    as spill_to_remote 
 from   snowflake.account_usage.query_history 
 where (bytes_spilled_to_local_storage > 1024 * 1024 or 
        bytes_spilled_to_remote_storage > 1024 * 1024 or 
        percentage_scanned_from_cache < 0.1) 
 and  elapsed_seconds > 120 
 and    bytes_scanned > 1024 * 1024 
 order by elapsed_seconds desc; 

上面的SQL代碼段可以幫助我們識別出，那些運(yùn)行超過了2分鐘，并已經(jīng)掃描了1兆數(shù)據(jù)量的查詢性能問題。如下兩個方面特別值得我們的關(guān)注：

• 表掃描：在大型數(shù)據(jù)表中，如果PCT_TABLE_SCAN的值比較高，或MB_SCANNED的量比較大，則都表明查詢的選擇性比較差。因此，我們需要檢查查詢中的WHERE子句，并適當(dāng)?shù)乜紤]使用集群鍵。
• 溢出：SPILL_TO_LOCAL或SPILL_TO_REMOTE中的任何值，都表明系統(tǒng)在小型虛擬倉庫上進(jìn)行了大型的操作。因此，我們需要考慮將查詢移至更大的倉庫中，或適當(dāng)?shù)貙ΜF(xiàn)有的倉庫進(jìn)行擴(kuò)容。

總結(jié)

業(yè)界關(guān)于Snowflake的一個常見誤解是：直接擴(kuò)容出更大的倉庫，是提高查詢性能的唯一方案。但這實(shí)際上并不一定是絕好的策略。我們需要厘清問題到底是發(fā)生在獲取數(shù)據(jù)環(huán)節(jié)、還是數(shù)據(jù)轉(zhuǎn)換部分、亦或最終用戶的查詢中。畢竟設(shè)計出可擴(kuò)容的大型倉庫，要比單純的查詢調(diào)整，更適合提高數(shù)據(jù)庫的查詢性能。

原標(biāo)題：Top 3 Snowflake Performance Tuning Tactics ，作者： John Ryan

【51CTO譯稿，合作站點(diǎn)轉(zhuǎn)載請注明原文譯者和出處為51CTO.com】