大数据从入门到实战 - Hive基本查询操作（一）

叮嘟！这里是小啊呜的学习课程资料整理。好记性不如烂笔头，今天也是努力进步的一天。一起加油进阶吧！

一、关于此次实践

1、实战简介

本实训主要介绍了hive的一些简单基本操作，比如where、group by、join等。

2、全部任务

二、实践详解

1、第1关：where操作

任务描述

本关任务：使用where和like求出编程要求中所给需求。

相关知识

where
将不满足条件的行过滤，在SQL语句中执行顺序优先于group by。

having
对where的一个补充，过滤成组后的数据，执行顺序后于group by。

like
like 操作符用于在WHERE子句中搜索列中的指定模式。%代表任意多个字符。

假设存在student表：

name	age
bob	22
cindy	27
herry	26
可以使用where过滤查询出成绩大于25岁的学生名字。

select name from student where age>25;

输出：

cindy
herry

本地部分文件内容：

本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年`
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/aaa.txt' into table table1;
----------禁止修改----------

----------Begin----------
select workingExp,company_name from table1 where responsibility like '%hive%' and salary > '8000';
----------End----------

2、第2关：group by操作

任务描述

本关任务：实现不同工作年限的平均工资需求。

相关知识

group by
group by表示按照某些字段的值进行分组，有相同的值放到一起，需要注意的是select后面的非聚合列必须出现在group by中；

假设存在st表：

city	salary	job
长沙	7000	大数据开发
北京	10000	大数据开发
广州	11000	大数据开发
长沙	7000	大数据开发

可以使用group by求出不同省份的平均工资：

select city,avg(salary)from st group by city;

输出：

长沙 7000
北京 10000
广州 11000

本地部分文件内容：

本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t1.txt' into table table1;
----------禁止修改----------

----------Begin----------
select avg(salary) as avgsalary,workingExp from table1 group by workingExp having avgsalary > 10000;
----------End----------

评测

3、第3关：join操作

任务描述

本关任务：通过关联求出每个城市名的平均工资。

相关知识

Hive只支持等值连接，即ON子句中只能使用等号连接

假设存在表a:

id	name
1	bob
2	lily
3	herry
表b:

cid	score
1	80
2	90
5	60

• 内连接(JOIN)
  内连接指的是把符合两边连接条件的数据查询出来：

select a.name,b.score from a join b on a.id=b.cid;

输出结果：

bob 80
lily 90

• 左外连接（LEFT OUTER JOIN）
  左表全部查询出来，右表不符合连接条件的显示为空：

select a.name,b.score from a left outer join b on a.id=b.cid;

输出结果：

bob   80
lily   90
herry  null

• 右外连接（RIGHT OUTER JOIN）
  右表全部查询出来，左表不符合连接条件的显示为空：

select a.name,b.score from a right outer join b on a.id=b.cid;

输出结果：

bob   80
lily 90
null  60

• 全外连接（FULL OUTER JOIN）
  左右表符合连接条件和不符合连接条件的都查出来，不符合的显示空：

select a.name,b.score from a full outer join b on  a.id=b.cid;

输出结果：

bob     80
lily    90
herry   null
null    60

• 左半开连接（LEFT SEMI JOIN)
  查询出满足连接条件的左边表记录，需要注意的是select和where语句中都不能使用右表的字段。

Hive不支持右半开连接：

select a.name from a LEFT SEMI JOIN  b on a.id=b.cid;

输出结果：

bob
lily

table1本地部分文件内容：

本科,北京联通支付有限公司,大数据开发工程师,10000,530,熟练使用hive等,1-3年
专科,北京联科数创科技有限公司,大数据分析师,8000,530,熟练使用MySQL等数据库,1-3年
本科,湖南智湘赢播网络技术有限公司,大数据开发工程师,16000,749,熟练使用spark等,3-5年

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t2.txt' into table table1;

create table if not exists table2(
city_code int comment '城市编码',
city_name string comment '城市名'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table2;
load data local inpath '/root/t22.txt' into table table2;
----------禁止修改----------

----------Begin----------
select avg(table1.salary),table2.city_name from table1 right outer join table2 on table1.city_code=table2.city_code group by table2.city_name ;
----------End----------

评测

Ending！
更多课程知识学习记录随后再来吧！

就酱，嘎啦！

注：
人生在勤，不索何获。