hive从入门到实战No.4

• • • • 维度建模缺点

hive企业应用

一、数据仓库架构设计

数据仓库的主要工作就是ETL,即是英文Extract-Transform-Load的缩写,用来描述数据从来源端经过装载(load)、抽取(extract)、转换(transform)至目的端的过程.

数据仓库构架设计,即为公司针对自身业务场景实现的水平分层、垂直分主题的数据仓库构建过程的顶层设计.

1.数据构架

        构架原则:先水平分层,在垂直分主题域

        数据架构分三层:

                源数据落地取(SDF:Source Data File)

                数据仓库层(DW:Data WareHouse)

                数据集市层(DM:Data Market)

        数据仓库进一步细分为三层

                源数据层(DWB)

                细节数据层(DWD)

                汇总数据层(DWS)

 2.数据仓库分层介绍(水平分层)

 3.按主题划分(垂直划分)

二.数据仓库建模 

1.概念定义

数据模型是抽象描述现实世界的一种工具和方法,是通过抽象的实体及实体之间练的形式,来表示现实世界中书屋的相互关系的一种映射.

数据模型表现的抽象实体和实体之间的关系,通过对实体和实体之间关系的定义和描述,来表达实际的业务中具体的业务关系.

数据仓库模型是数据模型中针对特定的数据仓库应用系统的一种特定的数据模型

数据建模即数据模型的构建和应用过程

数据仓库建模即数据仓库模型的构建和应用过程 

2.数据仓库建模的发展历史与意义

数据仓库建模的阶段发展

        简单报表阶段:a.该阶段系统的主要目标是解决一些日常的工作中业务人员需要的报表,以及生成一些简单的能够帮助领导进行决策所需要的汇总数据

        b.这个阶段的大部分表现形式为数据库和前端报表工具.

        c.特点:简单、单一

        数据集市阶段:a.该阶段系统主要是根据某个业务部门的需要,进行一定的数据的采集,整理,按照业务人员的需要,进行多维报表的展现,能狗提供对特定业务指导的数据,并且能够提供特定的领导决策数据.

        b.特点:多维度、业务场景化、按需定制性

        数据仓库阶段:a.该阶段系统主要是按照一定的数据模型,对整个企业的数据进行采集,整理,并且能按照各个业务部门的需要,提供跨部门的,完全一致的业务报表数据,能能够通过数据仓库生成对对业务具有指导性的数据,同时,为领导决策提供全面的数据支持.

        b.特点:全面、灵活、数据模型支持、体系化

数据建模的意义

        进行全面的业务梳理,改进业务流程

                对公司进行全面数据

                了解公司的业务运行架构和运行状态

                为改进公司架构、提升运营效率、指导生产提供科学支撑

        建立全方位的数据视角,消灭信息孤岛 和数据差异

                提供公司数据的全面视角,不再是部门各自为战

                清晰化部门间的内在联系,消灭部门间的信息孤岛

                保证公司全局数据的一致性,消灭差异性

        解决业务的变动和数据仓库的灵活性

                将底层技术实现与业务表达展现解耦

                需求的变动或新需求,可以最小化的成本达到标准

        帮助数据仓库系统本身的建设

                技术开发人员和业务需求人员较容易达成一致意见

                各方人员明确当前数据状况,便于做当前任务评估 和长远构建规划

