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

影響Snowflake查詢(xún)性能的因素

作為技術(shù)人員，我們經(jīng)常需要在對(duì)問(wèn)題不甚了了的情況下，提出并實(shí)施解決方案。那么總的說(shuō)來(lái)，我們?cè)诜治銎脚_(tái)上的性能問(wèn)題時(shí)，通常會(huì)從如下三個(gè)方面入手：

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

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

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

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

避免掃描文件

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

那么在復(fù)制數(shù)據(jù)之前，Snowflake會(huì)檢查文件是否已被加載。這是通過(guò)限制針對(duì)某個(gè)特定目錄的COPY，來(lái)實(shí)現(xiàn)最大化加載性能的第一種、也是最簡(jiǎ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; 

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

調(diào)整虛擬倉(cāng)庫(kù)和文件的大小

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

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

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

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

延遲與吞吐量

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

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

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

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

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

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

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的查詢(xún)性能

選擇必要列

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

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

最大化緩存使用率

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

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

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

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

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代碼段可以幫助我們識(shí)別出，那些運(yùn)行超過(guò)了2分鐘，并已經(jīng)掃描了1兆數(shù)據(jù)量的查詢(xún)性能問(wèn)題。如下兩個(gè)方面特別值得我們的關(guān)注：

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

總結(jié)

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

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

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