数据仓库

数据仓库

ETL构建企业级数据仓库流程

基本概念

业务板块：业务板块定义了数据仓库的多种命名空间，是一种系统级的概念对象。当数据的业务含义存在较大差异时，可以创建不同的业务板块，让各成员独立管理不同的业务，后续数据仓库的建设将按照业务绑卡进行划分

数据域：数据域主要用于存放统一业务板块内不同概念的指标，例如：划分出商品域，交易域，会员域等

业务过程：业务过程即业务活动中所有的事件，通常为不可拆分的事件。创建业务过程，是为了从顶层视角，贵方业务过长中事务内容的类型和唯一性

维度：维度即进行统计的对象。通常，维度是实际客观存在的实体。创建维度，即从顶层规范业务中实体的存在性和唯一性，维度即维度组合也是派生指标的统计粒度

指标：分为原子指标和派生指标。派生指标是以原子指标为基准，组装统计粒度，统计周期及业务限定而生成的

原子指标是对指标统计口径，具体算法的一个抽象。根据计算逻辑复杂性，原子指标分为：

原生的原子指标：例如支付金额

衍生原子指标：基于原子指标组合构建。例如，客单价通过支付金额除以买家数据组合而来

派生指标是业务中常用的统计指标。为了保证统计指标标准，规范，唯一性的生成

业务限定：统计的业务范围，用于筛选出符合业务规则的记录（类似于SQL中where后的条件，不包括时间区间）。

原子指标是计算逻辑的标准化定义，业务限定则是条件限定的标准化定义。

统计周期：统计的时间范围，也可以成为时间周期。例如最近一天，最近30天等（类似于SQL中where后时间条件）。

统计粒度：统计分析的对象或视角，定义数据需要汇总的程度，可以理解为聚合运算时的分组条件（类似于SQL中groupby的对象）。粒度是维度的一个组合，指明统计的范围。例如，某个指标是某个卖家在某个省份的交易额，则粒度就是卖家、省份这两个维度的组合。如果需要统计全表的数据， 则粒度为全表。在指定粒度时，需要充分考虑到业务和维度的关系。统计粒度也被称为粒度，是维度或维度组合，一般用于派生指标构建，是汇总表的唯一识别方式。

基本概念之间的关系和举例：

 确定需求

业务调研

充分的业务调研和需求分析是数据仓库建设的基石，直接决定数据仓库是否建设成功。

需要相关业务人员介绍具体的业务，以便明确每个团队的分析员，运营人员的需求，沉淀出相关文档。

通过调研了解一下信息：

  • 用户的组织架构和分工界面。例如：用户可能分为数据分析、运营、维护部门，各个部门对数仓需求不同，您需要对不同部门分别进行调研
  • 用户的整体业务架构，各个业务模块之间的联系与信息流动的流程。梳理出整体的业务数据框架
  • 各个已有的业务系统的主要功能及获取的数据

案例：

电商业务板块分为招商、供应链、营销、服务四个板块，每个板块的需求和数据应用都不同，在构建数仓之前，需要明确构建数仓服务的业务板块类型、每个板块具体满足什么业务需求。

公司电商 招商 供应链 营销 服务
商业目标/业务需求        
数据需求        
核心数据        
数据应用        

 

(1)分析业务流程

业务过程可以是单个业务时间（例如交易的支付、退款），也可以是某个事件的状态（例如当前账户余额），还可以是一系列相关

事件组成的业务流程；取决于分析的是某件事过去发生的情况，当前状态或是时间流转效率。

分析业务过程的流程如下：

  • 选择粒度，在业务过程事件分析中，需要判断所有分析细分的程度和范围，从而决定选择粒度
  • 识别维表，选择好粒度之后，选择需要基于此粒度设计维表，包括维度属性等，用于分析是进行分组和筛选
  • 最后，确定衡量的指标

案例：经过业务过程调研，电商营销业务的交易订单模块的业务流程：

（2）划分数据域

数据仓库是面向主题的应用，主要功能是将数据综合、归类并进行分析利用

数据域是指面向业务分析，将业务过程或维度进行抽象的集合，为保障整个体系的生命力，数据需要抽取提炼，

并长期维护更新，但是不轻易变动，划分数据域是满足两点：

  • 能涵盖当前所有业务需求
  • 能在新业务进入时，无影响的包含进已用的数据域中和扩展新的数据域

划分数据域，需要分析各个业务模块中有哪些业务活动。数据域，可以按照用户企业的部分划分，也可以按照业务过程或业务板块中的功能模块划分

案例：电商营销业务模块可以划分以下数据域，数据域中每一部分，都是根据实际业务过程进行归纳，抽象得出的

数据域 业务过程举例
会员和店铺域 注册、登录、装修、开店、关店
商品域 发布，上架，下架，重发
日志域 曝光、浏览，点击
交易域 下单，支付、发货、确认收货（交易成功）
服务域 商品收藏，拜访，培训、优惠券领用
采购域 商品采购

（3）定义维度与构建总线矩阵

明确每个业务域中有哪些业务过程后，需要定义维度，并基于维度构建总线矩阵

定义维度

在确认收货的业务过程中，维度所依赖的业务角度主要有两个，即商品和收货地点（地域）。

  • 商品角度分析：
    • 商品ID（主键）
    • 商品名称
    • 商品交易价格
    • 商品新旧程度：0全新；1闲置；2二手
    • 商品类目ID
    • 商品类目名称
    • 品类ID
    • 品类名称
    • 买家ID
    • 商品状态：0正常；1用户删除；2下架；3未上架
    • 商品所在城市
    • 商品所在省份
  • 地域缴费分析
    • 城市code
    • 城市名称
    • 省份code
    • 省份名称

构建总线矩阵

总线矩阵将用于指导后续事实模型中关联维度的定义,构建数据仓库的雪花模型

需要定义数据域下的业务和维度，并明确每个业务过程和哪些维度相关

