【Hive】hive影评案例

数据描述

1. users.dat 用户表
   数据格式为： 2::M::56::16::70072
   对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String
   对应字段中文解释：用户id，性别，年龄，职业，邮政编码

2. movies.dat 电影表
   数据格式为： 2::Jumanji (1995)::Adventure|Children’s|Fantasy
   对应字段为：MovieID BigInt, Title String, Genres String
   对应字段中文解释：电影ID，电影名字，电影类型

3. ratings.dat 评分表
   数据格式为： 1::1193::5::978300760
   对应字段为：UserID BigInt, MovieID BigInt, Rating Double, Timestamped String
   对应字段中文解释：用户ID，电影ID，评分，评分时间戳

数据要求

（1）写shell脚本清洗数据。（hive不支持解析多字节的分隔符，也就是说hive只能解析’:’, 不支持解析’::’，所以用普通方式建表来使用是行不通的，要求对数据做一次简单清洗）

（2）使用Hive能解析的方式进行
要求：外部表
hdfs: /user/data/yingping/movies ratings users

题目

-- 设置本地模式 及 显示表头
hive> set hive.exec.mode.local.auto=true;
hive> set hive.cli.print.header=true;
-- hdfs 数据目录创建
hive> dfs -mkdir -p /user/data/yingping/movies;
hive> dfs -mkdir -p /user/data/yingping/ratings;
hive> dfs -mkdir -p /user/data/yingping/users;
-- hdfs 数据目录 上传数据
hive> dfs -put users.dat /user/data/yingping/users;
hive> dfs -put movies.dat /user/data/yingping/movies;
hive> dfs -put ratings.dat /user/data/yingping/ratings;
-- 创建yingping数据库
hive> create database yingping;
hive> use yingping;

1. 正确建表，导入数据（三张表，三份数据），并验证是否正确

-- 创建表 movies
create external table if not exists movies(
MovieID BigInt, Title String, Genres String) 
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s') stored as textfile 
location '/user/data/yingping/movies';

hive> select * from movies limit 2;
OK
movies.movieid	movies.title	movies.genres
1	Toy Story (1995)	Animation|Children's|Comedy
2	Jumanji (1995)	Adventure|Children's|Fantasy
Time taken: 1.142 seconds, Fetched: 2 row(s)

-- 创建表 ratings
create external table if not exists ratings(UserID BigInt, MovieID BigInt, Rating Double, Timestamped String) 
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s %4$s') stored as textfile 
location '/user/data/yingping/ratings';

hive> select * from ratings limit 2;
OK
ratings.userid	ratings.movieid	ratings.rating	ratings.timestamped
1	1193	5.0	978300760
1	661	3.0	978302109
Time taken: 0.145 seconds, Fetched: 2 row(s)

-- 创建表 users
create external table if not exists users(userid bigint,gender string,age int,occupation string,zipcode string) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' 
with serdeproperties('input.regex'='(.*)::(.*)::(.*)::(.*)::(.*)','output.format.string'='%1$s %2$s %3$s %4$s %5$s') stored as textfile location '/user/data/yingping/users';

hive> select * from users limit 2;
OK
users.userid	users.gender	users.age	users.occupation	users.zipcode
1	F	1	10	48067
2	M	56	16	70072
Time taken: 0.137 seconds, Fetched: 2 row(s)

2. 求被评分次数最多的10部电影，并给出评分次数（电影名，评分次数）

----分析
--也就是要先求出，每一部电影总评分次数 排序
分组：电影id title ->movies表
求：count() ->ratings表
排序：count() desc limit 10

select 
a.Title,count(b.Rating) totalRate
from movies a join ratings b
on a.MovieID=b.MovieID
group by a.MovieID,a.Title
order by totalRate desc limit 10;

--结果
a.title	totalrate
American Beauty (1999)	3428
Star Wars: Episode IV - A New Hope (1977)	2991
Star Wars: Episode V - The Empire Strikes Back (1980)	2990
Star Wars: Episode VI - Return of the Jedi (1983)	2883
Jurassic Park (1993)	2672
Saving Private Ryan (1998)	2653
Terminator 2: Judgment Day (1991)	2649
Matrix, The (1999)	2590
Back to the Future (1985)	2583
Silence of the Lambs, The (1991)	2578

3. 分别求男性，女性当中评分最高的10部电影（性别，电影名，影评分）

方法一：分开求

----分析
--1.求男性
过滤条件：男性 ->users表
每一部电影的平均评分
分组：电影Id 电影名 ->movies表
求：avg(Rating) -> ratings表
排序：avg(Rating) desc limit 10

select
c.Gender Gender,
a.Title Title,
avg(b.Rating) avgrate 
from movies a join ratings b on a.MovieID=b.MovieId
join users c on b.UserID=c.UserID
where c.Gender='M'
group by a.MovieID,a.Title,c.Gender
order by avgrate desc limit 10;

--结果
gender	title	avgrate
M	Small Wonders (1996)	5.0
M	Smashing Time (1967)	5.0
M	Baby, The