HIVE優化淺談

作者：鄧力，entobit技術總監，八年大資料從業經曆，由一代HADOOP入坑，深耕雲計算應用領域，由從事亞馬遜EMR和阿裡雲EMR應用開發逐漸轉入大資料架構領域，對大資料生态及架構應用有深刻了解。

引言

随着商務/營運同學執行的HQL越來越多，整體HIVE執行效率變低，本文從HIVE切入，分析HQL面臨的問題和待優化部分，結合其他大資料架構來解決實際問題。以下内容沒有針對業務代碼提供優化建議.

常見的HQL

select型

設定hive.fetch.task.conversion=none會以叢集模式運作，無論是否有limit。在資料量小時建議使用hive.fetch.task.conversion=more,此時select配合limit以單機執行擷取樣本資料，執行更快

常見的select配合order by/group by等基本操作不在此贅述

注：

select查詢可以通過split.maxsize和split.minsize控制并發MAPPER數量

insert型

分為兩種

• insert into
• insert overwrite

配合分區可以達到重寫分區或者在分區追加資料的目的。還可以配合動态分區模式插入對應分區

開啟動态分區:

// 開啟動态分區模式
set hive.exec.dynamic.partition=true;
// 開啟動态分區非嚴格模式（多分區時首分區支援動态分區必要條件，首分區為靜态分區可以不設定）
set hive.exec.dynamic.partition.mode=nonstrict;
// 單節點上限
set hive.exec.max.dynamic.partitions.pernode=100;
// 叢集上限
set hive.exec.max.dynamic.partitions=1000;           

開啟之後可以利用SQL達到動态插入的目的:

// 根據分區day動态插入資料
insert into table default.test partition(day) select id,day from orginal           

CTAS

全稱CREATE TABLE AS SELECT語句，文法和MYSQL類似，可以指定存儲/壓縮包等

// 采用parquet存儲已準備配合SparkSQL使用
create table default.parquet_test stored as parquet as select * from default.test

// 同時可以指定壓縮格式
create table default.parquet_test stored as parquet
TBLPROPERTIES ( 'orc.compress'='SNAPPY')
as select * from default.test

// 指定OSS作為存儲(推薦)
create table default.parquet_test stored as parquet location 'oss:xxx:yyy/parquet/test'           

JOIN優化

上面提到了常見的不同HQL類型，實際在執行的HQL中更可能變慢的時JOIN部分，以下會根據不同的場景來分析

大表x小表

這裡可以利用mapjoin，SparkSQL中也有mapjoin或者使用廣播變量能達到同樣效果，此處描述HQL

// 開啟mapjoin并設定map表大小
set hive.auto.convert.join.noconditionaltask = true;
set hive.auto.convert.join.noconditionaltask.size = 10000000;
// 大表 join 小表
select * from big_table join small_table on big_table.id=small_table.id           

原理：将小表加載進入節點容器記憶體中，大表可以直接讀取節點容器記憶體中的資料進行比對過濾

大表x大表

小表可以放進記憶體，大表則不行。盡量避免大表x大表的執行需求。如果确認有此需求，可以參考以下方法

1. 嘗試将大右表自我join成為一張寬表

   // 利用右表的唯一屬性自我join
   select id, case when type='food' then 0 else 1 as type_tag,case when
   sale_type='city' then sales else null as sale_amount from group by id           

2. 嘗試先将大表按照主鍵分桶後join

   create table new_left as select * from left_table cluster by id
   create table new_right as select * from right_table cluster by id
   select * from new_left join new_right on new_left.id=new_right.id           

3. 根據資料大小量級合理增加reduce數量，reduce不宜設定過大

   // hadoop1代
   set mapred.reduce.tasks= 200;
   // hadoop2代
   set mapreduce.job.reduces=200;           

4. 利用ORC bloomfilter, 大幅度提高join效率

   注：parquet bloomfilter在開發中

   // 建立orc表
   create table default.right_orc stored as orcfile TBLPROPERTIES
   ('orc.compress'='SNAPPY',
   'orc.create.index'='true',
   'orc.bloom.filter.columns'='id')
   as select * from right_table
   // 使用新表join
   select * from left_orc join right_orc on left_orc.id=righ_orc.id           

5. 調整記憶體限制

   join時容易造成節點OOM，導緻任務失敗，可以嘗試以下方法：

   • map階段OOM，适當增加map階段記憶體 set mapreduce.map.memory.mb=3096

   • reduce階段OOM，适當增加reduce階段記憶體 set mapreduce.reduce.memory.mb=4096

     注: 預設執行引擎為mr，如果是TEZ，參考tez優化部分

6. 善用explain/analyze

   使用explain和analyze分析HQL語句和表，試圖從中找出實際資料中可以優化的部分，這裡和資料強關聯，需要根據實際資料考量

7. 資料預處理。将部分join放入離線計算任務，減少業務join的時間

更多思考

1. 檔案壓縮後仍然很大：可以使用GZIP壓縮代替SNAPPY，但是性能比SNAPPY差很多
2. HQL隊列擁擠：可以參考隊列搶占式資源排程政策，對小任務支援更好
3. HIVE作為資料倉庫/互動式查詢的優秀手段之一，是否有更好的計算架構可以替代：EMR SparkSQL可以替代大部分HIVE應用場景，并且3.22版本relational cache帶來了極強的性能優化。推薦使用

總結

HIVE本身确實是資料倉庫和互動式查詢的優秀架構，但随着資料的增多，join的複雜度和性能問題，需要花時間和精力解決性能優化的問題。除了基于HIVE本身優化，還可以接入計算性能更好的架構，SparkSQL relational cache對使用者透明，開發不需要關心底層優化邏輯，可以将更多精力放入業務設計開發中。後續将針對SparkSQL部分提供個人經驗供參考。

歡迎對EMR及相關技術感興趣的同學進釘釘群一起讨論 :)