快手大資料架構工程師鍾靚近日在A2M人工智慧與機器學習創新峰會分享了題為《SQL on Hadoop在快手大資料平臺的實踐與優化》的演講，主要從SQL on Hadoop介紹、快手SQL on Hadoop平臺概述、SQL on Hadoop在快手的使用經驗和改進分析、快手SQL on Hadoop的未來計劃四方面介紹了SQL on Hadoop架構。
01 SQL on Hadoop介紹
SQL on Hadoop，顧名思義它是基於Hadoop生態的一個SQL引擎架構，我們其實常常聽到Hive、SparkSQL、Presto、Impala架構，接下來，我會簡單的描述一下常用的架構情況。
SQL on Hadoop-HIVE
HIVE，一個資料倉儲系統。它將資料結構對映到儲存的資料中，通過SQL對大規模的分散式儲存資料進行讀、寫、管理。
根據定義的資料模式，以及輸出Storage，它會對輸入的SQL經過編譯、優化，生成對應引擎的任務，然後排程執行生成的任務。
HIVE當前支援的引擎型別有：MR、SPARK、TEZ。
基於HIVE本身的架構，還有一些額外的服務提供方式，比如HiveServer2與MetaStoreServer都是Thrift架構。
此外，HiveServer2提供遠端客戶端提交SQL任務的功能，MetaStoreServer則提供遠端客戶端操作後設資料的功能。
SQL on Hadoop介紹-SPARK
Spark，一個快速、易用，以DAG作為執行模式的大規模資料處理的統一分析引擎，主要模組分為SQL引擎、流式處理 、機器學習、圖處理。
SQL on Hadoop介紹-SPARKSQL
SPARKSQL基於SPARK的計算引擎，做到了統一資料訪問，整合Hive，支援標準JDBC連線。SPARKSQL常用於資料互動分析的場景。
SPARKSQL的主要執行邏輯，首先是將SQL解析為語法樹，然後語義分析生成邏輯執行計劃，接著與後設資料互動，進行邏輯執行計劃的優化，最後，將邏輯執行翻譯為物理執行計劃，即RDD lineage，並執行任務。
SQL on Hadoop介紹-PRESTO
PRESTO，一個互動式分析查詢的開源分散式SQL查詢引擎。
因為基於記憶體計算，PRESTO的計算效能大於有大量IO操作的MR和SPARK引擎。它有易於彈性擴充套件，支援可插拔連線的特點。
業內的使用案例很多，包括FaceBook、AirBnb、美團等都有大規模的使用。
SQL on Hadoop介紹-其它業內方案
我們看到這麼多的SQL on Hadoop架構，它側面地說明了這種架構比較實用且成熟。利用SQL on Hadoop架構，我們可以實現支援海量資料處理的需求。
02 快手SQL on Hadoop平臺概述
快手SQL on Hadoop平臺概覽—平臺規模
查詢平臺每日SQL總量在70萬左右，DQL的總量在18萬左右。AdHoc叢集主要用於互動分析及機器查詢，DQL平均耗時為300s；AdHoc在內部有Loacl任務及加速引擎應用，所以查詢要求耗時較低。
ETL叢集主要用於ETL處理以及報表的生成。DQL平均耗時為1000s，DQL P50耗時為100s，DQL P90耗時為4000s，除上述兩大叢集外，其它小的叢集主要用於提供給單獨的業務來使用。
快手SQL on Hadoop平臺概覽—服務層次
服務層是對上層進行應用的。在上層有四個模組，這其中包括同步服務、ETL平臺、AdHoc平臺以及使用者程式。在排程上層，同樣也有四方面的資料，例如服務端日誌，對它進行處理後，它會直接接入到HDFS裡，我們後續會再對它進行清洗處理；服務打點的資料以及資料庫資訊，則會通過同步服務入到對應的資料來源裡，且我們會將後設資料資訊存在後端後設資料系統中。
網頁爬取的資料會存入hbase，後續也會進行清洗與處理。
快手SQL on Hadoop平臺概覽—平臺元件說明
HUE、NoteBook主要提供的是互動式查詢的系統。報表系統、BI系統主要是ETL處理以及常見的報表生成，額外的後設資料系統是對外進行服務的。快手現在的引擎支援MR、Presto及Spark。
管理系統主要用於管理我們當前的叢集。HiveServer2叢集路由系統，主要用於引擎的選擇。監控系統以及運維繫統，主要是對於HiveServer2引擎進行運維。
我們在使用HiveServer2過程中，遇到過很多問題。接下來，我會詳細的為大家闡述快手是如何進行優化及實踐的。
03 SQL on Hadoop在快手的使用經驗和改進分析
HiveServer2多叢集架構
當前有多個HiveServer2叢集，分別是AdHoc與ETL兩大叢集，以及其他小叢集。不同叢集有對應的連線ZK，客戶端可通過ZK連線HiveServer2叢集。
為了保證核心任務的穩定性，將ETL叢集進行了分級，分為核心叢集和一般叢集。在客戶端連線HS2的時候，我們會對任務優先順序判定，高優先順序的任務會被路由到核心叢集，低優先順序的任務會被路由到一般叢集。
HiveServer2服務內部流程圖
BeaconServer服務
BeaconServer服務為後端Hook Server服務，配合HS2中的Hook，在HS2服務之外實現了所需的功能。當前支援的模組包括路由、審計、SQL重寫、任務控制、錯誤分析、優化建議等。
- 無狀態，BeaconServer服務支援水平擴充套件。基於請求量的大小，可彈性調整服務的規模。
- 配置動態載入，BeaconServer服務支援動態配置載入。各個模組支援開關，服務可動態載入配置實現上下線。比如路由模組，可根據後端加速引擎叢集資源情況 ，進行路由比率調整甚至熔斷。
- 無縫升級，BeaconServer服務的後端模組可單獨進行下線升級操作，不會影響Hook端HS2服務。
SQL on Hadoop平臺在使用中遇到的痛點
使用新引擎進行加速面臨的問題
- Hive支援SPARK與TEZ引擎，但不適用於生產環境。
- SQL on Hadoop的SQL引擎各有優缺點，使用者學習和使用的門檻較高。
- 不同SQL引擎之間的語法和功能支援上存在差異，需要大量的測試和相容工作，完全相容的成本較高。
- 不同SQL引擎各自提供服務會給數倉的血緣管理、許可權控制、運維管理、資源利用都帶來不便。
智慧引擎的解決方案
- 在Hive中，自定義實現引擎。
- 自動路由功能，不需要設定引擎，自動選擇適合的加速引擎。
- 根絕規則匹配SQL，只將相容的SQL推給加速引擎。
- 複用HiveServer2叢集架構。
智慧引擎：主流引擎方案對比
智慧引擎：HiveServer2自定義執行引擎的模組設計
基於HiveServer2，有兩種實現方式。JDBC方式是通過JDBC介面，將SQL傳送至後端加速引擎啟動的叢集上。PROXY方式是將SQL下推給本地的加速引擎啟動的Client。
JDBC方式啟動的後端叢集，均是基於YARN，可以實現資源的分時複用。比如AdHoc叢集的資源在夜間會自動回收，作為報表系統的資源進行復用。
智慧引擎：SQL路由方案設計架構
路由方案基於HS2的Hook架構，在HS2端實現對應 Hook，用於引擎切換；後端BeaconServer服務中實現路由 服務，用於SQL的路由規則的匹配處理。不同叢集可配置不同的路由規則。
為了保證後算路由服務的穩定性，團隊還設計了Rewrite Hook，用於重寫AdHoc叢集中的SQL，自動新增LIMIT上限，防止大資料量的SCAN。
智慧引擎：SQL路由規則一覽
智慧引擎：方案優勢
- 易於整合，當前主流的SQL引擎都可以方便的實現JDBC與PROXY方式。再通過配置，能簡單的整合新的查詢引擎，比如impala、drill等。
- 自動選擇引擎，減少了使用者的引擎使用成本，同時也讓遷移變得更簡單。並且在加速引擎過載 的情況下，可以動態調整比例，防止因過載 對加速效能的影響。
- 自動降級，保證了執行的可靠性。SQL路由支援failback模組，可以根據配置選擇是否再路由引擎執行失敗後，回滾到 MR執行。
- 模組複用，對於新增的引擎，都可以複用HiveServer2定製的血緣採集、許可權認證、併發鎖控制等方案，大大降低了使用成本。
- 資源複用，對於adhoc查詢佔用資源可以分時動態調整，有效保證叢集資源的利用率。
智慧引擎DQL應用效果
HiveServer2中存在的效能問題
FetchTask加速：預排序與邏輯優化
當查詢完成後，本地會輪詢結果檔案，一直獲取到LIMIT大小，然後返回。這種情況下，當有大量的小檔案存在，而大檔案在後端的時候，會導致Bad Case，不停與HDFS互動，獲取檔案資訊以及檔案資料，大大拉長執行時間。
在Fetch之前，對結果檔案的大小進行預排序，可以有數百倍的效能提升。
示例：當前有200個檔案。199個小檔案一條記錄a，1個大檔案混合記錄a與test共200條，大檔名index在小檔案之後。
FetchTask加速：預排序與邏輯優化
Hive中有一個SimpleFetchOptimizer優化器，會直接生成FetchTask，減小資源申請時間與排程時間。但這個優化會出現瓶頸。如果資料量小，但是檔案數多，需要返回的條數多， 存在能大量篩掉結果資料的Filter條件。這時候序列讀取輸入檔案，導致查詢延遲大，反而沒起到加速效果。
在SimpleFetchOptimizer優化器中，新增檔案數的判斷條件，最後將任務提交到叢集環境， 通過提高併發來實現加速。
示例：讀取當前500個檔案的分割槽。優化後的檔案數閾值為100。
大表Desc Table優化
一個表有大量的子分割槽，它的DESC過程會與後設資料互動，獲取所有的分割槽。但最後返回的結果，只有跟表相關的資訊。
與後設資料互動的時候，延遲了整個DESC的查詢，當後設資料壓力大的時候甚至無法返回結果。
針對於TABLE的DESC過程，直接去掉了跟後設資料互動獲取分割槽的過程，加速時間跟子分割槽數量成正比。
示例：desc十萬分割槽的大表。
其它改進
- 複用split計算的資料，跳過reduce估算重複統計輸入過程。輸入資料量大的任務，排程速率提升50%。
- parquetSerde init加速，跳過同一表的重複列剪枝優化，防止map task op init時間超時。
- 新增LazyOutputFormat，有record輸出再建立檔案，避免空檔案的產生，導致下游讀取大量空檔案消耗時間。
- statsTask支援多執行緒聚合統計資訊，防止中間檔案過多導致聚合過慢，增大執行時間。
- AdHoc需要開啟並行編譯，防止SQL序列編譯導致整體延遲時間增大的問題。
SQL on Hadoop平臺在使用中遇到的痛點
SQL on Hadoop在快手使用：常見可用性問題
HiveServer2服務啟動優化
HS2啟動時會對物化檢視功能進行初始化，輪詢整個元資料庫，導致HS2的啟動時間非常長，從下線狀態到重新上線間隔過大，可用性很差。
將物化檢視功能修改為延遲懶載入，單獨執行緒載入，不影響HS2的服務啟動。物化檢視支援載入中獲取已快取資訊，保證功能的可用性。
HS2啟動時間從5min+提升至<5s。
HiveServer2配置熱載入
HS2本身上下線成本較高，需要保證服務上的任務全部執行完成才能進行操作。配置的修改可作為較高頻率的操作，且需要做到熱載入。
在HS2的ThriftServer層我們增加了介面，與運維繫統打通後，配置下推更新的時候自動呼叫，可實現配置的熱載入生效。
HiveServer2的Scratchdir優化
HiveServer2的scratchdir主要用於執行過程中的臨時檔案儲存。當HS2中的會話建立時，便會建立scratchdir。 在HDFS壓力大的時候，大量的會話會阻塞在建立scratchdir過程，導致連線數堆積至上限，最終HS2服務無法再連入新連線，影響服務可用性。
對此，我們先分離了一般查詢與create temporay table查詢的scratch目錄，並支援create temporay table查詢的scratch的懶建立。 當create temporay table大量建立臨時檔案，便會影響HDFS NameNode延遲時間的時候，一般查詢的scratchdir HDFS NameNode可以正常響應。
此外，HS2還支援配置多scratch，不同的scratch能設定載入比率，從而實現HDFS的均衡負載。
Hive Stage併發排程異常修復
Hive排程其中存在兩個問題。
一、子Task非執行狀態為完成情況的時候，若有多輪父Task包含子Task，導致子Task被重複加入排程佇列。這種Case，需要將非執行狀態修改成初始化狀態。
二、當判斷子Task是否可執行的過程中，會因為狀態檢測異常，無法正常加入需要排程的子Task，從而致使查詢丟失Stage。而這種Case，我們的做法是在執行完成後，加入一輪Stage的執行結果狀態檢查，一旦發現有下游Stage沒有完成，直接丟擲錯誤，實現查詢結果狀態的完備性檢查。
其它改進
- HS2實現了介面終止查詢SQL。利用這個功能，可以及時終止異常SQL。
- metastore JDOQuery查詢優化，關鍵字異常跳過，防止後設資料長時間卡頓或者部分異常查詢影響後設資料。
- 增加開關控制，強制覆蓋外表目錄，解決insert overwrite外表，檔案rename報錯的問題。
- hive parquet下推增加關閉配置，避免parquet異常地下推OR條件，導致結果不正確。
- executeForArray函式join超大字串導致OOM，增加限制優化。
- 增加根據table的schema讀取分割槽資料的功能，避免未級聯修改分割槽schema導致讀取資料異常。
SQL on Hadoop平臺在使用中遇到的痛點
為什麼要開發SQL專家系統
- 部分使用者並沒有開發經驗，無法處理處理引擎返回的報錯。
- 有些錯誤的報錯資訊不明確，使用者無法正確瞭解錯誤原因。
- 失敗的任務排查成本高，需要對Hadoop整套系統非常熟悉。
- 使用者的錯誤SQL、以及需要優化的SQL，大量具有共通性。人力維護成本高，但系統分析成本低。
SQL專家系統
SQL專家系統基於HS2的Hook架構，在BeaconServer後端實現了三個主要的模組，分別是SQL規則控制模組、SQL錯誤分析模組，與SQL優化建議模組。SQL專家系統的知識庫，包含關鍵字、原因說明、處理方案等幾項主要資訊，存於後端資料庫中，並一直積累。
通過SQL專家系統，後端可以進行查詢SQL的異常控制，避免異常SQL的資源浪費或者影響叢集穩定。使用者在遇到問題時，能直接獲取問題的處理方案，減少了使用成本。
示例：空分割槽查詢控制。
作業診斷系統
SQL專家系統能解決一部分HS2的任務執行的錯誤診斷需求，但是比如作業健康度、任務執行異常等問題原因的判斷，需要專門的系統來解決，為此我們設計了作業診斷系統。
作業診斷系統在YARN的層面，針對不同的執行引擎，對蒐集的Counter和配置進行分析。在執行層面，提出相關的優化建議。
作業診斷系統的資料也能通過API提供給SQL專家系統，補充用於分析的問題原因。
作業診斷系統提供了查詢頁面來查詢執行的任務。以下是命中map輸入過多規則的任務查詢過程：
在作業介面，還可以檢視更多的作業診斷資訊，以及作業的修改建議。
SQL on Hadoop平臺在使用中遇到的痛點
SQL on Hadoop在快手使用：常見運維性問題
審計分析 - 架構圖
審計功能也是BeaconServer服務的一個模組。
通過HS2中配置的Hook，傳送需要的SQL、IP、User等資訊至後端，進行語法分析，便可提取出DataBase、Table、Columns與操作資訊，將其分析後再存入Druid系統。使用者可通過視覺化平臺查詢部分開放的資料。
審計分析 - 熱點資訊查詢
熱點資訊查詢即將熱點資訊展示了一段時間以內，使用者的熱點操作，這其中包括訪問過哪些庫，哪些表，以及哪些型別的操作。
審計分析 - 血緣資訊查詢
下圖可看出，血緣資訊展示了一張表建立的上游依賴，一般用於統計表的影響範圍。
審計分析 - 歷史操作查詢
歷史操作可以溯源到一段時間內，對於某張表的操作。能獲取到操作的使用者、客戶端、平臺、以及時間等資訊。一般用於跟蹤表的增刪改情況。
HiveServer2叢集AB切換方案
因為HiveServer2服務本身的上下線成本較高，如果要執行一次升級操作，往往耗時較長且影響可用性。HiveServer2叢集的AB切換方案，主要依靠A叢集線上，B叢集備用的方式，通過切換ZK上的線上叢集機器，來實現無縫的升級操作。
HiveServer2叢集動態上下線
HiveServer2叢集部署了Metrics監控，能夠實時地跟蹤叢集服務的使用情況。此外，我們對HS2服務進行了改造，實現了HS2 ZK下線和請求Cancel的介面。
當外部Monitor監控感知到連續記憶體過高，會自動觸發HS2服務程式的FGC操作，如果記憶體依然連續過高，則通過ZK直接下線服務，並根據查詢提交的時間順序，依次停止查詢，直到記憶體恢復，保證服務中剩餘任務的正常執行。
HiveServer2叢集管理平臺
HiveServer2在多叢集狀態下，需要掌握每個叢集、以及每個HS2服務的狀態。通過管理平臺，可以檢視版本情況、啟動時間、資源使用情況以及上下線狀態。
後續跟運維平臺打通，可以更方便地進行一鍵式灰度以及升級。
快手查詢平臺的改進總結
04 快手SQL on Hadoop的未來計劃
- 專家系統的升級，實現自動化引數調優和SQL優化
- AdHoc查詢的快取加速
新引擎的調研與應用