1. 概述
總結(jié)一些自己不常用但必須知道的hive功能
2. 導(dǎo)出數(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 | 全局排序，只有一個(gè)reduce | |
| sort by | 分區(qū)內(nèi)排序，對(duì)排序字段進(jìn)行隨機(jī)分區(qū)以使數(shù)據(jù)均勻，reduce多個(gè) | |
| distribute by | 搭配sort by使用，分區(qū)內(nèi)排序，對(duì)記錄按照distribute by的字段進(jìn)行hash分區(qū)，reduce多個(gè) | select * from test dristribute by row_key sort by entname desc; |
| cluster by | 當(dāng)distribute by與sort by字段相同時(shí)，可以使用cluster by來(lái)代替，cluster by不支持desc、asc，默認(rèn)就是asc | select * from test cluster by row_key; |
4. 分桶
一個(gè)分區(qū)代表一個(gè)目錄，分區(qū)針對(duì)的是目錄，而分桶針對(duì)的是數(shù)據(jù)文件，同一個(gè)數(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;
多用來(lái)采樣，語(yǔ)法是tablesample (bucket x out of y [on column]),其中x表示從第幾個(gè)bucket開始，y表示間隔多少個(gè)bucket在取樣及采樣的比例：
比如總bucket數(shù)為32，x為2，y為16，返回的數(shù)據(jù)量為32/16個(gè)桶，返回第2、18個(gè)桶的數(shù)據(jù)
比如總bucket數(shù)為32，x為2，y為64，返回的數(shù)據(jù)量為32/64個(gè)桶，返回第2個(gè)桶的1/2數(shù)據(jù)
還可以對(duì)bucket數(shù)成倍數(shù)關(guān)系，分桶字段類型相同的表執(zhí)行mapjoin
5. skewed table
語(yǔ)法：
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ù)會(huì)分別放到不同的子目錄中
這個(gè)表是用來(lái)優(yōu)化join時(shí)少量關(guān)聯(lián)key數(shù)據(jù)傾斜的，假設(shè)有2張表A、B，如果A表的key在值1、5、6時(shí)數(shù)據(jù)傾斜，B的各個(gè)key的數(shù)據(jù)量不大(即單獨(dú)key的數(shù)據(jù)可以放入內(nèi)存)。為了避免在key為1、5、6時(shí)數(shù)據(jù)傾斜，常規(guī)解決方式：
- 將A表的數(shù)據(jù)分為4部分，第一部分為key為1的數(shù)據(jù)，第二部分為key為5的數(shù)據(jù)，第三部分為key為6的數(shù)據(jù)，其余的數(shù)據(jù)為第四部分
- B表同樣處理
- 將第四部分分發(fā)到reducer中進(jìn)行join，將其余的三部分分為3個(gè)map task，B的數(shù)據(jù)加載到內(nèi)存，與A的對(duì)應(yīng)部分?jǐn)?shù)據(jù)進(jìn)行map join
這樣能解決數(shù)據(jù)傾斜的問(wèn)題，但是需要多次數(shù)據(jù)讀取，為了只讀取一次，引入了skewed table，skewed table讓hive知道哪些key需要mapjoin，list bucketing功能則已經(jīng)把數(shù)據(jù)進(jìn)行劃分，只需要讀取一遍數(shù)據(jù)
注意的是，這個(gè)功能只針對(duì)少量key數(shù)據(jù)傾斜，因?yàn)槊總€(gè)key對(duì)應(yīng)的目錄，會(huì)作為元數(shù)據(jù)添加到metastore
6. 窗口
聚合函數(shù)是多對(duì)1，而窗口還是多對(duì)多，行數(shù)不變，且也能獲取聚合值。注意窗口是針對(duì)一條數(shù)據(jù)而言的
6.1 開窗
開窗的關(guān)鍵字: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
語(yǔ)法OVER(window frame)，以當(dāng)前行為原點(diǎn)，n PRECEDING表示前n行，n FOLLOWING表示后n行，UNBOUNDED表示無(wú)窮大，UNBOUNDED PRECEDING表示窗口起點(diǎn)，UNBOUNDED FOLLOWING表示窗口終點(diǎn)，。RANGE表示有Order Specification，否則為ROWS
- Window的別名
舉例：
over()：相當(dāng)于over(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),窗口大小為整個(gè)數(shù)據(jù)集，一個(gè)窗口，對(duì)窗口內(nèi)的每一條數(shù)據(jù)，能看到整個(gè)窗口。over (partition by 字段1)：相當(dāng)于over(partition by 字段1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)，以字段1分組后的數(shù)據(jù)集，一個(gè)組一個(gè)窗口，對(duì)窗口內(nèi)的每一條數(shù)據(jù)，能看到整個(gè)窗口。over (order by 字段1)：相當(dāng)于over(order by 字段1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，窗口大小為整個(gè)數(shù)據(jù)集，一個(gè)窗口，對(duì)窗口內(nèi)的每一條數(shù)據(jù)，能看到第一行到自己。
6.3 窗口函數(shù)(windowing functions)
筆者習(xí)慣稱為錯(cuò)位函數(shù)，這些函數(shù)需要與窗口一起使用
LEAD：LEAD(列名, n, [為null時(shí)的替換值])，在窗口內(nèi)，以當(dāng)前行為原點(diǎn)，后第n行指定列的值，如果第n行沒(méi)有則用null代替，指定了第3個(gè)參數(shù)，使用第三個(gè)參數(shù)替代。注意只是一個(gè)值
LAG：LAG(列名, n, [為null時(shí)的替換值])，在窗口內(nèi)，以當(dāng)前行為原點(diǎn)，前第n行指定列的值，如果第n行沒(méi)有則用null代替，指定了第3個(gè)參數(shù)，使用第三個(gè)參數(shù)替代。注意只是一個(gè)值
FIRST_VALUE：FIRST_VALUE(列名, [布爾值，默認(rèn)false])，在窗口內(nèi)，能看到的第一行指定列的值，false表示不忽略null值
LAST_VALUE：LAST_VALUE(列名, [布爾值，默認(rèn)false])，在窗口內(nèi)，能看到的最后一行指定列的值，false表示不忽略null值
6.4 聚合函數(shù)
SUM、AVG、COUNT、MAX、MIN、COUNT(DISTINCT)
6.5 序號(hào)函數(shù)(Analytics functions)
- RANK：遇到重復(fù)的字段值，序號(hào)不變，但是序號(hào)不連續(xù)，從1開始
- ROW_NUMBER：不管重復(fù)，序號(hào)從1開始
- DENSE_RANK：遇到重復(fù)的字段值，序號(hào)不變，但是序號(hào)連續(xù)，從1開始
- CUME_DIST：小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)
- PERCENT_RANK：RANK的拓展，(當(dāng)前行的RANK值-1)/(分組內(nèi)總行數(shù)-1)
- NTILE：均勻進(jìn)行分組
7. 自定義函數(shù)
UDF：一條進(jìn)一條出
UDTF：全稱user-defined table-generating functions，一條進(jìn)多條輸出
UDAF: 多條進(jìn)一條輸出
8. 動(dòng)態(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];
用來(lái)統(tǒng)計(jì)表的信息。由于spark-sql生成的表沒(méi)有表的統(tǒng)計(jì)信息，當(dāng)需要在spark-sql中broadastHashJoin或者在hive中mapJoin時(shí)，讀取不到rawDataSize信息而不能啟用，這時(shí)候可以使用該命令來(lái)獲取表的統(tǒng)計(jì)信息
筆者遇到上述情況就使用
ANALYZE TABLE [db_name.]tablenameANALYZE TABLE [db_name.]tablename COMPUTE STATISTICS NOSCAN;
