技術(shù)干貨：SQL on Hadoop在快手大數(shù)據(jù)平臺的實踐與優(yōu)化

創(chuàng)見網(wǎng) ? 6年前掃碼分享

我是創(chuàng)始人李巖：很抱歉！給自己產(chǎn)品做個廣告，點擊進來看看。

快手大數(shù)據(jù)架構(gòu)工程師鐘靚近日在 A2M 人工智能與機器學習創(chuàng)新峰會分享了題為《SQL on Hadoop 在快手大數(shù)據(jù)平臺的實踐與優(yōu)化》的演講，主要從 SQL on Hadoop 介紹、快手 SQL on Hadoop 平臺概述、SQL on Hadoop 在快手的使用經(jīng)驗和改進分析、快手 SQL on Hadoop 的未來計劃四方面介紹了 SQL on Hadoop 架構(gòu)。

SQL on Hadoop 介紹

SQL on Hadoop，顧名思義它是基于 Hadoop 生態(tài)的一個 SQL 引擎架構(gòu)，我們其實常常聽到 Hive、SparkSQL、Presto、Impala 架構(gòu)。接下來，我會簡單的描述一下常用的架構(gòu)情況。

SQL on Hadoop-HIVE

HIVE，一個數(shù)據(jù)倉庫系統(tǒng)。它將數(shù)據(jù)結(jié)構(gòu)映射到存儲的數(shù)據(jù)中，通過 SQL 對大規(guī)模的分布式存儲數(shù)據(jù)進行讀、寫、管理。

技術(shù)干貨：SQL on Hadoop在快手大數(shù)據(jù)平臺的實踐與優(yōu)化

根據(jù)定義的數(shù)據(jù)模式，以及輸出 Storage，它會對輸入的 SQL 經(jīng)過編譯、優(yōu)化，生成對應(yīng)引擎的任務(wù)，然后調(diào)度執(zhí)行生成的任務(wù)。

HIVE 當前支持的引擎類型有：MR、SPARK、TEZ。

基于 HIVE 本身的架構(gòu)，還有一些額外的服務(wù)提供方式，比如 HiveServer2 與 MetaStoreServer 都是 Thrift 架構(gòu)。

此外，HiveServer2 提供遠程客戶端提交 SQL 任務(wù)的功能，MetaStoreServer 則提供遠程客戶端操作元數(shù)據(jù)的功能。

SQL on Hadoop 介紹-SPARK

Spark，一個快速、易用，以 DAG 作為執(zhí)行模式的大規(guī)模數(shù)據(jù)處理的統(tǒng)一分析引擎，主要模塊分為 SQL 引擎、流式處理、機器學習、圖處理。

SQL on Hadoop 介紹-SPARKSQL

SPARKSQL 基于 SPARK 的計算引擎，做到了統(tǒng)一數(shù)據(jù)訪問，集成 Hive，支持標準 JDBC 連接。SPARKSQL 常用于數(shù)據(jù)交互分析的場景。

SPARKSQL 的主要執(zhí)行邏輯，首先是將 SQL 解析為語法樹，然后語義分析生成邏輯執(zhí)行計劃，接著與元數(shù)據(jù)交互，進行邏輯執(zhí)行計劃的優(yōu)化，最后，將邏輯執(zhí)行翻譯為物理執(zhí)行計劃，即 RDD lineage，并執(zhí)行任務(wù)。

SQL on Hadoop 介紹-PRESTO

PRESTO，一個交互式分析查詢的開源分布式 SQL 查詢引擎。

因為基于內(nèi)存計算，PRESTO 的計算性能大于有大量 IO 操作的 MR 和 SPARK 引擎。它有易于彈性擴展，支持可插拔連接的特點。

業(yè)內(nèi)的使用案例很多，包括 FaceBook、AirBnb、美團等都有大規(guī)模的使用。

SQL on Hadoop 介紹-其它業(yè)內(nèi)方案

