hive函数使用
1、类型转换函数
eg:select id,cast(birthday as date) as bir,cast(salary as float) from t_fun;
2、数学运算函数
round(5,4) -> 5 四舍五入
round(5.1314,3) -> 5.135 保留三位小数及四舍五入
ceil(5.4) -> 6 向上取整
floor(5.4) -> 5 向下取整
abs(-5.4) -> 5.4 绝对值
greatest(3,5,6) -> 6 三个字段里的最大值(至少两个参数)
least(3,5,6) -> 3 多个字段里的最小值
max() min() 聚合函数(常+group by),一组里的最大、最小值
3、字符串函数
substr(string,int start) 截取子串
substr(string,int start,int len)
concat(string A,string B,……) 拼接字符串
concat_ws(string SEP,string A,string B,……) 加分隔符的拼接
length(string A) 字符串长度
split(string str,string pat) 切割
upper(string str) 转大写
4、时间函数
from_unixtime(bigint unixtime[,string format]) unix时间戳转字符串
unix_timestamp(string date,string pattern) 字符串转unix时间戳
to_date("2020-0929 18:46:20") 字符串转日期date
5、表生成函数
行 转 列函数:explode() 可配合distinct去重
表生成函数:lateral view 相当于两个表join
某文本文件xx.txt
内容如下:
hello jackson hello rich
hello jack hello jackson
jackson loves rich rich loves jackson
jack loves jackson love is what
what is love
使用hive做一个简单的wordcount
建表映射:create table t_wc(sentence string);
导入数据:load data inpath 'root/hivetest/xx.txt' into table t_wc;
HQL:
SELECT
word,count(1) as cnts
FROM
(
SELECT explode(split(sentence,' ')) AS word
FROM t_wc
) tmp
GROUP BY word
order by cnts desc;
6、集合函数
array_contains(Array<T>,value) 返回boolean值
sort_array(Array<T>) 返回排序后的数组
size(Array<T>) 返回一个int值
size(Map<K,V>) 返回一个int值
map_keys(Map<K,V>) 返回一个数组
map_vlaues(Map<K,V>) 返回一个数组
7、条件控制函数
a、case when
eg:CASE [表达式]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
……
WHEN conditionN THEN resultN
ELSE result
END
b、IF
8、json解析函数:表生成函数
json_tuple函数
eg:json_tuple(json,'movie','rate') 给一个json,输入要提取的key即可
9、row_number() over()函数:分组topN——解析函数
有如下数据 t_user 表:常查询出每种性别中年龄最大的两条数据
1,18,a,male
2,19,b,male
3,22,c,female
4,16,d,female
5,30,e,male
6,26,f,female
分组后标记序号再查询:
SELECT id,age,name,sex,
row_number() over(partition by sex order by age desc) as rn
FROM t_user;
即有:
SELECT *
FROM
(select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rn
FROM t_rn) tmp
where rn<3;
即可查询出每种性别 年龄的前top2
10、窗口分析函数——复杂报表
sum(amount) over(partition by uid by month
rows between unbounded preceding and current row);
回溯前面所有行直到当前行
sum() over() ——可实现再窗口中进行逐行累加。