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，评分，评分时间戳

数据：

链接：https://pan.baidu.com/s/1VL7Khp9TbEQ2kqDLNeInHA 密码：kfp5

数据要求：

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

Hive要求：正确建表，导入数据（三张表，三份数据），并验证是否正确

create table 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;
load data local inpath '/home/hadoop/moviedata/users.dat' INTO TABLE users;
select * from users limit 5;

create table movies(movieid BigInt, name String, type 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;
load data local inpath '/home/hadoop/moviedata/movies.dat' INTO TABLE movies;
select * from movies limit 5;

create table ratings(userid BigInt, movieid BigInt, rate Double, ts 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;
load data local inpath '/home/hadoop/moviedata/ratings.dat' INTO TABLE ratings;
select * from ratings limit 5;

问题6：求1997年上映的电影中，评分最高的10部Comedy类电影

核心要点：过滤条件  year = 1997      type = Comedy

                 按照评分进行降序排列 ， 取 Top10      order by avgrate desc limit 10;

通过上篇文章《Hive影评案例一》我们已经有了movie_rate_year表，里面有三个字段：movieid, avgrate,  year

根据题目所求创建一个表：movie_rate_year_type  包含：movieid, avgrate, year, type这四个字段。

create table movie_rate_year_type as 
select a.movieid as movieid, a.avgrate as avgrate, a.year as year, b.type as type 
from movie_rate_year a join movies b on a.movieid = b.movieid;

最后判断1997年上映类型为Comedy排名前10的电影

由于数据中电影的类型中字母有大小写之分，在进行判断的时候要考虑，判断一个字符串中是否包含一个子串这里使用instr( )；

select a.movieid as movieid, b.name as name, a.avgrate as avgrate 
from movie_rate_year_type a join movies b on a.movieid = b.movieid 
where year = 1997 and instr(lcase(a.type), "comedy") > 0
order by avgrate desc limit 10;

问题7： 该影评库中各种类型电影中评价最高的5部电影（类型，电影名，平均影评分）

核心要点：

            分组条件： group by  movietype

            取值： 每组数据按照平均影评分降序排列；

                        每组取前5条记录。

 注意：这里使用order进行排序的时候 order by avgrate desc limit 5；这样是不正确的；

上面这种情况是全局排序取前五，没有达到我们的要求，所以使用 order by movietype, avgrate desc limit 5；

通过分析数据我们可以看出，如果我们能将Comedy|Drama这个电影类型字段进行拆分，这会有利于我们的统计。

2324    Life Is Beautiful (La Vita � bella) (1997)      4.999861111111111 Comedy|Drama
2324    Life Is Beautiful (La Vita � bella) (1997)      4.999861111111111 Comedy
2324    Life Is Beautiful (La Vita � bella) (1997)      4.999861111111111 Drama

这个裂变的过程是这个题目的难点之一，对于这个问题使用的是explode( )函数，这个函数要传入一个Array或者Map类型的数据，那么我们就要考虑怎么将Comedy|Drama这种类型的字符串转换成Array或者Map了，Hive中有一个split( )函数，我们可以使用split( )函数指定分隔符将字符串转换为Array类型的数据，然后要考虑怎么数据进行上面的那种变形。Hive中有lateral view 这个操作，这样我们就能将原来的一条记录转换为多条记录了。

create table movie_rate_year_type_exp as 
select a.movieid as movieid, a.avgrate as avgrate, a.year as year, tv.movietype   
from movie_rate_year_type a 
lateral view explode(split(a.type, "\\|")) tv as movietype;

另一个难点是我们怎么进行分组取TopN，通过Hive提供的窗口函数我们可以完成这个操作；

这里使用的是row_number( )来为每一个组生成一个标记值index，通过over( )指明分桶和排序字段，这样我们就能够对每一组中排好序的记录进行递增编号，我们可以直接通过这个标号index来限制获取每一组的TopN.

select * from(
select movieid, avgrate, year, movietype, 
row_number() over (distribute by movietype sort by avgrate desc) as index 
from movie_rate_year_type_exp) a where a.index <= 5;

问题8： 各年评分最高的电影类型（年份，类型，影评分）

核心要点：

           分组：年

           排序：各类型电影的平均分

           各类型电影的平均分：按电影类型分组，求每个组中电影评分的平均分。

创建一个表：movietype_year_rate表中的字段：year  movietype  avgrate

我们在上一个题目中创建了一个movie_rate_year_type_exp表，我们按照这个表中的year和movietype进行分组求平均影评分

create table movietype_year_rate as 
select year, movietype, avg(avgrate) as avgrate 
from movie_rate_year_type_exp 
group by year, movietype;

最后我们给数据进行分组排序添加标号：

select * from (
select year, movietype, avgrate, 
row_number() over (distribute by year sort by avgrate desc) as index 
from movietype_year_rate) where index <= 1;

问题9： 每个地区最高评分的电影名，把结果存入HDFS（地区，电影名，影评分）

核心要点：
            分组条件：地区 users.zipcode
            电影的平均评分：avg(ratings.rate)
            排序：每一组中的记录参与降序排序
            取值：每个地区的评分最高的电影。 index = 1；

第一步：构建一个zip_id_rate表，用来存储每个地区每部电影的平均影评分及相关信息。

通过users.zipcode和ratings.movieid进行分组，求avg(ratings.rate).

create table zip_id_rate as 
select b.zipcode as zipcode, a.movieid  as movieid, avg(a.rate) as avgrate 
from ratings a join users b on a.userid = b.userid 
group by b.zipcode, a.movieid;

第二步：通过上面创建的表，我们对上面的数据进行分组排序，并添加编号。

create table zip_id_rate_index as 
select zipcode, movieid, avgrate, 
row_number() over (distribute by zipcode sort by avgrate desc) as index 
from zip_id_rate;

到这里我们就已经完成了数据的创建，剩下的就只有查询Top1并写入到HDFS了。

insert overwrite direcotry "/movie/zicode_max_rate/" 
select * from zip_id_rate_index where index <= 1;