目录
本节续写自第十八节
数据仓库设计
维度建模基本概念
维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的方法。数据集市可以理解为是一种"小型数据仓库"。
- 维度表(dimension)
维度表示你要对数据进行分析时所用的一个量,比如你要分析产品销售情况, 你可以选择按类别来进行分析,或按区域来分析。这样的按…分析就构成一个维度。再比如"昨天下午我在星巴克花费200元喝了一杯卡布奇诺"。那么以消费为主题进行分析,可从这段信息中提取三个维度:时间维度(昨天下午),地点维度(星巴克), 商品维度(卡布奇诺)。通常来说维度表信息比较固定,且数据量小。 - 事实表(fact table)
表示对分析主题的度量。事实表包含了与各维度表相关联的外键,并通过JOIN方式与维度表关联。事实表的度量通常是数值类型,且记录数会不断增加,表规模迅速增长。比如上面的消费例子,它的消费事实表结构示例如下:
消费事实表:Prod_id(引用商品维度表), TimeKey(引用时间维度表), Place_id(引用地点维度表), Unit(销售量)。
总的说来,在数据仓库中不需要严格遵守规范化设计原则。因为数据仓库的主导功能就是面向分析,以查询为主,不涉及数据更新操作。事实表的设计是以能够正确记录历史信息为准则,维度表的设计是以能够以合适的角度来聚合主题内容为准则。
维度建模三种模式
- 星型模式
星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心,所有的维度表直接连接在事实表上,像星星一样。
星形模式的维度建模由一个事实表和一组维表成,且具有以下特点:
a. 维表只和事实表关联,维表之间没有关联;
b. 每个维表主键为单列,且该主键放置在事实表中,作为两边连接的外键;
c. 以事实表为核心,维表围绕核心呈星形分布;
- 雪花模式
雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的,虽然这种模型相比星型更规范一些,但是由于这种模型不太容易理解,维护成本比较高,而且性能方面需要关联多层维表,性能也比星型模型要低。所以一般不是很常用。
- 星座模式
星座模式是星型模式延伸而来,星型模式是基于一张事实表的,而星座模式是基于多张事实表的,而且共享维度信息。
前面介绍的两种维度建模方法都是多维表对应单事实表,但在很多时候维度空间内的事实表不止一个,而一个维表也可能被多个事实表用到。在业务发展后期,绝大部分维度建模都采用的是星座模式。
网站日志数据仓库建立及分析案例
本项目中数据仓库的设计采用星型模型
事实表设计
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 | 访客终端信息 |
2、访问日志明细宽表:dw_weblog_detail(将ods_weblog_origin的部分字段裂开为多个字段,以获取更多的细分字段)
字段名 | 字段类型 | 字段含义 |
---|---|---|
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 | 客户终端标识 |
模块开发----ETL
ETL工作的实质就是从各个数据源提取数据,对数据进行转换,并最终加载填充数据到数据仓库维度建模后的表中。只有当这些维度/事实表被填充好,ETL工作才算完成。
本项目的数据分析过程在hadoop集群上实现,主要应用hive数据仓库工具,因此,采集并经过预处理后的数据,需要加载到hive数据仓库中,以进行后续的分析过程。
创建ODS层数据表
1、原始日志数据表
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';
2、创建点击流模型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';
3、创建点击流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';
4、导入ODS层数据
load data inpath '/weblog/weblog' overwrite into table
ods_weblog_origin partition(datestr='20130918');
load data inpath '/weblog/pageview' overwrite into table
ods_click_pageviews partition(datestr='20130918');
load data inpath '/weblog/visit' overwrite into table
ods_click_stream_visit partition(datestr='20130918');
生成基于ods_weblog_origin的宽表
1、创建明细表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);
2、基于源url字段http_referer
解析出ref_host、ref_path、ref_query、ref_query_id
4个字段
需要使用hive自带函数parse_url_tuple
来解析http_referer,然后利用LATERAL VIEW
将解析出字段拼接到原来的表上,这样就获得了具有ref_host、ref_path、ref_query、ref_que