hive函数整理

趁着离职前夕的空档，整理了hive的所有函数，有很多新的发现，后面不断温习吧。

一、函数help

1. desc function XX 显示函数描述信息

hive> desc function concat;
OK
concat(str1, str2, … strN) - returns the concatenation of str1, str2, … strN or concat(bin1, bin2, … binN) - returns the concatenation of bytes in binary data bin1, bin2, … binN

2. desc function extended XX 显示函数详细描述信息

hive> desc function extended concat;
OK
concat(str1, str2, … strN) - returns the concatenation of str1, str2, … strN or concat(bin1, bin2, … binN) - returns the concatenation of bytes in binary data bin1, bin2, … binN
Returns NULL if any argument is NULL.
Example:
SELECT concat(‘abc’, ‘def’) FROM src LIMIT 1;
‘abcdef’

所有函数都可以根据以上语句查阅语法和例子。

二、有特色的函数
2.1 时间函数
2.1.1 add_months(start_date, num_months) 日期的月份加减

hive> SELECT add_months(‘2009-04-02’, 1);
OK
2009-05-02

hive> SELECT add_months(‘2009-01-31’, 1);
OK
2009-02-28

hive> SELECT add_months(‘2009-01-31’, -1);
OK
2008-12-31

2.1.2 current_date() 当前日期（yyyy-mm-dd）
current_timestamp() 当前日期（yyyy-mm-dd hh:mm:ss.sss）

2.1.3 last_day(date) 日期date所在月份的最后一天

select last_day(‘2019-02-25’);
OK
2019-02-28

2.1.4 next_day(start_date, day_of_week) 生成日期start_date之后的第一个规定星期所对应日期

SELECT next_day(‘2019-03-28’,‘friday’);
OK
2019-03-29

2.1.5 trunc(date, fmt) 按精度fmt对date进行处理，只保留年份或月份（fmt=‘MONTH’/‘MON’/'MM’为月份，fmt=‘YEAR’/‘YYYY’/'YY’为年份）

SELECT trunc(‘2009-02-25’, ‘MM’);
OK
2009-02-01

2.2 数学函数
2.2.1 cbrt(double) 开方

hive> select cbrt(8);
OK
2.0

2.2.2 ceil(x) 或ceiling(x) 向上取整，即取不小于x的最小整数
floor(x) 向下取整

hive> select ceil(4.5);
OK
5

hive> select ceil(-4.5);
OK
-4

2.2.3 conv(num, from_base, to_base) 进制转换函数（to_base如果为负数，则取正处理）

hive> select conv(17,10,2);
OK
10001

hive> select conv(17,10,-2);
OK
10001

2.2.4 a div b a除以b的商（忽略余数）

2.2.5 greatest(v1, v2, …) 一组数的最大值
least(v1, v2, …) 一组数的最小值

hive> select greatest(1,3,4,5);
OK
5

2.2.6 histogram_numeric(expr, nb) 对数据集expr生成nb列的直方图，输出的数组(x,y)表示直方中心和高度

hive> select count_day,count(1) as number from dill_decoration_pacific_test_phone group by count_day;
OK
1 158169
2 14046
3 1805
4 295
5 70
6 24
select histogram_numeric(count_day,5) from dill_decoration_pacific_test_phone;
OK
[{“x”:1.0,“y”:158169.0},{“x”:2.0,“y”:14046.0},{“x”:3.0,“y”:1805.0},{“x”:4.191780821917812,“y”:365.0},{“x”:6.142857142857141,“y”:28.0}]

2.2.7 negative(a) 生成a的负数

2.3 字符串函数
2.3.1 coalesce(a1, a2, …) 输出第一个非空数值

hive> SELECT coalesce(NULL, 1, 2,3);
OK
1

2.3.2 concat_ws(separator, [string | array(string)]+) 按分隔符合并

hive> select concat_ws(’.’,‘www’,‘baidu’,‘com’);
OK
www.baidu.com
hive> select concat_ws(’.’,‘www’,array(‘baidu’,‘com’));
OK
www.baidu.com

2.3.3 find_in_set(str,str_array) 字符串精确匹配（分隔符限定为’,’）

hive> SELECT count_day,FIND_IN_SET(‘115’,count_day) from test1;
OK
72 0
50 0
18 0
6 0
5 0
115 1
86 0
85 0
50 0
45 0
115 1
50 0

分隔符为逗号，输出字符串所在的位置：

hive> SELECT FIND_IN_SET(‘b’, ‘a,b,c,d’);
OK
2

2.3.4 format_number(X, D) 将number类型X转为字符串，保留D位小数

hive> SELECT format_number(12332.123456, 2);
OK
12,332.12

2.3.5 initcap(str) 单词首字母大写，其他字母均小写（以空格计算单词）

SELECT initcap(‘tHe soap’);
OK
The Soap

2.3.6 instr(str, substr) 输出str中包含substr字符串的第一个位数

select instr(‘666233666233’,‘233’);
OK
4

