网络日志流量分析-第二部分.doc

一、课程计划

一、课程计划 1

二、模块开发----数据仓库设计 3

1．维度建模基本概念 3

2．维度建模三种模式 4

2.1．星型模式 4

2.2．雪花模式 5

2.3．星座模式 6

3．本项目中数据仓库的设计 7

3.1．事实表设计 7

3.2．维度表设计 8

三、模块开发----ETL 9

1．创建ODS层数据表 9

1.1．原始日志数据表 9

1.2．点击流模型pageviews表 9

1.3．点击流visit模型表 10

2．导入ODS层数据 10

3．生成ODS层明细宽表 11

3.1．需求实现 11

3.2． ETL实现 11

四、模块开发----统计分析 14

1．流量分析 14

1.1．多维度统计PV总量 14

1.2．人均浏览量 17

1.3．统计pv总量最大的来源TOPN (分组TOP) 18

2．受访分析（从页面的角度分析） 20

2.1．各页面访问统计 20

2.2．热门页面统计 20

3．访客分析 21

3.1．独立访客 21

3.2．每日新访客 22

4．访客Visit分析（点击流模型） 24

4.1．回头/单次访客统计 24

4.2．人均访问频次 24

5．关键路径转化率分析（漏斗模型） 25

5.1．需求分析 25

5.2．模型设计 25

5.3．开发实现 25

五、模块开发----结果导出 28

1． Apache Sqoop 28

2． Sqoop导入 29

2.1．导入mysql表数据到HDFS 29

2.2．导入mysql表数据到HIVE 30

2.3．导入表数据子集 30

2.4．增量导入 31

3． Sqoop导出 32

3.1．导出HDFS数据到mysql 32

六、模块开发----工作流调度 34

七、模块开发----数据可视化 35

1． Echarts介绍 35

2． Web程序工程结构 36

3．感受Echarts—简单入门 37

3.1．下载Echarts 37

3.2．页面引入Echarts 37

3.3．绘制一个简单的图表 37

4．数据可视化的展现 39

4.1． Mybatis example 排序问题 39

4.2． Echarts前端数据格式问题 39

4.3． Controller返回的json 39

二、模块开发----数据仓库设计

1．维度建模基本概念

维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的方法。数据集市可以理解为是一种"小型数据仓库"。

维度表(dimension)

维度表示你要对数据进行分析时所用的一个量,比如你要分析产品销售情况, 你可以选择按类别来进行分析,或按区域来分析。这样的按..分析就构成一个维度。再比如"昨天下午我在星巴克花费200元喝了一杯卡布奇诺"。那么以消费为主题进行分析，可从这段信息中提取三个维度：时间维度(昨天下午)，地点维度(星巴克), 商品维度(卡布奇诺)。通常来说维度表信息比较固定，且数据量小。

事实表(fact table)

表示对分析主题的度量。事实表包含了与各维度表相关联的外键，并通过JOIN方式与维度表关联。事实表的度量通常是数值类型，且记录数会不断增加，表规模迅速增长。比如上面的消费例子，它的消费事实表结构示例如下：

消费事实表：Prod_id(引用商品维度表), TimeKey(引用时间维度表), Place_id(引用地点维度表), Unit(销售量)。

总的说来，在数据仓库中不需要严格遵守规范化设计原则。因为数据仓库的主导功能就是面向分析，以查询为主，不涉及数据更新操作。事实表的设计是以能够正确记录历史信息为准则，维度表的设计是以能够以合适的角度来聚合主题内容为准则。

2．维度建模三种模式

2.1．星型模式

星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。

星形模式的维度建模由一个事实表和一组维表成，且具有以下特点：

a. 维表只和事实表关联，维表之间没有关联；

b. 每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；

c. 以事实表为核心，维表围绕核心呈星形分布；

2.2．雪花模式

雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的，虽然这种模型相比星型更规范一些，但是由于这种模型不太容易理解，维护成本比较高，而且性能方面需要关联多层维表，性能也比星型模型要低。所以一般不是很常用。

2.3．星座模式

星座模式是星型模式延伸而来，星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息。

前面介绍的两种维度建模方法都是多维表对应单事实表，但在很多时候维度空间内的事实表不止一个，而一个维表也可能被多个事实表用到。在业务发展后期，绝大部分维度建模都采用的是星座模式。

3．本项目中数据仓库的设计

