统计影音视频网站的常规指标，各种TopN指标

最新推荐文章于 2024-06-30 18:52:56 发布

taoyundao_1

最新推荐文章于 2024-06-30 18:52:56 发布

阅读量1k

点赞数 25

文章标签： hive spark

本文链接：https://blog.csdn.net/taoyundao_1/article/details/136109965

版权

Hive大数据分析

作者：陶运道

概述：大数据特点是数据量大，数据为半结构化以及完全非结构化数据类型，在应用之前要对数据进行清洗规整后，存放在数据仓库中。以供应用。

本文通过实例项目，详细讨论了数据清洗过程、清洗后数据以hive表形式存放在hdfs中、以及如何应用数据。

本项目已通过实验验证了项目可行性。

关键字：清洗，wordMap类，TOPN指标

1项目要求

统计影音视频网站的常规指标，各种TopN指标：（热搜）

（1）统计视频观看数Top10

（2）统计视频类别热度Top10

（3）统计视频观看数Top20所属类别

（4）统计视频观看数Top50所关联视频的所属类别Rank

（5）统计每个类别中的视频热度Top10

（6）统计每个类别中视频流量Top10

（7）统计上传视频最多的用户Top10以及他们上传的视频

（8）统计每个类别视频观看数Top10

2实验数据分析

2.1实验数据分析

项目数据在 video、user目录中，如图所示，在目录video有五个数据文件0.txt~4.txt。

为了便于研究0.txt等文件结构，将文件导入到EXCEL。数据文件格式如图1所示。

图1 数据文件格式

文件每行由多列组成，每列之间用‘\t’隔开，第4列中间有空格。每行列数不同。对这样数据，无法处理。

2.2 数据清洗规则

为了处理数据，首先对数据进行重组清洗操作。

处理原则：（1）每行9列或10列，列与列之间用’\t’分隔。

（2）多于10列将第10列及后面列并成一列，并以”&”分隔。

（3）第4列由于中间有空隔，去空格。

清洗规整后每行数据典型格式

图2 清洗后的数据格式

2.3 hive表结构设计

为了方便数据应用，创建hive表。

表1 视频表结构

字段	备注	详细描述
videoid	视频唯一id（String）	11位字符串
uploader	视频上传者（String）	上传视频的用户名String
age	视频年龄（int）	视频在平台上的整数天
category	视频类别（Array<String>）	上传视频指定的视频分类
length	视频长度（Int）	整形数字标识的视频长度
views	观看次数（Int）	视频被浏览的次数
rate	视频评分（Double）	满分5分
Ratings	流量（Int）	视频的流量，整型数字
conments	评论数（Int）	一个视频的整数评论数
related ids	相关视频id（Array<String>）	相关视频的id，最多20个

表2 用户表结构

字段	备注	字段类型
uploader	上传者用户名	string
videos	上传视频数	int
friends	朋友数量	int

3建立java项目完成对数据清洗

为了实现数据清洗，利用IDEA创建maven项目。

3.1建立maven：JAVA项目

（1）新建项目

File-New-Project-Java-Maven-create

图4 创建项目向导

（2）配置maven依赖

File-Setting-Build,Execution,Deployment-Build Tools-Maven

图5 配置maven依赖向导

按实际配置三项

Maven home path: maven安装路径

User setting file: settings.xml文件路径（一般在maven安装路径的conf目录中）

Local repository: 本地仓库目录（由用户在settings.xml设置）

（3）配置 pom.xml

将以下内容复制到pom.xml中。

<dependencies>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>3.2.3</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-hdfs</artifactId>
       <version>3.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-install-plugin</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-mapreduce-client-core</artifactId>
        <version>3.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-client</artifactId>
        <version>3.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.8.0</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

3.2 新建两个类Main、WordMapper,如图所示

图6 建立类图示

WordMapper类程序

import java.io.IOException;

import org.apache.hadoop.io.NullWritable;

