1. 概述
總結一些自己不常用但必須知道的hive功能
2. 導出數(shù)據(jù)
- INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] SELECT ... FROM ...
- export table 表名 to 'hdfs目錄';
- 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ī)解決方式:
- 將A表的數(shù)據(jù)分為4部分废菱,第一部分為key為1的數(shù)據(jù)抖誉,第二部分為key為5的數(shù)據(jù),第三部分為key為6的數(shù)據(jù)旁理,其余的數(shù)據(jù)為第四部分
- B表同樣處理
- 將第四部分分發(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部分組成:
- Partition Specification:over (partition by)
- Order Specification:over (sort by)
- 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
- Window的別名
舉例:
over()
:相當于over(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
,窗口大小為整個數(shù)據(jù)集,一個窗口赁豆,對窗口內的每一條數(shù)據(jù)冗美,能看到整個窗口。over (partition by 字段1)
:相當于over(partition by 字段1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
节预,以字段1分組后的數(shù)據(jù)集属韧,一個組一個窗口,對窗口內的每一條數(shù)據(jù)宵喂,能看到整個窗口。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ù)需要與窗口一起使用
LEAD
:LEAD(列名, n, [為null時的替換值])袱蚓,在窗口內几蜻,以當前行為原點,后第n行指定列的值颖低,如果第n行沒有則用null代替弧烤,指定了第3個參數(shù),使用第三個參數(shù)替代。注意只是一個值
LAG
:LAG(列名, n, [為null時的替換值])莺戒,在窗口內,以當前行為原點瘪校,前第n行指定列的值名段,如果第n行沒有則用null代替,指定了第3個參數(shù)麻惶,使用第三個參數(shù)替代信夫。注意只是一個值
FIRST_VALUE
:FIRST_VALUE(列名, [布爾值,默認false])脐彩,在窗口內姊扔,能看到的第一行指定列的值梅誓,false表示不忽略null值
LAST_VALUE
:LAST_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;