注：采用星型模型

3.1．事实表设计

原始数据表: ods_weblog_origin =>对应mr清洗完之后的数据
valid	string	是否有效
remote_addr	string	访客ip
remote_user	string	访客用户信息
time_local	string	请求时间
request	string	请求url
status	string	响应码
body_bytes_sent	string	响应字节数
http_referer	string	来源url
http_user_agent	string	访客终端信息

访问日志明细宽表：dw_weblog_detail
valid	string	是否有效
remote_addr	string	访客ip
remote_user	string	访客用户信息
time_local	string	请求完整时间
daystr	string	访问日期
timestr	string	访问时间
month	string	访问月
day	string	访问日
hour	string	访问时
request	string	请求url整串
status	string	响应码
body_bytes_sent	string	响应字节数
http_referer	string	来源url
ref_host	string	来源的host
ref_path	string	来源的路径
ref_query	string	来源参数query
ref_query_id	string	来源参数query值
http_user_agent	string	客户终端标识

3.2．维度表设计

时间维度 t_dim_time

date_Key

year

month

day

hour

访客地域维度t_dim_area

area_ID

北京

上海

广州

深圳

终端类型维度t_dim_termination

firefox

chrome

safari

ios

android

网站栏目维度 t_dim_section

跳蚤市场

房租信息

休闲娱乐

建材装修

本地服务

人才市场

注意：

维度表的数据一般要结合业务情况自己写脚本按照规则生成，也可以使用工具生成，方便后续的关联分析。

比如一般会事前生成时间维度表中的数据，跨度从业务需要的日期到当前日期即可.具体根据你的分析粒度,可以生成年，季，月，周，天，时等相关信息，用于分析。

三、模块开发----ETL

ETL工作的实质就是从各个数据源提取数据，对数据进行转换，并最终加载填充数据到数据仓库维度建模后的表中。只有当这些维度/事实表被填充好，ETL工作才算完成。

本项目的数据分析过程在hadoop集群上实现，主要应用hive数据仓库工具，因此，采集并经过预处理后的数据，需要加载到hive数据仓库中，以进行后续的分析过程。

1．创建ODS层数据表

1.1．原始日志数据表

drop table if exists ods_weblog_origin;

create table ods_weblog_origin(

valid string,

remote_addr string,

remote_user string,

time_local string,

request string,

status string,

body_bytes_sent string,

http_referer string,

http_user_agent string)

partitioned by (datestr string)

row format delimited

fields terminated by '\001';

1.2．点击流模型pageviews表

drop table if exists ods_click_pageviews;

create table ods_click_pageviews(

session string,

remote_addr string,

remote_user string,

time_local string,

request string,

visit_step string,

page_staylong string,

http_referer string,

http_user_agent string,

body_bytes_sent string,

status string)

partitioned by (datestr string)

row format delimited

fields terminated by '\001';

1.3．点击流visit模型表

drop table if exist ods_click_stream_visit;

create table ods_click_stream_visit(

session string,

remote_addr string,

inTime string,

outTime string,

inPage string,

outPage string,

referal string,

pageVisits int)

partitioned by (datestr string)

row format delimited

fields terminated by '\001';

2．导入ODS层数据

load data inpath '/weblog/preprocessed/' overwrite into table

ods_weblog_origin partition(datestr='20130918');--数据导入

show partitions ods_weblog_origin;---查看分区

select count(*) from ods_weblog_origin; --统计导入的数据总数

点击流模型的两张表数据导入操作同上。

注：生产环境中应该将数据load命令，写在脚本中，然后配置在azkaban中定时运行，注意运行的时间点，应该在预处理数据完成之后。

3．生成ODS层明细宽表

3.1．需求实现

整个数据分析的过程是按照数据仓库的层次分层进行的，总体来说，是从ODS原始数据中整理出一些中间表（比如，为后续分析方便，将原始数据中的时间、url等非结构化数据作结构化抽取，将各种字段信息进行细化，形成明细表），然后再在中间表的基础之上统计出各种指标数据。

3.2． ETL实现

l 建明细表ods_weblog_detail:

drop table ods_weblog_detail;

