函数分类,HIVE CLI命令,简单函数,聚合函数,集合函数,特殊函数(窗口函数,分析函数,混合函数,UDTF),常用函数Demo

  1. 云栖社区>
  2. 博客>
  3. 正文

函数分类,HIVE CLI命令,简单函数,聚合函数,集合函数,特殊函数(窗口函数,分析函数,混合函数,UDTF),常用函数Demo

涂作权 2017-06-11 13:40:00 浏览2445
展开阅读全文

1.1. 函数分类


1.2. HIVE CLI命令

显示当前会话有多少函数可用 
SHOW FUNCTIONS;

显示函数的描述信息 
DESC FUNCTION concat;


显示函数的扩展描述信息 
DESC FUNCTION EXTENDED concat;

1.3. 简单函数

函数的计算粒度为单条记录。 
关系运算 
数学运算 
逻辑运算 
数值计算 
类型转换 
日期函数 
条件函数 
字符串函数 
统计函数

1.4. 聚合函数

函数处理的数据粒度为多条记录。 
sum()—
求和 
count()—
求数据量 
avg()—
求平均直 
distinct—
求不同值数 
min—
求最小值 
max—
求最大值

1.5. 集合函数

复合类型构建 
复杂类型访问 
复杂类型长度

1.6. 特殊函数

窗口函数

应用场景 
用于分区排序 
动态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, explodeUDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTFUTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。

1.7. 常用函数Demo

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 '/home/tuzq/software/hivedata/employee.txt' into table employee;
优先级依次为NOT AND OR

select id,money from employee where (id = '2' or id = '3' or id = '4' orid = '5') AND (money > 120 AND money < 250);

 

数据准备:在/home/tuzq/software/hivedata下创建employee.txt,

数据内容如下:

[root@hadoop1 hivedata]# cat employee.txt

1     100.0      fds

2     150.0      hdfs

3     200.0      hdfs

4     35.0 hadoop

5     280.7      hive

[root@hadoop1 hivedata]#

hive中的显示效果如下:

带有条件的查询:

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='1' then 'A0001'when id='2' then 'A0002' when id='3' then 'A0003' when id='4' then 'A0004' endfrom 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(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST‘) from
employee limit 1;


字符串连接函数: concat 
语法: concat(string A, stringB…) 
返回值: 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,stringB,string C)
 
regexp_extract(string subject,stringpattern,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;

 

网友评论

登录后评论
0/500
评论
涂作权
+ 关注