【数仓项目记录3】数层分层与维度建模

用户行为数据

主要包括页面数据、事件数据、曝光数据、启动数据和错误数据。

页面数据

页面数据主要记录一个页面的用户访问情况，包括访问时间、停留时间、页面路径等信息。

事件数据

事件数据主要记录应用内一个具体操作行为，包括操作类型、操作对象、操作对象描述等信息。

曝光数据

曝光数据主要记录页面所曝光的内容，包括曝光对象，曝光类型等信息。

启动

启动数据记录应用的启动信息。

错误

错误数据记录应用使用过程中的错误信息，包括错误编号及错误信息。

电商常识

SKU是商品的最小单位，SPU是一类商品

业务表，与用户的操作相关，如添加收藏夹、购物车，下单，支付，退单、退款、评价、领优惠券等

后台管理系统面向公司内部开发者，维护商品信息、优惠信息等

业务数据的生成

使用项目提供的jar包生成某一日期下的数据，数据存储到hadoop102中的数据库中

Sqoop

Sqoop是Hadoop和关系数据库服务器之间传送数据的一种工具。它是用来从关系数据库如：MySQL，Oracle到Hadoop的HDFS(导入)，并从Hadoop的文件系统导出数据到关系数据库。
sqoop底层原理：mapreduce，但只有map没有reduce

表同步策略

全量同步策略：

就是每天一份完整的数据，作为一个分区（基于日期分区）
适用于数据量不大，而且每天都会有新数据插入，就输入修改的场景。
例如：编码字典表，品牌表，商品123级分类表，优惠规则表，活动表，活动参与商品表，加购表，商品收藏表，优惠券表，SKU商品表，SPU商品表。

增量同步：

就是每天存储一份增量数据作为一个分区。
适用于数据量大，而且每天只会有新数据插入的场景。（退休表，订单状态表，支付流水表，订单详情表，活动与订单关联表。商品评论表）

新增及变化同步策略：

每日新增及变化，就是存储创建时间和操作时间都是今天的数据。使用场景为表的数据量大，既有新增，又有变化。例如：用户表、订单表、优惠券领用表。

特殊策略：

某些特殊的表，可不遵循上述同步策略，列入某些不会发生变化的表（地区表，省份表，民族表）可以只存一份固定值

数仓分层

数仓理论

范式

范式其实就是建模需要遵循的一定的规则。
目的：降低数据的冗余性
为什么要降低数据冗余性？
（1）十几年前，磁盘很贵，为了减少磁盘存储。
（2）以前没有分布式系统，都是单机，只能增加磁盘，磁盘个数也是有限的
（3）一次修改，需要修改多个表，很难保证数据一致性
缺点：范式的缺点是获取数据时，需要通过Join拼接出最后的数据。
目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。

三范式区分

1.属性不可分割

2.不能存在（非主键）部分依赖（主键）

3.不能存在（非主键）传递依赖（主键）

关系建模

关系建模将复杂的数据抽象为两个概念——实体和关系，并使用规范化的方式表示出来。主要应用于业务中的关系型数据库中。
由于三范式的存在，表的数量会比较多，表与表之间的关系较为复杂。
数据冗余程度低，数据的一致性容易得到保证。由于数据分布于众多的表中，查询会相对复杂，在大数据的场景下，查询效率相对较低。

维度建模

当前时代采用的主要建模方式：维度建模
维度模型以数据分析作为出发点，不遵循三范式，故数据存在一定的冗余。维度模型面向业务，将业务用事实表和维度表呈现出来。表结构简单，故查询简单，查询效率较高。

维度表和事实表（重点）

维度表：一般是对事实的描述信息。每一张维表对应现实世界中的一个对象或者概念。 例如：用户、商品、日期、地区等。
维表的特征：
维表的范围很宽（具有多个属性、列比较多）
跟事实表相比，行数相对较小：通常< 10万条
内容相对固定：编码表

事实表中的每行数据代表一个业务事件（下单、支付、退款、评价等）。“事实”这个术语表示的是业务事件的度量值（可统计次数、个数、金额等），例如，2020年5月21日，宋宋老师在京东花了250块钱买了一瓶海狗人参丸。维度表：时间、用户、商品、商家。事实表：250块钱、一瓶
每一个事实表的行包括：具有可加性的数值型的度量值、与维表相连接的外键，通常具有两个和两个以上的外键。
事实表的特征：
非常的大
内容相对的窄：列数较少（主要是外键id和度量值）
经常发生变化，每天会新增加很多。