下表是A公司电商板块交易功能的总线矩阵，我们定义了购买省份、购买城市、类目ID、类目名称、品牌ID、品牌名称、商品ID、商品名称、成交金额等维度，并明确了不同业务过程包含了哪些维度。

（4）明确统计指标

指标定义注意事项

原子指标是明确的统计口径，计算逻辑（dwd明细事实表）。派生指标即常见的统计指标（dws聚合事实表）

派生指标=时间周期+业务限定+原子指标+统计粒度

真实操作过程中，DWD事实模型或DIM维度模型定义完成，才能创建原子指标。通常情况下，了解具体报表需求后，即可进行派生指标的创建。在新建派生指标前，必须完成原子指标的创建，且需要确认原子指标的来源模型中

有维度模型（关联或本身即维度模型），以保证可以设置派生指标的统计粒度。注意事项：

  • 原子指标和业务限定来源于同一张维度表或事实表，且继承来源表的数据域
  • 统计粒度和时间周期必选，是否选择业务限定有具体的派生指标语义决定。例如，如果支付金额为原子指标

          则最近七天买家支付金额（统计粒度为买家、时间周期为最近七天）和最近七天买家支付宝支付金额（统计

           粒度为买家，业务限定为支付宝，时间周期为最近其他）都可以作为派生指标

  • 派生指标唯一归属一个原子指标，且继承原子指标的数据域

根据业务需求确定指标

案例：数据需求为最近一天厨具类目的商品在各省的销售总额，该类目销售额top10的商品名称，各省购买力分布（人均消费额）等

需要用到对应事实模型的度量--商品的销售金额，根据业务需求，定义出：

  • 原子指标：商品成功交易的金额总和
  • 派生指标：最近一天全省厨具类目各商品的销售总额

                             最近一天全省厨具类目的人均消费额（消费总额除以人数）

梳理指标体系：
根据公司实际指标体系，简单的做下总结

  • 确定数据来源 如哪些业务系统，订单、商品、库存、供应商、合作商、采购、营建、资产、运营等系统。
  • 确定各系统的数据体系 如现制商品数、外购商品数、等效商品数、客均商品数、响应时长、超时时长、外送时长、准时率等。
  • 数据域划分 如用户域（用户注册、用户消费、用户留存）、流量域（用户下载、用户启动、用户使用（页面访问、下单、分享、点击）、用户下单）、订单域（订单、订单商品、订单制作、订单配送、订单评价）、

商品域（商品SKU、商品类目、商品配方）、结算域（收入、成本、增值税）、门店域（门店基础、门店人员、门店地址）、供应链域（门店库存、仓库库存、损耗）

数据仓库--业务流程

确定主题？

既然是仓库，就应该“按类”存储；按主题分类，确定好主题边界合理划分主题

比如财务为主题时，就要定义好边界，不能掺杂人力的数据

（1）划分主题域：

主题就是指我们要分析的具体方面；一是分析角度（维度），二是要分析具体量度

 

概念逻辑设计：业务模型

逻辑模型设计：抽象了主题和表的属性

物理模型：落地到数据库

数据仓库--数据来源

数据库、FTP/HTTP、日志、其他文档

数据仓库--数据采集

 数据源分为数据库，日志，平面文件

MySQL：使用Sqoop，DataX

Datax：Ali开源的，基于多线程级别的并行实现离线，异构平台同步工具，能够快速实现数据异构数据源的离线同步

流程：SourceData--Reader--Channel--Writer--TargetData

Sqoop：关系型数据与HDFS之间数据同步工具，多数使用sqoop1

Sqoop作为一个客户端，通过参数转化成MapReduce，提交到yarn集群上，由nodemanager通信采集；

采集数据源，采集只有map过程，指定map数量为4，相对指定4个程序读取数据源

问题：如果公司网络不是普遍连接，可能会使yarn的节点与数据库连接不上，导致不能导入

Sqoop1与Sqoop2：

Sqoop1：

仅有一个客户端，架构简单明了，部署即用，使用门槛比较低；但是耦合性强，用户密码暴露导致不安全

Sqoop2：

服务端部署，运行；提供cli，restapi，webUI等入口，concetor集中管理，RDBMS账号控制更安全，但Sqoop2仅负责数据的读写操作，部署相对负责

Log日志：使用Flume

包含三部分：

Source：负责根据配置策略，捕获event并投递到Channel中

Sink：从Channel中消费数据，投递到下一个目标端

Channel：一个暂缓event的数据通道

使用场景：

1.日志归集：将不日志合并

2.load balance

3.日志分流or多路存储

4.整合kafka（能够保持数据一致性，将所有数据落地kafka，如果有实时和离线可以保证订阅数据一致）

5.整合SparkStreaming

数据仓库--数仓分层

维度表设计原则

  • 尽可能生成丰富的维度属性。

    例如电商公司的商品维度可能有近百个维度属性，为下游的数据统计、分析、探查提供了良好的基础。

  • 尽可能多的给出包含一些富有意义的文字性描述。

    属性不应该是编码，而应该是真正的文字。在阿里巴巴维度建模中，通常是编码和文字同时存在，例如商品维度中的商品ID和商品标题、类目ID和类目名称等。ID通常用于不同表之间的关联，而名称通常用于报表标签。

  • 区分数值型属性和事实。

    数值型字段是作为事实还是维度属性，可以根据字段的常用用途区分。例如，若用于查询约束条件或分组统计，则是作为维度属性；若用于参与度量的计算，则是作为事实。

  • 尽量沉淀出通用的维度属性。
    • 通过逻辑处理得到维度属性。
    • 通过多表关联得到维度属性。
    • 通过单表的不同字段混合处理得到维度属性。
    • 通过对单表的某个字段进行解析得到维度属性。

