13 数据仓库的设计与ETL开发

13 数据仓库的设计与ETL开发

1.数据仓库的设计

一、维度建模的基本概念

维度表：

时间的维度：昨天   
地点：星巴克   
金钱的维度：两百块 
维度表看到的事情比较狭窄，仅仅从某一个方面来看，只能看得到某一块的东西

事实表：

昨天我去星巴克喝了一杯咖啡，花了两百块
（没发生的东西，一定不是事实，事实一定是建立在已经发生过的事情上面）

二、维度建模的三种方式

星型模型：以事实表为依据，周围很多维度表
订单的分析：用户 货运id 商品id

雪花模型：以事实表为依据 ，很多维度表围绕其中，然后维度表还可能有很多子维度

星座模式：多个事实表有可能会公用一些维度表，最常见的就是我们的省市区的公用

三、本项目中数据仓库的设计

事实表设计

求统计 15:00:00 16:00:00访问了多少个页面

select count(1) from   ods_weblog_origin  where  time_local  >=   15:00:00 and time_local  <= 16:00:00
union all 
select count(1) from   ods_weblog_origin  where  time_local  >=   16:00:00 and time_local  <= 17:00:00

第一步：按照小时进行分组 15  16  17 
第二步：分组之后，统计每组里面有多少天记录
select  count(1) from  ods_weblog_origin  group  by hour

为了方便我们的统计，将我们的日期字段给拆成这样的几个字段
将我们的ods_weblog_origin 这个表给拆开，拆我们的时间字段
daystr
timestr
month
day
hour

http_referer  http://www.baidu.com/hello.action?username=zhangsan  http://www.google.com?address=北京   http://www.sougou.com?money=50

ref_host   www.baidu.com
ref_path   /hello.action
ref_query   username
ref_query_id  zhangsan


www.baidu.com
www.google.com
www.sougou.com

维度表设计

注意：

维度表的数据一般要结合业务情况自己写脚本按照规则生成，也可以使用工具生成，方便后续的关联分析。
比如一般会事前生成时间维度表中的数据，跨度从业务需要的日期到当前日期即可.具体根据你的
分析粒度,可以生成年，季，月，周，天，时等相关信息，用于分析。

2.数据仓库ETL开发

1、ods层建表语句

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

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

2、ods数据导入

设置hive本地模式运行

set hive.exec.mode.local.auto=true;

导入清洗结果数据到贴源数据表ods_weblog_origin

load data local inpath '/export/hivedatas/weblog' overwrite into table ods_weblog_origin partition(datestr='20200918');
show partitions ods_weblog_origin;
select count(*) from ods_weblog_origin;

导入点击流模型pageviews数据到ods_click_pageviews表

load data local inpath '/export/hivedatas/pageview' overwrite into table ods_click_pageviews partition(datestr='20200918');

导入点击流模型visit数据到ods_click_stream_visit表

load data local inpath '/export/hivedatas/visit' overwrite into table ods_click_stream_visit partition(datestr='20200918');

3、ods层明细宽表

建表——明细宽表 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);

通过查询插入数据到明细宽表 ods_weblog_detail中
分步：
–抽取refer_url到中间表 t_ods_tmp_referurl
–也就是将来访url分离出host path query query id

drop table if exists t_ods_tmp_referurl;
create table t_ods_tmp_referurl as
SELECT 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; 

–抽取转换time_local字段到中间表明细表 t_ods_tmp_detail

2013-09-18 06:49:18
drop 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(t