事实表分类：
1）事务型事实表
以每个事务或事件为单位，例如一个销售订单记录，一笔支付记录等，作为事实表里的一行数据。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。
2）周期型快照事实表
周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额，或每月的账户余额等。
例如购物车，有加减商品，随时都有可能变化，但是我们更关心每天结束时这里面有多少商品，方便我们后期统计分析。
3）累积型快照事实表（新增及变化同步）
累计快照事实表用于跟踪业务事实的变化。例如，数据仓库中可能需要累积或者存储订单从下订单开始，到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单声明周期的进展情况。当这个业务过程进行时，事实表的记录也要不断更新。

维度模型分类

在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型


ODS层
（1）保持数据原貌不做任何修改，起到备份数据的作用。
（2）数据采用压缩，减少磁盘存储空间（例如：原始数据100G，可以压缩到10G左右）
（3）创建分区表，防止后续的全表扫描
DIM层DWD层需构建维度模型
维度建模一般按照以下四个步骤：
选择业务过程→声明粒度→确认维度→确认事实

DWS层和DWT层统称宽表层，这两层的设计思想大致相同
DWS和DWT层的区别：DWS层存放的所有主题对象当天的汇总行为，例如每个地区当天的下单次数，下单金额等，DWT层存放的是所有主题对象的累积行为，例如每个地区最近７天（１５天、３０天、６０天）的下单次数、下单金额等。
ADS层：对电商系统各大主题指标分别进行分析

数仓环境搭建

Hive on Spark

Hive引擎包括：默认MR、tez、spark
Hive on Spark：Hive既作为存储元数据又负责SQL的解析优化，语法是HQL语法，执行引擎变成了Spark，Spark负责采用RDD执行。（周围生态更好）
Spark on Hive : Hive只作为存储元数据，Spark负责SQL解析优化，语法是Spark SQL语法，Spark负责采用RDD执行。（计算能力更强）
但本项目选择Hive on Spark

注意官网下载的Hive3.1.2和Spark3.0.0默认是不兼容的。因为Hive3.1.2支持的Spark版本是2.4.5，所以需要我们重新编译Hive3.1.2版本。

这里使用的是尚硅谷提供的重新编译的hive3.1.2，解压后替换原有的hive文件夹即可，再替换数据库为mysql。
进入/opt/module/hive/lib 查询ls -al | grep spark，查看spark相关的包是否为3.0.0版本，若是，则说明替换成功。

Shell中单引号和双引号区别

