数仓的设计
维度建模的基本概念:
维度表:
用于描述环境的维度表,单一主键。维度表的属性是所有查询约束和报表标示的来源。维度提供数据的入口点,提供所有DW/BI分析的最终标识和分组。
所以维度建模表示每个业务过程包含的事实表,事实表里面存储事件的数值化度量,围绕事实表的是多个维度表,维度表包含事件发生的实际存在的文本环境。
事实表:
用于度量的事实表,事实表一般会有两个或者多个外健与维度表的主键进行关联。事实表的主键一般是组合健,表达多对多的关系。
维度建模的三种方式:
星型模型:以事实表为依据,周围很多维度表
例如: 订单的分析:用户 货运id 商品id
雪花模型:以事实表为依据 ,很多维度表围绕其中,然后维度表还可能有很多子维度
星座模式:多个事实表有可能会公用一些维度表,最常见的就是我们的省市区的公用
实际中大部分时候使用星座表
原始数据的拆分
以原始数据表ods_weblog_origin(对应MR清洗后的数据表)为例,需要建立数据宽表
所需要的信息字段:
valid string 是否有效
remote_addr string 访客ip
remote_user string 访客用户信息
time_local string 请求时间 2018-11-20 15:23:25
request string 请求url
status string 响应码
body_bytes_sent string 响应字节数
http_referer string 来源url
http_user_agent string 访客终端信息
首先建立ods_weblog_origin表
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';
创建点击流pageview表:
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';
创建点击流visit表
drop table if exists 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';
将清洗后的数据导入表中:
设置hive本地模式运行
set hive.exec.mode.local.auto=true;
导入清洗结果数据到贴源数据表ods_weblog_origin
load data local inpath '/export/servers/weblog/webout' overwrite into table ods_weblog_origin partition(datestr='20130918');
show partitions ods_weblog_origin;
select count(*) from ods_weblog_origin;
导入点击流模型pageviews数据到ods_click_pageviews表
load data local inpath '/export/servers/weblog/views' overwrite into table ods_click_pageviews partition(datestr='20130918');
select count(*) from ods_click_pageviews;
导入点击流模型visit数据到ods_click_stream_visit表
load data local inpath '/export/servers/weblog/visits' overwrite into table ods_click_stream_visit partition(datestr='20130918');
建立明细宽表:
明细宽表 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