Hive SQL案例_hive 数据库实例

本数据为某人口普查公开数据数据库抽取而来，该数据集类变量为年收入是否超过50k$，属性变量包含年龄、工作类型、教育程度等属性，统计对各因素对收入的影响。（超过50K的收入统一称为高收入）

示例数据(/root/cpllege/person.csv)：
66,Federal-gov,47358,10th,6,Married-civ-spouse,Craft-repair,Husband,White,Male,3471,0,40,United-States,<=50K

数据变量如下：
字段 类型 说明
age double 年龄
workclass string 工作类型
fnlwgt string 可代表的人数
edu string 教育程度
edu_num double 受教育时间
marital_status string 婚姻状况
occupation string 职业
relationship string 关系
race string 种族
sex string 性别
gain string 资本收益
loss string 资本损失
hours double 每周工作时长
native string 原籍
income string 收入

将数据上传到指定位置

1.创建指定文件夹，将单车数据上传至hdfs指定目录/college/下：

解析：

# 创建HDFS下路径/college
hadoop fs -mkdir -p /college
# 将数据上传至HDFS
hadoop fs -put /root/college/person.csv /college/
# 查看HDFS文件
hadoop fs -ls /college/
1
2
3
4
5
6

创建库表，导入数据

1.进入hive客户端，创建数据库hive，并使用；
2.创建person表；

解析：

①进入hive客户端，创建数据库hive，并使用；

打开终端，输入命令开启hive客户端：

hive
1

进入客户端之后，开始创建数据库：

# 创建数据库
create database hive;
# 使用数据库
use hive;
1
2
3
4

②创建bike表；
补充如下命令：

create table person (
age double,
workclass string,
fnlwgt string,
edu string,
edu_num double,
marital_status string,
occupation string,
relationship string,
race string,
sex string,
gain string,
loss string,
hours double,
native string,
income string
) row format delimited 
fields terminated by ',';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.导入本地数据。

load data local inpath '/root/college/person.csv' into table person;
1

4.统计表数据,结果写入本地/root/college000/01/中。

insert overwrite local directory '/root/college000/01'
row format delimited fields terminated by '\t'
Select count(*) from person; 
1
2
3

数据分析

1.计算较高收入人群占整体数据的比例，结果写入本地/root/college011/。
其他说明：结果四舍五入，保留两位小数。
笛卡尔积会针对表1和表2的每条数据做连接…,cross join 不需要后面加连接关键字on

set hive.strict.checks.cartesian.product=false;   # 默认不允许笛卡尔操作，这里允许操作
insert overwrite local directory '/root/college011/'
SELECT round((t2.v / t4.s),2)
FROM (SELECT count(*) AS v FROM person t1 where t1.income = '>50K') t2
JOIN (SELECT count(*) as s FROM person t3) t4;
1
2
3
4
5

2.计算学历为本科的人员在调查中的占比，结果写入本地/root/college012/。
其他说明：学士为Bachelors，结果四舍五入，保留两位小数。

insert overwrite local directory '/root/college012/'
SELECT round((t2.v / t4.s),2)
FROM (SELECT count(*) AS v FROM person t1 where t1.edu='Bachelors') t2
JOIN (SELECT count(*) as s FROM person t3) t4;
1
2
3
4

3.计算青年群体中高收入年龄层排行，结果写入本地/root/college013/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照年龄数量进行降序排列，再按照年龄升序排列；
其他说明：结果数据为排行前10，15-34岁为青年。

insert overwrite local directory '/root/college013/'
row format delimited fields terminated by '\t'
select age ,count(*) as sum from person
where age>=15 and age <=34 and income = '>50K'
group by age
order by sum desc,age asc limit 10;
1
2
3
4
5
6

4.计算男性群体中高收入职业排行，结果写入本地/root/college014/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照职业数量进行降序排列，再按照职业升序排列；
其他说明：数据排行为前5。

insert overwrite local directory '/root/college014/'
row format delimited fields terminated by '\t'
select occupation,count(*) as sum from person
where sex = 'Male' and income = '>50K'
group by occupation
order by sum desc,occupation asc limit 5;
1
2
3
4
5
6

5.对未婚人群高收入职业排行，结果写入本地/root/college015/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照职业数量进行降序排列，再按照职业升序排列；
其他说明：数据排行为前5。

insert overwrite local directory '/root/college015/'
row format delimited fields terminated by '\t'
select occupation,count(*) as sum from person
where marital_status = 'Never-married' and income = '>50K'
group by occupation
order by sum desc,occupation asc limit 5;
1
2
3
4
5
6

6.统计性别对于收入的影响，结果写入本地/root/college016/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照比例进行降序排列。
其他说明：结果为高收入中性别比例，结果四舍五入，保留两位小数。

insert overwrite local directory '/root/college016/'
row format delimited fields terminated by '\t'
SELECT t2.sex ,round((t2.v / t4.s),2)
FROM (SELECT t1.sex as sex , count(*) AS v FROM person t1 where t1.income = '>50K' group by sex order by v desc ) t2
JOIN (SELECT count(*) as s FROM person t3 where t3.income = '>50K') t4;
1
2
3
4
5

7.统计教育程度对于收入的影响，结果写入本地/root/college017/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照数量进行降序排列，再按照教育程度名称升序排列；
其他说明：数据为高收入中，对不同教育程度进行数量累加。

insert overwrite local directory '/root/college017/'
row format delimited fields terminated by '\t'
select edu ,count(*) as sum from person
where income = '>50K'
group by edu
order by sum desc ,edu asc ;
1
2
3
4
5
6

8.计算不同收入的平均工作时间，结果写入本地/root/college018/。
格式：对于结果中的二维数据，要求使用“\t”作为声明文件分隔符；
复合排列：先按照数量进行降序排列，再按照教育程度名称升序排列；
其他说明：根据收入类型对平均工时进行计算，结果四舍五入，保留整数。

insert overwrite local directory '/root/college018/'
row format delimited fields terminated by '\t'
select income ,round(avg(hours)) as sum from person
group by income 
order by sum desc ;
1
2
3
4
5