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;