总结：
（1）单引号不取变量值
（2）双引号取变量值
（3）反引号`，执行引号中命令
（4）双引号内部嵌套单引号，取出变量值
（5）单引号内部嵌套双引号，不取出变量值

梳理

本项目中的数据包含两个部分：用户行为数据和业务数据
用户行为数据，通过在前端页面埋点产生，然后通过flume/kafka/flume 传输到hdfs中；
业务数据从mysql数据库中获得，然后通过sqoop传输到hdfs中。
ods层：用户行为表1张；业务数据表27张，ods层建表结束后，从hdfs中的表中load数据到ods表中（相当于备份？）
DIM层：主要是纬度表：商品、活动、地区、时间、用户等。商品维度表：一行数据是一个SKU，
其中用户维度表是拉链表。用户表本身首日是全量同步，后续的每一天都是增量与变化同步。

拉链表是什么？为什么要做拉链表？

DWD层：明细数据层，将ODS表中的数据进行二次处理/分析；对于用户行为日志：ods表中存储的行为日志是string类型，在DWD层的各个表中把各种不同的日志内容解析出来。

Beeline连接报错：Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000/default

1. 还要单独开一个窗口启动hiveserver2服务，这个服务是beeline连接的，提供jdbc协议帮助操作hive的
   hive --service hiveserver2

2. 配置了元数据服务，必须单独开一个窗口先开启这个元数据服务，这个服务是上面操作了hive后，hive又会通过操作元数据的方式操作mysql
   hive --service metastore

DWD层的业务数据处理：注意点在维度建模，此处要建立事实表
维度建模一般按照以下四个步骤：
选择业务过程→声明粒度→确认维度→确认事实
选择业务过程：确定有哪些事实表
声明粒度：保存数据的细化程度或综合程度的级别（每行表示什么），尽可能选择最小粒度
确定维度：确定每张事实表的维度外键
确认事实：确认每张表的度量值字段

DWS层的意义：
将使用频率比较高的表单独保存，避免重复计算。按天进行轻度汇总
一个维度对应一个DWS层的表
宽表：一行是一个维度对象的当日行为

日期常用函数

1）date_format函数（根据格式整理日期）

hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06

2）date_add函数（加减日期）

hive (gmall)> select date_add('2020-06-14',-1);
2020-06-13
hive (gmall)> select date_add('2020-06-14',1);
2020-06-15

3）next_day函数
（1）取当前天的下一个周一

hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15

说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一

hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8

4）last_day函数（求当月最后一天日期）

hive (gmall)> select last_day('2020-06-14');
2020-06-30

DWS层的数据装载

一个维度对象在一天当中的汇总行为
分区：按天分区，每个分区保存当天活跃对象的汇总行为

新增访客的款表怎么设计？

DWT层

一个维度对象的累计汇总行为（7天、15天等）
分区：按天分区，每天的分区中存储截止到当日的全量的维度对象的汇总行为

DWT层字段是DWS层字段的若干倍。统计最近1日、最近7日、最近30日、截止至今的统计值。
最近1日：直接获得DWS层的数据，从最后一个分区中
最近7日：聚合DWS层最近7日的数据
最近30日：聚合DWS层最近7日的数据

用日期函数data_add往前-29天，如果是就+1
sum(if(dt='2020-06-14',login_count,0)) login_last_1d_count,
sum(if(dt='2020-06-14' and login_count>0,1,0)) login_last_1d_day_count,
sum(if(dt>=date_add('2020-06-14',-6),login_count,0)) login_last_7d_count,
sum(if(dt>=date_add('2020-06-14',-6) and login_count>0,1,0)) login_last_7d_day_count,
sum(if(dt>=date_add('2020-06-14',-29),login_count,0)) login_last_30d_count,
sum(if(dt>=date_add('2020-06-14',-29) and login_count>0,1,0)) login_last_30d_day_count,

截止至今：聚合DWS层全表的数据
初次登陆时间：为用户的注册时间，可以在用户表中查找到

hive-on-spark报错：org.apache.hadoop.hive.ql.parse.SemanticException:Failed to get a spark session

1.搭建好hive-on-spark后，首先测试搭建环境是否成功：

如：create table student (id int, name string);

insert into table student values(1,'abc');

如果执行成功，说明hive-on-spark搭建成功。如果失败，则搭建失败。就要考虑是版本兼容性问题还是配置文件出错。

2.如果在搭建成功的情况下，执行某些较为复杂的命令时报错：org.apache.hadoop.hive.ql.parse.SemanticException:Failed to get a spark session: org.apache.hadoop.hive.ql.metadata.HiveException: Failed to create Spark client for Spark session

可以尝试调整hadoop/etc/hadoop/capacity-scheduler.xml中yarn.scheduler.capacity.maximum-am-resource-percent参数，application master资源比例，默认为0.1，如果该值设置过大，就会导致mapreduce时内存不足，就会报上面错误。如果该值是默认值，在学习环境下application master分配内存较少，可能同时只能执行一个job，影响效率。可以尝试调整0.5，我从0.8调至0.5，问题解决。

3.如果上面还不行，调整yarn-site.xml中配置参数，比如最大最小内存，关闭内存检查等等，相关教程很多，可以尝试。

4.上面的操作都没解决这个错误...但下面的可以解决！
增加连接的时间
在hive-site.xml中增加

<property>
	<name>hive.spark.client.connect.timeout</name>
	<value>30000ms</value> //如果30000还是报错，改成900000.我在进行更大的宽表数据导入的时候就二次报错了（之前明明不报错了，猜测是表太大了。把这个值改大之后就成功运行了）
</property>

ADS层 应用数据层（重点）

ADS层不涉及建模，建表根据具体需求而定。
搭建数据仓库后续的各项应用所需的计算结果——报表应用/报表需求
ADS层也是面向不同的主题进行建表、分析，如访客主题、用户主题、商品主题、订单主题、优惠券主题、活动主题等

访客：路径分析
用户：流失用户数（过去第7天访问过，但是今天未访问）、回流用户数（今天访问了，但是上一次访问时间在7天之前），漏斗分析（反映一个业务过程从起点到终点各阶段用户转化情况），用户留存率（分为新增用户留存--某天新增的用户中有多少人有后续的活跃行为和活跃留存--分析某天的活跃用户中，有多少人有后续的活跃行为）

商品：复购率:一段时间内重复购买某品牌的人数与购买过该品牌的人数的比值。重复购买即购买次数大于等于2，购买过即购买次数大于1
本项目内统计的是1，7，30天内的各品牌复购率

订单：一段时间之内的（1，7，30天内的）统计订单总数、订单总金额和下单总人数
各省份的订单总数和订单总金额

优惠券统计：最近30日发布的所有优惠券的领用情况和补贴率，补贴率是指：优惠金额与使用优惠券的订单的原价金额的比值