hive学习笔记之六：HiveQL基础

fields terminated by ‘,’;

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

1,guangdong,guangzhou

2,guangdong,shenzhen

3,shanxi,xian

4,shanxi,hanzhong

6,jiangshu,nanjing

4. 加载数据到address表：

load data

local inpath ‘/home/hadoop/temp/202010/25/address.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 ‘/home/hadoop/temp/202010/25/student.txt’

into table student;

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

hive> select * from address;

OK

1 guangdong guangzhou

2 guangdong shenzhen

3 shanxi xian

4 shanxi hanzhong

6 jiangshu nanjing

Time taken: 0.043 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 taken: 0.068 seconds, Fetched: 5 row(s)

• 开始体验HiveQL

select和where

最普通的带条件查询：

hive> select * from address where city like ‘%a%’;

OK

1 guangdong guangzhou

3 shanxi xian

4 shanxi hanzhong

6 jiangshu nanjing

Time taken: 0.128 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 910 msec

OK

guangdong 2

jiangshu 1

shanxi 2

Time taken: 17.847 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

jiangshu 1

shanxi 2

Time taken: 18.036 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 250 msec

OK

shanxi 2

Time taken: 20.067 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: 40.315 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: 17.294 seconds, Fetched: 4 row(s)

自然连接(natural join)

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

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

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

Total MapReduce CPU Time Spent: 940 msec

OK

最后

面试是跳槽涨薪最直接有效的方式，马上金九银十来了，各位做好面试造飞机，工作拧螺丝的准备了吗？

掌握了这些知识点，面试时在候选人中又可以夺目不少，暴击9999点。机会都是留给有准备的人，只有充足的准备，才可能让自己可以在候选人中脱颖而出。

n)

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

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

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

Total MapReduce CPU Time Spent: 940 msec

OK

最后

面试是跳槽涨薪最直接有效的方式，马上金九银十来了，各位做好面试造飞机，工作拧螺丝的准备了吗？

掌握了这些知识点，面试时在候选人中又可以夺目不少，暴击9999点。机会都是留给有准备的人，只有充足的准备，才可能让自己可以在候选人中脱颖而出。

[外链图片转存中…(img-FKzq24M0-1714297270377)]

[外链图片转存中…(img-XBCyrTsH-1714297270378)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录