事实表设计原则

  • 尽可能包含所有与业务过程相关的事实。

    设计事实表的目的是度量业务过程，所以分析哪些事实与业务过程有关，是事实表设计中至关重要的。在事实表中应该尽量包含所有与业务过程相关的事实，即使存在冗余，但是因为事实通常为数字型，带来的存储开销不会很大。

  • 只选择与业务过程相关的事实。

    在选择事实时应该注意，只选择与业务过程有关的事实。例如，A公司的订单交易业务流程中，在设计下单这个业务过程的事实表时，不能包含支付金额这个表示支付业务过程的事实。

  • 在选择维度和事实之前，必须先声明粒度。

    粒度（数据行数的最小单位，非统计粒度）的声明是事实表设计中不可忽视的重要一步。粒度用于确定事实表中一行所表示业务的细节层次，决定了维度模型的扩展性。在选择维度和事实之前，必须先声明粒度，且每个维度和事实必须与所定义的粒度保持一致。在事实表中，通常通过业务描述来表述粒度并定义事实表主键，但对于聚集性事实表的粒度描述（例如存在下单、支付等多个事务），可以基于多个字段拼接，形成新的字段作为事实表主键，也可以不定义主键，这样一行记录即最小粒度。

  • 在同一个事实表中，不能包含多种不同粒度的事实。

    事实表中所有事实的粒度需要与表声明的粒度保持一致，在同一个事实表中不能有多种不同粒度的事实。

  • 事实的单位要保持一致。

    在同一个事实表中，事实的单位应该保持一致。例如，原订单金额、 订单优惠金额、订单运费金额这三个事实，应该采用一致的计量单位，例如统一为元，以方便使用。

  • 汇总表设计原则

    聚集是指针对原始明细粒度的数据进行汇总。DWS汇总数据层是面向分析对象的主题聚集建模。在本教程中，最终的分析目标为：最近一天某个类目（例如，厨具）商品在各省的销售总额、该类目销售额Top10的商品名称、各省用户购买力分布。因此，我们可以以最终交易成功的商品、类目、买家等角度对最近一天的数据进行汇总。数据聚集的注意事项如下：
    • 聚集是不跨越事实的。聚集是针对原始星形模型进行的汇总。为获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的，所以原子指标只能基于一张事实表定义，但是支持原子指标组合为衍生原子指标。
    • 聚集会带来查询性能的提升，但聚集也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前存在的、已经被汇总到聚集表中的数据需要被重新调整。

    此外，进行DWS层设计时还需遵循数据公用性原则。数据公用性：需考虑汇总的聚集是否可以提供给第三方使用。您可以思考，基于某个维度的聚集是否经常用于数据分析中。如果答案是肯定的，就有必要把明细数据经过汇总沉淀到聚集表中。

数据仓库--模型设计

  • 数据仓库整体采用Kimball维度建模的方式，主要有两点：
  1. 维度建模能够快速响应业务需求的变化
  2. 可以采用增量的方式进行开发，按数据域进行增量开发
  • 维度表设计规范 
  1. 每个拉链表生成唯一的代理健，代理健以_key结尾
  2. 每个维表生成一个特殊行，避免事实表数据丢失以及处理迟到维情况
  3. 尽可能的宽（反规范化）、包括可能的维度属性，减少关联并且保证一致性
  4. 维表属性划分按照自然属性进行划分，即属性不需要通过事实表进行建立关系的应放在同一个维表中
  • 事实表设计规范
  1. 将每个业务过程用独立的事实表来存放
  2. 将常用的维度采用退化维保留在事实表中
  3. 尽可能保留最细粒度的事实数据
  4. 尽量将不可加事实拆分成可加事实事实表划分依据：
  • 事实表划分依据
  1. 事实是否同时发生
  2. 事实表的粒度是否相同

经典数据仓库模型

数仓建模的目标：

访问性能：能够快速查询所需的数据，减少数据I/O

数据成本：减少不必要的数据冗余，实现计算结果数据复用，降低大数据系统中存储成本和计算成本

使用效率：改善用户应用体验，提高使用数据的效率

数据质量：改善数据统计口径的不一致性，减少数据计算错误的可能性，提供高质量的，一致的数据访问平台

一、实体关系（ER）模型

将事物抽象成"实体"

实体：客观存在的事物，商品，人，物等

属性：对主题的修饰，比如商品的名称，颜色，尺寸

关系：现实的物理事件是依附于实体的，商品入库事件，依附实体的商品、货位，就会有“库存”的属性产生；用户购买商品，依附实体用户，商品，就会有“数量”，“金额”等属性产生

购物过程：用户，商品，商家都是实体；用户购物的过程三方形成关系，实体自身带有属性

在设计表时，实体会形成表，而关系也形成表*（比如购买商品，用户和商家形成订单表，而购买件数，金额就行形成关系也会生成表）

实体之间的关系：

1：1的关系

1：n的关系

n：m的关系

针对商品入库，ER图构建

（1）抽象出实体：商品、货位

（2）实体之间的关系：一个货位可以存储多个商品，一个商品仅能放在一个货位上

（3）实体属性，关系属性：商品：ID，名称，颜色；货位：位置，容量

         入库关系:库存

ER实体模型

将实体抽象出来：商品，货位

关系也要抽象表：库存

相应的实体也具有相关属性

 

 

Bill Inmon：自上而下建设数据仓库，数据集市的信息来源于数据仓库；在数据仓库中，信息存储符合三范式

第一范式：字段都应该是原子性的

第二范式：实体的属性完全依赖于主关键字，不能存在仅依赖主关键字一部分的属性

拆分后分数值依赖于课程这个主键

学生ID 所属系 系主任 所修课程 分数
001 物理系 张三 0001 100
001 物理系 张三 0002 90

 

学生ID 所属系 系主任     学生ID 所修课程 分数
001 物理系 张三     001 0001 100
          001 0002 90

第三范式:任何非主属性不依赖于其他非主属性(传递ID)

商品颜色对应了商品ID有对应了ID,商品ID又对应了ID,形成传递

ID 商品ID 商品颜色 商家 用户ID
001 001001 白色 安踏 000001

 