我們看到這么多的 SQL on Hadoop 架構(gòu)，它側(cè)面地說明了這種架構(gòu)比較實用且成熟。利用 SQL on Hadoop 架構(gòu)，我們可以實現(xiàn)支持海量數(shù)據(jù)處理的需求。

02 快手 SQL on Hadoop 平臺概述

快手 SQL on Hadoop 平臺概覽—平臺規(guī)模

查詢平臺每日 SQL 總量在 70 萬左右，DQL 的總量在 18 萬左右。AdHoc 集群主要用于交互分析及機器查詢，DQL 平均耗時為 300s；AdHoc 在內(nèi)部有 Loacl 任務(wù)及加速引擎應(yīng)用，所以查詢要求耗時較低。

ETL 集群主要用于 ETL 處理以及報表的生成。DQL 平均耗時為 1000s，DQL P50 耗時為 100s，DQL P90 耗時為 4000s，除上述兩大集群外，其它小的集群主要用于提供給單獨的業(yè)務(wù)來使用。

快手 SQL on Hadoop 平臺概覽—服務(wù)層次

服務(wù)層是對上層進行應(yīng)用的。在上層有四個模塊，這其中包括同步服務(wù)、ETL 平臺、AdHoc 平臺以及用戶程序。在調(diào)度上層，同樣也有四方面的數(shù)據(jù)，例如服務(wù)端日志，對它進行處理后，它會直接接入到 HDFS 里，我們后續(xù)會再對它進行清洗處理；服務(wù)打點的數(shù)據(jù)以及數(shù)據(jù)庫信息，則會通過同步服務(wù)入到對應(yīng)的數(shù)據(jù)源里，且我們會將元數(shù)據(jù)信息存在后端元數(shù)據(jù)系統(tǒng)中。

網(wǎng)頁爬取的數(shù)據(jù)會存入 hbase，后續(xù)也會進行清洗與處理。

快手 SQL on Hadoop 平臺概覽—平臺組件說明

HUE、NoteBook 主要提供的是交互式查詢的系統(tǒng)。報表系統(tǒng)、BI 系統(tǒng)主要是 ETL 處理以及常見的報表生成，額外的元數(shù)據(jù)系統(tǒng)是對外進行服務(wù)的?？焓脂F(xiàn)在的引擎支持 MR、Presto 及 Spark。

管理系統(tǒng)主要用于管理我們當前的集群。HiveServer2 集群路由系統(tǒng)，主要用于引擎的選擇。監(jiān)控系統(tǒng)以及運維系統(tǒng)，主要是對于 HiveServer2 引擎進行運維。

我們在使用 HiveServer2 過程中，遇到過很多問題。接下來，我會詳細的為大家闡述快手是如何進行優(yōu)化及實踐的。

03-SQL on Hadoop 在快手的使用經(jīng)驗和改進分析

HiveServer2 多集群架構(gòu)

當前有多個 HiveServer2 集群，分別是 AdHoc 與 ETL 兩大集群，以及其他小集群。不同集群有對應(yīng)的連接 ZK，客戶端可通過 ZK 連接 HiveServer2 集群。

為了保證核心任務(wù)的穩(wěn)定性，將 ETL 集群進行了分級，分為核心集群和一般集群。在客戶端連接 HS2 的時候，我們會對任務(wù)優(yōu)先級判定，高優(yōu)先級的任務(wù)會被路由到核心集群，低優(yōu)先級的任務(wù)會被路由到一般集群。

BeaconServer 服務(wù)為后端 Hook Server 服務(wù)，配合 HS2 中的 Hook，在 HS2 服務(wù)之外實現(xiàn)了所需的功能。當前支持的模塊包括路由、審計、SQL 重寫、任務(wù)控制、錯誤分析、優(yōu)化建議等。

? 無狀態(tài)，BeaconServer 服務(wù)支持水平擴展?；谡埱罅康拇笮?，可彈性調(diào)整服務(wù)的規(guī)模。