3.如何构建数据模型

        数据模型的层次的一般划分

 

        各层次说明

                业务建模，生成业务模型，主要解决业务层面的分解和程序化。

                领域建模，生成领域模型，主要是对业务模型进行抽象处理，生成领域概念模型。

                逻辑建模，生成逻辑模型，主要是将领域模型的概念实体以及实体之间的关系进行数据库层次的逻辑化。

                物理建模，生成物理模型，主要解决，逻辑模型针对不同关系型数据库的物理化以及性能等一些具体的技术问题。

        构建方法

                数据模建构建与数据仓库架构设计有紧密关系，要优先吸收数据仓库架构设计即上一节内容。

                数据仓库的建模方法有很多，每一种建模方法则代表哲学上的一个观点，代表了一种归纳，概括世界的一种方法。

                目前的构建方法主要有三种：

                        范式建模法

                        维度建模法

                        实体建模法

        具体构建方法详解

                范式建模法

                        范式建模法其实是我们在构建数据模型常用的方法之一。

                        主要解决关系型数据库得数据存储，我们在关系型数据库中的建模方法，大部分采用的是三范式建模法。

                        数据库六大范式说明

                                第1范式-1NF：无重复的列、列不可再拆分。

                                第2范式-2NF：属性完全依赖于主键

                                第3范式-3NF：属性不依赖于其它非主属性，即属于依赖于主键不能出现传递依赖。

                                巴斯-科德范式（BCNF），第四范式(4NF），第五范式（5NF，又称完美范式）

                        特别说明

                                范式建模优点

                                        从关系型数据库的角度出发，结合了业务系统的数据模型，能够比较方便的实现数据仓库的建模

                                范式建模缺点

                                        其建模方法限定在关系型数据库之上，在有些时候(需要冗余的时候)反而限制了整个数据仓库模型的灵活性，性能等，特别是考虑到数据仓库的底层数据向数据集市的数据进行汇总时，需要灵活调整才能达到要求。

                                使用建议：当不需要冗余设计提高易用性和计算效率时，可以采用这种模式。（常见的即为web项目开发中)

                维度建模法

                        即按照事实表，维度表来构建数据仓库，即最被人广泛知晓的名字就是星型模式（Star-schema）和雪花模式（Snowflake-schema)

                        重要概念说明

                                事实表：发生在某个时间点上的一个事件，即具体的实体内容。比如以电商订单为例：下单是一个事实、付款是一个事实、退款是一个事实，所有事实的累计形成的表，均为事实表

                                维度表

                                        维度表是从事实表中抽离出来的分析粒度

                                        维度表可以看作是用户来分析数据的窗口(视角)，维度表中包含事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息

                        星型建模法

                                定义:维度表全部直接关联到事实表中，其形状类似星星，故称之

                               举例说明（销售类数据仓库构建 )

                                        如在地域维度表中，存在国家A省B的城市C，及国家A省B的城市D两条记录，那么国家A和省B的信息分别存储了两次，即存在冗余

                        雪花建模法

                                定义

                                        维度表并非全部关联到事实表中，存在一个或多个表没有直接关联到事实表中时，其形状类似雪花，故称之

                                举例如上

                                        将地域维表又分解为国家，省份，城市等维表

                                        优点是通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能。雪花型结构去除了数据冗余

                                        如下图所示

                        关于星形和雪花模型进行维度建模的对比说明

                                 定义

                                        星形模型：维度表全部直接关联到事实表中，其形状类似星星，故称之

                                        雪花模型：维度表并非全部关联到事实表中，存在一个或多个表没有直接关联到事实表中时，其形状类似雪花，故称之

                                相同点

                                        雪花模型属于星形模型的扩展，属于星形模型

                                        都是围绕事实表、维度表展开模型构建，只是层次设计不尽相同

                                差异点

                                        星型架构的设计由于没有像现实世界当中的抽象情况进行层级依赖，所以是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，所以数据有一定的冗余设计

                                        雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的“层次”区域，这些被分解的表都连接到主维度表而不是事实表

                                对比总结说明

                                        数据规范性：雪花胜于星型

                                        性能：雪花的表关联较多，并行性和计算性能上会低于性能上往往低于星型

                                        ETL开发:雪花关系多则关联多，代码量较复杂一些。而星型数据较集中，关联少，代码量会少一些

                                        实际使用，两者应用的均比较多，但星型略胜一筹

                        关于维度建模法的总结说明

                                广泛被使用的原因：在于针对各个维作了大量的预处理，如按照维进行预先的统计、分类、排序等，能够极大的提升数据仓库的处理能力

                                维度建模优点

                                        由于其可以有必要合理的冗余和其它范式建模的严格限制，相对于针对3NF 的建模方法，星型模式在性能上占据明显的优势

                                        维度建模非常直观，紧紧围绕着业务模型，可以直观的反映出业务模型中的业务问题。不需要经过特别的抽象处理，即可以完成维度建模                                 

                                维度建模缺点

                                        由于在构建星型模式之前需要进行大量的数据预处理，会带来大量的数据处理工作

                                        业务发生变化后，往往需要更新维度的预处理

                                        存储和处理过程中，数据冗余量较大

                                        依靠维度建模的话，其维度必然会且维护成本增大，不能保证数据来源的一致性和准确性，而且在数据仓库的底层，不是特别适用于维度建模的方法

                                使用建议：在数据架构设计中的细节数据层、汇总数据层、数据集市层等需要提升计算性能的时候，均可以使用，也是建模过程中逻辑建模阶段最常用的方法之一

                实体建模法

                        实体建模法并不是数据仓库建模中常见的一个方法

                        源于哲学的一个流派。从哲学的意义上说，客观世界应该是可以细分的，客观世界应该可以分成由一个个实体，以及实体与实体之间的关系组成

