Ⅰ Hive優化
（二）數據傾斜的解決方案
1、參數調節
hive.map.aggr=true
Map 端部分聚合，相當於Combiner
hive.groupby.skewindata=true
有數據傾斜的時候 進行負載均衡 ，當選項設定為true，生成的查詢計劃會有兩個 MR Job。第一個 MR Job 中，Map 的輸出結果集合會隨機分布到 Rece 中，每個 Rece 做部分聚合操作，並輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Rece 中 ，從而達到負載均衡的目的；第二個 MR Job 再根據預處理的數據結果按照 Group By Key 分布到 Rece 中（這個過程可以保證相同的 Group By Key 被分布到同一個 Rece 中），最後完成最終的聚合操作。
2、SQL語句調節
Join：
關於驅動表的選取：選用join key分布最均勻的表作為驅動表
做好列裁剪和filter操作，以達到兩表做join的時候，數據量相對變小的效果。
大表與小表Join
使用map join讓小的維度表（1000條以下的記錄條數）先進內存。在map端完成rece。
大表與大表Join：
把空值的key變成一個字元串加上隨機數，把傾斜的數據分到不同的rece上，由於null值關聯不上，處理後並不影響最終結果。
count distinct大量相同特殊值：
count distinct時，將值為空的情況單獨處理，如果是計算count distinct，可以不用處理，直接過濾，在最後結果中加1。如果還有其他計算，需要進行group by，可以先將值為空的記錄單獨處理，再和其他計算結果進行union。
group by維度過小：
採用sum() 與group by的方式來替換count(distinct)完成計算。
特殊情況特殊處理：
在業務邏輯優化效果的不大情況下，有些時候是可以將傾斜的數據單獨拿出來處理，最後union回去。
3、應用場景
（一）空值產生的數據傾斜
場景：
如日誌中，常會有信息丟失的問題，比如日誌中的user_id，如果取其中的user_id和用戶表中的user_id 關聯，會碰到數據傾斜的問題。
解決方法1 ： user_id為空的不參與關聯
解決方法2 ：賦與空值新的key值
結論：
方法2比方法1效率更好，不但io少了，而且作業數也少了。
解決方法1中 log讀取兩次，job是2。
解決方法2中 job數是1 。這個優化適合無效 id (比如 -99 , 」, null 等) 產生的傾斜問題。把空值的key變成一個字元串加上隨機數，就能把傾斜的數據分到不同的rece上，解決數據傾斜問題。
（二）不同數據類型關聯產生數據傾斜
場景：用戶表中user_id欄位為int，log表中user_id欄位既有string類型也有int類型。當按照user_id進行兩個表的Join操作時，默認的Hash操作會按int型的id來進行分配，這樣會導致所有string類型id的記錄都分配到一個Recer中。
解決方法 ：把數字類型轉換成字元串類型
（三）小表不小不大，怎麼用 map join 解決傾斜問題
使用 map join 解決小表(記錄數少)關聯大表的數據傾斜問題，這個方法使用的頻率非常高，但如果小表很大，大到map join會出現bug或異常，這時就需要特別的處理。
解決如下：
users 表有 600w+ 的記錄，把 users 分發到所有的 map 上也是個不小的開銷，而且 map join 不支持這么大的小表。如果用普通的 join，又會碰到數據傾斜的問題。
解決方法：
假如，log里user_id有上百萬個，這就又回到原來map join問題。所幸，每日的會員uv不會太多，有交易的會員不會太多，有點擊的會員不會太多，有傭金的會員不會太多等等。所以這個方法能解決很多場景下的數據傾斜問題。
Ⅱ HIVE 列表型的str中的元素去重
只是去重的話可以用collect_set，，，如果還需要保持有序可能就需要用collect_list了
Ⅲ Hive常用運算元實現原理簡述--MapRece版
Hive中的常用運算元包括distinct、join、group by、order by、distribute by、sort by、count等，這些操作符在SQL中使用起來很方便，能快速達到我們想要的效果，但是這些運算元在底層是怎麼實現的呢？
order by很容易想到執行原理，在一個rece中將所有記錄按值排序即可。因此order by在數據量大的情況下執行時間非常長，容易out of memory，非特殊業務需求一般不使用。distribute by也比較明顯，根據hash值將distribute的值分發到不同的rece。sort by是小號的order by，只負責將本recer中的值排序，達到局部有序的效果。sort by和distribute by配合使用風味更佳，二者可以合並簡寫為cluster by。count則更加明晰，在combiner或recer處按相同鍵累加值就能得到。
比較復雜的是distinct、join、group by，本文重點討論這三個運算元在MapRece引擎中的大致實現原理。班門弄斧，拋磚引玉。
map階段，將group by後的欄位組合作為key，如果group by單欄位那麼key就一個。將group by之後要進行的聚合操作欄位作為值，如要進行count，則value是1；如要sum另一個欄位，則value就是該欄位。
shuffle階段，按照key的不同分發到不同的recer。注意此時可能因為key分布不均勻而出現數據傾斜的問題。
rece階段，將相同key的值累加或作其他需要的聚合操作，得到結果。
對group by的過程講解的比較清楚的是這篇文章 http://www.mamicode.com/info-detail-2292193.html 圖文並茂，很生動。
實例如下圖，對應語句是 select rank, isonline, count(*) from city group by rank, isonline;
如果group by出現數據傾斜，除去替換key為隨機數、提前挑出大數量級key值等通用調優方法，適用於group by的特殊方法有以下幾種：
（1）set hive.map.aggr=true，即開啟map端的combiner，減少傳到recer的數據量，同時需設置參數hive.groupby.mapaggr.checkinterval 規定在 map 端進行聚合操作的條目數目。
（2）設置mapred.rece.tasks為較大數量，降低每個recer處理的數據量。
（3）set hive.groupby.skewindata=true，該參數可自動進行負載均衡。生成的查詢計劃會有兩個 MR Job。第一個 MR Job 中，Map 的輸出結果集合會隨機分布到Rece 中，每個 Rece 做部分聚合操作，並輸出結果，這樣處理的結果是相同的 Group By Key有可能被分發到不同的 Rece 中，從而達到負載均衡的目的；第二個 MR Job 再根據預處理的數據結果按照 Group ByKey 分布到 Rece 中（這個過程可以保證相同的 Group By Key 被分布到同一個 Rece中），最後完成最終的聚合操作。
Hive中有兩種join方式：map join和common join
如果不顯式指定map side join，或者沒有達到觸發自動map join的條件，那麼會進行rece端的join，即common join，這種join包含map、shuffle、rece三個步驟。
（1）Map階段
讀取源表的數據，Map輸出時候以Join on條件中的列為key，如果Join有多個關聯鍵，則以這些關聯鍵的組合作為key。Map輸出的value為join之後所關心的(select或者where中需要用到的)列；同時在value中還會包含表的Tag信息，用於標明此value對應哪個表。然後按照key進行排序。
（2）Shuffle階段
根據key的值進行hash,並將key/value按照hash值推送至不同的rece中，這樣確保兩個表中相同的key位於同一個rece中
（3）Rece階段
根據key的值完成join操作，期間通過Tag來識別不同表中的數據。
以下面的SQL為例，可用下圖所示過程大致表達其join原理。
SELECT u.name, o.orderid FROM user u JOIN order o ON u.uid = o.uid;
關聯欄位是uid，因此以uid為map階段的輸出key，value為選取的欄位name和標記源表的tag。shuffle階段將相同key的鍵值對發到一起，rece階段將不同源表、同一key值的記錄拼接起來，可能存在一對多的情況。
如果指定使用map join的方式，或者join的其中一張表小於某個體積（默認25MB），則會使用map join來執行。具體小表有多小，由參數 hive.mapjoin.smalltable.filesize 來決定。
Hive0.7之前，需要使用hint提示 /*+ mapjoin(table) */才會執行MapJoin,否則執行Common Join，但在0.7版本之後，默認自動會轉換Map Join，由參數 hive.auto.convert.join 來控制，默認為true。
以下圖為例說明map join如何執行，該圖來自 http://lxw1234.com/archives/2015/06/313.htm ，博主是一個水平深厚又樂於分享的前輩，圖片水印上也有其網址。
yarn會啟動一個Local Task（在客戶端本地執行的Task）--Task A，負責掃描小表b的數據，將其轉換成一個HashTable的數據結構，並寫入本地的文件中，之後將該文件載入到DistributeCache中。
接下來是Task B，該任務是一個沒有Rece的MR，啟動MapTasks掃描大表a,在Map階段，根據a的每一條記錄去和DistributeCache中b表對應的HashTable關聯，並直接輸出結果。
由於MapJoin沒有Rece，所以由Map直接輸出結果文件，有多少個Map Task，就有多少個結果文件。
distinct一般和group by同時出現。
當distinct一個欄位時，將group by的欄位和distinct的欄位組合在一起作為map輸出的key，value設置為1，同時將group by的欄位定為分區鍵，這可以確保相同group by欄位的記錄都分到同一個recer，並且map的輸入天然就是按照組合key排好序的。根據分區鍵將記錄分發到rece端後，按順序取出組合鍵中的distinct欄位，這時distinct欄位也是排好序的。依次遍歷distinct欄位，每找到一個不同值，計數器就自增1，即可得到count distinct結果。例如下面的SQL語句，過程可以下圖示意。
我暫時沒有理解這是怎麼實現的，別人寫的也沒有看明白。有善良的學富五車的大佬指點一下嗎？
Ⅳ Hive sql及窗口函數
hive函數：
1、根據指定條件返回結果：case when then else end as
2、基本類型轉換：CAST（）
3、nvl：處理空欄位：三個str時，是否為空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)與COUNT(*)：返回行數
如果表沒有主鍵，那麼count（1）比count（*）快；
如果有主鍵，那麼count（主鍵，聯合主鍵）比count（*）快；
count(1)跟count(主鍵)一樣，只掃描主鍵。count(*)跟count(非主鍵)一樣，掃描整個表。明顯前者更快一些。
性能問題：
1.任何情況下SELECT COUNT(*) FROM tablename是最優選擇,(指沒有where的情況）；
2.盡量減少SELECT COUNT(*) FROM tablename WHERE COL = 『value』 這種查詢；
3.杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = 『value』 的出現。
count(expression)：查詢 is_reply=0 的數量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct與group by
distinct去重所有distinct之後所有的欄位，如果有一個欄位值不一致就不作為一條
group by是根據某一欄位分組，然後查詢出該條數據的所需欄位，可以搭配 where max(time)或者Row_Number函數使用，求出最大的一條數據
7、使用with 臨時表名 as() 的形式,簡單的臨時表直接嵌套進sql中，復雜的和需要復用的表寫到臨時表中，關聯的時候先找到關聯欄位，過濾條件最好在臨時表中先過濾後關聯
處理json的函數：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字元串函數：
1、instr（』源字元串』 , 『目標字元串』 ,』開始位置』,』第幾次出現』）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字元串； destString代表要從源字元串中查找的子串；
2.start代表查找的開始位置，這個參數可選的，默認為1；
3.appearPosition代表想從源字元中查找出第幾次出現的destString，這個參數也是可選的， 默認為1
4.如果start的值為負數，則代表從右往左進行查找，但是位置數據仍然從左向右計算。
5.返回值為：查找到的字元串的位置。如果沒有查找到，返回0。
最簡單例子： 在abcd中查找a的位置，從第一個字母開始查，查找第一次出現時的位置
select instr(『abcd』,』a』,1,1) from al; —1
應用於模糊查詢：instr(欄位名/列名, 『查找欄位』)
select code,name,dept,occupation from staff where instr(code, 『001』)> 0;
等同於 select code, name, dept, occupation from staff where code like 『%001%』 ;
應用於判斷包含關系：
select ccn,mas_loc from mas_loc where instr(『FH,FHH,FHM』,ccn)>0;
等同於 select ccn,mas_loc from mas_loc where ccn in (『FH』,』FHH』,』FHM』);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
substr(time,1,8) 表示將time從第1位開始截取，截取的長度為8位
第一種用法：
substr（string A，int start）和 substring（string A，int start），用法一樣
功效：返回字元串A從下標start位置到結尾的字元串
第二種用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
功效：返回字元串A從下標start位置開始，長度為len的字元串
3、get_json_object(form_data,'$.學生姓名') as student_name
json_tuple 函數的作用：用來解析json字元串中的多個欄位
4、split(full_name, '\\.') [5] AS zq; 取的是數組里的第六個
日期(時間)函數：
1、to_date(event_time) 返回日期部分
2、date_sub：返回當前日期的相對時間
當前日期：select curdate()
當前日期前一天：select date_sub(curdate(),interval 1 day)
當前日期後一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 將現在的時間總秒數轉為標准格式時間，返回14天之前的時間
時間戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 將現在的時間總秒數轉為標准格式時間
from_unixtime(get_json_object(get_json_object(form_data,'$.挽單時間'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>時間戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 時間轉格式化時間
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比較函數: datediff語法: datediff(string enddate,string startdate)
返回值: int
說明: 返回結束日期減去開始日期的天數。
舉例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函數: date_add語法: date_add(string startdate, intdays)
返回值: string
說明: 返回開始日期startdate增加days天後的日期。
舉例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期減少函數: date_sub語法: date_sub (string startdate,int days)
返回值: string
說明: 返回開始日期startdate減少days天後的日期。
舉例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查詢近30天的數據
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 為table里的欄位，current_timestamp 返回當前時間 2018-06-01 11:00:00
3、trunc()函數的用法：當前日期的各種第一天,或者對數字進行不四捨五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期為2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回當月第一天.
上月1號 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回當年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回當前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回當年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 當前時間為14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函數沒有秒的精確
數字：TRUNC（number,num_digits） Number 需要截尾取整的數字。Num_digits 的默認值為 0。TRUNC()函數截取時不進行四捨五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四捨五入：
select round(1.455, 2) #結果是：1.46，即四捨五入到十分位，也就是保留兩位小數
select round(1.5) #默認四捨五入到個位，結果是：2
select round(255, -1) #結果是：260，即四捨五入到十位，此時個位是5會進位
floor()：地板數
ceil()天花板數
5、
6.日期轉年函數: year語法: year(string date)
返回值: int
說明: 返回日期中的年。
舉例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期轉月函數: month語法: month (string date)
返回值: int
說明: 返回日期中的月份。
舉例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期轉天函數: day語法: day (string date)
返回值: int
說明: 返回日期中的天。
舉例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期轉小時函數: hour語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
舉例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期轉分鍾函數: minute語法: minute (string date)
返回值: int
說明: 返回日期中的分鍾。
舉例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期轉秒函數: second語法: second (string date)
返回值: int
說明: 返回日期中的秒。
舉例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期轉周函數: weekofyear語法: weekofyear (string date)
返回值: int
說明: 返回日期在當前的周數。
舉例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
網站訪問量統計：
uv：每用戶訪問次數
ip：每ip(可能很多人)訪問次數
PV:是指頁面的瀏覽次數
VV:是指你訪問網站的次數
sql：
基本函數：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正則）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要與distribut by集合結合使用：
hive (default)> set maprece.job.reces=3; //先設置rece的數量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部門編號分區，再按照員工編號降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分區表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分區聯合查詢：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二級分區表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽樣查詢：分區針對的是數據的存儲路徑；分桶針對的是數據文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
設置開啟分桶與rece為1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽樣：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶數/y，x是從哪個桶開始抽取，y越大 抽樣數越少，y與抽樣數成反比，x必須小於y
給空欄位賦值：
如果員工的comm為NULL，則用-1代替或用其他欄位代替 ：select nvl(comm,-1) from emp;
case when:如何符合記為1，用於統計、分組統計
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用於組合歸類匯總(行轉列)：UDAF：多轉一
concat：拼接查詢結果
collect_set(col)：去重匯總，產生array類型欄位，類似於distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解釋：先第一次查詢得到一張沒有按照（星座血型）分組的表，然後分組，使用collect_set將名字組合成數組，然後使用concat將數組變成字元串
用於拆分數據：(列轉行)：UDTF：一轉多
explode(col)：將hive一列中復雜的array或者map結構拆分成多行。
lateral view 側面顯示：用於和UDTF一對多函數搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸開之後的列別名
temptable ：臨時表表名
解釋：用於和split, explode等UDTF一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。
開窗函數：
Row_Number,Rank，Dense_Rank over：針對統計查詢使用
Row_Number：返回從1開始的序列
Rank:生成分組中的排名序號，會在名詞s中留下空位。3 3 5
dense_rank：生成分組中的排名序號，不會在名詞中留下空位。3 3 4
over：主要是分組排序，搭配窗口函數使用
結果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往後
current row：當前行
unbounded：unbounded preceding 從前面的起點， unbounded following：到後面的終點
sum：直接使用sum是總的求和，結合over使用可統計至每一行的結果、總的結果、當前行+之前多少行/之後多少行、當前行到往後所有行的求和。
over(rowsbetween 3/current ) 當前行到往後所有行的求和
ntile：分片，結合over使用，可以給數據分片，返回分片號
使用場景：統計出排名前百分之或n分之一的數據。
lead,lag,FIRST_VALUE,LAST_VALUE
lag與lead函數可以返回上下行的數據
lead(col,n,dafault) 用於統計窗口內往下第n行值
第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL）
LAG(col,n,DEFAULT) 用於統計窗口內往上第n行值
第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL）
使用場景：通常用於統計某用戶在某個網頁上的停留時間
FIRST_VALUE:取分組內排序後，截止到當前行，第一個值
LAST_VALUE:取分組內排序後，截止到當前行,最後一個值
范圍內求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小於等於當前值的 行數 / 分組內總行數
–比如，統計小於等於當前薪水的人數，占總人數的比例
percent_rank:分組內當前行的RANK值-1/分組內總行數-1
總結：
在Spark中使用spark sql與hql一致，也可以直接使用sparkAPI實現。
HiveSql窗口函數主要應用於求TopN，分組排序TopN、TopN求和，前多少名前百分之幾。
與Flink窗口函數不同。
Flink中的窗口是用於將無線數據流切分為有限塊處理的手段。
window分類：
CountWindow：按照指定的數據條數生成一個 Window，與時間無關。
TimeWindow：按照時間生成 Window。
1. 滾動窗口（Tumbling Windows）：時間對齊，窗口長度固定，不重疊：：常用於時間段內的聚合計算
2.滑動窗口（Sliding Windows）：時間對齊，窗口長度固定，可以有重疊：：適用於一段時間內的統計（某介面最近 5min 的失敗率來報警）
3. 會話窗口（Session Windows）無時間對齊，無長度，不重疊：：設置session間隔，超過時間間隔則窗口關閉。
Ⅳ 數據分析課程筆記 - 19 - HiveSQL 常用優化技巧
大家好呀，這節課學習 HiveSQL 的常用優化技巧。由於 Hive 主要用來處理非常大的數據，運行過程由於通常要經過 MapRece 的過程，因此不像 MySQL 一樣很快出結果。而使用不同方法寫出來的 HiveSQL 語句執行效率也是不一樣的，因此為了減少等待的時間，提高伺服器的運行效率，我們需要在 HiveSQL 的語句上進行一些優化。
本節課的主要內容 ：
引言
1、技巧一：列裁剪和分區裁剪
（1）列裁剪
（2）分區裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by來替換distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：換個思路解題
6、技巧六：union all時可以開啟並發執行
7、技巧七：表連接優化
8、技巧八：遵循嚴格模式
Hive 作為大數據領域常用的數據倉庫組件，在平時設計和查詢時要特別注意效率。影響Hive效率的幾乎從不是數據量過大，而是數據傾斜、數據冗餘、job 或 I/O 過多、MapRece 分配不合理等等。對 Hive 的調優既包含對HiveSQL 語句本身的優化，也包含 Hive 配置項和 MR 方面的調整。
列裁剪就是在查詢時只讀取需要的列。當列很多或者數據量很大時，如果select 所有的列或者不指定分區，導致的全表掃描和全分區掃描效率都很低。Hive中與列裁剪優化相關的配置項是 hive.optimize.cp ，默認是 true 。
分區裁剪就是在查詢時只讀需要的分區。Hive中與分區裁剪優化相關的則是 hive.optimize.pruner ，默認是 true 。
HiveSQL中的 order by 與其他 SQL 語言中的功能一樣，就是將結果按某個欄位全局排序，這會導致所有map端數據都進入一個 rece 中，在數據量大時可能會長時間計算不完。
如果使用 sort by ，那麼就會視情況啟動多個 recer 進行排序，並且保證每個 recer 內局部有序。為了控制 map 端數據分配到 rece 的 key，往往還要配合 distribute by 一同使用。如果不加 distribute by 的話，map 端數據就會隨機分配給 recer。
這里需要解釋一下， distribute by 和 sort by 結合使用是如何相較於 order by 提升運行效率的。
假如我們要對一張很大的用戶信息表按照年齡進行分組，優化前的寫法是直接 order by age 。使用 distribute by 和 sort by 結合進行優化的時候， sort by 後面還是 age 這個排序欄位， distribute by 後面選擇一個沒有重復值的均勻欄位，比如 user_id 。
這樣做的原因是，通常用戶的年齡分布是不均勻的，比如20歲以下和50歲以上的人非常少，中間幾個年齡段的人又非常多，在 Map 階段就會造成有些任務很大，有些任務很小。那通過 distribute by 一個均勻欄位，就可以讓系統均勻地進行「分桶」，對每個桶進行排序，最後再組合，這樣就能從整體上提升 MapRece 的效率。
取出 user_trade 表中全部支付用戶：
原有寫法的執行時長：
優化寫法的執行時長：
考慮對之前的案例進行優化：
注意： 在極大的數據量（且很多重復值）時，可以先 group by 去重，再 count() 計數，效率高於直接 count(distinct **) 。
如果我們想知道用戶的性別分布、城市分布、等級分布，你會怎麼寫？
通常寫法：
缺點 ：要分別寫三次SQL，需要執行三次，重復工作，且費時。
那該怎麼優化呢？
注意 ：這個聚合結果相當於縱向地堆在一起了（Union all），分類欄位用不同列來進行區分，也就是每一行數據都包含 4 列，前三列是分類欄位，最後一列是聚合計算的結果。
GROUPING SETS() ：在 group by 查詢中，根據不同的維度組合進行聚合，等價於將不同維度的 group by 結果集進行 union all。聚合規則在括弧中進行指定。
如果我們想知道用戶的性別分布以及每個性別的城市分布，你會怎麼寫？
那該怎麼優化呢？
注意： 第二列為NULL的，就是性別的用戶分布，其餘有城市的均為每個性別的城市分布。
cube：根據 group by 維度的所有組合進行聚合
注意 ：跑完數據後，整理很關鍵！！！
rollup：以最左側的維度為主，進行層級聚合，是cube的子集。
如果我想同時計算出，每個月的支付金額，以及每年的總支付金額，該怎麼辦？
那應該如何優化呢？
條條大路通羅馬，寫SQL亦是如此，能達到同樣效果的SQL有很多種，要學會思路轉換，靈活應用。
來看一個我們之前做過的案例：
有沒有別的寫法呢？
Hive 中互相沒有依賴關系的 job 間是可以並行執行的，最典型的就是
多個子查詢union all。在集群資源相對充足的情況下，可以開啟並
行執行。參數設置： set hive.exec.parallel=true;
時間對比：
所謂嚴格模式，就是強制不允許用戶執行3種有風險的 HiveSQL 語句，一旦執行會直接報錯。
要開啟嚴格模式，需要將參數 hive.mapred.mode 設為 strict 。
好啦，這節課的內容就是這些。以上優化技巧需要大家在平時的練習和使用中有意識地去注意自己的語句，不斷改進，就能掌握最優的寫法。
Ⅵ hive數組去重
把數據放到collect_set裡面就好了
Ⅶ 使用sqoop將hive中的數據導入關系型資料庫怎麼去重
直接導入hive表
sqoop import --connect jdbc:postgresql://ip/db_name--username user_name --table table_name --hive-import -m 5
內部執行實際分三部，.將數據導入hdfs（可在hdfs上找到相應目錄），2.創建hive表名相同的表，3，將hdfs上數據傳入hive表中
sqoop根據postgresql表創建hive表
sqoop create-hive-table --connect jdbc:postgresql://ip/db_name --username user_name --table table_name --hive-table hive_table_name( --hive-partition-key partition_name若需要分區則加入分區名稱)
導入hive已經創建好的表中
sqoop import --connect jdbc:postgresql://ip/db_name --username user_name --table table_name --hive-import -m 5 --hive-table hive_table_name (--hive-partition-key partition_name --hive-partition-value partititon_value);
使用query導入hive表
sqoop import --connect jdbc:postgresql://ip/db_name --username user_name --query "select ,* from retail_tb_order where \$CONDITIONS" --hive-import -m 5 --hive-table hive_table_name (--hive-partition-key partition_name --hive-partition-value partititon_value);
注意：$CONDITIONS條件必須有，query子句若用雙引號，則$CONDITIONS需要使用\轉義，若使用單引號，則不需要轉義。
Ⅷ hive如何去掉重復數據，顯示第一條
去重沒問題啊，distinct 就好了，顯示第一條可以用row_number函數，不同版本hive的row_number有所不同，你自己查一下吧，row_number可以對同一個key從1開始編號的。
Ⅸ Hive優化
注意:以下SQL不會轉為Maprece來執行,Explain用於顯示執行計劃,可以來驗證sql是否發生maprece
select僅查詢本表欄位;
where僅對本表欄位做條件過濾;
比如下面的語句是會發生maprece的;(下面的rece沒有截圖)
(1)集群模式:hive默認採用的是集群的方式;
(2)本地模式:首先開啟本地模式,測試的時候就可以以本地模式來節省集群資源;
set hive.exec.mode.local.auto=true;
注意：hive.exec.mode.local.auto.inputbytes.max默認值為128M表示載入文件的最大值，若大於該配置仍會以集群方式來運行;
通過設置以下參數開啟並行模式：默認是不開啟並行計算,這是job是線性執行的;
set hive.exec.parallel=true;多個job並行執行提高效率;
注意：hive.exec.parallel.thread.number（一次SQL計算中允許並行執行的job個數的最大值）;
通過設置以下參數開啟嚴格模式：
set hive.mapred.mode=strict;（默認為：nonstrict非嚴格模式）
開啟嚴格模式後,查詢會有限制：
(1)對於分區表，必須添加where對於分區欄位的條件過濾,因為hive中的數據量一般都很大,避免全表掃描不添加會執行失敗,非分區表正常查詢;
(2)order by語句必須包含limit輸出限制；還是為了避免全表掃描
(3)限制執行笛卡爾積的查詢。
(1)Order By - 對於查詢結果做全排序，只允許有一個rece處理（當數據量較大時，應慎用。嚴格模式下，必須結合limit來使用）;
(2)Sort By - 對於單個rece的數據進行排序
(3)Distribute By - 分區排序，經常和Sort By結合使用
(4)Cluster By - 相當於 Sort By +Distribute By（Cluster By不能通過asc、desc的方式指定排序規則；可通過 distribute by column sort by column asc|desc 的方式）
Join計算時，將小表（驅動表）放在join的左邊
Map Join：在Map端完成Join,join操作對應maprece是rece階段,因為shuffle,跟rece階段比較浪費時間,所以才有了map join;
兩種實現方式：
(1)SQL方式，在SQL語句中添加MapJoin標記（mapjoin hint）
(2)開啟自動mapjoin : 通過修改以下配置啟用自動的map join：
set hive.auto.convert.join = true;（該參數為true時，Hive自動對左邊的表統計量，如果是小表就加入內存，即對小表使用Map
join）
相關配置參數：
hive.mapjoin.smalltable.filesize; (大表小表判斷的閾值，如果表的大小小於該值則會被載入到內存中運行）
hive.ignore.mapjoin.hint；（默認值：true；是否忽略mapjoin hint 即mapjoin標記）
hive.auto.convert.join.noconditionaltask;（默認值：true；將普通的join轉化為普通的mapjoin時，是否將多個mapjoin轉化為一個mapjoin）
hive.auto.convert.join.noconditionaltask.size;（將多個mapjoin轉化為一個mapjoin時，其表的最大值）
通過設置以下參數開啟在Map端的聚合：
set hive.map.aggr=true; 開啟後,map預聚合,相當於map端rece減輕rece 端壓力;
相關配置參數：
hive.groupby.mapaggr.checkinterval：map端group by執行聚合時處理的多少行數據（默認：100000）
hive.map.aggr.hash.min.rection：進行聚合的最小比例（預先對100000條數據做聚合，若聚合之後的數據量/100000的值大於該配置0.5，則不會聚合）
hive.map.aggr.hash.percentmemory：map端聚合使用的內存的最大值
hive.map.aggr.hash.force.flush.memory.threshold：map端做聚合操作是hash表的最大可用內容，大於該值則會觸發flush
hive.groupby.skewindata: 是否對GroupBy產生的數據傾斜做優化，默認為false,當設置為true時,會進行兩次mr,第一次把數據map端隨機分配分區,達到均衡數據的目的,第二次進行正常的分區演算法執行mr;
文件數目小，容易在文件存儲端造成壓力，給hdfs造成壓力，影響效率
設置合並屬性
是否合並map輸出文件：hive.merge.mapfiles=true
是否合並rece輸出文件：hive.merge.mapredfiles=true;
合並文件的大小：hive.merge.size.per.task=256*1000*1000
去重統計
數據量小的時候無所謂，數據量大的情況下，由於COUNT DISTINCT操作需要用一個Rece
Task來完成，這一個Rece需要處理的數據量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP
BY再COUNT的方式替換;
由於maptask的數量一般跟切片數量有關,所有我們主要對rece端設置數量;
Map數量相關的參數
mapred.max.split.size: 一個split的最大值，即每個map處理文件的最大值
mapred.min.split.size.per.node:一個節點上split的最小值
mapred.min.split.size.per.rack:一個機架上split的最小值
Rece數量相關的參數
mapred.rece.tasks:強制指定rece任務的數量
hive.exec.recers.bytes.per.recer:每個rece任務處理的數據量
hive.exec.recers.max:每個任務最大的rece數
適用場景：
(1)小文件個數過多
(2)task個數過多
通過 set mapred.job.reuse.jvm.num.tasks=n; 來設置（n為task插槽個數）;個人理解優點類似於各種連接池的作用;
缺點：設置開啟之後，task插槽會一直佔用資源，不論是否有task運行，直到所有的task即整個job全部執行完成時，才會釋放所有的task插槽資源！
Ⅹ hive優化方法
1、列裁剪和分區裁剪
2、謂詞下推
3、sort by 替換order by
4、group by 代替distinct
5、group by 配置調整
map 端預聚合：set hive.map.aggr=true ; set hive.groupby.mapaggr.checkinterval=100000
傾斜均衡配置項：set hive.groupby.skewindate=true
6、join優化
6.1 大小表，小表前置
6.2 多表Join時key相同
6.3 利用mapjoin特性
6.4 分桶表 mapjoin
6.5 傾斜均衡配置項：
設置hive.optimize.skewjoin=true，開啟後，在join過程中hive會將計數超過閾值hive.skewjoin.key(默認100000)的傾斜key對應的行臨時寫進文件中，然後再啟動另一個job做map join生成結果。通過hive.skewjoin.mapjoin.map.task參數還可以控制第二個job的mapper數量，默認10000.
6.7 優化sql處理join數據傾斜
6.7.1、空值或無意義的值：若不需要空值數據，就提前寫到where語句過濾掉，如果需要保留的話，將空值key用隨機方式打散。
6.7.2、單獨處理傾斜key
6.7.3、不同數據類型，join的關聯欄位類型不一樣，導致耗時長，所以需要注意做類型轉換（join關聯欄位，一個是int，一個是string，，計算key的hash值時默認時int類型做的，這樣導致所有真正的string類型的key都分配到一個recer上了）
6.7.4、mapjoin的小表比較大的時候，，無法直接使用mapjoin，則 select/*+mapjoin(b)*/ from a left join ( select /*+mapjoin (b)*/ from b inner join a )
6.8、maprece 優化
6.8.1、調整mapper數
mapper數量與輸入文件的split數息息相關。如果想減少mapper數，就適當提高mapred.min.split.size,split數就減少了；如果想增大mapper數，除了降低maperd.min.split.size之外，也可以提高mapred.map.task；一般來講，如果輸入文件是少量大文件，就減少mapper數；如果是大量非小文件，就增大mapper數，如果是小文件，就喝吧小文件
6.8.2 調整recer數
使用參數mapred.rece.task可以直接設定recer數量，如果不設置的話，hive會自行推測，推測邏輯如下：
參數hive.exec.recers.bytes.per.recer.設定每個recer能夠處理的最大的數據量，默認時1G
參數hive.exec.recers.max設定每個job的最大的recer數量，默認時999或者1009
得到recer數：recers_num=min(total_input_size/recers.bytes.per.recer，recers.max)
recer數量與輸出文件的數量相關，如果recer數太多，會產生大量小文件，對hdfs造成壓力，如果recer數太少，每個recer要處理很多數據，容易拖慢運行時間或造成oom
6.8.3 合並小文件
輸入階段合並：需要更改hive的輸入文件格式，即參數hive.input.format，默認值是org.apache.hadoop.hive.ql.io.hiveiputformat，我們該成org.apache.hadoop.hive.ql.io.combinehiveinputformat.
這樣比調整mapper數時，又多出兩個參數，分別是mapred.min.split.size.per.node和mapred.min.split.size.per.rack，含義是單節點和單機架上的最小split大小。如果發現又split大小小於這兩個默認值100MB，則會進行合並
輸出階段合並：直接將hive.merge.mapfiles和hive.merge.mapredfiles都設置為true即可。
hive.merge.mapfiles表示將map-only任務的輸出合並
hive.merge.mapredfiles表示將map-rece任務的輸出合並
另外hive.merge.size.per.task可以指定每個task輸出後合並文件大小的期望值
hive.merge.size.smallfiles.avgsize可以指定所有輸出文件大小的均值閾值，默認時1G
6.9 啟用壓縮
壓縮job的中間結果數據和輸出數據，可以少量CPU時間節省出很多空間。壓縮方式一般選擇snappy，效率最高，要啟用中間壓縮，需要設定：
hive.exec.compress.intermediate為true，同時指定壓縮方式hive.intermediate.compression.code為org.apache.hadoop.io.compress.snappycode.
另外，參數hive.intermediate.compression.type可以選對塊block還是對記錄record壓縮，block壓縮率比較高，輸出壓縮的配置基本相同：打開hive.exec.compress.output
6.10 jvm重用
在mr job中,默認時每執行一個task就會啟動一個jvm，如果task非常小而且碎，那麼jvm啟動和關閉耗時都會比較長。可以通過調節參數mapred.job.reuse.jvm.num.task來重用。例如將這個參數設置為5，那麼久代表同一個mr job中順序執行的5各task可以重復使用一個jvm，減少啟動和關閉開銷，但是他對不同mr job的task無效
6.11 採用合適的存儲格式
6.12 數據傾斜
數據傾斜原因：1、join 傾斜 2、聚合傾斜
group by 傾斜： group by 欄位中某個欄位的值過多，導致處理這個值得rece耗時很久
解決方法：set hive.map.aggr=true; -- 表示開啟map端聚合
set hive.groupby.skewindata=true; 注意：只能對單個欄位聚合 ， 生成兩個map job ，第一個map job中map輸出結果隨機分布到rece中，每個rece做部分聚合操作，並輸出結果，這樣相同的groub key有可能被分發到不同的rece中，從而達到負載均衡的目的；第二個map job再根據預處理的數據結果按照group by key分布到rece中，這個過程可以保證相同的key被分到同一個rece中，最後完成最終的聚合操作
6.13 優化in、exists語句
使用left semi join 替換in 、exists
6.14 合並 maprece操作
multi-group by 是hive的一個非常好的特性，它使得hive中利用中間結果變更非常方便
例如：
from ( select a.status,b.school from status_update a join profilees b on (a.userid=b.suerid)) subq1
insert overwirte table a1 partition(ds='2021-03-07')
select subq1.school,count(1) group by subq1.school
insert overwrite table b1 partition(ds='2021-03-07')
select subq1.status,count(1) group by subq1.status
上述語句使用了multi-group by特性聯系group by 2次數據，使用不同的group by key，這一特性可以減少一次maprece操作