SQL on Hadoop在快手大數據平臺的實踐與優化
快手大數據架構工程師鐘靚近日在A2M人工智能與機器學習創新峰會分享了題為《SQL on Hadoop在快手大數據平臺的實踐與優化》的演講，主要從SQL on Hadoop介紹、快手SQL on Hadoop平臺概述、SQL on Hadoop在快手的使用經驗和改進分析、快手SQL on Hadoop的未來計劃四方面介紹了SQL on Hadoop架構。
01SQL 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過程中，遇到過很多問題。接下來，我會詳細的為大家闡述快手是如何進行優化及實踐的。
03SQL 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查詢的緩存加速
新引擎的調研與應用