三、数据分析

1.概念定义

        数据分析是指用适当的统计分析(当下也包含机器学习等数据挖掘)的方法

        对收集来的大量数据进行分析，提取有用信息和形成结论而对数据加以详细研究和概括总结的过程

        这一过程也是质量管理体系的支持过程。在实用中，数据分析可帮助人们作出判断，以便采取适当行动

        辅助决策的最重要方法之一

2..专业术语

       

2.1 OLTP

• 全称：on-line transaction processing。中文意思： 联机事务处理
• 其是数据库的主要应用，主要是执行基本日常的事务处理，如数据库记录的增删查改。

比如在支付定或银行的一笔交易记录，就是一个典型的事务。 

• 主要特点

1. 实时性要求高，操作完后立刻要能看到结果。
2. 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。 
3. 交易一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取 
4. 高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。

• 总结

主要是指关系数据库中的增删查改，也是我们最常用操作，此为数据库的基础。

2.2 数据库事务ACID四大特性

• 原子性(Atomicity)

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consistency)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

• 隔离性(Isolation)

1. 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。
2. 如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。
3. 亦称为串行化

• 持久性(Durability)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

2.3 OLAP

• 全称：On-Line Analytical Processing，中文意思为： 联机分析处理
• 其是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。
• 主要特点

1. 实时性要求不是很高，比如最常见的应用就是天级更新数据，然后出对应的数据报表。 
2. 数据量大，因为OLAP支持的是动态查询，所以用户也需要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大; 
3. OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。

• 总结

1. 其是数据仓库的核心部件。
2. 所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库，用于处理商业智能(BI)、决策支持等重要的决策信息。
3. 数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析，读取较多，更新较少。
4. OLTP发展到一定阶段后产生的OLAP。

3.hive对数据分析的支持

Hive数据分析函数：分析函数、窗口函数、增强Group(用的极少，不做讲解)三类，及用于辅助表达的over从句。

3.1 产生背景

常规SQL语句中，明细数据和聚合后的数据不能同时出现在一张表中，而此类需求又常见。

如：员工既要查询当前收入多少又要显示本年度收入多少;

员工既要查询当前收入多少，又要显示历史总收入多少;

购物者既要查询当前剩余多少，又要显示历史充值多少等等。

该类函数即为解决两者可以同时出现的问题。

3.2 函数分类

• 分析函数（不支持与window子句联用，即ROWS BETWEEN）
  1. NTILE：序列分析函数，用于数据分片排序，对数据分片并返回当前分片值。(即对数据集分块，如第1块，第2块等)，不均匀的时候，依次增加前边分片序列的数量。
  2. ROW_NUMBER：序列分析函数，用于排序，按照顺序，不关心是否有相等情况，从1开始逐条给数据一个加1后的序列号。如1，2，3，4....
  3. RANK：序列分析函数，用于排序，按照顺序，关心相等情况，如遇到相等情况，名次会留下空位。如1,2,2,4,4,6......
  4. DENSE_RANK：序列分析函数，用于排序，关心相等情况，如遇到相等情况，名次不会留下空位。如1,2,2,3,3,4......
• 窗口函数
  1. LAG:函数LAG(col,n,DEFAULT)用于统计窗口内往上第n行值。

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

1. 1. LEAD:与LAG作用相反，函数形式如LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行(可选，默认为1)，第三个参数为默认值(当往下第n行为NULL时候，取默认值，如不指定，则为NULL)

