本文參考：黑澤君相關(guān)博客
本文是我總結(jié)日常工作中遇到的坑，結(jié)合黑澤君相關(guān)博客，選取、補充了部分內(nèi)容。
查詢函數(shù)（Hive高級）
- NVL(cloumn,replace_with)
如果cloumn為NULL，則NVL函數(shù)返回 replace_with 的值；
否則返回cloumn的值；
如果兩個參數(shù)都為NULL，則返回NULL。
hive> select nvl(a,1) from (select null as a) as aa;
1
Time taken: 0.147 seconds, Fetched: 1 row(s)
hive> select nvl(a,1) from (select 2 as a) as aa;
2
- case … when … then … else … end 函數(shù)
select 與 case結(jié)合使用最大的好處有兩點：
一是在顯示查詢結(jié)果時可以靈活的組織格式；
二是有效避免了多次對同一個表或幾個表的訪問。
Case具有兩種格式：
簡單Case函數(shù)
Case搜索函數(shù)
簡單Case函數(shù)
hive> CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
Case搜索函數(shù)
hive> CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END
種方式，可以實現(xiàn)相同的功能，
簡單Case函數(shù)的寫法相對比較簡潔，但是和Case搜索函數(shù)相比，功能方面會有些限制。
比如寫判斷式，或者對多個列判斷簡單Case函數(shù)都有點不方便。
在Case函數(shù)中Else部分的默認(rèn)值是NULL
行轉(zhuǎn)列
CONCAT(string A/col, string B/col, …)
返回輸入字符串連接后的結(jié)果，支持任意個輸入字符串。
CONCAT_WS(separator, str1, str2,…)
它是一個特殊形式的CONCAT()。
第一個參數(shù)是剩余參數(shù)間的分隔符；
分隔符可以是與剩余參數(shù)一樣的字符串；
如果分隔符是 NULL，返回值也將為NULL；
這個函數(shù)會跳過分隔符參數(shù)后的任何 NULL和空字符串； >分隔符將被加到被連接的字符串之間。
COLLECT_SET(col)
函數(shù)只接受基本數(shù)據(jù)類型，它的主要作用是將某字段的值進(jìn)行去重匯總，產(chǎn)生array類型字段
注意：CONCAT()和CONCAT_WS()都是UDTF函數(shù)
COLLECT_SET()函數(shù)類似聚合函數(shù)。
列轉(zhuǎn)行
EXPLODE(col)
將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行。LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解釋：lateral view用于和split,explode等UDTF函數(shù)一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進(jìn)行聚合。
hive> select movie explode(category) from movie_info;
上面是錯誤的。假設(shè)能執(zhí)行的話，得到的是笛卡爾積。
正確寫法
hive> select movie, category_name from
movie_info lateral view explode(category) table_tmp as category_name;
lateral view對原表的字段進(jìn)行了側(cè)寫，得到側(cè)寫表和側(cè)寫字段。
像split,explode等UDTF函數(shù)，是不能跟原表的字段直接進(jìn)行查詢的，UDTF函數(shù)一定要和lateral view聯(lián)合在一塊用。
- 窗口函數(shù)
OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化（比如在首行 或者最后一行）。
??CURRENT ROW：當(dāng)前行。
??n PRECEDING：往前n行數(shù)據(jù)。
??n FOLLOWING：往后n行數(shù)據(jù)。
??UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING 表示到后面的終點。
??LAG(col,n)：往前第n行數(shù)據(jù)。
??LEAD(col,n)：往后第n行數(shù)據(jù)。
??NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個組有編號，編號從1開始，對于每一行，NTILE返回此行所屬的組的編號。注意：n必須為int類型。
hive> desc business;
OK
name string
orderdate string
cost int
hive> select * from business;
OK
jack 2017-01-01 10
tony 2017-01-02 15
jack 2017-02-03 23
tony 2017-01-04 29
jack 2017-01-05 46
jack 2017-04-06 42
tony 2017-01-07 50
jack 2017-01-08 55
mart 2017-04-08 62
mart 2017-04-09 68
neil 2017-05-10 12
mart 2017-04-11 75
neil 2017-06-12 80
mart 2017-04-13 94
只有一個分區(qū)，所有行相加，得一個值
hive> select * ,sum(cost) over() as sample1 from business ;
OK
mart 2017-04-13 94 661
neil 2017-06-12 80 661
mart 2017-04-11 75 661
neil 2017-05-10 12 661
mart 2017-04-09 68 661
mart 2017-04-08 62 661
jack 2017-01-08 55 661
tony 2017-01-07 50 661
jack 2017-04-06 42 661
jack 2017-01-05 46 661
tony 2017-01-04 29 661
jack 2017-02-03 23 661
tony 2017-01-02 15 661
jack 2017-01-01 10 661
按orderdate排序，只有一個分區(qū)，區(qū)內(nèi)數(shù)據(jù)累加
hive> select * ,sum(cost) over(order by orderdate) from business ;
OK
jack 2017-01-01 10 10
tony 2017-01-02 15 25
tony 2017-01-04 29 54
jack 2017-01-05 46 100
tony 2017-01-07 50 150
jack 2017-01-08 55 205
jack 2017-02-03 23 228
jack 2017-04-06 42 270
mart 2017-04-08 62 332
mart 2017-04-09 68 400
mart 2017-04-11 75 475
mart 2017-04-13 94 569
neil 2017-05-10 12 581
neil 2017-06-12 80 661
等效上一個語句
hive> select * ,sum(cost) over(order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) from business ;
OK
jack 2017-01-01 10 10
tony 2017-01-02 15 25
tony 2017-01-04 29 54
jack 2017-01-05 46 100
tony 2017-01-07 50 150
jack 2017-01-08 55 205
jack 2017-02-03 23 228
jack 2017-04-06 42 270
mart 2017-04-08 62 332
mart 2017-04-09 68 400
mart 2017-04-11 75 475
mart 2017-04-13 94 569
neil 2017-05-10 12 581
neil 2017-06-12 80 661
按name分區(qū)，按orderdate排序，有多個分區(qū)，區(qū)內(nèi)數(shù)據(jù)各自累加
hive> select * ,sum(cost) over(partition by name order by orderdate) from business ;
OK
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 111
jack 2017-02-03 23 134
jack 2017-04-06 42 176
mart 2017-04-08 62 62
mart 2017-04-09 68 130
mart 2017-04-11 75 205
mart 2017-04-13 94 299
neil 2017-05-10 12 12
neil 2017-06-12 80 92
tony 2017-01-02 15 15
tony 2017-01-04 29 44
tony 2017-01-07 50 94
同上
hive> select * ,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) from business ;
當(dāng)前行和前一行累積
hive> select * ,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and CURRENT ROW) from business ;
OK
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 101
jack 2017-02-03 23 78
jack 2017-04-06 42 65
mart 2017-04-08 62 62
mart 2017-04-09 68 130
mart 2017-04-11 75 143
mart 2017-04-13 94 169
neil 2017-05-10 12 12
neil 2017-06-12 80 92
tony 2017-01-02 15 15
tony 2017-01-04 29 44
tony 2017-01-07 50 79
前一行、當(dāng)前行、下一行做累積
hive> select * ,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) from business ;
OK
jack 2017-01-01 10 56
jack 2017-01-05 46 111
jack 2017-01-08 55 124
jack 2017-02-03 23 120
jack 2017-04-06 42 65
mart 2017-04-08 62 130
mart 2017-04-09 68 205
mart 2017-04-11 75 237
mart 2017-04-13 94 169
neil 2017-05-10 12 92
neil 2017-06-12 80 92
tony 2017-01-02 15 44
tony 2017-01-04 29 94
tony 2017-01-07 50 79
當(dāng)前行和后續(xù)所有行做累積
hive> select * ,sum(cost) over(partition by name order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING) from business ;
OK
jack 2017-01-01 10 176
jack 2017-01-05 46 166
jack 2017-01-08 55 120
jack 2017-02-03 23 65
jack 2017-04-06 42 42
mart 2017-04-08 62 299
mart 2017-04-09 68 237
mart 2017-04-11 75 169
mart 2017-04-13 94 94
neil 2017-05-10 12 92
neil 2017-06-12 80 80
tony 2017-01-02 15 94
tony 2017-01-04 29 79
tony 2017-01-07 50 50
按月份分區(qū)
hive> select *,
> sum(cost) over(distribute by month(orderdate))
> from business;
OK
jack 2017-01-01 10 205
jack 2017-01-08 55 205
tony 2017-01-07 50 205
jack 2017-01-05 46 205
tony 2017-01-04 29 205
tony 2017-01-02 15 205
jack 2017-02-03 23 23
mart 2017-04-13 94 341
jack 2017-04-06 42 341
mart 2017-04-11 75 341
mart 2017-04-09 68 341
mart 2017-04-08 62 341
neil 2017-05-10 12 12
neil 2017-06-12 80 80
按月份分區(qū)（同上）
hive> select *,
> sum(cost) over(partition by month(orderdate))
> from business;
OK
jack 2017-01-01 10 205
jack 2017-01-08 55 205
tony 2017-01-07 50 205
jack 2017-01-05 46 205
tony 2017-01-04 29 205
tony 2017-01-02 15 205
jack 2017-02-03 23 23
mart 2017-04-13 94 341
jack 2017-04-06 42 341
mart 2017-04-11 75 341
mart 2017-04-09 68 341
mart 2017-04-08 62 341
neil 2017-05-10 12 12
neil 2017-06-12 80 80
按日期累加 從開始到現(xiàn)在
hive> select *,
> sum(cost) over(sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
> from business;
OK
jack 2017-01-01 10 10
tony 2017-01-02 15 25
tony 2017-01-04 29 54
jack 2017-01-05 46 100
tony 2017-01-07 50 150
jack 2017-01-08 55 205
jack 2017-02-03 23 228
jack 2017-04-06 42 270
mart 2017-04-08 62 332
mart 2017-04-09 68 400
mart 2017-04-11 75 475
mart 2017-04-13 94 569
neil 2017-05-10 12 581
neil 2017-06-12 80 661
按日期累加 某一天和前后一天 統(tǒng)計(當(dāng)前行和前邊一行及后面一行)
hive> select *,
> sum(cost) over(sort by orderdate rows between 1 PRECEDING and 1 FOLLOWING)
> from business;
OK
jack 2017-01-01 10 25
tony 2017-01-02 15 54
tony 2017-01-04 29 90
jack 2017-01-05 46 125
tony 2017-01-07 50 151
jack 2017-01-08 55 128
jack 2017-02-03 23 120
jack 2017-04-06 42 127
mart 2017-04-08 62 172
mart 2017-04-09 68 205
mart 2017-04-11 75 237
mart 2017-04-13 94 181
neil 2017-05-10 12 186
neil 2017-06-12 80 92
按用戶 日累積
hive> select *,
> sum(cost) over(distribute by name sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
> from business;
OK
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 111
jack 2017-02-03 23 134
jack 2017-04-06 42 176
mart 2017-04-08 62 62
mart 2017-04-09 68 130
mart 2017-04-11 75 205
mart 2017-04-13 94 299
neil 2017-05-10 12 12
neil 2017-06-12 80 92
tony 2017-01-02 15 15
tony 2017-01-04 29 44
tony 2017-01-07 50 94
查詢上一次，當(dāng)前購買時間
hive> select *,
> lag(orderdate, 1) over(distribute by name sort by orderdate) ,
> lead(orderdate, 1) over(distribute by name sort by orderdate)
> from business;
OK
jack 2017-01-01 10 NULL 2017-01-05
jack 2017-01-05 46 2017-01-01 2017-01-08
jack 2017-01-08 55 2017-01-05 2017-02-03
jack 2017-02-03 23 2017-01-08 2017-04-06
jack 2017-04-06 42 2017-02-03 NULL
mart 2017-04-08 62 NULL 2017-04-09
mart 2017-04-09 68 2017-04-08 2017-04-11
mart 2017-04-11 75 2017-04-09 2017-04-13
mart 2017-04-13 94 2017-04-11 NULL
neil 2017-05-10 12 NULL 2017-06-12
neil 2017-06-12 80 2017-05-10 NULL
tony 2017-01-02 15 NULL 2017-01-04
tony 2017-01-04 29 2017-01-02 2017-01-07
tony 2017-01-07 50 2017-01-04 NULL
查詢前20%時間的訂單信息
hive> select *
> from (select *,
> ntile(5) over(order by orderdate) as gid
> from business) as t
> where t.gid=1;
OK
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
注： ntile(n) 將有序分區(qū)中的行平均分發(fā)到指定數(shù)據(jù)的組中,每個組中記錄數(shù)量為total/n，
那么取前20%就是前五分之一，只要分為五個組，去第一個組即可
hive> select *,
> ntile(5) over(sort by orderdate)
> from business;
OK
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
jack 2017-01-05 46 2
tony 2017-01-07 50 2
jack 2017-01-08 55 2
jack 2017-02-03 23 3
jack 2017-04-06 42 3
mart 2017-04-08 62 3
mart 2017-04-09 68 4
mart 2017-04-11 75 4
mart 2017-04-13 94 4
neil 2017-05-10 12 5
neil 2017-06-12 80 5
rank函數(shù)
RANK()：排序相同時會重復(fù)，總數(shù)不會變。（兩個100分為列第一名和第二名，99分的為第三名）
DENSE_RANK()：排序相同時會重復(fù)，總數(shù)會減少。（兩個100分并列第一，99分的為第二名）
ROW_NUMBER()：會根據(jù)順序計算。
注意：使用rank函數(shù)需要配合over函數(shù)（窗口函數(shù)）使用
準(zhǔn)備數(shù)據(jù)
vim score.txt
李白,語文,100
李白,數(shù)學(xué),90
李白,天文,85
屈原,語文,100
屈原,數(shù)學(xué),85
屈原,天文,90
諸葛亮,語文,95
諸葛亮,數(shù)學(xué),99
諸葛亮,天文,110
祖沖之,語文,90
祖沖之,數(shù)學(xué),100
祖沖之,天文,90
hive> create table score(
> name string,
> subject string,
> score int)
> row format delimited fields terminated by ",";
OK
hive> load data local inpath '/root/tmp_lillcol/score.txt' into table score;
Loading data to table iptv.score
Table iptv.score stats: [numFiles=1, totalSize=226]
OK
Time taken: 0.325 seconds
hive> select * from score;
OK
李白 語文 100
李白 數(shù)學(xué) 90
李白 天文 85
屈原 語文 100
屈原 數(shù)學(xué) 85
屈原 天文 90
諸葛亮 語文 95
諸葛亮 數(shù)學(xué) 99
諸葛亮 天文 110
祖沖之 語文 90
祖沖之 數(shù)學(xué) 100
祖沖之 天文 90
根據(jù)學(xué)科排名，排序相同時會重復(fù)，總數(shù)不會變。
hive> select *,
> rank() over(partition by subject order by score desc)
> from score;
OK
諸葛亮 天文 110 1
祖沖之 天文 90 2
屈原 天文 90 2
李白 天文 85 4
祖沖之 數(shù)學(xué) 100 1
諸葛亮 數(shù)學(xué) 99 2
李白 數(shù)學(xué) 90 3
屈原 數(shù)學(xué) 85 4
屈原 語文 100 1
李白 語文 100 1
諸葛亮 語文 95 3
祖沖之 語文 90 4
Time taken: 19.103 seconds, Fetched: 12 row(s)
根據(jù)學(xué)科排名，排序相同時會重復(fù)，總數(shù)會減少。
hive> select *,
> dense_rank()over(partition by subject order by score desc)
> from score;
OK
諸葛亮 天文 110 1
祖沖之 天文 90 2
屈原 天文 90 2
李白 天文 85 3
祖沖之 數(shù)學(xué) 100 1
諸葛亮 數(shù)學(xué) 99 2
李白 數(shù)學(xué) 90 3
屈原 數(shù)學(xué) 85 4
屈原 語文 100 1
李白 語文 100 1
諸葛亮 語文 95 2
祖沖之 語文 90 3
根據(jù)學(xué)科排名，返回的是行號，分?jǐn)?shù)相同排序不同
hive> select *,
> row_number() over(partition by subject order by score desc)
> from score;
OK
諸葛亮 天文 110 1
祖沖之 天文 90 2
屈原 天文 90 3
李白 天文 85 4
祖沖之 數(shù)學(xué) 100 1
諸葛亮 數(shù)學(xué) 99 2
李白 數(shù)學(xué) 90 3
屈原 數(shù)學(xué) 85 4
屈原 語文 100 1
李白 語文 100 2
諸葛亮 語文 95 3
祖沖之 語文 90 4
函數(shù)
查看系統(tǒng)自帶的函數(shù)
hive> show functions;
OK
!
!=
%
&
*
+
-
/
<
<=
<=>
<>
=
==
>
>=
^
abs
acos
add_months
and
array
array_contains
ascii
asin
assert_true
atan
avg
base64
between
bin
case
cbrt
ceil
ceiling
coalesce
collect_list
collect_set
compute_stats
concat
concat_ws
context_ngrams
conv
corr
cos
count
covar_pop
covar_samp
crc32
create_union
cume_dist
current_database
current_date
current_timestamp
current_user
date_add
date_format
date_sub
datediff
day
dayofmonth
dayofweek
decode
degrees
dense_rank
div
e
elt
encode
ewah_bitmap
ewah_bitmap_and
ewah_bitmap_empty
ewah_bitmap_or
exp
explode
field
find_in_set
first_value
floor
format_number
from_unixtime
from_utc_timestamp
get_json_object
greatest
hash
hex
histogram_numeric
hour
if
in
in_file
index
initcap
inline
instr
isnotnull
isnull
java_method
json_tuple
lag
last_day
last_value
lcase
lead
least
length
levenshtein
like
ln
locate
log
log10
log2
logged_in_user
lower
lpad
ltrim
map
map_keys
map_values
matchpath
max
md5
min
minute
month
months_between
named_struct
negative
next_day
ngrams
noop
noopstreaming
noopwithmap
noopwithmapstreaming
not
ntile
nvl
or
parse_url
parse_url_tuple
percent_rank
percentile
percentile_approx
pi
pmod
posexplode
positive
pow
power
printf
radians
rand
rank
reflect
reflect2
regexp
regexp_extract
regexp_replace
repeat
reverse
rlike
round
row_number
rpad
rtrim
second
sentences
sha2
sign
sin
size
sort_array
soundex
space
split
sqrt
stack
std
stddev
stddev_pop
stddev_samp
str_to_map
struct
substr
substring
sum
tan
to_date
to_unix_timestamp
to_utc_timestamp
translate
trim
trunc
ucase
unbase64
unhex
unix_timestamp
upper
uuid
var_pop
var_samp
variance
version
weekofyear
when
windowingtablefunction
xpath
xpath_boolean
xpath_double
xpath_float
xpath_int
xpath_long
xpath_number
xpath_short
xpath_string
year
|
~
顯示自帶函數(shù)用法
hive> desc function extended minute;
OK
minute(date) - Returns the minute of date
date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
Example:
> SELECT minute('2009-07-30 12:58:59') FROM src LIMIT 1;
58
> SELECT minute('12:58:59') FROM src LIMIT 1;
58
簡單顯示
hive> desc function minute;
minute(date) - Returns the minute of date
自定義函數(shù)
Hive自定義函數(shù)包括三種UDF、UDAF、UDTF
UDF(User-Defined-Function) 一進(jìn)一出
UDAF(User- Defined Aggregation Funcation)聚集函數(shù)，多進(jìn)一出。Count/max/min
UDTF(User-Defined Table-Generating Functions) 一進(jìn)多出，如lateral view explore()
使用方式 ：在HIVE會話中add自定義函數(shù)的jar文件，然后創(chuàng)建function繼而使用函數(shù)
UDF(User-Defined-Function) 一進(jìn)一出
依賴：
sbt:
libraryDependencies += "org.apache.hive" % "hive-exec" % "1.2.1"
maven:
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
關(guān)鍵代碼：
import org.apache.hadoop.hive.ql.exec.UDF;
/**
* @author lillcol
* 2019/7/15-17:56
*/
public class MyUDF extends UDF {
public int evaluate(String str) {
try{
int n = Integer.parseInt(str);
return n*n;
}catch(Exception e){
e.printStackTrace();
return -1;
}
}
}
上述代碼打包為udf.jar
將jar包添加到hive的class path
hive> add jar /root/tmp_lillcol/udf.jar;
Added [/root/tmp_lillcol/udf.jar] to class path
Added resources: [/root/tmp_lillcol/udf.jar]
創(chuàng)建臨時函數(shù)與開發(fā)好的java class關(guān)聯(lián)
hive> create temporary function square as "com.iptv.test.MyUDF";
OK
hive> select * ,square(score) from score ;
OK
李白 語文 100 10000
李白 數(shù)學(xué) 90 8100
李白 天文 85 7225
屈原 語文 100 10000
屈原 數(shù)學(xué) 85 7225
屈原 天文 90 8100
諸葛亮 語文 95 9025
諸葛亮 數(shù)學(xué) 99 9801
諸葛亮 天文 110 12100
祖沖之 語文 90 8100
祖沖之 數(shù)學(xué) 100 10000
祖沖之 天文 90 8100