import org.apache.hadoop.io.Text;

import org.apache.hadoop.mapreduce.Mapper;

//mapper进程，每一个split（block）会启动该类，

public class WordMapper extends Mapper<Object, Text, Text, NullWritable> {

Text text = new Text();

@Override

// map()处理行由上下文机制传一行数据，格式键值对<key(行号)，Text(一行文本)>

protected void map(Object key, Text value, Context context) throws IOException, InterruptedException {

String ori=value.toString(); //取一行

StringBuilder etlString1 = new StringBuilder(); //创建字符串变量

String[] splits = ori.split("\t"); //将行数据"\t"分割，每个部分数据形成数组splits

if(splits.length < 9) return ; //数组长度小于9返回，不处理本行 (每行数组长度>=9)

splits[3] = splits[3].replace(" ", ""); //3列去除空格

/*下面将字符串数组整合一个字符串整合规则：

splits[0]”\t” splits[1] ”\t”…… ”\t”splits[8] splits数组长度=9

splits[0]”\t” splits[1] ”\t”…… ”\t”splits[8]& splits[9]& splits[9] splits数组长度>9

for(int i = 0; i < splits.length; i++) { //数组元素以一定分隔形式形成字符串。

if(i < 9){ //处理小于9列（0-8列）

if(i == splits.length - 1){ //如果只有9列有数据，将最后一列写入

etlString1.append(splits[i]);

}else{

etlString1.append(splits[i] + "\t");

}

}else{

if(i == splits.length - 1){

etlString1.append(splits[i]); //写入最后一列数据

}else{

etlString1.append(splits[i] + "&");//不是最后一列加&写入

}

String etlString= etlString1.toString();

// if(etlString.isEmpty()) return; //如果字符串是空串，返回，不处理

text.set(etlString); //设置对象text值

context.write(text,NullWritable.get());

}

（2）Main类程序

import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.*;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
public class Main {
    public static   void main(String[] args) throws IOException, URISyntaxException, InterruptedException, ClassNotFoundException,Exception
      { Configuration conf = new Configuration();
         conf.set("fs.defaultFS","hdfs://192.168.100.100:9000");
          Job job = Job.getInstance(conf, "word count"); //创建job将文件以行为单位进行处理
           job.setJarByClass(Main.class);   //设置job主类
           job.setMapperClass(WordMapper.class); //设置job mapper类
           job.setMapOutputValueClass(NullWritable.class);
           job.setMapOutputKeyClass(Text.class);
           job.setNumReduceTasks(0); //设置源目录下多个文件
          FileInputFormat.addInputPaths(job, "/video"); //原文件路径

//输出路径，注意运行前在/video下不能有out文件夹。
FileOutputFormat.setOutputPath(job, new Path("/kk/output"));

System.exit(job.waitForCompletion(true) ? 0 : 1);//结果本次任务
}
}

（3）程序打包运行

按图操作，项目加入依赖向导，File-Project structure

图7 加入依赖

选择主类向导

图8 加入主类

打包向导

图9 打包

3.3.运行程序

# hadoop fs –put /home/video / //将数据文件发至hdfs上

# hadoop fs –mkdir /kk // 创建hdfs输出目录

#hadoop fs –put /home/user.txt /kk //将数据文件发至hdfs上

将jar包文件复制到本地/home 并改名为1122.jar

# cd /home

# hadoop jar 1122.jar //运行程序

#hadoop fs –ls /kk/output //查看文件

图10 程序运行后数据文件

注意：数据清洗也可以用 spark 的RDD编程实现

代码如下：代码简单

运行spark-shell

#spark-shell

Scala> import java.lang

val f=sc.textFile("/video/*.txt")

scala>val f1 = f.map(x => {

val etlString1 = new lang.StringBuilder

val splits =x.split("\t")

//处理列数>=9列

if(splits.length<=8) etlString1.append("aaaaa")

if(splits.length>8)

{ splits(3) = splits(3).replace(" ", "") //去第4列空格

for (i <- 0 until splits.length) {

if (i < 9) { if (i == splits.length - 1) etlString1.append(splits(i))

else etlString1.append(splits(i) + "\t")

}

else if (i == splits.length - 1) etlString1.append(splits(i))

else etlString1.append(splits(i) + "&") }

}

etlString1 //map()将一行数据变成标准形式字符串

} )

val f2=f1.filter(x=>x.toString != "aaaaa") //过滤

f2.saveAsTextFile("/aaa")

导入hive表后总共743569行数据

注意：（1）在处理过程由于去空格语句位置不对，出现错误，最后想到，也可能splits数组元素不足3，所造成错误。

(2)在读取多个文件时出错：Caused by: org.apache.spark.SparkException: Kryo serialization failed: Buffer overflow. Available: 0, required: 88. To avoid this, increase spark.kryoserializer.buffer.max value.

解决方法：编辑spark下conf/ spark-defaults.conf 增加一行

spark.kryoserializer.buffer.max=128m //默认为64m 也可以再增加点

（3）val f2=f1.filter(x=>x.toString != "aaaaa") 语句中类型转换

（4）etlString1一定要定义为lang.StringBuilder，可随时将字符串插入该对象末尾

如果使用字符串变量，是没办法将串插入到变量中

4创建表并导入数据

4.1创建表

创建test数据库

创建表：gulivideo_ori，gulivideo_user_ori，

创建表：gulivideo_orc，gulivideo_user_orc

gulivideo_ori：

create table gulivideo_ori(

videoId string,

uploader string,

age int,

category array<string>,

length int,

views int,

rate float,

ratings int,

comments int,

relatedId array<string>)

row format delimited fields terminated by "\t"

collection items terminated by "&"

stored as textfile;

gulivideo_user_ori：

create table gulivideo_user_ori(

uploader string,

videos int,

friends int)

row format delimited

fields terminated by "\t"

stored as textfile;

把原始数据插入到orc表中

gulivideo_orc：

create table gulivideo_orc(

videoId string,

uploader string,

age int,

category array<string>,

length int,

views int,

rate float,

ratings int,

comments int,

relatedId array<string>)

row format delimited fields terminated by "\t"

collection items terminated by "&"

stored as orc;

gulivideo_user_orc：

create table gulivideo_user_orc(

uploader string,

videos int,

friends int)

row format delimited

fields terminated by "\t"

stored as orc;

4.2导入ETL后的数据

gulivideo_ori：

load data inpath "/kk/output" into table gulivideo_ori;

gulivideo_user_ori：

load data inpath "/kk/user.txt" into table gulivideo_user_ori;

4.3向ORC表插入数据

gulivideo_orc：

insert into table gulivideo_orc select * from gulivideo_ori;

gulivideo_user_orc：

insert into table gulivideo_user_orc select * from gulivideo_user_ori;

5 业务分析

5.1统计视频观看数Top10

思路：使用order by按照views字段做一个全局排序即可，同时我们设置只显示前10条。

最终代码：

select videoId,views from gulivideo_ori order by views desc limit 10

5.2统计视频类别热度Top10

类别category字段值包含多个视频，统计每个视频最多前10个类别

1) 一个videoId对应一个category字段。一个category字段值对应多个视频，需要先将category字段进行列转行(展开)。

2) 按照视频名称分组，依videoId统计个数。

3) 最后按照热度（点击次数即统计每个视频的videid之和）排序，显示前10条。

关键要理解以下函数：

lateral view explode(category) t_catetory as category_name

将一行category字段值变成多行

其中：转换虚拟表名t_catetory

例如有一行：

videoid category

1xbSFrHzFQ0 ["Film","Animation"]

变成

videoid cate

1xbSFrHzFQ0 Film

1xbSFrHzFQ0 Animation

查询代码：

select category_name as category, count(t1.videoId) as hot

from ( select videoId, category_name

from gulivideo_orc lateral view explode(category) t_catetory as category_name) t1

group by t1.category_name order by hot desc limit 10;

5.3统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

1) 先找到观看数最高的20个视频所属条目的所有信息，降序排列

2) 把这20条信息中的category分裂出来(列转行)

3) 最后查询视频分类名称和该分类下有多少个Top20的视频

最终代码：

SELECT cate category, COUNT(videoid) hot FROM

(SELECT videoid,cate FROM

(SELECT videoid, category, views FROM test.gulivideo_orc

ORDER BY views DESC limit 20)t1

Lateral view explode(category) tb1 as cate)t2

GROUP BY cate ORDER BY hot desc;

注解 1. 找出观看视频数前20名的记录集（包含字段，videoid, category, views）

SELECT videoid, category, views FROM gulivideo_orc

ORDER BY views DESC limit 20

查询结果：

videoid category views

dMH0bHeiRNg ["Comedy"] 42513417

0XxI-hvPRRA ["Comedy"] 20282464

2.再将category字段拆分成行记录集（字段包括videoid, cate（视频名称））

SELECT videoid, cate FROM

(SELECT videoid, category, views FROM gulivideo_orc

注意：查询时要有t1 别名

ORDER BY views DESC limit 20)t1

Lateral view explode(category) tb1 as cate

videoid cate

dMH0bHeiRNg Comedy

0XxI-hvPRRA Comedy

1dmVU08zVpA Entertainment

5.4统计视频观看数Top50所关联视频所属类别排序

1.查询观看数前50视频信息

SELECT videoid, views, relatedid FROM gulivideo_orc

ORDER BY views DESC LIMIT 50) ：t1

2.将t1字段relatedid展开（一行变多行），并去重

SELECT DISTINCT (relatedid_name)

FROM t1 lateral VIEW explode (relatedid) table_temp AS relatedid_name; t2

3.t2为视频观看top50所关联视频videoid字段表，将该表与原表inner join得到关联视频展开relatedid字段表。

SELECT category,relatedid FROM gulivideo_orc t3

INNER JOIN t2 ON t3.videoid = t2.relatedid_name; t4

4.将上述表中category字段行转列，得到最终的展开表。

SELECT category_name FROM

t4 lateral VIEW explode (category) table_temp AS category_name; t5

5. 对最终展开表的category_name字段group by之后再count一下，即可得到每个类别的总数，最后排序即可。

SELECT category_name, COUNT(*) hot

FROM t5 GROUP BY t5.category_name ORDER BY hot DESC ;

最终代码：

SELECT category_name,hot

FROM (SELECT category_name, COUNT(*) hot

FROM (SELECT category_name

FROM (SELECT category,relatedid

FROM gulivideo_orc t3

INNER JOIN

(SELECT DISTINCT(relatedid_name)

FROM (SELECT videoid,views,relatedid

FROM gulivideo_orc

ORDER BY views DESC LIMIT 50) t1 lateral VIEW explode (relatedid) table_temp AS relatedid_name) t2

ON t3.videoid = t2.relatedid_name) t4 lateral VIEW explode (category) table_temp AS category_name) t5 GROUP BY t5.category_name) t6 ORDER BY hot DESC;

5.5统计每个类别中的视频热度Top10，以Music为例

思路：

1) 要想统计Music类别中的视频热度Top10，需要先找到Music类别，那么就需要将category展开，所以可以创建一张表用于存放categoryId展开的数据。

2) 向category展开的表中插入数据。

3) 统计对应类别（Music）中的视频热度。

创建表类别表：

create table video_category( videoId string, uploader string, age int,

cate string, length int, views int, rate float, ratings int,

comments int, relatedId array<string>)

row format delimited fields terminated by "\t"

collection items terminated by "&"

stored as orc;