Hive常见内置函数及其使⽤函数分类
HIVE CLI命令
显⽰当前会话有多少函数可⽤
SHOW FUNCTIONS;
显⽰函数的描写叙述信息
DESC FUNCTION concat;
显⽰函数的扩展描写叙述信息
DESC FUNCTION EXTENDED concat;
简单函数
函数的计算粒度为单条记录。
关系运算
数学运算
逻辑运算
数值计算
类型转换
⽇期函数
条件函数
字符串函数
统计函数
聚合函数
函数处理的数据粒度为多条记录。
sum()—求和
count()—求数据量
avg()—求平均直
distinct—求不同值数
min—求最⼩值
max—求最⼈值
集合函数
复合类型构建
复杂类型訪问
复杂类型长度
特殊函数
窗体函数
应⽤场景
⽤于分区排序
动态Group By
Top N
累计计算
层次查询
Windowing functions
lead
lag
FIRST_VALUE
LAST_VALUE
分析函数
Analytics functions
RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
混合函数
java_method(class,method [,arg1 [,arg2])
reflect(class,method [,arg1 [,arg2..]])
hash(a1 [,a2...])
UDTF
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
ateral view⽤于和split, explode等UDTF⼀起使⽤，它可以将⼀⾏数据拆成多⾏数据，在此基础上可以对拆分后的数据进⾏聚合。lateral view⾸先为原始表的每⾏调⽤UDTF，UTDF会把⼀⾏拆分成⼀或者多⾏。lateral view再把结果组合。产⽣⼀个⽀持别名表的虚拟表。经常使⽤函数Demo：
hive 字符串转数组create table employee(
id string,
money double,
type string
)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
load data local inpath '/liguodong/hive/data' into table employee;
select * from employee;
优先级依次为NOT AND OR
select id,money from employee where (id='1001' or id='1002') and money='100';
cast类型转换
select cast(1.5 as int);
if推断
if(con,'','');
hive (default)> select if(2>1,'YES','NO');
YES
case when con then '' when con then '' else  '' end (''⾥⾯类型要⼀样)
select case when id='1001' then 'v1001' when id='1002' then 'v1002' else 'v1003' end from employee;
get_json_object
get_json_object(json 解析函数，⽤来处理json，必须是json格式)
select get_json_object('{"name":"jack","age":"20"}','$.name');
URL解析函数
parse_url(string urlString, string partToExtract [, string keyToExtract])
select parse_url('facebook/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from
employee limit 1;
字符串连接函数： concat
语法: concat(string A, string B…)
返回值: string
说明：返回输⼊字符串连接后的结果，⽀持随意个输⼊字符串
举例：
hive> select concat('abc','def’,'gh') from lxw_dual;
abcdefgh
带分隔符字符串连接函数： concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输⼊字符串连接后的结果， SEP 表⽰各个字符串间的分隔符
concat_ws(string SEP, array<string>)
举例：
hive> select concat_ws(',','abc','def','gh') from lxw_dual;
abc,def,gh
列出该字段全部不反复的值，相当于去重
collect_set(id)  //返回的是数组
列出该字段全部的值。列出来不去重
collect_list(id)  //返回的是数组
select collect_set(id) from taborder;
求和
sum(money)
统计列数
count(*)
select sum(num),count(*) from taborder;
窗体函数
first_value(第⼀⾏值)
first_value(money) over (partition by id order by money)
select ch,num,first_value(num) over (partition by ch order by num) from taborder;
rows between 1 preceding and 1 following (当前⾏以及当前⾏的前⼀⾏与后⼀⾏)
hive (liguodong)> select ch,num,first_value(num) over (partition by ch order by num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) from taborder; last_value 最后⼀⾏值
hive (liguodong)> select ch,num,last_value(num) over (partition by ch) from taborder;
lead
去当前⾏后⾯的第⼆⾏的值
lead(money,2) over (order by money)
lag
去当前⾏前⾯的第⼆⾏的值
lag(money,2) over (order by money)
```
```
select ch, num, lead(num,2) over (order by num) from taborder;
select ch, num, lag(num,2) over (order by num) from taborder;
rank排名
rank() over(partition by id order by money)
select ch, num, rank() over(partition by ch order by num) as rank from taborder;
select ch, num, dense_rank() over(partition by ch order by num) as dense_rank from taborder;
cume_dist
cume_dist (同样值的最⼤⾏号/⾏数)
cume_dist() over (partition by id order by money)
percent_rank (同样值的最⼩⾏号-1)/(⾏数-1)
第⼀个总是从0開始
percent_rank() over (partition by id order by money)
select ch,num,cume_dist() over (partition by ch order by num) as cume_dist,
percent_rank() over (partition by ch order by num) as percent_rank
from taborder;
ntile分⽚
ntile(2) over (order by money desc)  分两份
select ch,num,ntile(2) over (order by num desc) from taborder;
混合函数
select id,java_method("java.lang,Math","sqrt",cast(id as double)) as sqrt from hiveTest; UDTF
select id,adid
from employee
lateral view explode(split(type,'B')) tt as adid;
explode 把⼀列转成多⾏
hive (liguodong)>  select id,adid
>  from hiveDemo
>  lateral view explode(split(str,',')) tt as adid;
正則表達式
使⽤正則表達式的函数
regexp_replace(string subject A,string B,string C)
regexp_extract(string subject,string pattern,int index)
hive> select regexp_replace('foobar', 'oo|ar', '') from lxw_dual;
fb
hive> select regexp_replace('979|7.10.80|8684', '.*\\|(.*)',1) from hiveDemo limit 1; hive> select regexp_replace('979|7.10.80|8684', '(.*?)\\|(.*)',1) from hiveDemo limit 1;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。