现有如此三份数据:
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;