现有如此三份数据:
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,评分,评分时间戳
set hive.cli.print.current.db=true; //显示当前库
set hive.exec.mode.local.auto=true; //设置hive执行的本机模式
set hive.mapred.mode=nonstrict;
影评项目数据:
链接:https://pan.baidu.com/s/1Aq28cvfaSgdPe_TmsFqPaQ 密码:nlqo
题目要求:
数据要求:
(1)写shell脚本清洗数据。(hive不支持解析多字节的分隔符,也就是说hive只能解析’:’, 不支持解析’::’,所以用普通方式建表来使用是行不通的,要求对数据做一次简单清洗)
#!/bin/bash
echo "Wait for a moment"
cd /home/movetest/ml-1m
for i in $'*.dat'
do
echo $i
sed -i "s/::/:/g" $i
done
echo "have finished!"
或者:
#!/bin/bash
echo "Wait for a moment"
cd /home/movetest/ml-1m
sed -i "s/::/:/g" `grep "qwe" -rl ./`
echo "have finished!"
(2)使用Hive能解析的方式进行
注:建表时处理
Hive要求:
1、正确建表,导入数据(三张表,三份数据),并验证是否正确
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/movetest/ml-1m/users.dat' INTO TABLE users;
select * from users limit 10;
create table 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;
load data local inpath '/home/movetest/ml-1m/movies.dat' INTO TABLE movies;
select * from movies limit 10;
create table 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;
load data local inpath '/home/movetest/ml-1m/ratings.dat' INTO TABLE ratings;
select * from ratings limit 10;
2、求被评分次数最多的10部电影,并给出评分次数(电影名,评分次数)
分析:
表: movies ratings
要求的字段:title count(userid)
select a.title, count(b.userid) counts
from movies a join ratings b
on a.movieid = b.movieid
group by a.title,b.movieid
order by counts desc
limit 10
;
American Beauty (1999) 3428
Star Wars 2991
Star Wars 2990
Star Wars 2883
Jurassic Park (1993) 2672
Saving Private Ryan (1998) 2653
Terminator 2 2649
Matrix, The (1999) 2590
Back to the Future (1985) 2583
Silence of the Lambs, The (1991) 2578
Time taken: 125.833 seconds, Fetched: 10 row(s)
3、分别求男性,女性当中评分最高的10部电影(性别,电影名,影评分)
分析:
表: users movies ratings
要求的字段:gender title avg(rating)
select a.gender,a.title,avg(c.rating) avgs,count(c.rating) counts
from ratings c
join users a on c.userid = a.userid
join movies b on c.movieid = b.movieid
where a.gender = 'F'
group by b.movieid,b.title
order by avgs desc
limit 10
;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key ‘gender’
这里出错:参考,
https://blog.csdn.net/zhoujj303030/article/details/38424469
select collect_set(a.gender),collect_set(b.title),avg(c.rating) avgs,count(c.rating) counts
from ratings c
join users a on c.userid = a.userid
join movies b on c.movieid = b.movieid
where a.gender = 'F'
group by b.movieid,b.title
having counts >= 60
order by avgs desc
limit 10
;
["F"] ["Close Shave, A (1995)"] 4.644444444444445 180
["F"] ["Wrong Trousers, The (1993)"] 4.588235294117647 238
["F"] ["Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)"] 4.572649572649572 117
["F"] ["Wallace & Gromit"] 4.563106796116505 103
["F"] ["Schindler's List (1993)"] 4.56260162601626 615
["F"] ["Shawshank Redemption, The (1994)"] 4.539074960127592 627
["F"] ["Grand Day Out, A (1992)"] 4.537878787878788 132
["F"] ["To Kill a Mockingbird (1962)"] 4.536666666666667 300
["F"] ["Creature Comforts (1990)"] 4.513888888888889 72
["F"] ["Usual Suspects, The (1995)"] 4.513317191283293 413
结果是集合,改进:
select collect_set(a.gender)[0],collect_set(b.title)[0],avg(c.rating) avgs,count(c.rating) counts
from ratings c
join users a on