Hive SQL练习一

准备数据

1. 本次实战要准备两个表：学生表和住址表,字段都很简单,如下图所示,学生表有个住址ID字段,是住址表里的记录的唯一ID：
   
2. 先创建住址表：

create table address(addressid int,province string,city string)
row format delimited
fields terminated by ',';

3. 创建 address.txt文件，内容如下：

1,guangdong,guangzhou
2,guangdong,shenzhen
3,shanxi,xian
4,shanxi,hanzhong
5,jiangsu,nanjing

4. 加载数据到 address表：

load data
local inpath '/opt/module/hive/temp/202011/20/addreess.txt
into table address

5. 创建学生表,其 addressid字段关联了 address表的addressid字段：

create table student (name string,age int,addressid int)
row format delimited
fields terminated by ',';

6. 创建 student.txt文件,内容如下：

tom,11,1
jerry,12,2
mike,13,3
john,14,4
mary,15,5

7. 加载数据到student表：

load data
local inpath '/opt/module/hive/temp/202011/20/student.txt'
into table student;

8. 至此,本次操作所需数据都已准备完毕,如下所示：

hive> select * from address;
OK
1	guangdong	guangzhou
2	guangdong	shenzhen
3	shanxi	xian
4	shanxi	hanzhong
5	jiangsu	nanjing
Time taken: 0.097 seconds,Fetched: 5 row(s)
hive> select * from student;
OK
tom 11 1
jerry 12 2
mike 13 3
john 14 4
mary 15 5
Time token: 0.074 seconds, Fetched: 5 row(s)

开始加条件查询数据

select和where
最普通的带条件查询：

hive> select * from address where city like '%a%';
OK
1 guangdong	guangzhou
3 shanxi	xian
4 shanxi	hanzhong
6 jiangsu	nanjing
Time taken: 0.102 seconds,Fetched: 4 row(s)

group by

1. 按 province 字段分组：

select province,count(*) from address group by province;

该查询会触发MR计算,结果如下：

...
Total	MapReduce CPU Time Spent: 1 seconds 870 msec
OK
guangdong 2
jiangsu	1
shanxi 2
Time taken: 15.201 seconds, Fetched: 3 row(s)

2. 试一试嵌套查询,内部是查出 city 字段 带有 a 字母的记录,然后将这些记录按照 province 字段 分组：

select t.province,count(*) from(
	select * from address where city like '%a%'
) t
group by t.province;

结果如下：

Total MapReduce CPU Time Spent: 1 seconds 760 msec
OK
guangdong	1
jiangsu	1
shanxi	2
Time taken: 14.563 seconds,Fetched: 3 row(s)

having

• 前面的嵌套查询,结果有两个省：guangdong 和 shanxi,如果再加个条件: 只显示城市数量大于 1 的省,首先想到的是再加一层嵌套：

select t1.* from (
	select t.province, count(*) as cnt from (
		select * from address where city like '%a%'
	) t
group by t.province) t1
where t1.cnt > 1;

结果如下,可见只有 shanxi被显示了：

Total MapReduce CPU Time Spent: 2 seconds 234 msec
OK
shanxi	2
Time taken:	14.654	seconds, Fetched: 1 row(s)

• 对于上述SQL, 可以用 having 语法进行分组筛选，得到同样数据：

select t.province,count(*) as cnt from(
	select * from address where city like '%a%'
	) t
group by t.province having cnt>1;

order by

• 对分组结果做排序：

select t.province,count(*) as cnt from (
	select * from address where city like '%a%'
) t
group by t.province order by cnt;

会触发MR，结果如下：

Total MapReduce CPU Time Spent: 3 seconds 50 msec
OK
jiangshu	1
guangdong	1
shanxi	2
Time taken:	38.456	seconds, Fetched: 3 row(s)