? 配置動態(tài)加載，BeaconServer 服務(wù)支持動態(tài)配置加載。各個模塊支持開關(guān)，服務(wù)可動態(tài)加載配置實現(xiàn)上下線。比如路由模塊，可根據(jù)后端加速引擎集群資源情況，進行路由比率調(diào)整甚至熔斷。

? 無縫升級，BeaconServer 服務(wù)的后端模塊可單獨進行下線升級操作，不會影響 Hook 端 HS2 服務(wù)。

SQL on Hadoop 平臺在使用中遇到的痛點

? Hive 支持 SPARK 與 TEZ 引擎，但不適用于生產(chǎn)環(huán)境。

? SQL on Hadoop 的 SQL 引擎各有優(yōu)缺點，用戶學習和使用的門檻較高。

? 不同 SQL 引擎之間的語法和功能支持上存在差異，需要大量的測試和兼容工作，完全兼容的成本較高。

? 不同 SQL 引擎各自提供服務(wù)會給數(shù)倉的血緣管理、權(quán)限控制、運維管理、資源利用都帶來不便。

智能引擎的解決方案

? 在 Hive 中，自定義實現(xiàn)引擎。

? 自動路由功能，不需要設(shè)置引擎，自動選擇適合的加速引擎。

? 根絕規(guī)則匹配 SQL，只將兼容的 SQL 推給加速引擎。

? 復(fù)用 HiveServer2 集群架構(gòu)。

智能引擎：HiveServer2 自定義執(zhí)行引擎的模塊設(shè)計

基于 HiveServer2，有兩種實現(xiàn)方式。JDBC 方式是通過 JDBC 接口，將 SQL 發(fā)送至后端加速引擎啟動的集群上。PROXY 方式是將 SQL 下推給本地的加速引擎啟動的 Client。

JDBC 方式啟動的后端集群，均是基于 YARN，可以實現(xiàn)資源的分時復(fù)用。比如 AdHoc 集群的資源在夜間會自動回收，作為報表系統(tǒng)的資源進行復(fù)用。

路由方案基于 HS2 的 Hook 架構(gòu)，在 HS2 端實現(xiàn)對應(yīng) Hook，用于引擎切換；后端 BeaconServer 服務(wù)中實現(xiàn)路由服務(wù)，用于 SQL 的路由規(guī)則的匹配處理。不同集群可配置不同的路由規(guī)則。

為了保證后算路由服務(wù)的穩(wěn)定性，團隊還設(shè)計了 Rewrite Hook，用于重寫 AdHoc 集群中的 SQL，自動添加 LIMIT 上限，防止大數(shù)據(jù)量的 SCAN。

智能引擎：方案優(yōu)勢

? 易于集成，當前主流的 SQL 引擎都可以方便的實現(xiàn) JDBC 與 PROXY 方式。再通過配置，能簡單的集成新的查詢引擎，比如 impala、drill 等。??

? 自動選擇引擎，減少了用戶的引擎使用成本，同時也讓遷移變得更簡單。并且在加速引擎過載的情況下，可以動態(tài)調(diào)整比例，防止因過載對加速性能的影響。

? 自動降級，保證了運行的可靠性。SQL 路由支持 failback 模塊，可以根據(jù)配置選擇是否再路由引擎執(zhí)行失敗后，回滾到 MR 運行。

? 模塊復(fù)用，對于新增的引擎，都可以復(fù)用 HiveServer2 定制的血緣采集、權(quán)限認證、并發(fā)鎖控制等方案，大大降低了使用成本。

? 資源復(fù)用，對于 adhoc 查詢占用資源可以分時動態(tài)調(diào)整，有效保證集群資源的利用率。

智能引擎 DQL 應(yīng)用效果

HiveServer2 中存在的性能問題

FetchTask 加速：預(yù)排序與邏輯優(yōu)化

