hadoop项目--网站流量日志分析--3

一、 模块开发-数据仓库设计1 维度建模基本概念维度模型是数据仓库领域大师Ralph Kimall所倡导，他的数据仓库工具箱，是数据仓库工程领域最流行的数仓建模经典。维度建模以分析决策的需求出发构建模型，构建的数据模型为分析需求服务，因此它重点解决用户如何更快速完成分析需求，同时还有较好的大规模复杂查询的响应性能。维度建模是专门应用于分析型数据库、数据仓库、数据集市建模的方法。数据集市可以理解为是一种小型数据仓库。1.1 事实表发生在现实世界中的操作型事件，其所产生的可度量数值，存储在事实表中。从最低的粒度级别来看，事实表行对应一个度量事件，反之亦然。事实表表示对分析主题的度量。比如一次购买行为我们就可以理解为是一个事实。图中的订单表就是一个事实表，你可以理解他就是在现实中发生的一次操作型事件，我们每完成一个订单，就会在订单中增加一条记录。事实表的特征：表里没有存放实际的内容，他是一堆主键的集合，这些ID分别能对应到维度表中的一条记录。事实表包含了与各维度表相关联的外键，可与维度表关联。事实表的度量通常是数值类型，且记录数会不断增加，表数据规模迅速增长。1.2 维度表每个维度表都包含单一的主键列。维度表的主键可以作为与之关联的任何事实表的外键，当然，维度表行的描述环境应与事实表行完全对应。维度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性。维度表示你要对数据进行分析时所用的一个量,比如你要分析产品销售情况, 你可以选择按类别来进行分析,或按区域来分析。这样的按.分析就构成一个维度。上图中的用户表、商家表、时间表这些都属于维度表，这些表都有一个唯一的主键，然后在表中存放了详细的数据信息。总的说来，在数据仓库中不需要严格遵守规范化设计原则。因为数据仓库的主导功能就是面向分析，以查询为主，不涉及数据更新操作。事实表的设计是以能够正确记录历史信息为准则，维度表的设计是以能够以合适的角度来聚合主题内容为准则。2 维度建模三种模式2.1 星型模式星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。星形模式的维度建模由一个事实表和一组维表成，且具有以下特点： a. 维表只和事实表关联，维表之间没有关联； b. 每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；c. 以事实表为核心，维表围绕核心呈星形分布；2.2 雪花模式雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的，虽然这种模型相比星型更规范一些，但是由于这种模型不太容易理解，维护成本比较高，而且性能方面需要关联多层维表，性能也比星型模型要低。所以一般不是很常用。2.3 星座模式星座模式是星型模式延伸而来，星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息。前面介绍的两种维度建模方法都是多维表对应单事实表，但在很多时候维度空间内的事实表不止一个，而一个维表也可能被多个事实表用到。在业务发展后期，绝大部分维度建模都采用的是星座模式。3 本项目中数据仓库的设计本项目中采用星型模型，事实表就是网站流量日志，维度表取决于业务。3.1 事实表设计原始数据表: ods_weblog_origin =对应mr清洗完之后的数据validstring是否有效remote_addrstring访客ipremote_userstring访客用户信息time_localstring请求时间requeststring请求urlstatusstring响应码body_bytes_sentstring响应字节数http_refererstring来源urlhttp_user_agentstring访客终端信息访问日志明细宽表：dw_weblog_detailvalidstring是否有效remote_addrstring访客ipremote_userstring访客用户信息time_localstring请求完整时间daystrstring访问日期timestrstring访问时间monthstring访问月daystring访问日hourstring访问时requeststring请求url整串statusstring响应码body_bytes_sentstring响应字节数http_refererstring来源urlref_hoststring来源的hostref_pathstring来源的路径ref_querystring来源参数queryref_query_idstring来源参数query值http_user_agentstring客户终端标识3.2 维度表设计 时间维度 t_dim_timedate_Keyyearmonthdayhour访客地域维度t_dim_areaarea_ID北京上海广州深圳终端类型维度t_dim_terminationucfirefoxchromesafariiosandroid网站栏目维度 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 delimitedfields 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 delimitedfields 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 delimitedfields terminated by 001;2 导入ODS层数据load data inpath /weblog/preprocessed/ overwrite into tableods_weblog_origin partition(datestr=20181101);-数据导入show partitions ods_weblog_origin;-查看分区select count(*) from ods_weblog_origin; -统计导入的数据总数点击流模型的两张表数据导入操作同上。注：生产环境中应该将数据load命令，写在脚本中，然后配置在azkaban中定时运行，注意运行的时间点，应该在预处理数据完成之后。3 明细表、宽表、窄表3.1 概念事实表的数据中，有些属性共同组成了一个字段（糅合在一起），比如年月日时分秒构成了时间,当需要根据某一属性进行分组统计的时候，需要截取拼接之类的操作，效率极低。为了分析方便，可以事实表中的一个字段切割提取多个属性出来构成新的字段，因为字段变多了，所以称为宽表，原来的成为窄表。又因为宽表的信息更加清晰明细，所以也可以称之为明细表。3.2 明细表（宽表）实现 建明细表dw_weblog_detail:drop table dw_weblog_detail;create table dw_weblog_detail(valid string, -有效标识remote_addr string, -来源IPremote_user string, -用户标识time_local string, -访问完整时间daystr string, -访问日期timestr string, -访问时间month string, -访问月day string, -访问日hour string, -访问时request string, -请求的urlstatus string, -响应码body_bytes_sent string, -传输字节数http_referer string, -来源urlref_host string, -来源的hostref_path string, -来源的路径ref_query string, -来源参数queryref_query_id string, -来源参数query的值http_user_agent string -客户终端标识)partitioned by(datestr string); 通过查询插入数据到明细宽表 dw_weblog_detail中1、 抽取refer_url到中间表 t_ods_tmp_referurl也就是将来访url分离出host path query query iddrop table if exists t_ods_tmp_referurl;create table t_ods_tmp_referurl asSELECT 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_detaildrop 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,12,2) as hourfrom t_ods_tmp_referurl b;3、以上语句可以合成一个总的语句insert into table shizhan.dw_weblog_detail partition(datestr=20181101)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,12,2) 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_agentfrom(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;三、 流量分析常见分类指标是网站分析的基础，用来记录和衡量访问者在网站自的各种行为。比如我们经常说的流量就是一个网站指标，它是用来衡量网站获得的访问量。在进行流量分析之前，我们先来了解一些常见的指标。1 基础级指标PageView浏览次数（PV）:用户每打开1个网站页面，记录1个PV。用户多次打开同一页面PV累计多次。通俗解释就是页面被加载的总次数。Unique Visitor独立访客（UV）: 1天之内，访问网站的不重复用户数（以浏览器cookie为依据），一天内同一访客多次访问网站只被计算1次。访问次数（VV）：访客从进入网站到离开网站的一系列活动记为一次访问，也称会话(session),1次访问(会话)可能包含多个PV。IP：1天之内，访问网站的不重复IP数。一天内相同IP地址多次访问网站只被计算1次。曾经IP指标可以用来表示用户访问身份，目前则更多的用来获取访问者的地理位置信息。2 复合级指标平均访问频度: 平均每个独立访客一天内访问网站的次数（产生的session个数）, 平均访问频度=访问次数/独立访客数人均浏览页数（平均访问深度）：平均每个独立访客产生的浏览次数。人均浏览页数=浏览次数/独立访客。平均访问时长：平均每次访问（会话）在网站上的停留时间。体现网站对访客的吸引程度。平均访问时长=访问总时长/访问次数。跳出率: 跳出率是指用户到达你的网站上并在你的网站上仅浏览了一个页面就离开的访问次数与所有访问次数的百分比。是评价网站性能的重要指标。3 分析模型3.1 基础分析（PV,IP,UV）对比分析：根据选定的两个对比时段，提供网站流量在时间上的纵向对比报表，发现网站发展状况、发展规律、流量变化率等。访问明细：提供最近7日的访客访问记录，可按每个PV或每次访问行为（访客的每次会话）显示，并可按照来源、搜索词等条件进行筛选。 通过访问明细，用户可以详细了解网站流量的累计过程，从而为用户快速找出流量变动原因提供最原始、最准确的依据。3.2 来源分析来源分类：提供不同来源形式（直接输入、搜索引擎、其他外部链接、站内来源）、不同来源项引入流量的比例情况。通过精确的量化数据，帮助用户分析什么类型的来路产生的流量多、效果好，进而合理优化推广方案。搜索引擎：提供各搜索引擎以及搜索引擎子产品引入流量的比例情况。搜索词：提供访客通过搜索引擎进入网站所使用的搜索词，以及各搜索词引入流量的特征和分布。帮助用户了解各搜索词引入流量的质量，进而了解访客的兴趣关注点、网站与访客兴趣点的匹配度，为优化SEO（搜索引擎优化）方案及SEM（搜索引擎营销）提词方案提供详细依据。最近7日的访客搜索记录，可按每个PV或每次访问行为（访客的每次会话）显示，并可按照访客类型、地区等条件进行筛选。为您搜索引擎优化提供最详细的原始数据。来源升降榜：提供开通统计后