SQL on Hadoop在快手大數據平臺的實踐與優化

快手大數據架構工程師鍾靚

本文是根據快手大數據架構工程師鍾靚於 5月18-19日在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查詢的緩存加速

新引擎的調研與應用

以上內容來自鍾靚老師的分享。是否還想看更多關於快手老師的演講？6月21-23日來參加GIAC全球互聯網架構大會深圳站吧~我們邀請到了快手應用研發部測試負責人羋峮，將為我們講述《快手移動端線上質量監控》的話題。

此外，本屆大會，組委會還邀請到了105位來自Google、微軟、Oracle、eBay、百度、阿里、騰訊、商湯、圖森、字節跳動、新浪、美團點評等一線互聯網大廠嘉賓出席，圍繞AI、大中臺、Cloud-Native、IoT、混沌工程、Fintech、數據及商業智能、工程文化及管理、經典架構等專題分享他們的實踐經驗、遇到的問題及解決方案。現在填寫報名信息，

還可免費獲得GIAC峰會所有的PPT！快來識別圖中二維碼報名吧！