ID 商品ID 商家 用户id     商品id 商品颜色 尺寸
001 001001 安踏 000001     001001 百色 40码

E-R实体关系模型应用场景：

ER模型是数据库设计的理论基础；

数据仓库底层ods，dwd也多可以采用ER关系模型设计 

二、维度模型

事实表和维度的分界线：

用于度量的事实表
事实定义：表示某个业务度量
存储什么内容：组织机构业务过程事件的性能度量结果。
存储原则：将来源于同一个业务过程的底层度量结果存储于一个维度模型中。
为什么这么存储：因为数据量巨大。
事实有哪些类型：可加，半可加，不可加
事实表的粒度的类型：事务，周期性，累计快照
注意事项：
    1，允许多个组织的业务用户访问同一个单一的集中式数据仓库，确保他们能在整个企业中使用一致的数据。
    2，物理世界的每个度量事件对应的事实表行具有一对一的关系。每行的数据是一个特定级别的细节(粒度)数据
    3，同一个事实表的度量行比具有相同的粒度

用于描述环境的维度表
存储的内容：包含于业务过程度量事件有关的文本环境
维度表的语意：“谁(who)，什么(what)，哪里(where)，何时(when)，为什么(why)，如何(how)”
维度表的特点：通常有多列(多个属性)，和事实表比较 维度表通常小的多，单一主键
作用：是用于和事实表连接操作时实现参照完整性的基础，可作为查询约束、分组、报表标识。
注意：
    1，尽量减少在维度表中使用代码，将代码替换为详细的文本属性。尽量减少他们对代码转换注释的依赖。
    2，为维度属性提供详细的业务术语耗费的精力越多，效果越好，强大的维度属性带来的回报是健壮的分片-分块能力。
    3，连续值数字基本上可以认为属于事实，来自于不太大列表的离散数字基本可以认为是维度属性
    4，维度表可以冗余，不一定要满足第三范式，通常是非规范化的。对于维度表的存储空间的权衡 往往 关注 简单性和可访问性；是否方便使用，是否能提高查询性能。

 

以日常工作量为例

工作量的属性：人员，工作日期，上班时长，加班时长，工作内容，是否外勤

上班时长，加班时长是主干；以工作量为主题的主要内容

人员，日期，是否外勤可以本分类的为单独列出来，称为维度表

事实表可以设计成如下
WorkDate  EmployeeID，WorkTypeID，Islegwork，Content，
而时间，员工，工作类型，是否外勤则归为维度表。

 

Ralph Kimball：自下而上建设数据仓库，数据仓库是企业内所有数据集市的集合，信息总是被存储在多维模型中

（公司是从小到大的，先构建一个面向订单的主题，再构建一个面向仓库的主题，而主题按照维度建模，所有主题最终汇总形成数据仓库）

第一种方式将所有独立系统数据整合很难和容易遗漏；第二种方式划分主题，围绕这个主题创建一个集市，在创建另一个主题时，再构建集市，问题主题设置过多集市就会出现一个字段有不同的表示

最终将数据仓库划分不同的层次。（ODS，DW，DM）

维度建模将数据仓库中的表划分为事实表和维度表两种类型

事实表:在ER模型中抽取了有实体，关系，属性三种类型，在现实世界中，每一个操作性事件，基本都是发生在实体间；伴随着这种操作事件的发生，会产生度量的值，而这个过程就产生了一个事实表，存储了每一个可度量的事件。

电商场景：一次购买事件，设计主体包括客户，商品，商家；产生的可度量值包括商品金额，数量，件数等

用于度量的事实表

存储内容：业务过程事件的性能度量结果
存储原则：来源于同一业务过程的底层度量结果存储于同一维度模型
事实表类型：可加，半可加，不可加
事实表粒度：通用，周期性，累积快照

注意事项：
1，允许多个业务用户访问同一个单一的集中式数据仓库，确保使用数据的一致性
2，物理世界的每个度量事件对应的事实表行具有一对一的关系。每行的数据是一个特定级别的细节(粒度)数据
3，同一个事实表的度量行比具有相同的粒度

 

维度表：看待事物的角度。比如从颜色、尺寸的角度来比较手机的外观，从CPU、内存等比较手机性能

维度表一般为单一主键，在ER模型中，主体为客观存在的事物，会带有自己的描述性属性，属性一般为文本性，描述性的；这些描述性被称为维度

比如商品：单一主键：商品ID，属性包括产地，颜色，材质，尺寸，单价等，但并非属性一定是文本，比如单价，尺寸，均为数值型描述的，日常主要的维度抽象包括：时间维度表，地域维度表等

维度表之间还可以有层级关系：dim_乡镇~dim_县~dim_市~dim_省

用于描述环境的维度表

存储内容：业务过程度量事件有关的文本环境
维度表的语意：“谁(who)，什么(what)，哪里(where)，何时(when)，为什么(why)，如何(how)”
维表特点：通常有多列(多个属性)，单一主键，利用关键字通过事实表外键约束于事实表的某一行

注意：
    1，尽量减少在维度表中使用代码，将代码替换为详细的文本属性。尽量减少他们对代码转换注释的依赖。
    2，为维度属性提供详细的业务术语耗费的精力越多，效果越好，强大的维度属性带来的回报是健壮的分片-分块能力。
    3，连续值数字基本上可以认为属于事实，来自于不太大列表的离散数字基本可以认为是维度属性
    4，维度表可以冗余，不一定要满足第三范式，通常是非规范化的。对于维度表的存储空间的权衡 往往 关注 简单性和可访问性；是否方便使用，是否能提高查询性能。

 

案例
以购物订单为例,以维度建模的方式设计该模型

涉及到的实时表为订单表,订单明细表,维度包括商品维度,用户维度,商家维度,区域维度,时间维度

商品维度:商品ID,商品名称,商品种类,单价,产地等
用户维度:用户ID,姓名,性别,年龄,常住地,职业,学历等
时间维度:日期ID,日期,周几,上/中/下旬,是否周末,是否假期等