create table ods_weblog_detail(

valid string, --有效标识

remote_addr string, --来源IP

remote_user string, --用户标识

time_local string, --访问完整时间

daystr string, --访问日期

timestr string, --访问时间

month string, --访问月

day string, --访问日

hour string, --访问时

request string, --请求的url

status string, --响应码

body_bytes_sent string, --传输字节数

http_referer string, --来源url

ref_host string, --来源的host

ref_path string, --来源的路径

ref_query string, --来源参数query

ref_query_id string, --来源参数query的值

http_user_agent string --客户终端标识

)

partitioned by(datestr string);

l 通过查询插入数据到明细宽表 ods_weblog_detail中

1、抽取refer_url到中间表 t_ods_tmp_referurl

也就是将来访url分离出host path query query id

drop table if exists t_ods_tmp_referurl;

create table t_ods_tmp_referurl as

SELECT a.*,b.*

FROM ods_weblog_origin a

LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id;

注：lateral view用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据。

UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。Explode也是拆列函数，比如Explode (ARRAY) ，array中的每个元素生成一行。

2、抽取转换time_local字段到中间表明细表 t_ods_tmp_detail

drop table if exists t_ods_tmp_detail;

create table t_ods_tmp_detail as

select b.*,substring(time_local,0,10) as daystr,

substring(time_local,12) as tmstr,

substring(time_local,6,2) as month,

substring(time_local,9,2) as day,

substring(time_local,11,3) as hour

from t_ods_tmp_referurl b;

3、以上语句可以合成一个总的语句

insert into table shizhan.ods_weblog_detail partition(datestr='2013-09-18')

select c.valid,c.remote_addr,c.remote_user,c.time_local,

substring(c.time_local,0,10) as daystr,

substring(c.time_local,12) as tmstr,

substring(c.time_local,6,2) as month,

substring(c.time_local,9,2) as day,

substring(c.time_local,11,3) as hour,

c.request,c.status,c.body_bytes_sent,c.http_referer,c.ref_host,c.ref_path,c.ref_query,c.ref_query_id,c.http_user_agent

from

(SELECT

a.valid,a.remote_addr,a.remote_user,a.time_local,

a.request,a.status,a.body_bytes_sent,a.http_referer,a.http_user_agent,b.ref_host,b.ref_path,b.ref_query,b.ref_query_id

FROM shizhan.ods_weblog_origin a LATERAL VIEW

parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as ref_host, ref_path, ref_query,

ref_query_id) c;

四、模块开发----统计分析

数据仓库建设好以后，用户就可以编写Hive SQL语句对其进行访问并对其中数据进行分析。

在实际生产中，究竟需要哪些统计指标通常由数据需求相关部门人员提出，而且会不断有新的统计需求产生，以下为网站流量分析中的一些典型指标示例。

注：每一种统计指标都可以跟各维度表进行钻取。

1．流量分析

1.1．多维度统计PV总量

按时间维度

--计算每小时pvs，注意gruop by语法

select count(*) as pvs,month,day,hour from ods_weblog_detail group by month,day,hour;

方式一：直接在ods_weblog_detail单表上进行查询

--计算该处理批次（一天）中的各小时pvs

drop table dw_pvs_everyhour_oneday;

create table dw_pvs_everyhour_oneday(month string,day string,hour string,pvs bigint) partitioned by(datestr string);

insert into table dw_pvs_everyhour_oneday partition(datestr='20130918')

select a.month as month,a.day as day,a.hour as hour,count(*) as pvs from ods_weblog_detail a

where a.datestr='20130918' group by a.month,a.day,a.hour;

--计算每天的pvs

drop table dw_pvs_everyday;

create table dw_pvs_everyday(pvs bigint,month string,day string);

insert into table dw_pvs_everyday

select count(*) as pvs,a.month as month,a.day as day from ods_weblog_detail a

group by a.month,a.day;

方式二：与时间维表关联查询

--维度：日

drop table dw_pvs_everyday;

create table dw_pvs_everyday(pvs bigint,month string,day string);

insert into table dw_pvs_everyday

select count(*) as pvs,a.month as month,a.day as day from (select distinct month, day from t_dim_time) a

join ods_weblog_detail b

on a.month=b.month and a.day=b.day

group by a.month,a.day;

--维度：月

drop table dw_pvs_everymonth;

create table dw_pvs_everymonth (pvs bigint,month string);

insert into table dw_pvs_everymonth

select count(*) as pvs,a.month from (select distinct month from t_dim_time) a

join ods_weblog_detail b on a.month=b.month group by a.month;

