数仓设计

维度建模

以维度为标准 开展数据的分析需求
适用于面向分析领域的理论。比如分析型数据库 数据仓库 数据集市（OLAP）

• 事实表

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

• 维度表

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

本项目中数据仓库的设计

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

事实表设计

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

访问日志明细宽表：dw_weblog_detail

维度表设计

多维度数据分析

所谓的多维数据分析就是指通过不同维度的聚集计算出某种度量值。
常见度量值：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）

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