目录
数仓设计
维度建模
以维度为标准 开展数据的分析需求
适用于面向分析领域的理论。比如分析型数据库 数据仓库 数据集市(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