大数据 第八章 hive函数和hql详解

函数查询语法

1.各种join
select * from a.,b. from a left join b on a.userId=b.userId;
left out join 左外连接：把左边的全部返回，右边的如果没有匹配上则为null
inner join 只匹配左右表都有的数据才显示
left semi join ：和inner join类似，只不过只返回左边表的数据。
2.分组聚合查询
group by
需要将数据先分组再统计。
查询每条url的访问总数：
select url,cont(*) from a group by url;
查询每个url的访问者中ip地址最大的
select url,max(ip) from a group by url;

查询8月4号之后，每天某个url的总访问次数和访问者中ip地址最大的
select count(1),max(ip) from a where url=‘’ and day>‘20230804’ group by day ;
如果需要在分组之后再进行条件过滤，需要使用baving

数据类型

1.数字类型，日期时间类型，字符串类型，混杂类型
2.复合类型：array
使用复合类型，需要在建表的时候定义复合类型数据的分隔方式。collection items terminated by ‘:’
查询的时候可以操作复合类型了，比如select field1,actors[0] from a ;
数组包含哪些值
select field1,actors from a where array_contains(actors,‘吴刚’);

3.复合类型：map<string,string>
在建表语句时定义map中key和value的分隔方式：create table t_family(id int ,name string ,family_members map<string,string>,age int ) row format delinited fields terminated by ‘,’ collection items terminated by ‘#’
map keys terminated by ‘:’;
查询时候，可以family_members[‘father’],map_keys(family_members) ,map_values(family_members),size(family_members)等方法
4.struct类型
create table t_user(id int,name string,info struct<age:int,sex:string,addr:string) collection items terminated by ‘:’

hive函数

1.时间戳相关的函数
获取当前时间的毫秒数时间戳
select unix_timestamp();
unix时间戳转为字符串
select from_unixtime(unix_timestamp(),‘yyyy/MM/dd HH:mm:ss’);
字符串转为时间戳
select unix_timestamp(‘2020/09/08 12:23:54’,‘yyyy/MM/dd HH:mm:ss’);
字符串转为date
select to_date(‘2020/2/3 12:45:24’);
2.表生成函数，行转列
explode()
select explode(subjects) from t_stu_subject;
subjects是数组，这样就可以把这个数组的所有值都打散，这样就可以去重了。
lateral view()
可以实现不仅将subjects字段打散，打散之后还可以和id，name等字段横向连接
select id,name,tmp.sub from t_stu_subject lateral view explode(subjects) tmp as sub;
3.条件函数
case when
示例：
select id,name,
case
when age<28 then ‘youngth’
when age>27 and age<40 then ‘zhongnian’
else ‘old’
end
from t_user;
if
select moive_name,if(array_contains(actors,‘吴刚’),‘好电影’,‘烂电影’),'rom t_movie;
4.窗口分析函数 row_number_over
需求：如果想要查看男女中年纪最大的前两条数据，怎么做？
需要先按照性别分组，然后每个组中按照年龄排序，然后选出每个组中的前两条。
select *
from
(select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rn
from t_table) temp
where rn<3;
– rn是分组排序后的序号，rn<3即可以取到前两条数据。
5.窗口函数sum() over()
需要开发hql脚本，来统计出如下累计报表
| 用户 | 月份 | 当月累计金额 | 总共累计金额
|A|2020-01|40|40
|A|2020-02|20|60
|A|2020-03|20|80
|B|2020-01|30|30
|B|2020-02|20|50
|B|2020-03|40|90

select uid,month,amount,
sum(amount) over(partition by uid order by month rows between unbounded preceding and current row) as accumulate
from t_table;
6.自定义函数
需求：原始数据为：
{“movie”:“113”,“rate”:“4”,“uid”:3}
想要转为113，4，3 把key都去掉。
思路：需要定义一个自定义的函数来实现。
用java中的方法实现。
然后将jar包放到hive的classpath中，调用自定义函数即可
java中继承UDF类，重载evaluate()方法。

public class MyJsonParser extends UDF {

	// 重载父类中的一个方法evaluate()
	public String evaluate(String json, int index) {

		// {"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
		String[] fields = json.split("\"");
		
		String movie = fields[3];  //1
		String rate = fields[7];   //2
		String ts = fields[11];   //3
		String uid = fields[15];   //4
		
		
		
		return fields[4 * index - 1];
	}

}

将程序打包，放到hive的类路径中，之后
select myjson(json,1) as movie,myjson(json,2) as ts from t_table;
7. json解析函数
json_tuple函数可以将json中指定的key的value值获取到
实例：
select json_tuple(json,‘movie’,‘rate’,‘timestamp’) as (movie,rate,ts) from t_table;

实战练习

1.有一个文件里面写满了单词，要统计每个单词出现的次数
思路：1.首先将文件每行作为一个字段sentence写到table中；
2.用split将setence按照空格分隔成array；
3.用explode()函数，行转列；
4.用group by函数分组即可。