离线数仓的概述

1、数仓的概述
数仓主要是用于数据的存储、管理和分析
数仓与关系型数据库最大的区别在于能够存储历史数据,后续可以将数据按照时间曲线分析。
2、数仓的架构
日志数据->日志服务器本地磁盘[多台]->flume->kafka->flume->HDFS->HIVE[ODS/DWD/DIM/DWS/ADS] ->DATAX ->MYSQL ->可视化
->增量导入->maxwell->kafka->flume
业务数据->mysql-> ->HDFS->HIVE[ODS/DWD/DIM/DWS/ADS] ->DATAX ->MYSQL ->可视化
->全量导入->datax
日志数据的格式[json格式]:
页面日志: { “common”:{…},“actions”:[ {…},{…},… ],“displays”:[ {…},{…},… ],“page”:{…},“error”:{…},“ts”:… }
启动日志: { “common”:{…},“start”:{…},“ts”:… }
业务数据同步策略
全量同步: 每天将mysql表所有数据同步到HDFS一个目录
使用场景: mysql表数据量比较少或者表数据量大并且每天新增和修改的数据比较多
优点: 同步简单[直接查询全表]、使用简单[最新数据查询最新目录,历史某天数据查询历史某天目录即可]
缺点: 如果表数据量大并且每天新增和修改的数据比较少,此时多个目录中存在大量的重复数据,占用过多的磁盘空间
增量同步:
首日: 每天将mysql表所有数据同步到HDFS一个目录
每日: 将当天新增和修改的数据同步到HDFS一个目录
使用场景: 用于表数据量大并且每天新增和修改的数据比较少
优点: 没有数据冗余,节省了磁盘空间
缺点: 同步稍微复杂[需要查询出当天的新增和修改的数据]、使用复杂[最新的数据分布在多个目录,使用的时候需要查询多个目录]
业务数据的格式
增量同步数据格式: 普通文本保存,文件中是json数据
首日:
{“type”:“bootstrap-start”,“data”:{},“ts”:…}
{“type”:“bootstrap-insert”,“data”:{…},“ts”:…}
…
{“type”:“bootstrap-complete”,“data”:{},“ts”:…}
每日:
{“type”:“insert”,“data”:{…},“ts”:…}
{“type”:“delete”,“data”:{…},“ts”:…}
{“type”:“update”,“data”:{…},“old”:{…},“ts”:…}
全量同步数据格式: 普通文件保存,文件中的数据列之间通过\t分割
3、数仓建模概述
建模: 将数据有序的组织与存储[建表]
模型: 将数据有序的组织与存储的方法[指导建表的理论]
常用模型:
ER模型: 以实体关系的方式描述企业业务,以规范化的形式设计数据库的过程,要符合3NF
实体: 对象[比如班级、学生]
关系: 一对一、一对多、多对多
规范化: 以一定的表示设计
优点: 将一个表拆分为多个表,能够减少数据冗余,增强数据的一致性。
缺点: 表比较多,后续查询的时候性能会比较低,不适合用于数据分析
ER模型一般用于关系型数据库的建模。
维度模型: 以事实和维度的方式描述企业业务
优点: 将多个表合并成一个表,表会比较少,所以查询性能会比较高
缺点: 数据冗余,一致性比较差
维度模型一般用于数仓的建模
4、维度建模之事实表
1、事实表的概述
1、定义: 围绕着业务过程设计的就是事实表
2、事实表的列一般两种: 维度表外键、度量值
3、事实表特点: 细[列相对比较少]长[行相对比较多]
4、事实表的分类: 事务型事实表、周期快照型事实表、累积快照型事实表
2、事务型事实表
1、定义: 记录业务过程最细粒度操作的就是事务型事实表
粒度: 表中每行意思代表的含义
2、设计流程
1、选择业务过程[决定需要创建多少个事务型事实表]:
选择需求需要的或者后续可能用到的业务过程
一个业务过程对应一张事务型事实表
2、声明粒度[决定表中每行存储的什么东西]
需要选择最细粒度
粒度越细,后续能够满足的需求就越多
3、确定维度[决定表有哪些维度外键的列]
选择该业务过程相关联的维度
维度要尽可能丰富,维度越丰富,后续能够分析的指标就越多
4、确定度量[决定表有哪些度量值的列]
度量值一般是数字类型[金额、件数、次数等]
3、不足
只要给一个业务过程创建事务型事实表,那么该业务过程所有的指标都可以用事务型事实表计算。
但是在某个些场景下,用事务型事实表计算的时候性能会比较低。
1、存量[现存的数量]型指标
2、多事务[多个业务过程]关联指标
以上两种场景如果用事务型事实表计算的话,需要多个事务型事实表join操作才能计算,事务型事实表数据量一般都比较大,大表join性能比较慢。
3、周期快照事实表
1、定义: 以固定的时间周期记录业务过程的表
2、场景: 主要用于存量型指标和状态型指标
3、周期型快照事实表用于存量型指标和状态型指标的原因:
在mysql业务表中一般都有直接记录存量结果的表,所以我们可以直接将该mysql表的数据每天同步一份到数仓,后续直接查询使用即可,避免了多个事务型事实表join操作。
4、周期快照事实表是由需求驱动,有存量型与状态型指标才会创建周期快照事实表。
5、设计过程
1、声明粒度
周期快照事实表的粒度 = 周期 + 维度
周期一般是每日
维度由需求决定
2、确定事实
事实由需求决定[比如需求需要统计xxx件数,事实就是件数]
6、事实类型
可加事实: 度量值与表中所有维度聚合都有意义,一般指事务型事实表的事实
半可加事实: 度量值与表中部分维度聚合都有意义,一般指周期型快照事实表的事实
不可加事实: 度量值不能直接累加[比如比率的结果]
4、累积型快照事实表
1、定义: 记录一个业务流程中多个关键的业务过程的进度
2、特点: 累积型快照事实表中对应流程的多个业务过程都有一个日期字段,只有当流程走到对应业务过程的时候,该日期字段才有值。
3、场景: 用于流程中多个业务过程的时间间隔的需求
4、设计流程
1、选择业务过程
选择一个流程中的多个关键的业务过程
一个流程对应一张累积型快照事实表
2、声明粒度
需要最细粒度
3、确定维度
将流程中的多个业务过程的所有维度全部选择
还需要给每个业务过程加上一个时间维度
4、确定度量
将流程中的多个业务过程的度量值全部选择
5、维度建模之维度表
1、定义: 业务过程的环境描述
2、维度表中的列有主键、维度属性
3、设计步骤
1、确定维度
事实表中的一个维度一般需要创建一张维度表
如果多个事实表都需要同一个的维度,此时只需要创建一张公共的维度表即可。
如果维度表的维度属性很少,此时该维度可以不用创建维度表,直接将维度属性冗余到事实表中即可[维度退化]
2、确定主维表和相关维表
主维表、相关维表是指与维度相关的业务表[mysql表]
主维表的粒度与维度表的粒度是一致的
3、确定维度属性
维度表的维度属性主要从主维表和相关维表直接获取或者通过一定的加工得到
维度属性的获取原则:
1、维度属性要尽可能丰富
维度属性越丰富,后续能够分析的指标就越多
2、维度属性不要只是编码,一般编码和文字共存
3、尽量沉淀出通用的维度属性,避免后续的重复数据处理
4、维度设计要点
1、规范化与反规范化
规范化: 以一定的标准设计数据库的过程,通常是将一张表拆分为多个表,减少数据冗余提高一致性
反规范化: 将多个表合并成一个表,减少join操作,提高查询性能
数仓中一般选择使用反规范化
星型模型: 以反规范化形式设计的维度表,一个维度一个表
雪花模型: 以规范化形式设计的维度表,一个维度可能有多个表
2、维度变化
维度表的数据不是一成不变的,是可能时间的变化而改变.
所以,维度根据历史数据的保存方式分为两种: 全量快照表、拉链表
全量快照表: hive表每个分区中都保存当天的全量维度数据
同步策略:全量快照维度表是采用全量同步的方式将mysql的数据同步到HDFS
适用场景:表数据量小或者表数据量大但是每天新增和修改的数据特别多
缺点: hive表多个分区中存在大量的重复数据
优点: 使用简单，需要最新的维度数据只需要查询hive最新分区即可,需要某天的历史数据,只需要查询历史某天分区即可
拉链表: 能够记录数据生命周期的表就是拉链表
同步策略: 拉链表一般是通过增量同步的方式将Mysql的数据同步到HDFS
适用场景: 表数据量大但是每天新增和修改的数据不是特别多
特点: 表中每条数据都有两个日期字段[生效日期、失效日期],如果数据至今有效,一般失效日期是一个极大值[9999-99-99]。
优点: 可以减少数据冗余,节省磁盘空间
缺点: 使用会比较复杂
查询最新数据: select … from … where 失效日期 = ‘9999-99-99’
查询历史数据: select … form … where 生效日期<=指定日期 and 失效日期>=指定日期
3、多值维度
定义: 事实表一条数据可以关联上维度表多条数据
解决方案:
1、降低事实表的粒度
2、如果粒度不能降低
1、如果维度值的个数固定,此时事实表可以创建多个列,分别保存这多个维度外键
2、如果维度值的个数不固定,此时事实表只需要创建一个复杂类型[array]的列,保存这多个维度外键
4、多值属性
定义: 维度表的某个维度属性有多个值
解决方案：
1、如果维度属性的值的个数固定,在维度表中创建多个列,一个列保存一个维度属性值
2、如果维度属性的值的个数不固定,在维度表创建一个复杂类型的列,保存个维度属性的多个值
6、数仓的设计
1、分层
好处: 将复杂的问题简单化,避免数据的重复处理
ODS: 原始数据层[表中保存是采集的原始数据,没有做任何改变]
DWD: 数据明细层[该层的表是事实表]
DIM: 公共维度层[该层的表是维度表]
DWS: 公共汇总层[表中保存的是多个指标需要的共同的聚合结果]
ADS: 数据应用层[保存的是需要结果]
2、数仓的构建流程
->构建业务总线矩阵->构建DWD+DIM
数据调研[业务调研/需求调研]->明确数据域-> ->开发 + 调度
->构建指标体系->构建DWS
3、数据调研
业务调研
熟悉业务流程: 将所有流程的中每个业务过程异议列举出来
熟悉业务数据: 需要知道每个业务过程对表数据的影响
需求调研: 要根据需求能够找到与之对应的业务过程和维度数据
4、明确数据域
将数据划分多个域,后续便于数据的查找。
划分数据域没有明确的标准,只需要后续能够根据数据域快速找到数据即可
5、构建总线矩阵
通过业务调研,将业务过程一一列举,作为表格的行,维度作为表格的列。
6、构建指标体系
原子指标 = 业务过程[表] + 度量值[度量的列] + 聚合逻辑
原子指标一般没有具体的需求对应
派生指标 = 原子指标 + 统计周期[时间范围] + 业务限定[where条件] + 统计粒度[分组的字段]
衍生指标 = 一个或者多个派生指标通过复杂的逻辑计算得到
意义: 当分析的需求足够多的时候,会发现多个需求对应同一个派生指标,所以此时为了减少数据重复计算,可以将该公共的派生指标的结果保存起来,后续需要的时候直接查询使用即可。
7、构建维度模型
业务矩阵的每一行对应一张事务型事实表
业务矩阵的每一列一般可以对应一张维度表[维度退化的例外]
8、构建汇总模型一个DWS的表保存是统计周期相同、业务过程相同、统计粒度相同的多个派生指标
一个DWS的表保存是统计周期相同、业务过程相同、统计粒度相同的多个派生指标
7、ODS建模
1、建多少张表: 由采集的数据的种类数决定,一种数据一张表
2、建内部表还是外部表:
内部表: 删除hive表的时候会将元数据与HDFS数据都删除
外部表: 删除hive表的时候只会将元数据删除
公司中一般都是使用外部表
3、表的字段名、类型、表的存储格式
要根据HDFS文件决定:
1、如果文件是普通文本文件[文件中列之间是通过固定分隔符分割]
1、列名与类型的定义
1、如果数据是从Mysql采集到HDFS的,此时hive ods表的列名建议用mysql的列名,类型与mysql 列的类型保持一致。
2、如果数据不是从Mysql采集的,此时ods的列名自定义,类型根据值定义
2、表的存储格式定义:
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘分隔符’ STORED AS TEXTFILE
2、如果文件是文本[文本中的数据是json数据]
此时建表方式有两种:
1、第一种方式
1、列名与类型的定义
字段只定义一个,列名自己指定,类型是string
2、表的存储格式定义
stored as textFile
2、第二种方式
1、列名与列类型的定义
列名与json中一级属性名保持一致,列的类型根据json属性值的类型决定
2、表的存储格式定义
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.JsonSerDe’ stored as textFile
3、如果文件是parquet/orc
1、列名与类型的定义
列名与类型都要与parquet/orc文件中的列名与类型保持一致
2、表的存储格式定义
stored as parquet/orc
4、分区: 公司中一般都是按天分区
5、表数据存储位置:与其他表的父目录保持一致即可
6、是否要压缩: ODS存储的是原始数据,数据量比较大,所以为了节省磁盘空间都会压缩
7、压缩格式: ODS一般选择压缩比比较大的压缩
8、数据加载方式:
load data inpath ‘/…/…’ overwrite into table 表名 partition(分区字段名=值)
8、hive常用的复杂数据类型
array
建表的时候字段类型为array的定义: array<元素类型>
构建array对象: array(元素,…)
获取数组中的某个元素: array对象[角标]
map
建表的时候字段类型为map的定义: map<k的类型,v的类型>
构建array对象: map(k1,v1,k2,v2,…)
获取map的元素
获取所有key: map_keys(map对象)
获取所有value: map_values(map对象)
根据key获取value: map对象[‘key’]
struct
建表的时候字段类型为struct的定义: struct<属性名1:类型,属性名2:类型,…>
构建struct对象：
struct(属性值1,属性值2,…) [此种方式构建的struct对象的属性名是默认的]
named_struct(“属性名”:属性值,“属性名”:属性值) [此种方式构建的struct对象的属性名是自定义的]
根据属性名获取属性值: struct对象.属性名
9、DIM层建模
1、建多少张表
维度表的个数由事实表的维度决定,一般一个维度需要创建一张维度表
如果多个事实用到同一个维度,此时只需要创建一张公共的维度表即可
如果维度属性很少,该维度可以不用创建表,直接将维度属性冗余到事实表中即可
2、内部还是外部表: 公司中一般都是使用外部表
3、表的字段
维度表的字段分为两种: 主键、维度属性
需要找到维度有关的主维表和相关维表
维度表的粒度与主维表的粒度是一致的[也就是说维度表的主键就是主维表的主键]
维度表的维度属性来源:
1、直接将主维表和相关维度表的所有字段取出,去重,去掉一些没有实际意义的字段
2、可以加工得到一些通用的维度属性,避免后续需求需要的时候数据重复处理
注意: 如果主维表与某个相关维度表是1对N的关系,此时就会出现多值属性的问题,此时可以在维度表中创建一个特殊类型的字段,用于保存该相关维表的维度属性值。
4、分区: 一般按天分区
5、表存储格式: dim层的维度表是经常查询的,所以为了提高查询效率一般选用列式存储[stored as parquet/orc]
6、表数据的存储位置: 与其他表的父目录保持一致即可
7、是否压缩: 是
8、压缩格式: dim层的维度表是经常查询的,所以选用速度比较块的压缩[snappy]
9、数据加载方式
全量快照维度: hive表每个分区保存当天的全量的维度数据
insert overwrite table dim表名 partition(分区字段名=‘当日’) select … from ods主维表当日分区 left join ods相关维度表当日分区 …
拉链表:
分为两种分区
9999-99-99分区: 保存的是截止到当天为止所有最新的维度数据。
普通日期分区: 保存的是该日期失效的维度数据。
首日: 拉链表采用的是增量同步,首日是同步了mysql表所有的数据,此时mysql的数据肯定是最新的,所以首日数据只需要全部放入9999-99-99分区即可
insert overwrite table dim表 partition(分区字段名=‘9999-99-99’) select * from ods表 where 分区字段名=‘首日’
每日: 每日同步的时候是同步mysql表当日的新增和修改的数据,所以此时需要对9999-99-99分区的数据进行更新,同时需要把过期的数据移到普通日期分区中
insert overwrite table dim表 partition(分区字段名) select * from dim表9999-99-99分区 full join ods表当日分区 …
10、DWD层建表
1、建多少张表
事务型事实表个数 = 业务过程的个数 = 业务矩阵的行数
周期快照事实表个数 = 存量型指标/状态型指标需求需要的业务过程的个数
累积型快照型事实表个数 = 多事务关联指标需求需要的业务流程的个数
2、内部还是外部表: 公司中一般都是使用外部表
3、表的字段
事务型事实表字段 = 业务主键[对应最细粒度的业务表的主键] + 维度外键 + 冗余的维度 + 度量值
周期快照事实表字段 = 业务主键[对应业务表的主键] + 周期[天] + 需求需要的维度 + 需求需要的度量值
累积快照型事实表 = 业务主键 + 流程中的多个业务过程需要的维度 + 流程中的多个业务过程的日期维度 + 流程中的多个业务过程的度量值
4、分区: 一般按天分区
5、表存储格式: dwd层的维度表是经常查询的,所以为了提高查询效率一般选用列式存储[stored as parquet/orc]
6、表数据的存储位置: 与其他表的父目录保持一致即可
7、是否压缩: 是
8、压缩格式: dwd层的维度表是经常查询的,所以选用速度比较块的压缩[snappy]
9、数据加载方式
事务型事实表加载:
事务型事实表每个分区保存的是当日的行为操作数据
首日: 首日数据保存了首日和首日之前所有日期的数据,所以需要将数据区分,将数据根据事件产生的时间放入不同分区中
insert overwrite table dwd partition(分区字段名) select … from ods表首日分区
每日: 每日数据只有当日新增和修改的,所以数据只需要放入当日分区即可
insert overwrite table dwd表 partition(分区字段名=当日) select … from ods表…
周期快照事实表字段
周期快照事实表每个分区保存的是当日mysql表所有数据,也就是保存的是当日表中所有的事实记录
insert overwrite table dwd表 partition(分区字段名=值) select * from ods表当日分区 …
累积快照型事实表
分区有两种:
9999-12-31分区: 存放是截止到当前为止流程未完成的数据
普通日期分区[2020-06-14]: 存放的是指定日期完成的流程数据
首日: 需要区分首日数据中已完成和未完的流程数据,未完成的写入9999-12-31分区,已完成的写入完成日期对应分区中
insert overwrite table dwd累积事实表 partition(分区字段名) select * from ods表1 left join …
每日: (需要拿到之前未完成的流程数据+当日新增的流程数据)区分出今天完成和到目前未完成的,未完成的写入9999-12-31分区,已完成的写入完成日期对应分区中
insert overwrite table dwd累积事实表 partition(分区字段名) select * from (dwd累积事实表9999-12-31分区 union all 当日新增流程) left join …