通过日志分析mysql访问量,网站流量日志分析（模块开发——数据仓库设计）

目录

数仓设计

维度建模

以维度为标准 开展数据的分析需求

适用于面向分析领域的理论。比如分析型数据库 数据仓库 数据集市(OLAP)

事实表

分析主题的客观事件度量 是分析主题的数据聚集 事实表中一条记录往往对应着客观的一个事件

往往是一堆主键的聚集

维度表

所谓的维度就是指看待问题的角度 可以通过不同的维度去分析同一个事实表 得出不同的分析结果

维度表可以跟事实表进行关联查询

本项目中数据仓库的设计

本项目中采用星型模型，事实表就是网站流量日志维度取决于业务。

事实表设计

原始数据表: ods_weblog_origin =>对应mr清洗完之后的数据

字段

数据类型

注解

valid

string

是否有效

remote_ip

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_ip

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

客户终端标识

维度表设计

多维度数据分析

所谓的多维数据分析就是指通过不同维度的聚集计算出某种度量值。

常见度量值：max min count sum avg topN

举个栗子：统计来自于北京女性24岁未婚的过去三年购物金额最多的前三个。

维度：地域 性别 年龄 婚姻 时间

度量值：sum(订单金额)--->top3

维度建模的三种模式

星型模式一个事实表多个维度表 维度表之间没有关系 维度表跟事实表进行关联 企业数仓发展初期常见的模型

雪花模式一个事实表多个维度表 维度表可以继续关联维度表 不利于后期维护 企业中尽量避免演化成该种模型

星座模式多个事实表 多个维度表 某些维度表可以共用 企业数仓发展中后期常见的模型

本项目模式设计

因为项目中只有一个分析的主题：网站的流量日志情况 因此对应的事实表只有一个：网站流量日志

本项目采用星型模式

事实表：对应着数据预处理完之后的原始网站日志情况

维度表：通常要结合业务决定分析的维度 要和事实表能够关联上 要以能够涵盖事实表为基本标准

Q:问点击流模型数据算什么类型的表？

点击流模型数据既不是事实表 也不是维度表 是一个业务模型数据 可以称之为事实表的业务延伸。

数据入库ETL

创建ODS层表

表名通常以简短的英文表示，不用使用汉语拼音甚至中文

建表的时候表的字段顺序类型要和数据保持一致

通常企业中采用分区表进行优化，方便后续查询管理

启动 hive 连接工具 beeline

service mysqld start

nohup bin/hive --service hiveserver2 > /dev/null 2>&1

bin/beeline

!connect jdbc:hive2://node03:10000

建数据库和实体表

# 原始数据表

对应mr清洗完之后的数据，而不是原始日志数据

drop table if exists ods_weblog_origin;

create table ods_weblog_origin(

valid string,

remote_ip 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';

# 点击流pageview表

drop table if exists ods_click_pageviews;

create table ods_click_pageviews(

session string,

remote_ip 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';

# 点击流visit表

drop table if exists ods_click_stream_visit;

create table ods_click_stream_visit(

session string,

remote_ip string,

inTime string,

outTime string,

inPage string,

outPage string,

referal string,

pageVisits int)

partitioned by (datestr string)

row format delimited

fields terminated by '\001';

# 时间维度表

drop table if exists t_dim_time;

create table t_dim_time(date_key int,year string,month string,day string,hour string) row format delimited fields terminated by ',';

导入ODS层表数据

原始日志表

load data local inpath '/opt/data/sql/part-m-00000' into table ods_weblog_origin partition(datestr='20181101');

点击流模型之pageviews

load data local inpath '/opt/data/sql/part-r-00000' into table ods_click_pageviews partition(datestr='20181101');

点击流模型之visit

load data local inpath '/opt/data/sql/part-r-00000' into table ods_click_stream_visit partition(datestr='20181101');

时间维度表数据

load data local inpath '/opt/data/sql/dim_time.dat' into table t_dim_time;

宽表窄表的引入

--需求：统计今天每个小时访问量有多少？

--需要根据小时hour进行分组 group by 分组之后统计每个组内的个数count

--当下：group by(substring(time_local,12,2))

--缺点：每一条记录在分组之前 都需要进行所谓的截取操作

--原因：表中的某些字段看似一个字段 实则糅杂了多个属性在一起

--解决：把糅合在一起的属性拆分出来变成单独独立的新字段 hour

group by(hour)

--结果：因为表的字段相比较之前变多了 称之为宽表。原来对应的表称之为窄表。又因为变宽之后信息更加详细具体，所以也可以称之为明细表。

宽表的实现

宽表的数据由何而来

由窄表数据得到 所谓窄表就是原始数据表

insert into 宽 + select from 窄

宽表需要扩宽哪些字段

跟业务需求相关，本项目中进行两个字段的扩宽

时间字段：time_local

来访字段：http_referer

使用什么技术进行字段的扩宽

insert into 宽 + select from 窄

至于插入什么样的数据完全取决于查询语句返回的结果。

因此在查询的时候就需要使用hive的函数进行字段的扩宽操作。

时间字段的拓宽：substring(time_local)

来源url字段拓宽：hive内置的解析url函数 是一个标准的udtf函数 parse_url_tuple

分析指标和模型

基础级指标

例子：

张三今天上午来到网站打开3个页面 下午来到网站打开2个页面 晚上又来到网站打开5个页面

问：pv uv vv ip？

pv:页面加载总次数 10

uv:独立访客数 1

vv:会话次数 3

ip: 1

复合指标

平均访问频度：一天之内人均会话数

==总的会话次数(session)/总的独立访客数

==vv/uv

平均访问深度：一天之内人均浏览页面数

==总的页面浏览数/总的独立访客数

==pv/uv

平均会话时长:平均每次会话停留的时间

=总的会话停留时间/会话次数(vv)

首页跳出率=访问网站且访问一个页面且该页面是首页/总的访问次数

标签：string,ods,数据仓库,模块,time,维度,table,日志,local

来源： https://www.cnblogs.com/alidata/p/13470212.html