2.3.7 isnotnull(a) 判断列字段a是否为null（每行判断）
ps:isnull(a) 输出结果完全相反

select bool,count(1) as number from (select isnotnull(count_day) as bool from dill_decoration_pacific_test_phone)a group by bool;
OK
true 174413

2.3.8 levenshtein(str1, str2) 计算字符串的编辑距离

select levenshtein(‘abc’,‘abd’);
OK
1

2.3.9 lpad(str, len, pad) 在str左侧插入pad，直到str长度为len
从右侧插入是函数rpad

select lpad(‘hi’,5,’?!’);
OK
?!?hi
select lpad(‘hiii’,5,’?!’);
OK
?hiii
select lpad(‘hiii’,2,’?!’);
OK
hi

2.3.10 ltrim(str) 去除字符串前的空格

SELECT ltrim(’ facebook’);
OK
facebook

2.3.11 nvl(value,default_value) 对value中的空值取默认值default_value

2.3.12 parse_url(url, part) 截取url中的指定部分part
part：HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO
（必须为大写）

select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'HOST’);
OK
weibo.com
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'PATH’);
OK
/534712304/home
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'QUERY’);
OK
wvr=5
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'REF’);
OK
NULL
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'PROTOCOL’);
OK
https
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'AUTHORITY’);
OK
weibo.com
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'FILE’);
OK
/534712304/home?wvr=5
select parse_url(‘https://weibo.com/534712304/home?wvr=5’,'USERINFO’);
OK
NULL

2.3.13 regexp_extract(str, regexp[, idx]) 对str按指定正则规则划分，并取指定块

select regexp_extract(‘apple#orange#banana’,’([a-z]+)#([a-z]+)#([a-z]+)’,2);
OK
orange

2.3.14 regexp_replace(str, regexp, rep) 对str满足正则规则部分替换为rep

SELECT regexp_replace(‘100-200’, ‘(\d+)’, ‘num’);
OK
100-200

2.3.15 repeat(str, n) 对str重复n次

select repeat(‘abc’,3);
OK
abcabcabc

2.3.16 str_to_map(text, delimiter1, delimiter2) 对str转为map格式

select str_to_map(‘aaa:11&bbb:22’, ‘&’, ‘:’);
OK
{“bbb”:“22”,“aaa”:“11”}

2.4 行列转换函数
2.4.1 collect_list(x) 列转行（不去重）

hive> select msisdn,tag_name_lv5 from test1;
OK
00008670be10399962abee0bd6c1aa79 支付宝
00008670be10399962abee0bd6c1aa79 QQ音乐
00008670be10399962abee0bd6c1aa79 网易云音乐
00008670be10399962abee0bd6c1aa79 腾讯视频
00008670be10399962abee0bd6c1aa79 淘宝
00008670be10399962abee0bd6c1aa79 支付宝
00008670be10399962abee0bd6c1aa79 腾讯视频
hive> select msisdn,collect_list(tag_name_lv5) from test1 group by msisdn;
OK
00008670be10399962abee0bd6c1aa79
[“支付宝”,“腾讯视频”,“支付宝”,“QQ音乐”,“网易云音乐”,“腾讯视频”,“淘宝”]

其他应用：可指定任意一列数（和row_number作用类似）

hive> select msisdn,collect_set(tag_name_lv5)[0] from test1 group by msisdn;
OK
00008670be10399962abee0bd6c1aa79 支付宝

2.4.2 collect_set(x) 列转行（去重）

hive> select msisdn,collect_set(tag_name_lv5) from test1 group by msisdn;
OK
00008670be10399962abee0bd6c1aa79
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”]

2.4.3 posexplode(a) 对a按顺序行转列，并输出位置（从0开始）

select apps,item,app from (select msisdn,collect_set(tag_name_lv5) as apps from test1 group by msisdn)a lateral view posexplode(apps) t as item,app;
OK
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”] 0 支付宝
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”] 1 腾讯视频
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”] 2 QQ音乐
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”] 3 网易云音乐
[“支付宝”,“腾讯视频”,“QQ音乐”,“网易云音乐”,“淘宝”] 4 淘宝

2.4.4 stack(n, cols…) 列转行，n为行数

2.5 统计函数
2.5.1 covar_pop(x,y)=(SUM(x*y)-SUM(x)SUM(y)/COUNT(x,y))/COUNT(x,y)
总体协方差
covar_samp(x,y)=(SUM(xy)-SUM(x)*SUM(y)/COUNT(x,y))/(COUNT(x,y)-1)
样本协方差
corr(x,y)=COVAR_POP(x,y)/(STDDEV_POP(x)*STDDEV_POP(y))
相关系数

2.5.2 percentile(expr, pc) 计算expr的pc分位数（pc属于[0,1]）,要求expr为int

select percentile(count_day,1) from dill_decoration_pacific_test_phone;
OK
3.0

percentile_approx(expr, pc, [nb]) 用于大数据集的分位数计算，[nb]为设定精度，当expr的去重值大于[nb]时，则输出近似值。expr数值型都可以。

