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
  • 1
  • 2
  • 3
  • 4

分析函数

Analytics functions

RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

混合函数

java_method(class,method [,arg1 [,arg2])
reflect(class,method [,arg1 [,arg2..]])
hash(a1 [,a2...])
  • 1
  • 2
  • 3

UDTF

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*  
fromClause: FROM baseTable (lateralView)*  
  • 1
  • 2

ateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。

常用函数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 '/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';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

cast类型转换

select cast(1.5 as int);
  • 1

if判断

if(con,'','');

hive (default)> select if(2>1,'YES','NO');
YES
  • 1
  • 2
  • 3
  • 4
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;
  • 1
  • 2
  • 3

get_json_object

get_json_object(json 解析函数,用来处理json,必须是json格式)
select get_json_object('{"name":"jack","age":"20"}','$.name');
  • 1
  • 2

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;
  • 1
  • 2
  • 3
  • 4

字符串连接函数: concat
语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串
举例:

hive> select concat('abc','def’,'gh') from lxw_dual;
abcdefgh
  • 1
  • 2

带分隔符字符串连接函数: 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
  • 1
  • 2
  • 3
  • 4
  • 5

列出该字段所有不重复的值,相当于去重
collect_set(id)  //返回的是数组

列出该字段所有的值,列出来不去重 
collect_list(id)   //返回的是数组

select collect_set(id) from taborder;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

求和
sum(money)

统计列数
count(*)

select sum(num),count(*) from taborder;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

窗口函数

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;
  • 1
  • 2
  • 3
  • 4
  • 5

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;
  • 1
  • 2
  • 3
  • 4

last_value 最后一行值
hive (liguodong)> select ch,num,last_value(num) over (partition by ch) from taborder;
  • 1
  • 2
  • 3

 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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

 select ch, num, lag(num,2) over (order by num) from taborder;
  • 1

rank排名

rank() over(partition by id order by money)
  • 1
select ch, num, rank() over(partition by ch order by num) as rank from taborder;
  • 1

select ch, num, dense_rank() over(partition by ch order by num) as dense_rank from taborder;
  • 1

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

 ntile分片 
 ntile(2) over (order by money desc)  分两份 

 select ch,num,ntile(2) over (order by num desc) from taborder;
  • 1
  • 2
  • 3
  • 4

混合函数

select id,java_method("java.lang,Math","sqrt",cast(id as double)) as sqrt from hiveTest;
  • 1

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

正则表达式
使用正则表达式的函数
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;
  • 1

hive> select regexp_replace('979|7.10.80|8684', '(.*?)\\|(.*)',1) from hiveDemo limit 1;
  •  

一、关系运算: 4 1. 等值比较: = 4 2. 不等值比较: 4 3. 小于比较: < 4 4. 小于等于比较: 5 6. 大于等于比较: >= 5 7. 空值判断: IS NULL 5 8. 非空判断: IS NOT NULL 6 9. LIKE比较: LIKE 6 10. JAVA的LIKE操作: RLIKE 6 11. REGEXP操作: REGEXP 7 二、数学运算: 7 1. 加法操作: + 7 2. 减法操作: - 7 3. 乘法操作: * 8 4. 除法操作: / 8 5. 取余操作: % 8 6. 位与操作: & 9 7. 位或操作: | 9 8. 位异或操作: ^ 9 9.位取反操作: ~ 10 三、逻辑运算: 10 1. 逻辑与操作: AND 10 2. 逻辑或操作: OR 10 3. 逻辑非操作: NOT 10 四、数值计算 11 1. 取整函数: round 11 2. 指定精度取整函数: round 11 3. 向下取整函数: floor 11 4. 向上取整函数: ceil 12 5. 向上取整函数: ceiling 12 6. 取随机数函数: rand 12 7. 自然指数函数: exp 13 8. 以10为底对数函数: log10 13 9. 以2为底对数函数: log2 13 10. 对数函数: log 13 11. 幂运算函数: pow 14 12. 幂运算函数: power 14 13. 开平方函数: sqrt 14 14. 二进制函数: bin 14 15. 十六进制函数: hex 15 16. 反转十六进制函数: unhex 15 17. 进制转换函数: conv 15 18. 绝对值函数: abs 16 19. 正取余函数: pmod 16 20. 正弦函数: sin 16 21. 反正弦函数: asin 16 22. 余弦函数: cos 17 23. 反余弦函数: acos 17 24. positive函数: positive 17 25. negative函数: negative 17 五、日期函数 18 1. UNIX时间戳转日期函数: from_unixtime 18 2. 获取当前UNIX时间戳函数: unix_timestamp 18 3. 日期转UNIX时间戳函数: unix_timestamp 18 4. 指定格式日期转UNIX时间戳函数: unix_timestamp 18 5. 日期时间转日期函数: to_date 19 6. 日期转年函数: year 19 7. 日期转月函数: month 19 8. 日期转天函数: day 19 9. 日期转小时函数: hour 20 10. 日期转分钟函数: minute 20 11. 日期转秒函数: second 20 12. 日期转周函数: weekofyear 20 13. 日期比较函数: datediff 21 14. 日期增加函数: date_add 21 15. 日期减少函数: date_sub 21 六、条件函数 21 1. If函数: if 21 2. 非空查找函数: COALESCE 22 3. 条件判断函数:CASE 22 4. 条件判断函数:CASE 22 七、字符串函数 23 1. 字符串长度函数:length 23 2. 字符串反转函数:reverse 23 3. 字符串连接函数:concat 23 4. 带分隔符字符串连接函数:concat_ws 23 5. 字符串截取函数:substr,substring 24 6. 字符串截取函数:substr,substring 24 7. 字符串转大写函数:upper,ucase 24 8. 字符串转小写函数:lower,lcase 25 9. 去空格函数:trim 25 10. 左边去空格函数:ltrim 25 11. 右边去空格函数:rtrim 25 12. 正则表达式替换函数:regexp_replace 26 13. 正则表达式解析函数:regexp_extract 26 14. URL解析函数:parse_url 26 15. json解析函数:get_json_object 27 16. 空格字符串函数:space 27 17. 重复字符串函数:repeat 27 18. 首字符ascii函数:ascii 28 19. 左补足函数:lpad 28 20. 右补足函数:rpad 28 21. 分割字符串函数: split 28 22. 集合查找函数: find_in_set 29 八、集合统计函数 29 1. 个数统计函数: count 29 2. 总和统计函数: sum 29 3. 平均值统计函数: avg 30 4. 最小值统计函数: min 30 5. 最大值统计函数: max 30 6. 非空集合总体变量函数: var_pop 30 7. 非空集合样本变量函数: var_samp 31 8. 总体标准偏离函数: stddev_pop 31 9. 样本标准偏离函数: stddev_samp 31 10.中位数函数: percentile 31 11. 中位数函数: percentile 31 12. 近似中位数函数: percentile_approx 32 13. 近似中位数函数: percentile_approx 32 14. 直方图: histogram_numeric 32 九、复合类型构建操作 32 1. Map类型构建: map 32 2. Struct类型构建: struct 33 3. array类型构建: array 33 十、复杂类型访问操作 33 1. array类型访问: A[n] 33 2. map类型访问: M[key] 34 3. struct类型访问: S.x 34 十一、复杂类型长度统计函数 34 1. Map类型长度函数: size(Map) 34 2. array类型长度函数: size(Array) 34 3. 类型转换函数 35
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值