《Hive数据仓库应用与实战》课件 项目六-任务一-Youtube视频统计与社交网络

《Hive数据仓库应用与实战》Hive数据仓库应用与实战项目六Hive项目实战任务一Youtube视频统计与社交网络回顾知识点1数据模型的概念回顾知识点2三种重要的数据模型回顾知识点3

数据仓库架构回顾任务引入2021年，YouTube每月登录用户超过20亿。如此大的用户群体也是YouTube很早就开始使用大数据，并不断提升其业务水平的原因。任务概要任务描述:本任务属于实战项目，同学们要根据业务需求对“YouTube视频统计与社交网络”数据集进行分析，得出相应的结果，本项目侧重于HiveQL的编写，希望同学们能用尽量简洁明了的HiveQL完成。任务教学目标：清洗问题数据根据业务分析数据一数据来源二数据清洗教学内容三准备工作四导入ETL后的数据五向ORC表插入数据六业务分析数据来源知识点一（一）数据来源1、Youtube视频表格式Youtube视频表列名注释视频ID一个11位字符串，是唯一的上传一个字符串的视频上传者的用户名年龄视频上传日期和2007年2月15日之间的整数天（YouTube的设立）类别由上传者选择的视频类别的字符串长度视频长度的整数v观看数一整数的视图率一个浮点数的视频速率评分整数的评分评论数一整数的评论相关视频ID最多20个字符串的相关视频ID（一）数据来源2、用户表格式Youtube用户表列名uploadervideosfriends类型stringintint解释上传者上传视频数朋友数数据清洗知识点二（二）数据清洗1、ETL——ETLUtil首先在eclipse中创建项目MapreduceExample，导入依赖包列表如下，这些JAR包位于Hadoop安装目录的/share/hadoop目录下。/usr/local/hadoop/share/hadoop/common目录中的hadoop-common-2.7.1.jar和hadoop-nfs-2.7.1.jar/usr/local/hadoop/share/hadoop/common/lib目录中所有JAR包。可以用Ctrl+A，全选。/usr/local/hadoop/share/hadoop/mapreduce目录中的所有JAR包。可以用Ctrl+A，全选。/usr/local/hadoop/share/hadoop/mapreduce/lib目录中所有JAR包。可以用Ctrl+A，全选。（二）数据清洗1、ETL——ETLUtil创建项目成功后，创建package名称YoutubeETL，新建ETLUtil类。（二）数据清洗2、ETL——Mapper编写Mapper类（二）数据清洗3、ETL——Runner编写Runner类，详细代码见教材（二）数据清洗4、执行ETL将待清洗的数据（视频表文件，非用户表文件）通过FTP工具传输到Linux目录/opt/module/datas/080327/下。将用户表文件传输到/opt/module/datas/080327user/下。将待清洗的数据上传至HDFS中[root@localhosthadoop]#hdfsdfs-mkdir-p/youtube/video/080327/[root@localhosthadoop]#hdfsdfs-mkdir-p/youtube/output/080327/[root@localhosthadoop]#hdfsdfs-put/opt/module/datas/080327/*/youtube/video/080327/（二）数据清洗4、执行ETL将用户表文件上传至HDFS中[root@localhosthadoop]#hdfsdfs-mkdir-p/youtube/user/[root@localhosthadoop]#hdfsdfs-put/opt/module/datas/080327user/*/youtube/user/将编写好的代码打包为YoutubeETL.jar，放至/opt/module/myapp/文件夹下，运行jar包[root@localhosthadoop]#bin/hadoopjar/opt/module/myapp/YoutubeETL.jar/youtube/video/080327//youtube/output/080327/准备工作知识点三（三）准备工作1、创建表创建表：youtube_oricreatetableyoutube_ori(videoIdstring,uploaderstring,ageint,categoryarray<string>,lengthint,viewsint,ratefloat,ratingsint,commentsint,relatedIdarray<string>)rowformatdelimitedfieldsterminatedby"\t"collectionitemsterminatedby"&"storedastextfile;（三）准备工作1、创建表创建表：youtube_user_ori：createtableyoutube_user_ori(uploaderstring,videosint,friendsint)clusteredby(uploader)into24bucketsrowformatdelimitedfieldsterminatedby"\t"storedastextfile;（三）准备工作1、创建表创建表：youtube_orc：createtableyoutube_orc(videoIdstring,uploaderstring,ageint,categoryarray<string>,lengthint,viewsint,ratefloat,ratingsint,commentsint,relatedIdarray<string>)clusteredby(uploader)into8bucketsrowformatdelimitedfieldsterminatedby"\t"collectionitemsterminatedby"&"storedasorc;（三）准备工作1、创建表创建表：youtube_user_orc：createtableyoutube_user_orc(uploaderstring,videosint,friendsint)clusteredby(uploader)into24bucketsrowformatdelimitedfieldsterminatedby"\t"storedasorc;导入ETL后的数据知识点四（四）导入ETL后的数据youtube_ori：：loaddatainpath"/youtube/output/080327/*"intotableyoutube_ori;youtube_user_ori：