2.5.3 stddev_pop(x) 总体标准差
stddev_samp(x) 样本标准差

2.5.4 var_pop(x) 总体方差
var_samp(x) 样本方差

2.6 窗口函数
2.6.1 cume_dist 小于等于当前值的行数/分组内总行数

hive> select msisdn,tag_name_lv5,row_num,cume_dist() over (order by row_num desc) as a2 from test1;
OK
0000ac3f14df80848188ca40fb109e8e 美团 5 0.16666666666666666
00008670be10399962abee0bd6c1aa79 淘宝 5 0.16666666666666666
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.4166666666666667
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.4166666666666667
0000ac3f14df80848188ca40fb109e8e 唱吧 4 0.4166666666666667
0000ac3f14df80848188ca40fb109e8e 华为商城 3 0.5833333333333334
00008670be10399962abee0bd6c1aa79 网易云音乐 3 0.5833333333333334
00008670be10399962abee0bd6c1aa79 QQ音乐 2 0.75
0000ac3f14df80848188ca40fb109e8e 拼多多 2 0.75
0000ac3f14df80848188ca40fb109e8e 腾讯新闻 1 1.0
00008670be10399962abee0bd6c1aa79 支付宝 1 1.0
00008670be10399962abee0bd6c1aa79 支付宝 1 1.0

hive> select msisdn,tag_name_lv5,row_num,cume_dist() over (partition by msisdn order by row_num desc) as a2 from test1;
OK
00008670be10399962abee0bd6c1aa79 淘宝 5 0.14285714285714285
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.42857142857142855
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.42857142857142855
00008670be10399962abee0bd6c1aa79 网易云音乐 3 0.5714285714285714
00008670be10399962abee0bd6c1aa79 QQ音乐 2 0.7142857142857143
00008670be10399962abee0bd6c1aa79 支付宝 1 1.0
00008670be10399962abee0bd6c1aa79 支付宝 1 1.0
0000ac3f14df80848188ca40fb109e8e 美团 5 0.2
0000ac3f14df80848188ca40fb109e8e 唱吧 4 0.4
0000ac3f14df80848188ca40fb109e8e 华为商城 3 0.6
0000ac3f14df80848188ca40fb109e8e 拼多多 2 0.8
0000ac3f14df80848188ca40fb109e8e 腾讯新闻 1 1.0

2.6.2 dense_rank 排序时采用连续序号

hive> select msisdn,tag_name_lv5,row_num,dense_rank() over (partition by msisdn order by row_num asc) as a2,rank() over (partition by msisdn order by row_num asc) as a3 from test1;
OK
00008670be10399962abee0bd6c1aa79 支付宝 1 1 1
00008670be10399962abee0bd6c1aa79 支付宝 1 1 1
00008670be10399962abee0bd6c1aa79 QQ音乐 2 2 3
00008670be10399962abee0bd6c1aa79 网易云音乐 3 3 4
00008670be10399962abee0bd6c1aa79 腾讯视频 4 4 5
00008670be10399962abee0bd6c1aa79 腾讯视频 4 4 5
00008670be10399962abee0bd6c1aa79 淘宝 5 5 7
0000ac3f14df80848188ca40fb109e8e 腾讯新闻 1 1 1
0000ac3f14df80848188ca40fb109e8e 拼多多 2 2 2
0000ac3f14df80848188ca40fb109e8e 华为商城 3 3 3
0000ac3f14df80848188ca40fb109e8e 唱吧 4 4 4
0000ac3f14df80848188ca40fb109e8e 美团 5 5 5

2.6.3 percent_rank 分组算百分比排名

select msisdn,tag_name_lv5,row_num,percent_rank() over (partition by msisdn order by row_num asc) as a2 from test1;
OK
00008670be10399962abee0bd6c1aa79 支付宝 1 0.0
00008670be10399962abee0bd6c1aa79 支付宝 1 0.0
00008670be10399962abee0bd6c1aa79 QQ音乐 2 0.3333333333333333
00008670be10399962abee0bd6c1aa79 网易云音乐 3 0.5
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.6666666666666666
00008670be10399962abee0bd6c1aa79 腾讯视频 4 0.6666666666666666
00008670be10399962abee0bd6c1aa79 淘宝 5 1.0
0000ac3f14df80848188ca40fb109e8e 腾讯新闻 1 0.0
0000ac3f14df80848188ca40fb109e8e 拼多多 2 0.25
0000ac3f14df80848188ca40fb109e8e 华为商城 3 0.5
0000ac3f14df80848188ca40fb109e8e 唱吧 4 0.75
0000ac3f14df80848188ca40fb109e8e 美团 5 1.0

2.7 特色函数
2.7.1 printf(String format, Obj… args) 输出有printf风格的结果，类似python

SELECT printf(“Hello World %d %s”, 100, “days”);
OK
Hello World 100 days

2.7.2 reflect(class,method[,arg1[,arg2…]]) 调用java所有内置函数

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！