订单中包含的度量:商品件数,总金额,总减免
         描述性属性:下单时间,结算时间,订单状态等
订单明细包含度量:商品ID,件数,单价,减免金额
         描述性属性:入购物车时间,状态

 

星型/雪花模型对比:

冗余:雪花模型符合业务逻辑设计，采用#NF设计,有效降低数据冗余：星型模型的维度表设计不符合3NF，犯规范化，维度表之间不会直接关联，牺牲部分存储空间

性能：雪花模型由于存在维度间的关联，采用3NF降低冗余，通常在使用过程中，需要连接更多维度表，导致性能降低；星型模型反3NF，采用降维的操作将维度整合，以存储空间为代价有效降低维度表连接数，性能较雪花模型高。

ETL：雪花模型符合业务ER模型设计原则，在ETL过程中相对简单，但是由于附属模型限制，ETL任务并行较低；星型模型在设计维度表时反范式设计，所以在ETL过程中整合业务数据到维度表有一定难度，但由于避免附属维度，可并行化处理

问题：数据仓库，不针对某一个分析主题，而是有多个分析主题，即多个事实表，维度表怎样设计？

多个事实表，维度共用

问题：即使同一分析主题，也可能存在多个事实表，维度表如何设计，多个时间维度？

维度同样共用

维度表设计：

1.代理键：维度表中必须有一个能够唯一表示一行记录的列，通过该列维护维度表与事实表之间的关系，一般在维度表中业务主键符合条件可以当做维度主键

问题：当整合多个数据源的维度时，不同数据源的业务主键重复怎么办？

维护一个与系统业务不相关的唯一标识的自增代理键（通常是数值型，自增）；

hive中如何实现代理键自增id？

（1）使用UDF（2）使用MySQL语句

思路：获取上一日最大的id值，在最大id上实现新增数据的自增
全量数据
s1,s2----dim_good1

增量数据
s1,s2----tmp_inc
--query '....s1 where create_time>'2020-02-01'
--query '....s2 where create_time>'2020-02-01''

代理键
pid

insert overwrite table dim_good1 partition(dt='2020-02-01')
select
t2*,
（对新增id进行排序再加上上一日最大id）
row_number() over(order by id) + t1.max_id as gid
from tmp_inc as t2
cross join
（获取上一日最大id）
(select coalesce(max(gid),0) as max_id from dim_good1 where dt='2020-01-31') t1
union all 
select * from dim_good1 where dt='2020-01-31'

2.稳定维度表(不需要进行分区)

部分维度表的维度时在维度表产生后，属性是稳定的，无变化的；比如时间维度，区域维度等，针对这种维度，设计维度表时候，仅需要完整的数据，不需要天的快照数据，因为当前数据状态既是历史数据状态

3.缓慢渐变维度 

维度数据会随着时间发生变化，变化速度比较缓慢，这种维度数据通常称作为缓慢渐变维；由于数据仓库需要追溯历史变化，尤其是一些重要的数据，所以历史状态也需要采取一定的措施进行保存

  • 每天保存当前数据的全量快照数据，该方案适合数量较小的维度，使用简单的方式保存历史状态
  • 在维度表中添加关键属性值的历史字段，仅保留上一个状态
id name dept last_dept
001 小王 dp1 dp2

拉链表

当维度数据发生变化时，将旧数据置为失效，将更改后的数据当做新的记录插入到维度表中，并开始生效，这样能够记录数据在某种粒度上的变化历史

id name dept start_date end_date
1 小王 dp1 2020-01-29 2020-01-31
1 小王 dp2 2020-02-01 9999-12-31
因为是对维度表做拉链，所以同一个维度实体必然存在多条记录，此时维度的原子性主键就不存在了（id不是主键了）

问题：拉链表怎么和事实表关联？

添加代理键（如何不添加uid，id都是1，按部门统计时就会出现2X2的现象）

（可以统计出同一个用户在不同时期所在部门，如果不用拉链表，保留的只是最新的数据，那么按部门统计时使用最新的数据，没有之前的数据）

问题:事实表中id变化为uid，事实表来源于业务表，代理键和业务本身没有关系，那么怎么在实时表中装载代理键？

事实表中历史的用户维度id不会发生变化，所以事实表的代理键仅发生在新增数据上

--事实表装载代理键--
fact_order：oid（订单id） uid（用户id，当成代理键） tm_id ...

dim_user：uid（创建自增id） id（业务id） name dapt start_time end_date
（将维度表的uid装载到事实表的uid）

order：oid id create_time update_time

筛选出昨天新增数据
--query ' order where create_time>=2020-02-01' --order_inc

insert overwrite table fact_oder partition(dt='...')
select tmp.* from
（select t1.* ，t2.uid 
from order_inc as t1（昨天采集的数据）
join dim_user as t2 on t1.id=t2.id）tmp（关联后有多条记录）
where create_time>=start_date and create_tim<=end_date(维度的生效时间)
union all
select * from fact_order

 

问题：如何取2020-02-05的数据？

select * from use where start_date<=2020-02-05 and  end_date>=2020-02-05

 

代理键是维度建模中极力推荐的方式，它的应用能有效的隔离源端变化带来的数仓结构不稳定问题，同时也能提高数据检索能力

但是，代理键维护代价非常高，尤其是数据装载过程中，对事实表带来了较大的影响，在基于hive的数据建设影响更加严重，比如代理将成本

事实表中关联键的转载，不支持非等值关联问题带来etl过程更加复杂（对于维度时尽量不是用拉链表）

故，在大数据体系下，谨慎使用代理键，同时对于缓慢渐变维度场景，可以考虑空间换时间，每天保留全量快照；但是这样会带来存储成本，根据实际情况衡量

维度表拆分、合并

  • 不同的主题同样的维度关注点是不一样的，不同的主题共用一个维度表保证数据一致性，但是可以将维度提取出一部分作为维度子表供应一个主题

