Hive实战之Youtube数据集

  • 时间:2017-07-20
  • 分类:数据库/系统
  • 3073 人浏览
[导读]本次实战的数据来自于”YouTube视频统计与社交网络”的数据集,是西蒙弗雷泽大学计算机学院在2008年所爬取的数据

1 数据来源

本次实战的数据来自于”YouTube视频统计与社交网络”的数据集,是西蒙弗雷泽大学计算机学院在2008年所爬取的数据
数据集地址:http://netsg.cs.sfu.ca/youtubedata/

1. 1 Youtube视频表格式如下:视频ID  一个11位字符串,是唯一的  上传  一个字符串的视频上传者的用户名  年龄  视频上传日期和2007年2月15日之间的整数天(YouTube的设立)  类别  由上传者选择的视频类别的字符串  长度  视频长度的整数v  观看数  一整数的视图  率  一个浮点数的视频速率  评分  整数的评分  评论数  一整数的评论  相关视频ID  最多20个字符串的相关视频ID

数据之间采用” ”作为分隔符

具体数据如下:

ifnlnji-Y4s  Hooran  1162  Travel & Events  239  189  4.8  10  3  tpAL3I0urI4 … ifnlnji-Y4s

数据量大小为1G,条数为500万+

1.2 用户表类型  string  int  int  解释  上传者  上传视频数  朋友数 2 实战演练准备 2.1 环境搭建

使用环境为
hive-1.1.0-cdh5.4.5
hadoop-2.6.0-cdh5.4.5

演示形式为使用hive shell

2.2 数据清洗

我们一起来看看数据

ifnlnji-Y4s  Hooran  1162  Travel & Events  239  189  4.8  10  3  tpAL3I0urI4 … ifnlnji-Y4s

主要的问题在于category和relatedIDs处理,由于Hive是支持array格式的,所以我们想到的是使用array来存储category和relatedIDs,但是我们发现category的分割符是”&”而realatedIDs的分隔符是” ”,我们在创建表格的时候能够指定array的分隔符,但是只能指定一个,所以再将数据导入到Hive表格之前我们需要对数据进行一定转换和清洗

并且数据中肯定会存在一些不完整数据和一些奇怪的格式,所以数据的清洗是必要的,我在这里所使用的数据清洗方式是使用Spark进行清洗,也可以使用自定义UDF函数来进行清洗

数据清洗注意点
1)我们可以看到每行数据以” ”作为分隔符,每行有十列数据,最后一列关联ID可以为空,那么我们对数据进行split之后数组的大小要大于8
2)数据中存在"uNiKXDA8eyQ KRQE 1035 News & Politics 107"这样格式的数据,所以在处理category时需要注意 News & Politics中间的&

处理后的数据如下:

PkGUU_ggO3k  theresident  704  Entertainment  262  11235  3.85  247  280  PkGUU_ggO3k&EYC5bWF0ss8&…shU2hfHKmU0&p0lq5-8IDqY  RX24KLBhwMI  lemonette  697  People&Blogs  512  24149  4.22  315  474  t60tW0WevkE&WZgoejVDZlo&…s8xf4QX1UvA&2cKd9ERh5-8

下面的实战都是基于数据清洗后的数据进行的

2.3 创建表格和数据导入 2.3.1 youtube表格的创建和导入

1)youtube1的创建,文件格式为textfile
create table youtube1(videoId string, uploader string, age int, category array, length int, views int, rate float, ratings int, comments int,relatedId array)
row format delimited
fields terminated by “ ”
collection items terminated by “&”
stored as textfile;

2)youtube2的创建,文件格式为orc
create table youtube2(videoId string, uploader string, age int, category array, length int, views int, rate float, ratings int, comments int,relatedId array)
row format delimited
fields terminated by “ ”
collection items terminated by “&”
stored as orc;

3)youtube3的创建,文件格式为orc,进行桶分区
create table youtube3(videoId string, uploader string, age int, category array, length int, views int, rate float, ratings int, comments int,relatedId array)
clustered by (uploader) into 8 buckets
row format delimited
fields terminated by “ ”
collection items terminated by “&”
stored as orc;