• 1. FIRST_VALUE: 取分组内排序后，截止到当前行，第一个值
  2. LAST_VALUE:与FIRST_VALUE相反，取分组内排序后，截止到当前行，最后一个值
• over()从句：指定分析窗口函数的细化落围规则
  1. 与标准的聚合函数COUNT、SUM、MIN、MAX、AVG联用，如sum(...) over(...)
  2. 与区配的分析窗口联用，如ROW_NUMBER() over(...)
  3. 使用PARTITION BY语句，使用一个或者多个原始数据类型的列
  4. 使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
  5. 使用窗口规范，窗口规范支持以下格式：

1. 1. ROWS BETWEEN:即为window子句或称窗口子句，属于物理截取，即物理窗口，从行数上控制截取数据的大小多少。
   2. RANGE BETWEEN: 即为window子句或称窗口子句，属于逻辑截取，即逻辑窗口，从列值上控制窗口的大小多少。
   3. PRECEDING：window子句之往前
   4. FOLLOWING：window子句之往后
   5. CURRENT ROW：window子句之当前行
   6. UNBOUNDED：window子句之起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED FOLLOWING：表示到后面的终点。

1. 1. 注意：order by子句后边如果没有跟着多大窗口，则默认为range between unbounded preceding and current row

3.3 应用示例

• 数据准备(中间以"\t"为分隔)

工号(workno)    姓名(name)    报销项(item)    费用(cost)    时间(orderdate)

t001    张一    水费    100    2017-06-01 09:00:00

t002    张二    团建    500    2017-07-02 09:00:00

t003    张三    市场    1700    2017-08-01 09:00:00

t004    张四    维修    150    2017-08-11 09:00:00

t005    张五    出差    800    2017-08-21 11:00:00

t005    张五    市场    1000    2017-09-29 11:00:00

t005    张五    福利    300    2017-09-29 11:00:00

t004    张四    市场    300    2018-01-05 11:00:00

t004    张四    市场    550    2018-01-06 09:00:00

t003    张三    维修    90    2018-02-01 09:00:00

t002    张二    电费    200    2018-02-05 09:00:00

t002    张二    团建    300    2018-02-08 15:00:00

t005    张五    市场    1280    2018-03-07 15:00:00

t005    张五    市场    2280    2018-04-07 15:00:00

t002    张二    维修    230    2018-04-01 15:00:00

t004    张四    福利    200    2018-04-28 15:00:00

t004    张四    福利    1000    2017-12-30 15:00:00

t004    张四    福利    10000    2018-02-13 15:00:00

• 需求列表

1.求2018.02月份总报销金额，及报销明细

//重点考查over子句，达到明细和汇总一起显示

select name,cost,orderdatetime,sum(cost) over() from analysis_daily_expense 

where substring(orderdatetime,1,7)='2018-02';

 

2.求工号t004的历史总报销金额，及报销明细

//重点考查over子句及where联用，达到明细和汇总一起显示及筛选

select workno,name,cost,orderdatetime,

sum(cost) over() from analysis_daily_expense 

where workno='t004';

 

3.求所有人的报销明细，及公司所在年度总报销金额

//重点考查partition by用法

select workno,name,cost,orderdatetime,year(orderdatetime),

sum(cost) over(partition by year(orderdatetime))

from analysis_daily_expense;

 

4.求所有人的报销明细，并计算每次报销后当前公司的年度报销总金额

//重点考查order by用法，当order by后无窗口大小限制时，即为整组的第一行到当前行，

//默认会追加range between unbounded preceding and current row

select workno,name,cost,orderdatetime,year(orderdatetime),

sum(cost) over(partition by year(orderdatetime) order by orderdatetime asc)

from analysis_daily_expense;

 

5.求所有人的报销明细，及公司所在年度总报销金额，并按报销时间升序排列。

//重点考查order by用法

select workno,name,cost,orderdatetime,year(orderdatetime),

sum(cost) over(partition by year(orderdatetime))

from analysis_daily_expense

order by orderdatetime;

 

6.求所有人的报销明细，并计算每个人本次报销后，其个人合计已报销的总费用

//重点考查window子句