當查詢完成后，本地會輪詢結(jié)果文件，一直獲取到 LIMIT 大小，然后返回。這種情況下，當有大量的小文件存在，而大文件在后端的時候，會導(dǎo)致 Bad Case，不停與 HDFS 交互，獲取文件信息以及文件數(shù)據(jù)，大大拉長運行時間。

在 Fetch 之前，對結(jié)果文件的大小進行預(yù)排序，可以有數(shù)百倍的性能提升。

示例：當前有 200 個文件。199 個小文件一條記錄 a，1 個大文件混合記錄 a 與 test 共 200 條，大文件名 index 在小文件之后。

FetchTask 加速：預(yù)排序與邏輯優(yōu)化

Hive 中有一個 SimpleFetchOptimizer 優(yōu)化器，會直接生成 FetchTask，減小資源申請時間與調(diào)度時間。但這個優(yōu)化會出現(xiàn)瓶頸。如果數(shù)據(jù)量小，但是文件數(shù)多，需要返回的條數(shù)多，存在能大量篩掉結(jié)果數(shù)據(jù)的 Filter 條件。這時候串行讀取輸入文件，導(dǎo)致查詢延遲大，反而沒起到加速效果。

在 SimpleFetchOptimizer 優(yōu)化器中，新增文件數(shù)的判斷條件，最后將任務(wù)提交到集群環(huán)境，通過提高并發(fā)來實現(xiàn)加速。

示例：讀取當前 500 個文件的分區(qū)。優(yōu)化后的文件數(shù)閾值為 100。

大表 Desc Table 優(yōu)化

一個表有大量的子分區(qū)，它的 DESC 過程會與元數(shù)據(jù)交互，獲取所有的分區(qū)。但最后返回的結(jié)果，只有跟表相關(guān)的信息。

與元數(shù)據(jù)交互的時候，延遲了整個 DESC 的查詢，當元數(shù)據(jù)壓力大的時候甚至無法返回結(jié)果。

針對于 TABLE 的 DESC 過程，直接去掉了跟元數(shù)據(jù)交互獲取分區(qū)的過程，加速時間跟子分區(qū)數(shù)量成正比。

示例：desc 十萬分區(qū)的大表。

其它改進

? 復(fù)用 split 計算的數(shù)據(jù)，跳過 reduce 估算重復(fù)統(tǒng)計輸入過程。輸入數(shù)據(jù)量大的任務(wù)，調(diào)度速率提升 50%。

? parquetSerde init 加速，跳過同一表的重復(fù)列剪枝優(yōu)化，防止 map task op init 時間超時。

? 新增 LazyOutputFormat，有 record 輸出再創(chuàng)建文件，避免空文件的產(chǎn)生，導(dǎo)致下游讀取大量空文件消耗時間。

? statsTask 支持多線程聚合統(tǒng)計信息，防止中間文件過多導(dǎo)致聚合過慢，增大運行時間。

? AdHoc 需要打開并行編譯，防止 SQL 串行編譯導(dǎo)致整體延遲時間增大的問題。

SQL on Hadoop 平臺在使用中遇到的痛點

HiveServer2 服務(wù)啟動優(yōu)化

HS2 啟動時會對物化視圖功能進行初始化，輪詢整個元數(shù)據(jù)庫，導(dǎo)致 HS2 的啟動時間非常長，從下線狀態(tài)到重新上線間隔過大，可用性很差。

將物化視圖功能修改為延遲懶加載，單獨線程加載，不影響 HS2 的服務(wù)啟動。物化視圖支持加載中獲取已緩存信息，保證功能的可用性。

HS2 啟動時間從 5min+提升至<5s。

HiveServer2 配置熱加載

HS2 本身上下線成本較高，需要保證服務(wù)上的任務(wù)全部執(zhí)行完成才能進行操作。配置的修改可作為較高頻率的操作，且需要做到熱加載。

在 HS2 的 ThriftServer 層我們增加了接口，與運維系統(tǒng)打通后，配置下推更新的時候自動調(diào)用，可實現(xiàn)配置的熱加載生效。