比如京东自营产品和其他商铺：可以基于商家维度进一步拆分将特有属性列拆分出来

  • 固有信息和非固有信息拆分：

          姓名，性别，身份证，出生年月日（不要使用年龄，因为年龄是不断变化的）；

          常住地，手机号等非固有在一个扩展表中（除非维度变化非常频繁，不推荐使用；把动静数据放在一起）

案例：

 针对公司员工进行维度设计：
公司包括销售体系，技术体系，行政体系，物流体系等；很多部门都很关注公司员工信息，比如物流体系分析人员发货错误率，人员效率；
财务部门关注员工薪资，待遇以便分析财务预算等，HR关注薪资待遇以便评估公司人才计划等

从另一个角度看，销售体系员工有相对应的销售区域划分，技术体系员工有相应的技术方向等

 问题：不同主题的用户维度是对不齐的，比如物流人员就没有技术方面；技术人员没有销售大区。

方案1：针对不同的主题，建一个基础维度表，将所有信息写入到维度表中（针对不同的主题，维度表的字段可能为空）；如果针对某个主题，可以

在提取一个子维度表（推荐）

方案2：将所有主题共有的信息提取出来作为一个共用维度表，在针对各个主题特有信息做一个特有维度表

事实表设计：包含业务表本身的数据，业务表的度量

一般事实表中包含两部分信息：维度、度量，度量即为事实；但是有些特殊情况下，事实表中无度量信息，只是记录一个实际业务动作。

明细事实表：

事实表有粒度大小之分，基于数据仓库层次架构，明细事实表一般存在于dwd层（维度规范化），该层事实表表设计不进行聚合，汇总动作，仅做数据规范化，数据降维动作，同时数据保持业务事务粒度，确保数据信息无丢失

数据降维：为了提高模型易用性，将常规维度表中的常用属行数据冗余到相应的事实表中，从而在使用的时候避免维度表关联的方式，即为数据降维（将常用的维度表字段拿到事实表中），也就是有些属性不用单独再去创建维度表，可以降维到事实表中

设计事实表的主要业务依据是业务过程，每个业务动作事件，都可以作为一个事实，那么在一个订单处理过程中，会有多个动作，这个过程中事实表怎样设计？

方案1：单事件事实表

             对于每一个业务动作事件，设计一个事实表，仅记录该事件的事实以及状态（每产生一个动作就是一个事实表）

单事件事实表更方便跟踪业务流程细节数据，针对特殊业务场景比较方便和灵活，数据处理上也更加灵活；

不方便的地方就是数仓中需要管理的太多的事实表，同时跟踪业务流转不够直观

方案2：流程事实表

             对于一个业务流程主体，设计一个事实表，跟踪整个流程的事实以及状态流转（比如订单从下单，到最后的支付完成这个状态）

流程事实表：能过直观的跟踪业务流转和当前状态，流程事实表中，方便大部分的通用分析应用场景，由于和业务的数据模型设计思路一致，

也是目前最常用的事实表设计，但是细节数据跟踪不到位，特殊场景的分析不够灵活

场景案例：

出行领域，用户下单打车，改订单的整个流程：
用户下单--司机接单--司机做单--乘客支付--评价、投诉

如何设计明细事实表？

 

事务性事实表：（很难看出数据流转的过程，能更好的分析数据）

 

流程事实表：

事实表存储：

增量存储：

                 即每周期仅处理增量部分数据，针对状态无变化的数据比较合适

全量快照：

                 状态有变化，但每天保存当天的快照数据，对于数据量在可控范围内的情况可以采用保存策略：

  • 如果存储空间和成本可接受，完整存储，确保能够追溯到历史每天数据状态
  • 存储空间有限，考虑移动到历史快照数据到冷盘，需要使用时可恢复
  • 数据历史状态数据无太大价值，可以考虑部分删除，比如保留每月最后一天的快照数据

拉链表：

             数据量大，但缓慢变化，需要跟踪历史状态，和缓慢渐变维度类似

案例：信用卡场景
用户的信用额度，已用额度存在缓慢的变化，有需要跟踪变化的记录，设计相关事实表

源数据包括用户id，卡id，额度，已用额度，剩余额度，创建时间，更新时间

设计拉链表：

credit_aount:cart_id,use_id,amount,used_amount,create_time,update_time

1,采集数据:s_credit_amount（变化的数据）
--query 'select * from where update_time>=2020-02-01'

2,dwd拉链表:d_credit_amount_1  --2020-01-31
（拉链表的使用，数据状态发生变化后，之前日期的数据使之失效；变化后的数据是最新的数据）

drop table if exists tmp_credit_amount;
create table tmp_credit_amount as
select tmp.* from
(select
t1.card_id,
t1.user_id,
t1.amount,
t1.used_amount,
t1.create_time,
t1.start_date
(case when t2.card_id is not null（状态发生改变） and t1.end_date>'2020-02-01' then '2020-01-31'（状态改变多次，去最后一次）
else t1.end_date end ) as end_date
from d_credit_amount_1 as t1
left join
s_credit_amount（发生变化的表） as t2
on t1.credit=t2.credit（以上是发生变化的数据失效）
union all
t1.card_id,
t1.user_id,
t1.amount,
t1.used_amount,
t1.create_time,
'2020-02-01' as start_date,
'9999-12-31' as end_date
from s_credit_amount as t1) tmp

insert overwrite table d_credit_amount_1
select * from tmp_credit_amout


--基于全量快照数据做拉链
（某些数据源可能没有create_time和update_time）
dwd:d_credit_amount_d（全量快照）  
cart_id,use_id,amount,used_amount,create_time
根据全量快照表构建拉链表d_credit_amount_1