--另外，也可以直接利用之前的计算结果。比如从之前算好的小时结果中统计每一天的

Insert into table dw_pvs_everyday

Select sum(pvs) as pvs,month,day from dw_pvs_everyhour_oneday group by month,day having day='18';

按终端维度

数据中能够反映出用户终端信息的字段是http_user_agent。

User Agent也简称UA。它是一个特殊字符串头，是一种向访问网站提供所使用的浏览器类型及版本、操作系统及版本、浏览器内核、等信息的标识。例如：

User-Agent,Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.276 Safari/537.36

上述UA信息就可以提取出以下的信息：

chrome 58.0、浏览器chrome、浏览器版本58.0、系统平台windows

浏览器内核webkit

这里不再拓展相关知识，感兴趣的可以查看参考资料如何解析UA。

可以用下面的语句进行试探性统计，当然这样的准确度不是很高。

select distinct(http_user_agent) from ods_weblog_detail where http_user_agent like '%Chrome%' limit 200;

按栏目维度

网站栏目可以理解为网站中内容相关的主题集中。体现在域名上来看就是不同的栏目会有不同的二级目录。比如某网站网址为www.xxxx.cn,旗下栏目可以通过如下方式访问：

栏目维度：../job

栏目维度：../news

栏目维度：../sports

栏目维度：../technology

那么根据用户请求url就可以解析出访问栏目，然后按照栏目进行统计分析。

按referer维度

--统计每小时各来访url产生的pv量

drop table dw_pvs_referer_everyhour;

create table dw_pvs_referer_everyhour(referer_url string,referer_host string,month string,day string,hour string,pv_referer_cnt bigint) partitioned by(datestr string);

insert into table dw_pvs_referer_everyhour partition(datestr='20130918')

select http_referer,ref_host,month,day,hour,count(1) as pv_referer_cnt

from ods_weblog_detail

group by http_referer,ref_host,month,day,hour

having ref_host is not null

order by hour asc,day asc,month asc,pv_referer_cnt desc;

--统计每小时各来访host的产生的pv数并排序

drop table dw_pvs_refererhost_everyhour;

create table dw_pvs_refererhost_everyhour(ref_host string,month string,day string,hour string,ref_host_cnts bigint) partitioned by(datestr string);

insert into table dw_pvs_refererhost_everyhour partition(datestr='20130918')

select ref_host,month,day,hour,count(1) as ref_host_cnts

from ods_weblog_detail

group by ref_host,month,day,hour

having ref_host is not null

order by hour asc,day asc,month asc,ref_host_cnts desc;

注：还可以按来源地域维度、访客终端维度等计算

1.2．人均浏览量

需求描述：统计今日所有来访者平均请求的页面数。

人均浏览量也称作人均浏览页数，该指标可以说明网站对用户的粘性。

人均页面浏览量表示用户某一时段平均浏览页面的次数。

计算方式：总页面请求数/去重总人数

remote_addr表示不同的用户。可以先统计出不同remote_addr的pv量，然后累加（sum）所有pv作为总的页面请求数，再count所有remote_addr作为总的去重总人数。

--总页面请求数/去重总人数

drop table dw_avgpv_user_everyday;

create table dw_avgpv_user_everyday(

day string,

avgpv string);

insert into table dw_avgpv_user_everyday

select '20130918',sum(b.pvs)/count(b.remote_addr) from

(select remote_addr,count(1) as pvs from ods_weblog_detail where datestr='20130918' group by remote_addr) b;

1.3．统计pv总量最大的来源TOPN (分组TOP)

需求描述：统计每小时各来访host的产生的pvs数最多的前N个（topN）。

row_number()函数

语法：row_number() over (partition by xxx order by xxx) rank，rank为分组的别名，相当于新增一个字段为rank。
partition by用于分组，比方说依照sex字段分组
order by用于分组内排序，比方说依照sex分组，组内按照age排序
排好序之后，为每个分组内每一条分组记录从1开始返回一个数字
取组内某个数据，可以使用where 表名.rank>x之类的语法去取

以下语句对每个小时内的来访host次数倒序排序标号:

select ref_host,ref_host_cnts,concat(month,day,hour),

row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od from dw_pvs_refererhost_everyhour;

效果如下：

根据上述row_number的功能，可编写hql取各小时的ref_host访问次数topn