HiveServer2 的 Scratchdir 優(yōu)化

HiveServer2 的 scratchdir 主要用于運行過程中的臨時文件存儲。當 HS2 中的會話創(chuàng)建時，便會創(chuàng)建 scratchdir。在 HDFS 壓力大的時候，大量的會話會阻塞在創(chuàng)建 scratchdir 過程，導(dǎo)致連接數(shù)堆積至上限，最終 HS2 服務(wù)無法再連入新連接，影響服務(wù)可用性。

對此，我們先分離了一般查詢與 create temporay table 查詢的 scratch 目錄，并支持 create temporay table 查詢的 scratch 的懶創(chuàng)建。當 create temporay table 大量創(chuàng)建臨時文件，便會影響 HDFS NameNode 延遲時間的時候，一般查詢的 scratchdir HDFS NameNode 可以正常響應(yīng)。

此外，HS2 還支持配置多 scratch，不同的 scratch 能設(shè)置加載比率，從而實現(xiàn) HDFS 的均衡負載。

Hive Stage 并發(fā)調(diào)度異常修復(fù)

Hive 調(diào)度其中存在兩個問題。

一、子 Task 非執(zhí)行狀態(tài)為完成情況的時候，若有多輪父 Task 包含子 Task，導(dǎo)致子 Task 被重復(fù)加入調(diào)度隊列。這種 Case，需要將非執(zhí)行狀態(tài)修改成初始化狀態(tài)。

二、當判斷子 Task 是否可執(zhí)行的過程中，會因為狀態(tài)檢測異常，無法正常加入需要調(diào)度的子 Task，從而致使查詢丟失 Stage。而這種 Case，我們的做法是在執(zhí)行完成后，加入一輪 Stage 的執(zhí)行結(jié)果狀態(tài)檢查，一旦發(fā)現(xiàn)有下游 Stage 沒有完成，直接拋出錯誤，實現(xiàn)查詢結(jié)果狀態(tài)的完備性檢查。

其它改進

? HS2 實現(xiàn)了接口終止查詢 SQL。利用這個功能，可以及時終止異常 SQL。

? metastore JDOQuery 查詢優(yōu)化，關(guān)鍵字異常跳過，防止元數(shù)據(jù)長時間卡頓或者部分異常查詢影響元數(shù)據(jù)。

? 增加開關(guān)控制，強制覆蓋外表目錄，解決 insert overwrite 外表，文件 rename 報錯的問題。

? hive parquet 下推增加關(guān)閉配置，避免 parquet 異常地下推 OR 條件，導(dǎo)致結(jié)果不正確。

? executeForArray 函數(shù) join 超大字符串導(dǎo)致 OOM，增加限制優(yōu)化。

? 增加根據(jù) table 的 schema 讀取分區(qū)數(shù)據(jù)的功能，避免未級聯(lián)修改分區(qū) schema 導(dǎo)致讀取數(shù)據(jù)異常。

SQL on Hadoop 平臺在使用中遇到的痛點

為什么要開發(fā) SQL 專家系統(tǒng)

? 部分用戶并沒有開發(fā)經(jīng)驗，無法處理處理引擎返回的報錯。

? 有些錯誤的報錯信息不明確，用戶無法正確了解錯誤原因。

? 失敗的任務(wù)排查成本高，需要對 Hadoop 整套系統(tǒng)非常熟悉。

? 用戶的錯誤 SQL、以及需要優(yōu)化的 SQL，大量具有共通性。人力維護成本高，但系統(tǒng)分析成本低。

SQL 專家系統(tǒng)

SQL 專家系統(tǒng)基于 HS2 的 Hook 架構(gòu)，在 BeaconServer 后端實現(xiàn)了三個主要的模塊，分別是 SQL 規(guī)則控制模塊、SQL 錯誤分析模塊，與 SQL 優(yōu)化建議模塊。SQL 專家系統(tǒng)的知識庫，包含關(guān)鍵字、原因說明、處理方案等幾項主要信息，存于后端數(shù)據(jù)庫中，并一直積累。

