今日总结10.12

04.大型数据库应用技术课堂测试05-日志数据分析

 

Result文件数据说明：

 

Ip：106.39.41.166,（城市）

 

Date：10/Nov/2016:00:01:02 +0800,（日期）

 

Day：10,（天数）

 

Traffic: 54 ,（流量）

 

Type: video,（类型：视频video或文章article）

 

Id: 8701（视频或者文章的id）

 

测试要求：

 

1、 数据清洗：按照进行数据清洗，并将清洗后的数据导入hive数据库中。

 

两阶段数据清洗：

 

（1）第一阶段：把需要的信息从原始日志中提取出来

 

ip:    199.30.25.88

 

time:  10/Nov/2016:00:01:03 +0800

 

traffic:  62

 

文章： article/11325

 

视频： video/3235

 

（2）第二阶段：根据提取出来的信息做精细化操作

 

ip--->城市 city（IP）

 

date--> time:2016-11-10 00:01:03

 

day: 10

 

traffic:62

 

type:article/video

 

id:11325

 

（3）hive数据库表结构:

 

create table data(  ip string,  time string , day string, traffic bigint,

 

type string, id   string )

 

 

create table if not exists data(
    ip string,
    `time` string,
    day string,
    traffic bigint,
    type string,
    id string
)
 row format delimited fields terminated by ',';
load data inpath '/data/result.csv' into table data;

create table if not exists data1
comment "数据清洗一  "
as
select
  ip,
  `time`,
  traffic,
  type,
  id
  from data;

select * from data1 limit 100;

create table if not exists data2(
    ip string,
    `time` string,
    day string,
    traffic bigint,
    type string,
    id string
)
 row format delimited fields terminated by ',';

INSERT INTO data2 (ip, `time`, day, traffic, type, id)
SELECT
    ip,
     FROM_UNIXTIME(UNIX_TIMESTAMP(SUBSTRING_INDEX(`time`, ' ', 1), 'dd/MMM/yyyy:HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') AS `time`,
    day,
    CAST(traffic AS BIGINT),
    type,
    id
FROM
    data;

 

 

2、数据分析：在HIVE统计下列数据。

 

（1）统计最受欢迎的视频/文章的Top10访问次数 （video/article）

 

（2）按照地市统计最受欢迎的Top10课程 （ip）

 

（3）按照流量统计最受欢迎的Top10课程 （traffic）

 

 

 

-- 统计最受欢迎的视频/文章的Top10访问次数
create table data_type(id string,type string,cnt_tp int) row format delimited fields terminated by ',';

insert overwrite table data_type select id,type,count(*) from data2 group by type,id order by count(*) desc limit 10;
-- 按照地市统计最受欢迎的Top10课程 （ip）

create table data_ip(ip string,type string,id string,cnt_ip int) row format delimited fields terminated by ',';
insert overwrite table data_ip select ip,type,id,count(*) from data2 group by ip,type,id order by count(*) desc limit 10;

-- 按照流量统计最受欢迎的Top10课程 （traffic）
create table data_traffic(id string,type string,liulan int) row format delimited fields terminated by ',';
insert overwrite table data_traffic select id,type,sum(traffic) from data2 group by type,id order by sum(traffic) desc limit 10;

然后将hive中的这三个表导入到mysql数据库中就行,下一篇就是我做的前端可视化展示.