drop table dw_pvs_refhost_topn_everyhour;

create table dw_pvs_refhost_topn_everyhour(

hour string,

toporder string,

ref_host string,

ref_host_cnts string

)partitioned by(datestr string);

insert into table dw_pvs_refhost_topn_everyhour partition(datestr='20130918')

select t.hour,t.od,t.ref_host,t.ref_host_cnts from

(select ref_host,ref_host_cnts,concat(month,day,hour) as hour,

row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od

from dw_pvs_refererhost_everyhour) t where od<=3;

结果如下：

2．受访分析（从页面的角度分析）

2.1．各页面访问统计

主要是针对数据中的request进行统计分析，比如各页面PV ，各页面UV 等。

以上指标无非就是根据页面的字段group by。例如：

--统计各页面pv

select request as request,count(request) as request_counts from

ods_weblog_detail group by request having request is not null order by request_counts desc limit 20;

2.2．热门页面统计

--统计每日最热门的页面top10

drop table dw_hotpages_everyday;

create table dw_hotpages_everyday(day string,url string,pvs string);

insert into table dw_hotpages_everyday

select '20130918',a.request,a.request_counts from

(select request as request,count(request) as request_counts from ods_weblog_detail where datestr='20130918' group by request having request is not null) a

order by a.request_counts desc limit 10;

3．访客分析

3.1．独立访客

需求描述：按照时间维度比如小时来统计独立访客及其产生的pv。

对于独立访客的识别，如果在原始日志中有用户标识，则根据用户标识即很好实现;此处，由于原始日志中并没有用户标识，以访客IP来模拟，技术上是一样的，只是精确度相对较低。

--时间维度：时

drop table dw_user_dstc_ip_h;

create table dw_user_dstc_ip_h(

remote_addr string,

pvs bigint,

hour string);

insert into table dw_user_dstc_ip_h

select remote_addr,count(1) as pvs,concat(month,day,hour) as hour

from ods_weblog_detail

Where datestr='20130918'

group by concat(month,day,hour),remote_addr;

在此结果表之上，可以进一步统计，如每小时独立访客总数：

select count(1) as dstc_ip_cnts,hour from dw_user_dstc_ip_h group by hour;

--时间维度：日

select remote_addr,count(1) as counts,concat(month,day) as day

from ods_weblog_detail

Where datestr='20130918'

group by concat(month,day),remote_addr;

--时间维度：月

select remote_addr,count(1) as counts,month

from ods_weblog_detail

group by month,remote_addr;

3.2．每日新访客

需求：将每天的新访客统计出来。

实现思路：创建一个去重访客累积表，然后将每日访客对比累积表。

--历日去重访客累积表

drop table dw_user_dsct_history;

create table dw_user_dsct_history(

day string,

ip string

)

partitioned by(datestr string);

--每日新访客表

drop table dw_user_new_d;

create table dw_user_new_d (

day string,

ip string

)

partitioned by(datestr string);

--每日新用户插入新访客表

insert into table dw_user_new_d partition(datestr='20130918')

select tmp.day as day,tmp.today_addr as new_ip from

(

select today.day as day,today.remote_addr as today_addr,old.ip as old_addr

from

(select distinct remote_addr as remote_addr,"20130918" as day from ods_weblog_detail where datestr="20130918") today

left outer join

dw_user_dsct_history old

on today.remote_addr=old.ip

) tmp

where tmp.old_addr is null;

--每日新用户追加到累计表

insert into table dw_user_dsct_history partition(datestr='20130918')

select day,ip from dw_user_new_d where datestr='20130918';

验证查看：

select count(distinct remote_addr) from ods_weblog_detail;

select count(1) from dw_user_dsct_history where datestr='20130918';

select count(1) from dw_user_new_d where datestr='20130918';

注：还可以按来源地域维度、访客终端维度等计算

4．访客Visit分析（点击流模型）

4.1．回头/单次访客统计

需求：查询今日所有回头访客及其访问次数。

实现思路：上表中出现次数>1的访客，即回头访客；反之，则为单次访客。

drop table dw_user_returning;

create table dw_user_returning(

day string,

remote_addr string,

acc_cnt string)

partitioned by (datestr string);

insert overwrite table dw_user_returning partition(datestr='20130918')

select tmp.day,tmp.remote_addr,tmp.acc_cnt

from

