第11章综合案例2影评大数据分析

实验目的及要求

（1）现有电影、影评和用户信息3个数据文件，将对其进行大数据分析。

实验系统环境及版本

1. Linux Ubuntu 20.04

2. JDK1.8

3. Hadoop3.1.0

4. MySQL8.0.28

5. Hive3.1.2

实验任务

1. 评分次数最多的10部电影；

2. 性别当中评分最高的10部电影；

3. 一部电影各年龄段的平均影评；

4. 评分最高的10部电影的平均评分；

5. 好片最多年份的最好看电影Top10；

6. 评分最高的10部Comedy类电影；

7. 各种类型电影中评价最高的5部电影。

实验内容及步骤

创建一个数据仓库movie

hive> create database movie;

hive> use movie;

创建t_user表及导入数据

hive> create table t_user(userid bigint,sex string,age int,occupation string,zipcode string) row format delimited fields terminated by ‘\t’;

hive> load data local inpath ‘/opt/datas/user.txt’ into table t_user;

创建t_movie表及导入数据

hive> create table t_movie(movieid bigint,moviename string,movietype string) row format delimited fields terminated by ‘\t’;

hive> load data local inpath ‘/opt/datas/movie.txt’ into table t_movie;

创建t_ratings表及导入数据

hive> create table t_ratings(

> userid bigint,

> movieid bigint,

> rate double,

> times string)

> row format serde ‘org.apache.hadoop.hive.serde2.RegexSerDe’

> stored as textfile;

hive> load data local inpath “/opt/datas/ratings.dat” into table t_ratings;

基本信息查询

查询表t_user的记录总数：

hive> select count( *) from t_user;

查询表t_movie的记录总数：

hive> select count( *) from t_movie;

查询表t_rating的记录总数：

hive> select count( *) from t_ratings;

查看3个表文件的数据大小：

hdfs dfs -du -h /user/hive/warehouse/movie.db/t_movie

hdfs dfs -du -h /user/hive/warehouse/movie.db/t_user

hdfs dfs -du -h /user/hive/warehouse/movie.db/t_ratings

1.评分次数最多的10部电影

统计评分次数最多的10部电影，并给出评分次数（电影名、评分次数）。

按照电影名进行分组统计，求出每部电影的评分次数并按照评分次数降序排序，保存在表answer2中：

hive> create table answer2 as

> select a.moviename as moviename,count(a.moviename) as total from t_movie a join t_ratings b on a.movieid=b.movieid

> group by a.moviename order by total desc limit 10;

查询表answer2：

hive> select * from answer2;

2.性别当中评分最高的10部电影

统计男性、女性当中评分最高的10部电影（性别、电影名、影评分）。

（1）创建表answer3_F，保存女性当中评分最高的10部电影（性别、电影名、影评分），分组条件为评论次数大于或等于50次：

create table answer3_F as

select “F” as sex, c.moviename as name, avg(a.rate) as avgrate, count(c.moviename) as total

from t_ratings a join t_user b on a.userid=b.userid

join t_movie c on a.movieid=c.movieid

where b.sex=“F”

group by c.moviename

having total >= 50

order by avgrate desc

limit 10;

（2）查询表answer3_F：

select sex,name,round(avgrate,2),total from answer3_F;

（3）创建表answer3_M，保存男性当中评分最高的10部电影（性别、电影名、影评分），要求评论次数大于或等于50次：

create table answer3_M as

select “M” as sex, c.moviename as name, avg(a.rate) as avgrate, count(c.moviename) as total

from t_ratings a join t_user b on a.userid=b.userid

join t_movie c on a.movieid=c.movieid

where b.sex=“M”

group by c.moviename

having total >= 50

order by avgrate desc

limit 10;

（4）查询表answer3_M：

select sex,name,round(avgrate,2),total from answer3_M;

2.一部电影各年龄段的平均影评

统计movieid = 2116这部电影各年龄段的平均影评（年龄段、影评分）。

（1）对t_user和t_ratings表进行联合查询，用movieid=2116作为过滤条件，用年龄段作为分组条件，查询结果保存在表answer4中：

create table answer4 as

select a.age as age, avg(b.rate) as avgrate

from t_user a join t_ratings b on a.userid=b.userid

where b.movieid=2116

group by a.age;

（2）查询表answer4：

select age,round(avgrate,2) from answer4;

3.评分最高的10部电影的平均影评分

统计最喜欢看电影（影评次数最多）的那位女性评分最高的10部电影的平均影评分（观影者、电影名、影评分）。