通過 SQL 專家系統(tǒng)，后端可以進行查詢 SQL 的異常控制，避免異常 SQL 的資源浪費或者影響集群穩(wěn)定。用戶在遇到問題時，能直接獲取問題的處理方案，減少了使用成本。

示例：空分區(qū)查詢控制。

作業(yè)診斷系統(tǒng)

SQL 專家系統(tǒng)能解決一部分 HS2 的任務(wù)執(zhí)行的錯誤診斷需求，但是比如作業(yè)健康度、任務(wù)執(zhí)行異常等問題原因的判斷，需要專門的系統(tǒng)來解決，為此我們設(shè)計了作業(yè)診斷系統(tǒng)。

作業(yè)診斷系統(tǒng)在 YARN 的層面，針對不同的執(zhí)行引擎，對搜集的 Counter 和配置進行分析。在執(zhí)行層面，提出相關(guān)的優(yōu)化建議。

作業(yè)診斷系統(tǒng)的數(shù)據(jù)也能通過 API 提供給 SQL 專家系統(tǒng)，補充用于分析的問題原因。

作業(yè)診斷系統(tǒng)提供了查詢頁面來查詢運行的任務(wù)。以下是命中 map 輸入過多規(guī)則的任務(wù)查詢過程：

在作業(yè)界面，還可以查看更多的作業(yè)診斷信息，以及作業(yè)的修改建議。

SQL on Hadoop 平臺在使用中遇到的痛點

SQL on Hadoop 在快手使用：常見運維性問題

審計分析 - 架構(gòu)圖

審計功能也是 BeaconServer 服務(wù)的一個模塊。

通過 HS2 中配置的 Hook，發(fā)送需要的 SQL、IP、User 等信息至后端，進行語法分析，便可提取出 DataBase、Table、Columns 與操作信息，將其分析后再存入 Druid 系統(tǒng)。用戶可通過可視化平臺查詢部分開放的數(shù)據(jù)。

審計分析 - 熱點信息查詢

熱點信息查詢即將熱點信息展示了一段時間以內(nèi)，用戶的熱點操作，這其中包括訪問過哪些庫，哪些表，以及哪些類型的操作。

審計分析 - 血緣信息查詢

下圖可看出，血緣信息展示了一張表創(chuàng)建的上游依賴，一般用于統(tǒng)計表的影響范圍。

審計分析 - 歷史操作查詢

歷史操作可以溯源到一段時間內(nèi)，對于某張表的操作。能獲取到操作的用戶、客戶端、平臺、以及時間等信息。一般用于跟蹤表的增刪改情況。

HiveServer2 集群 AB 切換方案

因為 HiveServer2 服務(wù)本身的上下線成本較高，如果要執(zhí)行一次升級操作，往往耗時較長且影響可用性。HiveServer2 集群的 AB 切換方案，主要依靠 A 集群在線，B 集群備用的方式，通過切換 ZK 上的在線集群機器，來實現(xiàn)無縫的升級操作。

HiveServer2 集群動態(tài)上下線

HiveServer2 集群部署了 Metrics 監(jiān)控，能夠?qū)崟r地跟蹤集群服務(wù)的使用情況。此外，我們對 HS2 服務(wù)進行了改造，實現(xiàn)了 HS2 ZK 下線和請求 Cancel 的接口。

當外部 Monitor 監(jiān)控感知到連續(xù)內(nèi)存過高，會自動觸發(fā) HS2 服務(wù)進程的 FGC 操作，如果內(nèi)存依然連續(xù)過高，則通過 ZK 直接下線服務(wù)，并根據(jù)查詢提交的時間順序，依次停止查詢，直到內(nèi)存恢復(fù)，保證服務(wù)中剩余任務(wù)的正常運行。