hive總結

1. 概述

總結一些自己不常用但必須知道的hive功能

2. 導出數(shù)據(jù)

  1. INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] SELECT ... FROM ...
  2. export table 表名 to 'hdfs目錄';
  3. import table [partition(字段='值')] from 'hdfs目錄';

3. 排序

操作 描述 舉例
order by 全局排序内边,只有一個reduce
sort by 分區(qū)內排序,對排序字段進行隨機分區(qū)以使數(shù)據(jù)均勻漠其,reduce多個
distribute by 搭配sort by使用和屎,分區(qū)內排序,對記錄按照distribute by的字段進行hash分區(qū)柴信,reduce多個 select * from test dristribute by row_key sort by entname desc;
cluster by 當distribute by與sort by字段相同時,可以使用cluster by來代替潜沦,cluster by不支持desc、asc窃判,默認就是asc select * from test cluster by row_key;

4. 分桶

一個分區(qū)代表一個目錄喇闸,分區(qū)針對的是目錄询件,而分桶針對的是數(shù)據(jù)文件,同一個數(shù)據(jù)文件中的分區(qū)字段的hash值%桶數(shù)相同

set hive.enforce.bucketing = true;
create table bucketed_t (id string, name string) clustered by (id) into 256 buckets; -- 以字段id分桶
insert overwrite table bucketed_t select id, name from other_table;

多用來采樣刻蟹,語法是tablesample (bucket x out of y [on column]),其中x表示從第幾個bucket開始嘿辟,y表示間隔多少個bucket在取樣及采樣的比例:

比如總bucket數(shù)為32,x為2红伦,y為16,返回的數(shù)據(jù)量為32/16個桶召调,返回第2蛮浑、18個桶的數(shù)據(jù)
比如總bucket數(shù)為32,x為2艺沼,y為64蕴掏,返回的數(shù)據(jù)量為32/64個桶,返回第2個桶的1/2數(shù)據(jù)

還可以對bucket數(shù)成倍數(shù)關系剩拢,分桶字段類型相同的表執(zhí)行mapjoin

5. skewed table

語法:

create table t (key string, value string) skewed by key on (1,5,6) [stored as directories]饶唤,添加stored as directories則開啟list bucketing功能,將key為1募狂,5,6的數(shù)據(jù)會分別放到不同的子目錄中

這個表是用來優(yōu)化join時少量關聯(lián)key數(shù)據(jù)傾斜的性穿,假設有2張表A、B吗坚,如果A表的key在值1呆万、5、6時數(shù)據(jù)傾斜牡彻,B的各個key的數(shù)據(jù)量不大(即單獨key的數(shù)據(jù)可以放入內存)出爹。為了避免在key為1、5严就、6時數(shù)據(jù)傾斜,常規(guī)解決方式:

  1. 將A表的數(shù)據(jù)分為4部分废菱,第一部分為key為1的數(shù)據(jù)抖誉,第二部分為key為5的數(shù)據(jù),第三部分為key為6的數(shù)據(jù)旁理,其余的數(shù)據(jù)為第四部分
  2. B表同樣處理
  3. 將第四部分分發(fā)到reducer中進行join我磁,將其余的三部分分為3個map task,B的數(shù)據(jù)加載到內存芋哭,與A的對應部分數(shù)據(jù)進行map join

這樣能解決數(shù)據(jù)傾斜的問題郁副,但是需要多次數(shù)據(jù)讀取,為了只讀取一次,引入了skewed table肥隆,skewed table讓hive知道哪些key需要mapjoin稚失,list bucketing功能則已經(jīng)把數(shù)據(jù)進行劃分,只需要讀取一遍數(shù)據(jù)

注意的是吸占,這個功能只針對少量key數(shù)據(jù)傾斜凿宾,因為每個key對應的目錄,會作為元數(shù)據(jù)添加到metastore

6. 窗口

聚合函數(shù)是多對1,而窗口還是多對多遍略,行數(shù)不變,且也能獲取聚合值下愈。注意窗口是針對一條數(shù)據(jù)而言的

6.1 開窗

開窗的關鍵字:OVER

6.2 窗口規(guī)格(Window Specification)