数据导入:
1)load data inpath “path” into table youtube1;
2)由于无法将textfile格式的数据导入到orc格式的表格,所以数据需要从youtube1导入到youtube2和youtube3:
insert into table youtube2 select * from youtube1;
insert into table youtube3 select * from youtube1;

2.3.2 user表格的创建和导入

1)user_tmp的创建,文件格式textfile,24buckets
create table user_tmp(uploader string,videos int,friends int)  
clustered by (uploader) into 24 buckets  
row format delimited  
fields terminated by “ ”  
stored as textfile;

2)user的创建,文件格式orc,24buckets
create table user(uploader string,videos int,friends int)  
clustered by (uploader) into 24 buckets  
row format delimited  
fields terminated by “ ”  
stored as orc;

user表的数据导入也是同理
数据导入:
1)load data inpath “path” into table user_tmp;
2)由于无法将textfile格式的数据导入到orc格式的表格,所以数据需要从user_tmp导入到user:
insert into table user select * from user_tmp;

3 实战需求

1)统计出观看数最多的10个视频
2)统计出视频类别热度的前10个类型
3)统计出视频观看数最高的50个视频的所属类别
4)统计出观看数最多的前N个视频所关联的视频的所属类别排行
5)筛选出每个类别中热度最高的前10个视频
6)筛选出每个类别中评分最高的前10个视频
7)找出用户中上传视频最多的10个用户的所有视频
8)筛选出每个类别中观看数Top10

4 实战演练 4.1 统计出观看数最多的10个视频

select * from youtube3 order by views desc limit 10;

结果如下:

4.2 统计出视频类别热度的前10个类型

select tagId, count(a.videoid) as sum from (select videoid,tagId from youtube3 lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc limit 10;

结果:

4.3 统计出视频观看数最高的50个视频的所属类别

select tagId, count(a.videoid) as sum from (select videoid,tagId from (select * from youtube3 order by views desc limit 20) e lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc;

结果:

4.4 统计出观看数最多的前50个视频所关联的视频的所属类别排行

思路:





结果:

4.5 筛选出某个类别(如music)中热度最高的前10个视频

思路:

创建一个表格,存储每个视频对应一个标签的信息
create table youtube_category(videoId string, uploader string, age int, categoryId string, length int, views int, rate float, ratings int, comments int,relatedId array)
row format delimited
fields terminated by “ ”
collection items terminated by “&”
stored as orc; 将转换后的数据进行插入
insert into table youtube_category select videoid,uploader,age,categoryId,length,views,rate,ratings,comments,relatedId from youtube3 lateral view explode(category) catetory as categoryId; 根据观看数和类别进行查询
select * from youtube_category where categoryId=”Music” order by views desc limit 10;

结果如下:

4.6 筛选出每个类别中评分最高的前10个视频

select *  from youtube_category where categoryId=”Music” order by ratings desc limit 10;

结果如下:

4.7 找出用户中上传视频最多的10个用户的所有视频

思路:

筛选出用户表中上传视频数最多的前十位
select * from user order by videos desc limit 10; 将筛选结果跟youtube3进行join得出结果
select b.,a.videos,a.friends from (select from user order by videos desc limit 10) a join youtube3 b on a.uploader = b.uploader order by views desc limit 20;

结果如下:

4.8 筛选出每个类别中观看数Top10

select a.* from (select videoId,categoryId,views,row_number() over(partition by categoryId order by views desc) rank from youtube_category) a where rank<=10;

5 总结

上面的8个例子向大家展示Hive中简单和稍微复杂的操作,有的启动一个Job就可以完成,有的则需要启动多个Job才能完成,我们也可以看到,启动的Job越多运行时间就会越长,但是实际工作中的操作只会远比我们所演示要更加复杂,越是复杂的操作就更加需要去优化,来达到减少运行时间的目的,所以下一篇我们来看看Hive的优化实践

来源:本文为线上采编,如涉及作品内容、版权和其它问题,请及时与本网联系,我们将在第一时间删除!