（1）查询最喜欢看电影的那位女性，查询的字段分别为t_user.sex（性别）和count t_ratings.userid（观影次数）：

select a.userid, count(a.userid) as total

from t_ratings a join t_user b on a.userid = b.userid

where b.sex=“F”

group by a.userid

order by total desc

limit 10;

（2）根据上述（1）中查询的女性userid作为Where过滤条件，以看过的电影的影评分rate作为排序条件进行排序，统计出评分最高的10部电影，并将查询结果保存在表answer5_B中：

create table answer5_B as

select a.movieid as movieid, a.rate as rate

from t_ratings a

where a.userid=1150

order by rate desc

limit 10;

（3）查询表answer5_B：

select * from answer5_B;

（4）统计上述（3）中10部电影的平均影评分，需要查询的字段分别为answer5_B.movieid（电影的ID）和t_ratings.rate（影评分），并将查询结果保存在表answer5_C中：

create table answer5_C as

select b.movieid as movieid, c.moviename as moviename, avg(b.rate) as avgrate

from answer5_B a join t_ratings b on a.movieid=b.movieid

join t_movie c on b.movieid=c.movieid

group by b.movieid,c.moviename;

（5）查询表answer5_C：

select movieid,moviename,round(avgrate,2) from answer5_C;

4.好片最多年份的最好看电影Top10

年份的最好看电影Top10。

（1）将t_rating和t_movie表进行联合查询，截取电影名中的上映年份，并将查询结果保存至表answer6_A：

create table answer6_A as select

a.movieid as movieid, a.moviename as moviename,

substr(a.moviename,-5,4) as years, avg(b.rate) as avgrate

from t_movie a join t_ratings b on a.movieid=b.movieid

group by a.movieid, a.moviename;

（2）查询表answer6_A：

select * from answer6_A;

（3）按照年份将answer6_A分组，评分≥4.0作为过滤条件，按照count(years)作为排序条件进行查询：

select years, count(years) as total

from answer6_A a

where avgrate >= 4.0

group by years

order by total desc

limit 10;

（4）按照years=1998作为Where过滤条件，按照评分作为排序条件进行查询，并保存至表answer6_C：

create table answer6_C as

select a.moviename as name, a.avgrate as rate

from answer6_A a

where a.years=1998

order by rate desc

limit 10;

（5）查询表answer6_C：

select name,round(rate,2) from answer6_C;

5.评分最高的10部Comedy类电影

统计1997年上映的电影中评分最高的10部Comedy类电影。

（1）将answer6_A表和t_movie表进行联合查询，保存至表answer7_A：

create table answer7_A as

select b.movieid as id, b.moviename as name, b.years as years, b.avgrate as rate, a.movietype as type

from t_movie a join answer6_A b on a.movieid=b.movieid;

（2）表answer7_A按照电影类型中是否包含Comedy和按照评分≥4.0作为Where过滤条件，按照评分作为排序条件进行查询，将结果保存到表answer7_B中。其中，instr函数返回字符串str中子字符串substr第一次出现的位置，在SQL中第一字符的位置是1，如果str不含substr，则返回0。lcase函数把字段的值转换为小写。

create table answer7_B as

select t.id as id, t.name as name, t.rate as rate

from answer7_A t

where t.years=1997 and instr(lcase(t.type),‘comedy’) >0

order by rate desc

limit 10;

（3）查询表answer7_B：

select id,name,round(rate,2) from answer7_B;

6.各种类型电影中评价最高的5部电影

统计各种类型电影中评价最高的5部电影（类型、电影名、平均影评分）。

（1）将表answer7_A中的type字段进行裂变，将结果保存到表answer8_A中。其中，Lateral View用于和Split、Explode等函数一起使用，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合。Lateral View首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，Lateral View再把结果组合，产生一个支持别名表的虚拟表。

create table answer8_A as

select a.id as id, a.name as name, a.years as years, a.rate as rate, tv.type as type

from answer7_A a

lateral view explode(split(a.type,“\\|”)) tv as type;

（2）查询表answer8_A：

select * from answer8_A limit 10 ;

（3）按照type分组，添加一列记录每组的顺序，将结果保存到表answer8_B中：

create table answer8_B as

select id,name,years,rate,type,row_number() over(distribute by type sort by rate desc ) as num

from answer8_A;

（4）查询表answer8_B：

select * from answer8_B limit 10;

（5）从表answer8_B中取出num列序号≤5：

select a.id, a.name, a.years, round(a.rate,2), a.type, a.num from answer8_B a where a.num <= 5 limit 10;