4部分組成:

  1. Partition Specification:over (partition by)
  2. Order Specification:over (sort by)
  3. Window Frame:

3.1 (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
3.2 (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
3.3 (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
語法OVER(window frame)势似,以當前行為原點僧著,n PRECEDING表示前n行,n FOLLOWING表示后n行盹愚,UNBOUNDED表示無窮大,UNBOUNDED PRECEDING表示窗口起點毅舆,UNBOUNDED FOLLOWING表示窗口終點愈腾,。RANGE表示有Order Specification悦即,否則為ROWS

  1. Window的別名

舉例:

  1. over():相當于over(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),窗口大小為整個數(shù)據(jù)集,一個窗口赁豆,對窗口內的每一條數(shù)據(jù)冗美,能看到整個窗口。
  2. over (partition by 字段1):相當于over(partition by 字段1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)节预,以字段1分組后的數(shù)據(jù)集属韧,一個組一個窗口,對窗口內的每一條數(shù)據(jù)宵喂,能看到整個窗口。
  3. over (order by 字段1):相當于over(order by 字段1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)拙泽,窗口大小為整個數(shù)據(jù)集裸燎,一個窗口,對窗口內的每一條數(shù)據(jù)荷荤,能看到第一行到自己移稳。
6.3 窗口函數(shù)(windowing functions)

筆者習慣稱為錯位函數(shù),這些函數(shù)需要與窗口一起使用

LEADLEAD(列名, n, [為null時的替換值])袱蚓,在窗口內几蜻,以當前行為原點,后n行指定列的值颖低,如果第n行沒有則用null代替弧烤,指定了第3個參數(shù),使用第三個參數(shù)替代。注意只是一個值
LAGLAG(列名, n, [為null時的替換值])莺戒,在窗口內,以當前行為原點瘪校,前n行指定列的值名段,如果第n行沒有則用null代替,指定了第3個參數(shù)麻惶,使用第三個參數(shù)替代信夫。注意只是一個值
FIRST_VALUEFIRST_VALUE(列名, [布爾值,默認false])脐彩,在窗口內姊扔,能看到的第一行指定列的值梅誓,false表示不忽略null值
LAST_VALUELAST_VALUE(列名, [布爾值,默認false])梗掰,在窗口內及穗,能看到的最后一行指定列的值,false表示不忽略null值

6.4 聚合函數(shù)

SUM埂陆、AVG焚虱、COUNT、MAX鹃栽、MIN、COUNT(DISTINCT)

6.5 序號函數(shù)(Analytics functions)
  • RANK:遇到重復的字段值薇芝,序號不變,但是序號不連續(xù)嚷缭,從1開始
  • ROW_NUMBER:不管重復黄娘,序號從1開始
  • DENSE_RANK:遇到重復的字段值,序號不變优床,但是序號連續(xù)誓焦,從1開始
  • CUME_DIST:小于等于當前值的行數(shù)/分組內總行數(shù)
  • PERCENT_RANK:RANK的拓展,(當前行的RANK值-1)/(分組內總行數(shù)-1)
  • NTILE:均勻進行分組

7. 自定義函數(shù)

UDF:一條進一條出
UDTF:全稱user-defined table-generating functions移层,一條進多條輸出
UDAF: 多條進一條輸出

8. 動態(tài)分區(qū)

官方文檔最給力

9. ANALYZE

ANALYZE TABLE [db_name.]tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)]
  COMPUTE STATISTICS 
  [FOR COLUMNS]          -- (Note: Hive 0.10.0 and later.)
  [CACHE METADATA]       -- (Note: Hive 2.1.0 and later.)
  [NOSCAN];

用來統(tǒng)計表的信息赫粥。由于spark-sql生成的表沒有表的統(tǒng)計信息,當需要在spark-sql中broadastHashJoin或者在hive中mapJoin時频蛔,讀取不到rawDataSize信息而不能啟用秦叛,這時候可以使用該命令來獲取表的統(tǒng)計信息

筆者遇到上述情況就使用

ANALYZE TABLE [db_name.]tablenameANALYZE TABLE [db_name.]tablename COMPUTE STATISTICS  NOSCAN;
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
  • 序言:七十年代末挣跋,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子避咆,更是在濱河造成了極大的恐慌查库,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,807評論 6 518
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件磷籍,死亡現(xiàn)場離奇詭異,居然都是意外死亡院领,警方通過查閱死者的電腦和手機比然,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,284評論 3 399
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來万俗,“玉大人饮怯,你說我怎么就攤上這事】馓龋” “怎么了论矾?”我有些...
    開封第一講書人閱讀 169,589評論 0 363
  • 文/不壞的土叔 我叫張陵,是天一觀的道長饱亿。 經(jīng)常有香客問我闰靴,道長,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,188評論 1 300
  • 正文 為了忘掉前任膘掰,我火速辦了婚禮佳遣,結果婚禮上,老公的妹妹穿的比我還像新娘窒舟。我一直安慰自己诵盼,他們只是感情好银还,可當我...
    茶點故事閱讀 69,185評論 6 398
  • 文/花漫 我一把揭開白布蛹疯。 她就那樣靜靜地躺著热监,像睡著了一般。 火紅的嫁衣襯著肌膚如雪列吼。 梳的紋絲不亂的頭發(fā)上苦始,一...
    開封第一講書人閱讀 52,785評論 1 314
  • 那天,我揣著相機與錄音凑耻,去河邊找鬼柠贤。 笑死,一個胖子當著我的面吹牛邻吭,可吹牛的內容都是我干的澄港。 我是一名探鬼主播涌哲,決...
    沈念sama閱讀 41,220評論 3 423
  • 文/蒼蘭香墨 我猛地睜開眼棍掐,長吁一口氣:“原來是場噩夢啊……” “哼腊敲!你這毒婦竟也來了枯芬?” 一聲冷哼從身側響起采郎,我...
    開封第一講書人閱讀 40,167評論 0 277
  • 序言:老撾萬榮一對情侶失蹤蒜埋,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后整份,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,698評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡蚯撩,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 38,767評論 3 343
  • 正文 我和宋清朗相戀三年烛占,在試婚紗的時候發(fā)現(xiàn)自己被綠了忆家。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,912評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡揭芍,死狀恐怖卸例,靈堂內的尸體忽然破棺而出,到底是詐尸還是另有隱情筷转,我是刑警寧澤,帶...
    沈念sama閱讀 36,572評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站袭蝗,受9級特大地震影響,放射性物質發(fā)生泄漏朵逝。R本人自食惡果不足惜乡范,卻給世界環(huán)境...
    茶點故事閱讀 42,254評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧闰蚕,春花似錦、人聲如沸涩哟。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,746評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽器仗。三九已至,卻和暖如春威鹿,著一層夾襖步出監(jiān)牢的瞬間轨香,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,859評論 1 274
  • 我被黑心中介騙來泰國打工科雳, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留脓杉,地道東北人。 一個月前我還...
    沈念sama閱讀 49,359評論 3 379
  • 正文 我出身青樓蚌堵,卻偏偏與公主長得像沛婴,于是被迫代替她去往敵國和親嘁灯。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 45,922評論 2 361

推薦閱讀更多精彩內容

  • 1.6.1 Hive的架構 1.6.2 Hive和數(shù)據(jù)庫比較 Hive 和數(shù)據(jù)庫除了擁有類似的查詢語言性雄,再無類似之...
    JayWolf閱讀 979評論 0 0
  • 本文參考:黑澤君相關博客 本文是我總結日常工作中遇到的坑秒旋,結合黑澤君相關博客诀拭,選取、補充了部分內容耕挨。 查詢函數(shù)(...
    利伊奧克兒閱讀 481評論 0 0
  • 這一周主要學習了 Hive 的一些基礎知識,學習了多個 Hive 窗口函數(shù)贪庙,雖然感覺這些窗口函數(shù)沒有實際的應用...
    大石兄閱讀 2,756評論 2 8
  • 1 Hive 基本概念 1.1 什么是 Hive止邮? Hive:由 Facebook 開源用于解決海量結構化日志的數(shù)...
    djm猿閱讀 579評論 0 1
  • 漸變的面目拼圖要我怎么拼滑沧? 我是疲乏了還是投降了? 不是不允許自己墜落滓技, 我沒有滴水不進的保護膜。 就是害怕變得面...
    悶熱當乘涼閱讀 4,254評論 0 13