loaddatainpath"/youtube/user/*"intotableyoutube_user_ori;向ORC表插入数据知识点五（五）向ORC表插入数据youtube_orc：insertintotableyoutube_orcselect*fromyoutube_ori;youtube_user_orc：

insertintotableyoutube_user_orcselect*fromyoutube_user_ori;业务分析知识点六（六）业务分析思路：使用orderby按照views字段做一个全局排序即可，同时我们设置只显示前10条。selectvideoId,uploader,age,category,length,views,rate,ratings,commentsfromyoutube_orcorderbyviewsdesclimit10;1、统计视频观看数Top10（六）业务分析思路：1、即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。2、我们需要按照类别groupby聚合，然后count组内的videoId个数即可。3、因为当前表结构为：一个视频对应一个或多个类别。所以如果要groupby类别，需要先将类别进行列转行(展开)，然后再进行count即可。4、最后按照热度排序，显示前10条。2、统计视频类别热度Top10（六）业务分析2、统计视频类别热度Top10selectcategory_nameascategory,count(t1.videoId)ashotfrom(selectvideoId,category_namefromyoutube_orclateralviewexplode(category)t_catetoryascategory_name)t1groupbyt1.category_nameorderbyhotdesclimit10;（六）业务分析3、统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数思路：1、先找到观看数最高的20个视频所属条目的所有信息，降序排列。2、把这20条信息中的category分裂出来(列转行)。3、最后查询视频分类名称和该分类下有多少个Top20的视频。（六）业务分析3、统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数selectcategory_nameascategory,count(t2.videoId)ashot_with_viewsfrom(selectvideoId,category_namefrom(select*fromyoutube_orcorderbyviewsdesclimit20)t1lateralviewexplode(category)_catetoryascategory_name)t2groupbycategory_nameorderbyhot_with_viewsdesc;（六）业务分析4、统计视频观看数Top50所关联视频的所属类别Rank思路：1、查询出观看数最多的前50个视频的所有信息(当然包含了每个视频对应的关联视频)，记为临时表t1。2、将找到的50条视频信息的相关视频relatedId列转行，记为临时表t2。3、将相关视频的id和youtube_orc表进行innerjoin操作。4、按照视频类别进行分组，统计每组视频个数，然后排行。（六）业务分析4、统计视频观看数Top50所关联视频的所属类别Rankselectcategory_nameascategory,count(t5.videoId)ashotfrom(selectvideoId,category_namefrom(selectdistinct(t2.videoId),t3.categoryfrom(selectexplode(relatedId)asvideoIdfrom(select*fromyoutube_orcorderbyviewsdesclimit50)t1)t2innerjoinyoutube_orct3ont2.videoId=t3.videoId)t4lateralviewexplode(category)t_catetoryascategory_name)t5groupbycategory_nameorderbyhotdesc;（六）业务分析思路：1、要想统计Music类别中的视频热度Top10，需要先找到Music类别，那么就需要将category展开，所以可以创建一张表用于存放categoryId展开的数据。2、向category展开的表中插入数据。3、统计对应类别（Music）中的视频热度。5、统计每个类别中的视频热度Top10，以Music为例（六）业务分析1、创建表类别表：createtableyoutube_category(videoIdstring,uploaderstring,ageint,categoryIdstring,lengthint,viewsint,ratefloat,ratingsint,commentsint,relatedIdarray<string>)rowformatdelimitedfieldsterminatedby"\t"collectionitemsterminatedby"&"storedasorc;5、统计每个类别中的视频热度Top10，以Music为例（六）业务分析2、向类别表中插入数据：insertintotableyoutube_categoryselectvideoId,uploader,age,categoryId,length,views,rate,ratings,comments,relatedIdfromyoutube_orclateralviewexplode(category)catetoryascategoryId;5、统计每个类别中的视频热度Top10，以Music为例（六）业务分析3、统计Music类别的Top10selectvideoId,viewsfromyoutube_categorywherecategoryId="Music"orderbyviewsdesclimit10;5、统计每个类别中的视频热度Top10，以Music为例（六）业务分析思路：1、创建视频类别展开表（categoryId列转行后的表）2、按照ratings排序即可selectvideoId,views,ratingsfromyoutube_categorywherecategoryId="Music"orderbyratingsdesclimit10;6、统计每个类别中视频流量Top10，以Music为例（六）业务分析思路：1、先找到上传视频最多的10个用户的用户信息select*fromyoutube_user_orcorderbyvideosdesclimit10;7、统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频（六）业务分析思路：2、通过uploader字段与youtube_orc表进行join，得到的信息按照views观看次数进行排序即可。