(select '20130918' as day,remote_addr,count(session) as acc_cnt from ods_click_stream_visit group by remote_addr) tmp

where tmp.acc_cnt>1;

4.2．人均访问频次

需求：统计出每天所有用户访问网站的平均次数（visit）

总visit数/去重总用户数

select count(pagevisits)/count(distinct remote_addr) from ods_click_stream_visit where datestr='20130918';

5．关键路径转化率分析（漏斗模型）

5.1．需求分析

转化：在一条指定的业务流程中，各个步骤的完成人数及相对上一个步骤的百分比。

5.2．模型设计

定义好业务流程中的页面标识，下例中的步骤为：

Step1、 /item

Step2、 /category

Step3、 /index

Step4、 /order

5.3．开发实现

l 查询每一个步骤的总访问人数

--查询每一步人数存入dw_oute_numbs

create table dw_oute_numbs as

select 'step1' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20130920' and request like '/item%'

union

select 'step2' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20130920' and request like '/category%'

union

select 'step3' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20130920' and request like '/order%'

union

select 'step4' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20130920' and request like '/index%';

注：UNION将多个SELECT语句的结果集合并为一个独立的结果集。

l 查询每一步骤相对于路径起点人数的比例

思路：级联查询，利用自join

--dw_oute_numbs跟自己join

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr;

--每一步的人数/第一步的人数==每一步相对起点人数比例

select tmp.rnstep,tmp.rnnumbs/tmp.rrnumbs as ratio

from

(

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr) tmp

where tmp.rrstep='step1';

l 查询每一步骤相对于上一步骤的漏出率

--自join表过滤出每一步跟上一步的记录

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr

where cast(substr(rn.step,5,1) as int)=cast(substr(rr.step,5,1) as int)-1;

select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as leakage_rate

from

(

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr) tmp

where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1;

l 汇总以上两种指标

select abs.step,abs.numbs,abs.rate as abs_ratio,rel.rate as leakage_rate

from

(

select tmp.rnstep as step,tmp.rnnumbs as numbs,tmp.rnnumbs/tmp.rrnumbs as rate

from

(

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr) tmp

where tmp.rrstep='step1'

) abs

left outer join

(

select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as rate

from

(

select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn

inner join

dw_oute_numbs rr) tmp

where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1

) rel

on abs.step=rel.step;

五、模块开发----结果导出

为了将我们计算出来的数据通过报表的形式展现到前台页面上去，我们可以通过sqoop将我们计算后的数据导出到关系型数据库mysql当中去（通常计算之后的数据量一般都不会太大，可以考虑使用关系型数据库的方式来做我们的报表展现，如果统计之后的数据量仍然很大，那么就应该考虑使用大数据的技术来实现我们数据的展现）

这里选择几张hive表进行导出，其他的所有的导出基本上都是一样

1．第一步：创建mysql数据库以及对应的数据库表

SQLyog Ultimate v8.32

MySQL - 5.6.22-log : Database - weblog

*********************************************************************

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`weblog` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `weblog`;

/*Table structure for table `dw_pvs_everyday` */

DROP TABLE IF EXISTS `dw_pvs_everyday`;