1,获取上日发生变化的数据（可使用md5值）
select t1.* 
from
(select
t1.card_id,
t1.user_id,
t1.amount,
t1.used_amount,
t1.create_time,
md5(concat(card_id,user_id,amount,user_amount,create_time)) as md5_flag
from d_credit_amount_d dt='2020-02-01') t1（以上发生变化的数据）
left join
(select
t2.card_id,
t2.user_id,
t2.amount,
t2.used_amount,
t2.create_time,
md5(concat(card_id,user_id,amount,user_amount,create_time)) as md5_flag
from d_credit_amount_d dt='2020-01-31') t2（以上是之前的数据）
on t1.card_id=t2.card_id
where t1.md5!=t2.md5（新产生的数据不等于原来的数据，说明数据变化了） or t2.card_id is null（判断空值）；

 

聚合事实表：

相对于明细事实表，聚合事实表通常是在明细事实表基础上，按照一定的粒度粗细进行汇总、聚合操作，

它的粒度较明细数据粒度粗，同时伴随这细节信息的丢失（比如：订单金额时，可能下单时间就会丢掉）；

在数仓层次结构中，通常位于dws层，一般作为通用汇总层数据存在，也可以是更高粒度的指标数据，

但是同一个事实表中，尽可能保证事实粒度一致

  • 日粒度（日报）
  • 周期性积累（包含周、月、年）
  • 历史积累（是根据之前的数据求出来的，比如：累积金额，最近的下单时间）
  • 可累加事实：在一定粒度范围内，可累加的事实度量，比如：订单金额，订单数
  • 不可累加事实：在更高粒度上不可累加的事实，比如：通过率，转化率，下单用户数 

          （对于不可累加的事实表，通常拆分设计，通过率=通过数/申请数；有细粒度数据去重计算而来的事实，正常储存，

              但是更粗粒度累积是不可直接使用，比如一天的通过率是20%，七天不能直接相加）

注意：在同一个事实表中要保持数据粒度的一致性（虽然可以算，但是不推荐）

 聚合事实表存储

在数据仓库中，按照日期范围的不同，通常包括以下类别的聚合事实表；

  • 公共维度层-通用汇总（封装底层计算逻辑，避免上次接触明细）

           应对大部分可预期、常规的数据需求，通常针对模式相对稳定的分析，BI指标计算、特征提取等场景，封装部分业务处理，计算逻辑，尽量避免用户直接使用底层明细数据，该层用到的数据范围比较广泛

  • 日粒度

           主要应对模式稳定的分析，BI日报，特征提取场景，同时日粒度也为后续积累计算提供粗粒度的底层，数据范围一般我上一日的数据

  • 周期性积累

          主要应对明确的周期性分析，BI周期性报表，数据范围一般在某一周期内(一般来自于日粒度,公共维度层)

  • 历史累积

          历史以来某一特定数据的累积，通常在用户画像，经营分析，特征提取当面场景较多，书籍数据范围比较广泛，通常是计算耗时较长的一部分，比如某门店累积营业额（非可度量，描述性）

订单粒度（通用汇总：就是在订单的明细表进行轻度计算）
订单id，用户id，司机id，
性别，年龄，下单时间（维度退化），
用户等待接单时长，用户等待司机时长，预估时长，预估路程，实际时长，实际路程（指标）
应付金额，实付金额，优惠券减免，新用户减免（属于支付系统事实表，在通用层尽量汇总全面）

用户粒度（通用汇总）
用户id
性别，年龄（维度退化）
下单笔数，接单笔数，主动取消笔数，被动取消笔数，行程（指标）
优惠，消极评价笔数，中性评价笔数，积极评级笔数（属于支付系统事实表，在通用层尽量汇总全面）

司机粒度（通用汇总）
司机id，
品牌，车系（维度退化）
出车时长，做单时长，行驶里程，做单金额，奖励金额（指标）
主动取消笔数，被动取消笔数（属于订单系统事实表，在通用层尽量汇总全面）

日粒度数据（交易相关日报，用到时间、区域等维度表）
时间id，地域id
下档用户数，下单数，支付用户数，支付单数，应收金额，实付金额，优惠金额（指标；根据实际业务需求划分）

累积粒度
注册用户数，下单用户数，首次下单用户数，取消订单用户数，支付用户数，被投诉用户数

 

1.3数据仓库的开发

背景

互联网信用贷款行业，主要业务流程

用户注册后，通过一系列的信息认证，信息认证成功后，可以申请授信，然后有风控策略、模型给出信用评定，同时给予相应额度

用户获取额度后可以在平台发起贷款，贷款也要再次通多相应的风控模型，评级通过后可以放宽用户收到放款后，需要分期还款，每期归还一定的额度

 

1.3.1梳理业务流程

（1）梳理业务流

（2）梳理数据流

比如：用户注册提供了什么信息（姓名，性别，手机号）

 （3）

数据类型，存储介质

（4）

需求--功能性需求，非功能性需求

  • 梳理出ER实体关系模型

（5）数据采集方案

  •  表的数据量、每日增量、create_time和update_time，自增id，源表索引

           条件1：自增id，全量抽取（--split by id -- m4，抽取策略是取id最大值与最小值/4）

           条件2：create_time，增量抽取（--split by create_time -- m4，这时不要指定id；避免数据倾斜）

  • 确定使用增量还是全量

          用户、订单等变化频率不大，推荐使用全量采集

          申请授信表变化量大的表，增量采集

（6）数据仓库模型设计

  • 抽象维度表

  • 根据维度表和ER实体表创建事实明细表

 

  • 聚合事实模型设计（通用汇总层）

（7）数据仓库ETL落地

1.3.1数据开发步骤

  • 确定统计那些指标（根据业务需求）
    • 需求确定  比如：pv  uv  topn
    • 建模确定表结构create table t1 ( pv int , uv int ,topn string)
    • 实现方案确定（将确定的指标封装进去即可，之后通过大量的逻辑运算得到t表即可）
  • 数据开发过程
    • 表落地
    • 写sql语句实现业务逻辑
    • 部署代码
    • 数据测试
    • 试运行和上线

1.3.2案例步骤：

用户画像开发--客户基本属性表