• 对于 order by 的实现,是在最后通过一个 reducer进行 全部排序,该过程可能耗时较长,针对这种情况,hive 提供了 sort by , 功能与 order by 一样,但是会在每个 reducer 中进行排序, 这样最终做排序的时候效率就会提升;
• 要注意的是： sort by 解决的问题是最终结果排序的效率，因此数据量不大时,排序不是瓶颈,此时使用 sort by 也不会加快整体速度;

内连接(inner join)

• 内连接用 join 简写,与连接标准匹配的数据在两张表中都存在,才会保留：

select
  s.name, s.age,
  a.province, a.city
from
	student s
	inner join
	address a
on 
  	s.addressid=a.addressid;

结果如下：

Total MapReduce CPU Time Spent: 1 seconds 20 msec
OK
tom 11	guangdong	guangzhou
jerry	12	guangdong	shenzhen
mike	13	shanxi	xian
john	14	shanxi	hanzhong
Time taken:14.546 seconds, Fetched:	4 row(s)

自然连接(natural join)

• 自然连接是在两张表中寻找数据类型和列明都相同的字段,并自动连接起来:

select name, age, province, city from student natural join address;

结果如下,可见不会根据 student 表的 addressid 字段值去 address 查找记录,而是将 address 的记录全部连接一次;

Total MapReduce CPU Time Spent: 940 msec
OK
tom	11	guangdong	guangzhou
jerry	12	guangdong	guangzhou
mike	13	guangdong	guangzhou
john	14	guangdong	guangzhou
mary	15	guangdong	guangzhou
tom	11	guangdong	shenzhen
jerry	12	guangdong	shenzhen
mike	13	guangdong	shenzhen
john	14	guangdong	shenzhen
mary	15	guangdong	shenzhen
tom	11	shanxi	xian
jerry	12	shanxi	xian
mike	13	shanxi	xian
john	14	shanxi	xian
mary	15	shanxi	xian
tom	11	shanxi	hanzhong
jerry	12	shanxi	hanzhong
mike	13	shanxi	hanzhong
john	14	shanxi	hanzhong
mary	15	shanxi	hanzhong
tom	11	jiangshu	nanjing
jerry	12	jiangshu	nanjing
mike	13	jiangshu	nanjing
john	14	jiangshu	nanjing
mary	15	jiangshu	nanjing
Time taken: 18.525 seconds, Fetched: 25 row(s)

左外连接(left outer join)

• 以连接中的左表为主：

select
	s.name,	s.age,	s.addressid,
	a.province,	a.city
from
	student s
	left outer join
	address a
on
	s.addressid=a.addressid;

结果如下,可见 name = mary的记录, address等于5的记录,因此province 和city 字段都展示了 NULL,而在前面使用 inner join 时,结果中没有这条记录：当右表没有符合条件的数据时，以NULL值进行展示。

Total MapReduce CPU Time Spent: 789 mesc
OK
tom	11	1	guangdong	guangzhou
jerry	12	2	guangdong	shenzhen
mike	13	3	shanxi	xian
john	14	4	shanxi	hanzhong
mary	15	5	NULL	NULL
Time taken: 18.442 seconds, Fetched: 5 row(s)

右外连接(right outer join)
和 做连接类似, 只不过是 以 右表为主,左边中没有的以NULL展示,右表中符合条件的数据会全部展示出来。

全外连接(full outer join)
查询结果等于左外连接和右边连接之和,语法是 full outer join：

select 
  s.name, s.age, s.addressid, 
  a.province, a.city 
from 
  student s 
  full outer join 
  address a 
on 
  s.addressid=a.addressid;

结果如下：

Total MapReduce CPU Time Spent: 2 seconds 760 msec
OK
tom	11	1	guangdong	guangzhou
jerry	12	2	guangdong	shenzhen
mike	13	3	shanxi	xian
john	14	4	shanxi	hanzhong
mary	15	5	NULL	NULL
NULL	NULL	NULL	jiangshu	nanjing
Time taken: 25.489 seconds, Fetched: 6 row(s)