select workno,name,cost,orderdatetime,year(orderdatetime),

sum(cost) over(partition by name order by orderdatetime range between unbounded preceding and current row)

from analysis_daily_expense;

 

7.求所有人的报销明细，并计算每个人本次报销后，历史报销记录中的最低报销金额和最高报销金额

//重点考查window子句，各子句之间是独立不干扰的

select workno,name,cost,orderdatetime,year(orderdatetime),

max(cost) over(partition by name order by orderdatetime rows between unbounded preceding and current row) as max_cost,

min(cost) over(partition by name order by orderdatetime rows between unbounded preceding and current row) as min_cost

from analysis_daily_expense;

 

8.将报销金额从少到多分成5个层级，查看每个的报销明细属于哪个层级

//重点考查ntile数据分块使用

select workno,name,cost,orderdatetime,

ntile(5) over(order by cost asc) as block

from analysis_daily_expense;

 

9.求所有报销明细排序，获取该次报销属于个人第多少次报销、该次报销在公司所有报销时间中排多少名topN

//重点考查row_number,rank,dense_rank数据排序使用

select workno,name,cost,orderdatetime,

row_number() over(partition by name order by orderdatetime asc) as person,

rank() over(order by orderdatetime asc) as topN_1,

dense_rank() over(order by orderdatetime asc) as topN_2

from analysis_daily_expense;

 

10.求所有报销明细中，个人上一次报销是什么时候

// 重点考查lag，其计算公式为lag(col,n,DEFAULT) 用于统计窗口内往上第n行值，DEFAULT可以不填，则为NULL

select workno,name,cost,orderdatetime,

lag(orderdatetime,1) over(partition by workno order by orderdatetime asc) as last_orderdatetime

from analysis_daily_expense;

 

11.求所有报销明细中，个人下一次报销是什么时候

// 重点考查lead，其计算公式为lead(col,n,DEFAULT) 用于统计窗口内往下第n行值，DEFAULT可以不填，则为NULL

select workno,name,cost,orderdatetime,

lead(orderdatetime,1) over(partition by workno order by orderdatetime asc) as last_orderdatetime

from analysis_daily_expense;

12.求所有报销明细中，个人第一次报销是以什么名义，最后一次报销是以什么名义？

// 重点考查first_value，last_value使用，注意指定order by，否则的话是按物理存储顺序取数，导致出错

select workno,name,cost,orderdatetime,

first_value(item) over(partition by workno order by orderdatetime asc) as first_item,

first_value(item) over(partition by workno order by orderdatetime desc) as last_item    

from analysis_daily_expense;

3.4 数据分析的附加项

• 理解和表达能力良好，能够快速理解和响应需求。
• 思路清晰，快速迭代。
• PPT、Excel的熟练操作,主要是为数据分析报告做准备。
• 分类聚类算法有一定的了解和掌握，若能简单学会用R将会如虎添翼。

四. 在线数据备份

将生产系统中的数据库拷贝到hive中一份作灾备。

• 自行实现
  1. 在hive中创建要备份表的scheme,建立分区表，如按天来分区
  2. 将mysql生产库中的库表同步到hadoop客户端中，形成文件data.txt
  3. load local data.txt文件上传至hive表对应的hdfs目录中，加载到对应的天分区
  4. 周期性执行上边三步骤
• 使用sqoop实现在线数据备份

五. 数据工程师岗位职责

• 数据仓库工程师
  1. 数据ETL清洗到位。
  2. 数据质量监控与运维
  3. 性能优化
• 数据分析工程师
  1. 数据ETL清洗
  2. 数据统计分析、数据挖掘算法应用
  3. 业务洞察、经营指导、辅助决策
• 数据开发工程师
  1. 是一个统称概念，只要是偏数据维度的工程师种类，均可称为数据开发工程师。
  2. 包括了数据仓库、数据分析、数据挖掘、数据相关的代码开发工作。
  3. 但一般会侧重一些开发工作，比如写java,scala代码场景更多。
• 数据挖掘工程师
  1. 统计分析
  2. 特征提取
  3. 模型训练
  4. 数据预测与评估
  5. ABTest与反馈迭代
  6. 模型上线与调优