--用户画像-客户基本属性模型表
create database if not exists gdm;
create table if not exists gdm.itcast_gdm_user_basic( 
user_id string                  ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string              ,--用户生日
user_age  bigint            ,--用户年龄
hex_phone string            ,--手机号
fore_phone string            ,--手机前3位
dw_date timestamp
) partitioned by (dt string);

#***************************
--客户基本属性模型表BDM层
--一般从mysql直接抽取过来，不做任何处理
create database if not exists bdm;
create external table if not exists bdm.itcast_bdm_user( 
user_id string                ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string            ,--用户生日
hex_phone string            ,--手机号
) partitioned by (dt string)
row format delimited fields terminated by ',';
alter table itcast_bdm_user add partition (dt='2017-01-01') location '/business/itcast_bdm_user/2017-01-01';

--客户基本属性表FDM层
--我们通过对BDM层预处理来构建FDM层，比如年龄：我们在BDM只有个生日，我们可以通过生日得到年龄字段或是对手机号进行切割得到前三位
create database if not exists fdm;
create table if not exists fdm.itcast_fdm_user_wide( 
user_id string                ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string            ,--用户生日
user_age  bigint            ,--用户年龄
hex_phone string            ,--手机号
fore_phone string            ,--手机前3位
dw_date  timestamp
) partitioned by (dt string);

--加载数据
insert overwrite table fdm.itcast_fdm_user_wide partition(dt='2017-01-01')
select 
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
(year(current_date)-(year(birthday))) as age,
t.hex_phone,
substring(t.hex_phone,3) as fore_phone,
from_unixtime(unix_timestamp())  dw_date 
from bdm.itcast_bdm_user t where dt='2017-01-01';

--用户画像-客户基本属性模型表GDM层
create database if not exists gdm;
create  table if not exists gdm.itcast_gdm_user_basic( 
user_id string                  ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string              ,--用户生日
user_age  bigint            ,--用户年龄
hex_phone string            ,--手机号
fore_phone string            ,--手机前3位
dw_date timestamp
) partitioned by (dt string);

--加载数据
insert overwrite table  gdm.itcast_gdm_user_basic partition(dt='2017-01-01') 
select 
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
t.user_age,
t.hex_phone,
t.fore_phone,
from_unixtime(unix_timestamp())  dw_date
from (select * from fdm.itcast_fdm_user_wide where dt='2017-01-01') t;

 

演示模型表开发脚本：
######################
#名称：客户基本属性模型表
#    itcast_gdm_user_basic.sh
######################
#!/bin/sh

#获取昨天时间
yesterday=`date -d '-1 day' "+%Y-%m-%d"`

#指定跑哪一天数据
if [ $1 ];then
    yesterday=$1
fi

SPARK_SUBMIT_INFO="/export/servers/spark/bin/spark-sql --master spark://node1:7077 --executor-memory 1g --total-executor-cores 2 --conf spark.sql.warehouse.dir=hdfs://node1:9000/user/hive/warehouse"

SOURCE_DATA="/root/source_data"

SQL_BDM="create database if not exists bdm;
create external table if not exists bdm.itcast_bdm_user( 
user_id string                ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string        ,--用户生日
hex_phone string            ,--手机号
) partitioned by (dt string)
row format delimited fields terminated by ','
location '/business/bdm/itcast_bdm_user' ;
alter table bdm.itcast_bdm_user add partition (dt='$yesterday');"


SQL_FDM="create database if not exists fdm;
create table if not exists fdm.itcast_fdm_user_wide( 
user_id string                ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string        ,--用户生日
user_age  bigint            ,--用户年龄
hex_phone string            ,--手机号
fore_phone string            ,--手机前3位
dw_date  timestamp
) partitioned by (dt string);"

##加载数据
LOAD_FDM="
insert overwrite table fdm.itcast_fdm_user_wide partition(dt='$yesterday')
select 
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
(year(current_date)-(year(birthday))) as age,
t.hex_phone,
substring(t.hex_phone,3) as fore_phone,
from_unixtime(unix_timestamp())  dw_date 
from bdm.itcast_bdm_user t where dt='$yesterday';"

SQL_GDM="create database if not exists gdm;
create  table if not exists gdm.itcast_gdm_user_basic( 
user_id string                ,--用户ID
user_name string            ,--用户登陆名
user_sex  string            ,--用户性别
user_birthday string        ,--用户生日
user_age  bigint            ,--用户年龄
hex_phone string            ,--手机号
fore_phone string            ,--手机前3位
dw_date timestamp
) partitioned by (dt string);"


##加载数据到GDM
LOAD_GDM="insert overwrite table  gdm.itcast_gdm_user_basic partition(dt='$yesterday') 
select 
t.user_id,
t.user_name,
t.user_sex,
t.user_birthday,
t.user_age,
t.hex_phone,
t.fore_phone,
from_unixtime(unix_timestamp())  dw_date
from (select * from fdm.itcast_fdm_user_wide where dt='$yesterday') t;"


##创建BDM层表
echo "${SQL_BDM}"
$SPARK_SUBMIT_INFO -e "${SQL_BDM}"

##添加数据到BDM
hdfs dfs -put $SOURCE_DATA/itcast_bdm_user.txt /business/bdm/itcast_bdm_user/"dt=$yesterday"

##创建FDM层表
echo "${SQL_FDM}"
$SPARK_SUBMIT_INFO -e "${SQL_FDM}"

##导入数据到FDM
echo "${LOAD_FDM}"
$SPARK_SUBMIT_INFO -e "${LOAD_FDM}"

##创建GDM层表
echo "${SQL_GDM}"
$SPARK_SUBMIT_INFO -e "${SQL_GDM}"

##导入GDM数据
echo "${LOAD_GDM}"
$SPARK_SUBMIT_INFO -e "${LOAD_GDM}"

 

 

posted @ 2019-09-03 23:01  IT界一个小学生  阅读(2496)  评论(0)  编辑  收藏  举报