广电数据分析

案例需求分析

依据现有用户观看历史和用户信息数据，分析二者的常规指标，即各种TopN指标，列如：

  1. 基本信息查询
  2. 单个用户观看时长Top20
  3. 用户观看时长Top20
  4. 电视观看数Top20
  5. 电视观看时常Top20
  6. 用户正常状态及数量

案例数据及建表

案例数据已经经过ETF，符合数据处理的要求，在此基础上创建数据仓库表并向其中导入数据。

原始数据

用户观看历史文件486.5MB，共有约3360218条数据，每条记录有17个字段。用户信息数据文件922.8KB，共有15660条数据，每条记录有7个字段。

数据表结构

数据表的字段及其数据类型等结构信息分别详见表

  1. 观看历史表

    字段名称 描述 字段数据类型
    phone_no 用户名 String
    duration 观看时长（ms） Int

  2. 用户信息表

    字段名称 描述 字段数据类型
    phone_no 用户名 String
    owner_name 用户等级名称 String

创建表

  1. 创建存储格式为TextFile的表text_see和text_user（用于存储原始数据

    观看历史表text_see：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    create table text_see(
        phone_no string,
        duration int,
        station_name string,
        origin_time string,
        end_time string,
        res_name string,
        owner_code string,
        owner_name string,
        category_name string,
        res_type string,
        vod_title string,
        program_title string,
        day string,
        origin_time1 string,
        end_time1 string,
        wat_time int,\
        data string
    )
    row format delimited fields terminated by ","
    stored as textfile;

    用户信息表text_user：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table text_user(
        phone_no string,
        owner_name striing,
        run_name string,
        run_time string,
        sm_name string,
        owner_code string
    )
    row format delimited fields terminated by ","
    stored as textfile;

  2. 创建存储格式为ORC的表orc_see和orc_user

    观看历史表orc_see：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    create table orc_see(
        phone_no string,
        duration int,
        station_name string,
        origin_time string,
        end_time string,
        res_name string,
        owner_code string,
        owner_name string,
        category_name string,
        res_type string,
        vod_title string,
        program_title string,
        day string,
        origin_time1 string,
        end_time1 string,
        wat_time int,\
        data string
    )
    row format delimited fields terminated by ","
    stored as orc;

    用户信息表orc_user：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table orc_user(
        phone_no string,
        owner_name string,
        run_name string,
        run_time string,
        sm_name string,
        owner_code string
    )
    row format delimited fields terminated by ","
    stored as orc;

向TextFile表导入数据

观看历史文件储存在本地系统/opt/datas目录下，将其导入表text_see中：

load data local inpath "/opt/datas/media3.txt" into table text_see;

用户信息文件存储在本地系统/opt/datas目录下，将其导入表text_user中：

load data local inpath "/opt/datas/userevents.txt" into table text_user;

向ORC表导入数据

将表text_see中的数据加载到orc_see中给：

insert into table orc_see select * from text_see;

将表text_user中的数据加载到orc_user中给：

insert into table orc_user select * from text_user;

创建完成的表：

show tables

展位图

数据统计分析

创建数据仓库表并向其中导入数据后，可以对用户观看历史和用户信息数据进行大数据分析。

基本信息查询

查询表orc_see的记录总数：

select count(*) from orc_see;

查询表orc_user的记录总数：

select count(*) from orc_user;

查看表text_see的数据大小：

dfs -du -h /user/hive/warehouse/text_see;

查看表text_user的数据大小：

dfs -du -h /user/hive/warehouse/text_user;

单个用户观看时长Top20

对orc_see表使用order by按照duration字段做一个全局降序排序，并且设置只显示前20条数据，即Top20。

1
2
3
4
select phone_no, duration
from orc_see
order byy duration desc
limit 20;

用户观看时长Top20

按照用户group by聚合，然后统计组内的时长即可。按照时长排序，显示前20条，即Top20。

1
2
3
4
5
6
select
    phone_no as name, sum(wat_time) as time
from orc_see
group by phone_no
order by time desc
limit 20;

电视观看数Top20

统计观看数最高的20个电视节目及类别（包含Top20观看数）：

1
2
3
4
5
6
select
	station_name as station, count(category_name) as 'num'
from orc_see
group by station_name
order by num desc
limit 20;

电视观看时长Top20

统计电视观看时长Top20：

1
2
3
4
5
6
select
	station_name as station,sum(duration) as 'num'
from orc_see
group by station_name
order by num desc
limit 20;

用户正常状态及数量

依据orc_user表对run_name状态进行计数和排序

1
2
select run_name, count(run_name)
from orc_user