CREATE TABLE `dw_pvs_everyday` (

`pvs` varchar(32) DEFAULT NULL,

`month` varchar(16) DEFAULT NULL,

`day` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `dw_pvs_everyhour_oneday` */

DROP TABLE IF EXISTS `dw_pvs_everyhour_oneday`;

CREATE TABLE `dw_pvs_everyhour_oneday` (

`month` varchar(32) DEFAULT NULL,

`day` varchar(32) DEFAULT NULL,

`hour` varchar(32) DEFAULT NULL,

`pvs` varchar(32) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `dw_pvs_referer_everyhour` */

DROP TABLE IF EXISTS `dw_pvs_referer_everyhour`;

CREATE TABLE `dw_pvs_referer_everyhour` (

`refer_url` varchar(2048) DEFAULT NULL,

`referer_host` varchar(64) DEFAULT NULL,

`month` varchar(32) DEFAULT NULL,

`day` varchar(32) DEFAULT NULL,

`hour` varchar(32) DEFAULT NULL,

`pv_referer_cnt` varchar(32) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2．第二步：通过sqoop命令来进行导出

/export/servers/sqoop-1.4.6-cdh5.14.0/bin/sqoop export --connect jdbc:mysql://192.168.1.106:3306/weblog --username root --password admin --m 1 --export-dir /user/hive/warehouse/weblog.db/dw_pvs_everyday --table dw_pvs_everyday --input-fields-terminated-by '\001'

/export/servers/sqoop-1.4.6-cdh5.14.0/bin/sqoop export --connect jdbc:mysql://192.168.1.106:3306/weblog --username root --password admin --m 1 --export-dir /user/hive/warehouse/weblog.db/dw_pvs_everyhour_oneday/datestr=20130918 --table dw_pvs_everyhour_oneday --input-fields-terminated-by '\001'

/export/servers/sqoop-1.4.6-cdh5.14.0/bin/sqoop export --connect jdbc:mysql://192.168.1.106:3306/weblog --username root --password admin --m 1 --export-dir /user/hive/warehouse/weblog.db/dw_pvs_referer_everyhour/datestr=20130918 --table dw_pvs_referer_everyhour --input-fields-terminated-by '\001'

六、模块开发----工作流调度

整个项目的数据按照处理过程，从数据采集到数据分析，再到结果数据的导出，一系列的任务可以分割成若干个azkaban的job单元，然后由工作流调度器调度执行。

调度脚本的编写难点在于shell脚本。但是一般都是有固定编写模式。大家可以参考资料中的脚本进行编写。

第一步：开发我们的DateUtil工具类

开发我们的DateUtil工具类，用于获取前一天的时间

public class DateUtil {
    /**
     * 获取昨日的日期
     * @return
     */
    public static String getYestDate(){
        Calendar instance = Calendar.getInstance();
        instance.add(Calendar.DATE,-1);
        Date time = instance.getTime();
        String format = new SimpleDateFormat("yyyy-MM-dd").format(time);
        return format;
    }
    public static void main(String[] args) {
        getYestDate();
    }
}

第二步：定义我们的数据每日上传目录

定义我们的文件每日上传目录，并将我们的数据上传到对应的目录下面去

hdfs dfs -mkdir -p /weblog/20180205/input

hdfs dfs -put access.log.fensi /weblog/20180205/input

第三步：根据文件上传目录，改造MR程序

改造WebLogProcessor程序

String inputPath= "hdfs://node01:8020/weblog/"+DateUtil.getYestDate()+"/input";
String outputPath="hdfs://node01:8020/weblog/"+DateUtil.getYestDate()+"/weblogPreOut";
FileSystem fileSystem = FileSystem.get(new URI("hdfs://node01:8020"), conf);
if (fileSystem.exists(new Path(outputPath))){
fileSystem.delete(new Path(outputPath),true);
}

FileInputFormat.setInputPaths(job, new Path(inputPath));
FileOutputFormat.setOutputPath(job, new Path(outputPath));

改造ClickStreamPageView

String inputPath="hdfs://node01:8020/weblog/"+DateUtil.getYestDate()+"/weblogPreOut";
String outputPath="hdfs://node01: 8020/weblog/"+DateUtil.getYestDate()+"/pageViewOut";
FileSystem fileSystem = FileSystem.get(new URI("hdfs://node01: 8020"), conf);
if (fileSystem.exists(new Path(outputPath))){
fileSystem.delete(new Path(outputPath),true);
}

FileInputFormat.setInputPaths(job, new Path(inputPath));
FileOutputFormat.setOutputPath(job, new Path(outputPath));

改造ClickStreamVisit

String inputPath = "hdfs://node01: 8020/weblog/"+ DateUtil.getYestDate() + "/pageViewOut";
String outPutPath="hdfs://node01: 8020/weblog/"+ DateUtil.getYestDate() + "/clickStreamVisit";
FileSystem fileSystem = FileSystem.get(new URI("hdfs://node01: 8020"),conf);
if (fileSystem.exists(new Path(outPutPath))){
fileSystem.delete(new Path(outPutPath),true);
}

FileInputFormat.setInputPaths(job, new Path(inputPath));
FileOutputFormat.setOutputPath(job, new Path(outPutPath));

第四步：将程序打成jar包

第五步：开发azkaban调度脚本

程序调度一共分为以下步骤：

第一步：第一个MR程序执行

第二步：第二个MR程序执行

第三步：第三个MR程序执行

第四步：hive表数据加载

第五步：hive表数据分析

第六步：分析结果通过sqoop导出

第六步：定时执行

定于每天晚上两点钟定时开始执行任务

0 2 ? * *

七、模块开发----数据可视化

1． Echarts介绍

ECharts是一款由百度前端技术部开发的，基于Javascript的数据可视化图表库，提供直观，生动，可交互，可个性化定制的数据可视化图表。

提供大量常用的数据可视化图表，底层基于ZRender（一个全新的轻量级canvas类库），创建了坐标系，图例，提示，工具箱等基础组件，并在此上构建出折线图（区域图）、柱状图（条状图）、散点图（气泡图）、饼图（环形图）、K线图、地图、力导向布局图以及和弦图，同时支持任意维度的堆积和多图表混合展现。

2． Web程序工程结构

本项目是个纯粹的JavaEE项目，基于ssm的框架整合构建。使用maven的tomcat插件启动项目。

3．感受Echarts—简单入门

3.1．下载Echarts

从官网下载界面选择你需要的版本下载，根据开发者功能和体积上的需求，提供了不同打包的下载，如果在体积上没有要求，可以直接下载完整版本。开发环境建议下载源代码版本，包含了常见的错误提示和警告。

3.2．页面引入Echarts

ECharts 3 开始只需要像普通的 JavaScript 库一样用 script 标签引入即可。

<!DOCTYPE html>

<html>

<head>

</head>

</html>

3.3．绘制一个简单的图表

在绘图前我们需要为 ECharts 准备一个具备高宽的 DOM 容器：

<body>

</body>

然后就可以通过 echarts.init 方法初始化一个 echarts 实例并通过 setOption 方法生成一个简单的柱状图，下面是完整代码。

<!DOCTYPE html>

<html>

<head>

<title>ECharts</title>

</head>

<body>

// 基于准备好的dom，初始化echarts实例

var myChart = echarts.init(document.getElementById('main'));

// 指定图表的配置项和数据

var option = {

title: {

text: 'ECharts 入门示例'

tooltip: {},

legend: {

data:['销量']

xAxis: {

data: ["衬衫","羊毛衫","雪纺衫","裤子","高跟鞋","袜子"]

yAxis: {},

series: [{

name: '销量',

type: 'bar',

data: [5, 20, 36, 10, 10, 20]

}]

};

// 使用刚指定的配置项和数据显示图表。

myChart.setOption(option);

</script>

</body>

</html>

不出意外的话你就可以看见如下的图表：

4、三大框架环境搭建

第一步：创建数据库并导入数据

SQLyog Ultimate v8.32

MySQL - 5.6.22-log : Database - web_log_view

*********************************************************************

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`web_log_view` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `web_log_view`;

/*Table structure for table `t_avgpv_num` */

DROP TABLE IF EXISTS `t_avgpv_num`;

CREATE TABLE `t_avgpv_num` (

`id` int(11) DEFAULT NULL,

`dateStr` varchar(255) DEFAULT NULL,

`avgPvNum` decimal(6,2) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `t_avgpv_num` */

insert into `t_avgpv_num`(`id`,`dateStr`,`avgPvNum`) values (1,'20130919','13.40'),(2,'20130920','17.60'),(3,'20130921','15.20'),(4,'20130922','21.10'),(5,'20130923','16.90'),(6,'20130924','18.10'),(7,'20130925','18.60');

/*Table structure for table `t_flow_num` */

DROP TABLE IF EXISTS `t_flow_num`;

CREATE TABLE `t_flow_num` (

`id` int(11) DEFAULT NULL,

`dateStr` varchar(255) DEFAULT NULL,

`pVNum` int(11) DEFAULT NULL,

`uVNum` int(11) DEFAULT NULL,

`iPNum` int(11) DEFAULT NULL,

`newUvNum` int(11) DEFAULT NULL,

`visitNum` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `t_flow_num` */

insert into `t_flow_num`(`id`,`dateStr`,`pVNum`,`uVNum`,`iPNum`,`newUvNum`,`visitNum`) values (1,'20131001',4702,3096,2880,2506,3773),(2,'20131002',7528,4860,4435,4209,5937),(3,'20131003',7286,4741,4409,4026,5817),(4,'20131004',6653,5102,4900,2305,4659),(5,'20131005',5957,4943,4563,3134,3698),(6,'20131006',7978,6567,6063,4417,4560),(7,'20131007',6666,5555,4444,3333,3232);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;