基于OneData方法论构建数据仓库
本文介绍基于OneData方法论构建标准、规范的数据仓库。
1 数仓构建流程
下图为构建数据仓库的整体流程。
1.1基本概念
在正式阅读本文章之前，需要了解以下基本概念：
- 业务板块：业务板块定义了数据仓库的多种命名空间，是一种系统级的概念对象。当数据的业务含义存在较大差异时，可以创建不同的业务板块，后续数据仓库的建设将按照业务板块进行划分。
- 数据域：数据域主要用于存放同一业务板块内不同概念的指标。例如，可以划分出商品域、交易域、会员域等，用于存放不同意义的指标。
- 业务过程：业务过程即业务活动中所有的事件，通常为不可拆分的事件。创建业务过程，是为了从顶层视角，规范业务中的事务内容的类型及唯一性。
- 维度：维度即进行统计的对象。通常，维度是实际客观存在的实体。遵循Ralph Kimball的维度建模理论，创建维度，即从顶层规范业务中实体（或称主数据）的存在性及唯一性。维度及维度组合，也是派生指标的统计粒度。
- 指标：指标分为原子指标和派生指标。派生指标是以原子指标为基准，组装统计粒度、统计周期及业务限定而生成的。
- 原生的原子指标：例如支付金额。
- 衍生原子指标：基于原子指标组合构建。例如，客单价通过支付金额除以买家数组合而来。
- 原子指标是对指标统计口径、具体算法的一个抽象。根据计算逻辑复杂性，将原子指标分为两种：
- 派生指标是业务中常用的统计指标。为保证统计指标标准、规范、无二义性地生成，OneData方法论将派生指标抽象为四部分：派生指标=原子指标+业务限定+统计周期+统计粒度。
- 业务限定：统计的业务范围，用于筛选出符合业务规则的记录（类似于SQL中where后的条件，不包括时间区间）。原子指标是计算逻辑的标准化定义，业务限定则是条件限制的标准化定义。
- 统计周期：统计的时间范围，也可以称为时间周期。例如最近1天、最近30天等（类似于SQL中where后的时间条件）。
-
统计粒度：统计分析的对象或视角，定义数据需要汇总的程度，可以理解为聚合运算时的分组条件（类似于SQL中group by的对象）。粒度是维度的一个组合，指明统计范围。
例如，某个指标是某个卖家在某个省份的成交额，则粒度就是卖家、省份这两个维度的组合。如果需要统计全表的数据，则粒度为全表。在指定粒度时，需要充分考虑到业务和维度的关系。统计粒度也被称为粒度，是维度或维度组合，一般用于派生指标构建，是汇总表的唯一性识别方式。
基本概念之间的关系和举例如下图所示。
2 确定需求
首先需要确定数仓构建的目标与需求，进行全面的业务调研。需要了解真实的业务需求是什么，以及确定整个业务系统能解决什么问题。
2.1 业务调研
充分的业务调研和需求分析是数据仓库建设的基石，直接决定数据仓库能否建设成功。在数仓建设项目启动前，需要请相关的业务人员介绍具体的业务，以便明确各个团队的分析员、运营人员的需求，沉淀出相关文档。
可以通过调查表、访谈等形式详细了解以下信息：
-
用户的组织架构和分工界面。
例如，用户可能分为数据分析、运营、维护部门，各个部门对数仓的需求不同，需要对不同部门分别进行调研。
- 用户的整体业务架构，各个业务模块之间的联系与信息流动的流程。梳理出整体的业务数据框架。
- 各个已有的业务系统的主要功能及获取的数据。
本文章以A公司的电商业务为例，梳理出业务数据框架如下图所示。A公司的电商业务板块分为招商、供应链、营销、服务四个板块，每个板块的需求和数据应用都不同。在构建数仓之前，需要明确构建数仓服务的业务板块类型、每个板块具体满足什么业务需求。
此外，还需要进一步了解各业务板块中已有的业务流程。业务流程通常与业务板块紧密耦合，对应一个或多个表及其所属数据源，可以作为构建数仓的原始数据来源。下表展现的是一个营销业务板块的业务流程模块。
业务流程A公司电商营销管理商品管理Y用户管理Y购买流程Y交易订单Y用户反馈Y
说明Y表示包含该功能模块，N表示不包含。
本文章中，假设用户是电商营销部门的营销数据分析师。数据需求为最近一天某个商品类目（例如厨具）在各省的销售总额、该类目销售额Top10的商品名称、各省用户购买力分布（人均消费额）等，用于营销分析。最终的业务需求是通过营销分析完成该商品类目的精准营销，提升销售总额。通过业务调研，我们将着力分析营销业务板块的交易订单功能模块。
2.2 需求分析
在未考虑数据分析师、业务运营人员的数据需求的情况下，单纯根据业务调研建设的数据仓库，可能可用性较差。完成业务调研后， 需要进一步收集数据使用者的需求，进而对需求进行深度思考和分析，并改进数据仓库。
需求分析的途径有两种：
- 通过与分析师、业务运营人员的沟通获知需求。
- 对报表系统中现有的报表进行研究分析。
在需求分析阶段， 需要沉淀出业务分析或报表中的指标，以及指标的定义和粒度。粒度可以作为维度的输入。建议 思考下列问题，对后续的数据建模将有巨大的帮助：
- 业务数据是根据什么（维度、统计粒度，简称“粒度”，是维度或维度组合）汇总的，衡量标准是什么？例如，“省份”或者“类目”是维度，订单数是原子指标。
- 基于上个问题，进一步思考明细数据层的事实模型和公共可引用的维度模型、汇总数据层的汇总模型应该如何设计？是否有公共使用，命名及逻辑相似的统计指标，目前已经重复建设使用，需要通过上述设计规范化？
举例： 数据分析师需要了解A公司电商业务中最近1天厨具类目的成交金额。
- 当获知这个需求后， 需要分析：根据什么（维度）汇总、汇总什么（原子指标）、汇总的范围有多大（业务范围即业务限定，时间范围即统计周期）。例如，类目是统计粒度（基于维度），成交金额的总和是原子指标。该案例中，粒度应该是“类目”，“类目为厨具”是业务限定，最近1天是统计周期。
说明 本例从类目为统计粒度的角度，分析需求处理。 可以在即席查询中定义汇总模型的筛选过滤条件，设定统计粒度的维度属性值为厨具，以免汇总模型数据稀疏。在真实业务场景下，可以根据业务需求、使用频度、复用性及汇总层数据计算存储进行考虑，拆解分析。例如，本例中还可以定义全表为粒度，只是该粒度中无需维度，然后定义业务限定是类目为厨具，其他保持不变，如无特殊数据情况，也可得到相同数据结果，只是计算存储过程消耗可能有不同。上述案例，不同路径，组合定义出来的派生指标，可能是相同结果，但是命名、计算逻辑实现可能略有不同。
- 基于上述拆解， 还需要进一步思考并设计明细数据层的事实模型（原子指标中成交金额的数据来源）、公共可引用的维度模型（统计粒度的来源，且需要与成交金额所属事实模型有关联关系）和汇总数据层模型（原子指标、业务限定、统计周期的拆解和定义方式）。
需求调研的分析产出通常是记录业务需求的规范定义文档（派生指标、原子指标、业务限定、统计周期、统计粒度（即维度））。结合业务调研情况， 可以进一步产出设计明细逻辑模型设计文档（维度模型、事实模型）与概念模型设计文档（维度、业务过程及其关系）。
2.3 分析业务过程
用户在业务系统中，通过埋点或日常积累的方式，获取了充足的业务数据。为梳理数据之间的逻辑关系和流向，需要理解用户的业务过程及数据系统。
可以采用过程分析法，列出整个业务过程涉及的每个环节，包括技术、数据、系统环境等。分析完企业的工作职责范围（部门）后，通过逆向工程抽取业务系统的真实模型。 从以下几方面分析数据仓库涉及的源系统及业务管理系统：
- 每个业务会生成哪些数据，存在于什么数据库中。
- 对业务过程进行分解，了解过程中的每一个环节会产生哪些数据，数据的内容是什么。
- 数据在什么情况下会更新，更新逻辑是什么。
业务过程可以是单个业务事件（例如交易的支付、退款），也可以是某个事件的状态（例如当前的账户余额），还可以是一系列相关业务事件组成的业务过程。具体取决于分析的是某些事件过去的发生情况、当前状态，或是事件流转效率。分析业务过程的流程如下：
- 选择粒度。在业务过程事件分析中， 需要预判所有分析需要细分的程度和范围，从而决定选择的粒度。
- 设计维表。选择好粒度之后， 需要基于此粒度设计维表，包括维度属性等，用于分析时进行分组和筛选。
- 确定衡量指标。
本文章中，经过业务过程调研，我们了解到A公司电商营销业务的交易订单功能模块的业务过程如下。
这是一个非常典型的电商交易业务过程图。在该业务过程中，有创建订单、买家付款、卖家发货、确认收货四个核心业务环节。确认收货即表示交易成功，所以我们重点分析确认收货环节。
在明确用户的业务过程之后， 可以根据需要分析决策的业务数据域，并在相应的数据域下创建具体的业务过程。
3 架构设计与规范定义
3.1 划分数据域
数据仓库是面向主题的应用，主要功能是将数据综合、归类并进行分析利用。数据仓库模型设计除横向的分层外，通常还需要根据业务情况纵向划分数据域。数据域是联系较为紧密的数据主题的集合，是业务对象高度概括的概念层次归类，目的是便于数据的管理和应用。
通常需要阅读各源系统的设计文档、数据字典和数据模型设计文档，研究逆向导出的物理数据模型。然后，进行跨源的主题域合并，梳理出整个企业的数据域。
数据域是指面向业务分析，将业务过程或维度进行抽象的集合。为保障整个体系的生命力，数据域需要抽象提炼，并长期维护更新，但不轻易变动。划分数据域时，需满足以下两点：
- 能涵盖当前所有的业务需求。
- 能在新业务进入时，无影响地被包含进已有的数据域中和扩展新的数据域。
在业务调研之后，可以进行数据域的划分。划分数据域，需要分析各个业务模块中有哪些业务活动。数据域，可以按照用户企业的部门划分，也可以按照业务过程或者业务板块中的功能模块划分。
例如，A公司电商营销业务板块可以划分为如下表所示的数据域。数据域中的每一部分，都是根据实际业务过程进行归纳、抽象得出的。
数据域业务过程举例会员和店铺域注册、登录、装修、开店、关店商品域发布、上架、下架、重发日志域曝光、浏览、单击交易域下单、支付、发货、确认收货（交易成功）服务域商品收藏、拜访、培训、优惠券领用采购域商品采购（供应链管理）
3.2 定义维度与构建总线矩阵
根据OneData方法论，明确每个数据域中有哪些业务过程后， 需要开始定义维度，并基于维度构建总线矩阵。
3.2.1 定义维度
在划分数据域、构建总线矩阵时，需要结合对业务过程的分析定义维度。本文章中，以A电商公司的营销业务板块为例，在交易数据域中，我们重点分析确认收货（交易成功）的业务过程。
在确认收货的业务过程中，维度所依赖的业务角度主要有两个，即商品和收货地点（地域）。本文章中，假设收货和购买是同一个地点。
- 从商品角度分析，我们可以定义出以下维度：
- 商品ID（主键）
- 商品名称
- 商品交易价格
- 商品新旧程度：0全新；1闲置；2二手
- 商品类目ID
- 商品类目名称
- 品类ID
- 品类名称
- 买家ID
- 商品状态：0正常；1用户删除；2下架；3未上架
- 商品所在城市
- 商品所在省份
- 从地域角度分析，我们可以定义出以下维度：
- 城市code
- 城市名称
- 省份code
- 省份名称
作为维度建模的核心，维度在企业级数据仓库中必须具有唯一性。维度在每个业务板块内必须具有唯一性，即每个维度在所属业务板块内有且只有一种定义。例如本文章内的省份维度，对于营销业务板块内的任何业务过程所传达的信息都是一致的。
维度创建后，创建对应的维表（即维度逻辑表），并对维表进行添加属性、添加关联维度、添加子维度和物理化等操作。维表与维度一一对应，是通过丰富维度中的属性信息构建而成的。
3.2.2 构建总线矩阵
明确每个数据域中有哪些业务过程后，即可构建总线矩阵，该总线矩阵将用于指导后续事实模型中关联维度的定义，构建数据仓库的雪花模型。
需要定义每个数据域下的业务过程和维度，并明确每个业务过程与哪些维度相关。下表是A公司电商板块交易功能的总线矩阵，我们定义了购买省份、购买城市、类目ID、类目名称、品牌ID、品牌名称、商品ID、商品名称、成交金额等维度，并明确了不同业务过程包含了哪些维度。
说明Y表示包含该维度，N表示不包含。
3.3 明确统计指标
统计指标包括派生指标、原子指标、业务限定、业务过程和统计粒度（即维度）。在设计模型前，建议先完成该部分工作，以便设计出易于使用的数据仓库。
3.3.1 指标定义注意事项
原子指标是明确统计口径和计算逻辑，事实模型或维度模型定义完成，即可创建原子指标。派生指标即常见的统计指标，派生指标=统计周期+业务限定+原子指标+统计粒度。
派生指标，注意事项如下：
- 已完成原子指标且需要确认原子指标的来源模型中有维度模型，以保证可以确定派生指标的统计粒度。
- 原子指标和业务限定来源于同一张维度表或事实表，且继承来源表的数据域。
- 统计粒度和时间周期必须考虑在内，是否需要业务限定由具体的派生指标语义决定。例如，如果支付金额为原子指标，则最近7天买家支付金额（统计粒度为买家、时间周期为最近7天）和最近7天买家支付宝支付金额（统计粒度为买家、业务限定为支付宝支付、时间周期为最近7天）都可以作为派生指标。
- 派生指标唯一归属于一个原子指标，且继承原子指标的数据域。
3.3.2 确定指标
本文章中，用户是A公司电商营销部门的营销数据分析师。数据需求为最近一天厨具类目的商品在各省的销售总额、该类目销售额前10的商品名称、各省用户购买力分布（人均消费额）等，用于营销分析。
基于规范定义，确认业务过程为确认收货（交易成功），对应事实模型中的度量（商品的销售金额）。因此根据业务需求，我们可以定义出原子指标和派生指标：
- 原子指标：商品成功交易的金额的总和。
- 派生指标：
- 最近一天全省厨具类目各商品的销售总额。
- 最近一天全省厨具类目的人均消费额（消费总额除以人数）。
最近一天全省厨具类目各商品的销售总额降序排序，取前10名的名称，即可得到该类目销售额前10的商品名称。
4 模型设计
4.1 技术架构选型
根据OneData方法论最佳实践，在设计数据模型前， 需要完成技术架构的选型。
完整的技术架构如下图所示。其中，数据集成及同步负责完成源业务系统数据引入。基于OneData方法论——OneModel、OneID、OneService，囊括了数据建模研发、运维中心、监控报警、数据资产等在内的一系列功能。
4.2 数仓分层
基于OneData方法论最佳实践，在数据体系中，建议将数据仓库分为三层：数据引入层（ODS，Operational Data Store）、数据公共层（CDM，Common Dimenions Model）和数据应用层（ADS，Application Data Store）。
数据仓库自顶向下的分层和各层用途如下图所示。
- 数据引入层（ODS，Operational Data Store，又称数据基础层）：将原始数据几乎无处理地存放在数据仓库系统中，结构上与源系统基本保持一致，是数据仓库的数据准备区。这一层的主要职责是将基础数据同步、存储到Hive。
- 数据公共层（CDM，Common Dimenions Model）：存放明细事实数据、维表数据及公共指标汇总数据。其中，明细事实数据、维表数据一般根据ODS层数据加工生成。公共指标汇总数据一般根据维表数据和明细事实数据加工生成。
- CDM层又细分为维度层（DIM）、明细数据层（DWD）和汇总数据层（DWS），采用维度模型方法作为理论基础， 可以定义维度模型主键与事实模型中外键关系，减少数据冗余，也提高明细数据表的易用性。在汇总数据层同样可以关联复用统计粒度中的维度，采取更多的宽表化手段构建公共指标数据层，提升公共指标的复用性，减少重复加工。
- 维度层（DIM，Dimension）：以维度作为建模驱动，基于每个维度的业务含义，通过添加维度属性、关联维度等定义计算逻辑，完成属性定义的过程并建立一致的数据分析维表。为了避免在维度模型中冗余关联维度的属性，基于雪花模型构建维度表。 在数据开发中，维度层的表通常也被称为维度逻辑表。
- 明细数据层（DWD，Data Warehouse Detail）：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细事实表。可以结合企业的数据使用特点，将明细事实表的某些重要属性字段做适当冗余，也即宽表化处理。 在数据开发中，明细数据层的表通常也被称为事实逻辑表。
- 汇总数据层（DWS，Data Warehouse Summary）：以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标表。以宽表化手段物理化模型，构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。 在数据开发中，汇总数据层的表通常也被称为汇总逻辑表，用于存放派生指标数据。
- 数据应用层（ADS，Application Data Store）：存放数据产品个性化的统计指标数据，根据CDM层与ODS层加工生成。
4.3 数据引入层（ODS）
基于OneData方法论最佳实践，ODS层存放从业务系统获取的最原始的数据，是其他上层数据的源数据。业务数据系统中的数据通常为长期累积的、非常细节的数据，且访问频率很高，是面向应用的数据。
4.3.1 数据引入层表设计
本文章中，在ODS层主要包括的数据有：交易系统订单详情、用户信息详情、商品详情等。这些数据未经处理，是最原始的数据。在逻辑层面上，这些数据都是以二维表的形式存储。严格地说，虽然ODS层不属于数仓建模的范畴，但是合理地规划ODS层并做好数据同步也非常重要。本文章中，使用了6张ODS表：
- 记录用于拍卖的商品信息：s_auction。
- 记录用于正常售卖的商品信息：s_sale。
- 记录用户详细信息：s_users_extra。
- 记录新增的商品成交订单信息：s_biz_order_delta。
- 记录新增的物流订单信息：s_logistics_order_delta。
- 记录新增的支付订单信息：s_pay_order_delta。
说明：
表或字段命名尽量和业务系统保持一致，但是需要通过额外的标识来区分增量和全量表。在数据开发中，di后缀的事实模型为增量表（事务型），df后缀的事实模型为全量表（周期快照型）。
命名时需要特别注意冲突处理。例如，不同业务系统的表可能是同一个名称，为区分两个不同的表， 可以将这两个同名表的来源数据库名称作为后缀或前缀。例如，表中某些字段的名称刚好和关键字重名了，可以通过规范定义后缀添加_col1解决。
4.3.2 ODS层设计规范
ODS层表命名、数据同步任务命名、数据产出及生命周期管理、资产质量规范。
命名规范
-
表命名规范
表命名规则：{层次}{源系统表名}{保留位/delta与否}。
- 增量数据：{project_name}.s{源系统表名}delta。
- 全量数据：{project_name}.s{源系统表名}。
- ODS ETL过程的临时表：{project_name}.tmp{临时表所在过程的输出表}{从0开始的序号}。
- 按小时同步的增量表：{project_name}.s{源系统表名}{delta}_{hh}。
- 按小时同步的全量表：{project_name}.s{源系统表名}{hh}。
- 当不同源系统同步到同一个database下的表命名冲突时，需要给同步较晚的表名加上源系统的dbname以解决冲突。
- 字段命名规范
- 字段默认使用源系统的字段名。
- 字段名与Hive关键字冲突时，在源字段名后加上col，即源字段名col。Hive关键字详情如下。
注意
命名表、列或分区时，不要使用保留字与关键字，否则可能会报错。
保留字不区分大小写。
在对表、列或是分区命名时如若使用关键字，需给关键字加``符号进行转义，否则会报错.
% & && ( ) * +
- . / ; < <= <>
= > >= ? ADD AFTER ALL
ALTER ANALYZE AND ARCHIVE ARRAY AS ASC
BEFORE BETWEEN BIGINT BINARY BLOB BOOLEAN BOTH DECIMAL
BUCKET BUCKETS BY CASCADE CASE CAST CFILE
CHANGE CLUSTER CLUSTERED CLUSTERSTATUS COLLECTION COLUMN COLUMNS
COMMENT COMPUTE CONCATENATE CONTINUE CREATE CROSS CURRENT
CURSOR DATA DATABASE DATABASES DATE DATETIME DBPROPERTIES
DEFERRED DELETE DELIMITED DESC DESCRIBE DIRECTORY DISABLE
DISTINCT DISTRIBUTE DOUBLE DROP ELSE ENABLE END EXCEPT
ESCAPED EXCLUSIVE EXISTS EXPLAIN EXPORT EXTENDED EXTERNAL
FALSE FETCH FIELDS FILEFORMAT FIRST FLOAT FOLLOWING
FORMAT FORMATTED FROM FULL FUNCTION FUNCTIONS GRANT
GROUP HAVING HOLD_DDLTIME IDXPROPERTIES IF IMPORT IN
INDEX INDEXES INPATH INPUTDRIVER INPUTFORMAT INSERT INT
INTERSECT INTO IS ITEMS JOIN KEYS LATERAL
LEFT LIFECYCLE LIKE LIMIT LINES LOAD LOCAL
LOCATION LOCK LOCKS LONG MAP MAPJOIN MATERIALIZED
MINUS MSCK NOT NO_DROP NULL OF OFFLINE OFFSET
ON OPTION OR ORDER OUT OUTER OUTPUTDRIVER
OUTPUTFORMAT OVER OVERWRITE PARTITION PARTITIONED PARTITIONPROPERTIES PARTITIONS
PERCENT PLUS PRECEDING PRESERVE PROCEDURE PURGE RANGE
RCFILE READ READONLY READS REBUILD RECORDREADER RECORDWRITER
REDUCE REGEXP RENAME REPAIR REPLACE RESTRICT REVOKE
RIGHT RLIKE ROW ROWS SCHEMA SCHEMAS SELECT
SEMI SEQUENCEFILE SERDE SERDEPROPERTIES SET SHARED SHOW
SHOW_DATABASE SMALLINT SORT SORTED SSL STATISTICS STATUS STORED
STREAMTABLE STRING STRUCT TABLE TABLES TABLESAMPLE TBLPROPERTIES
TEMPORARY TERMINATED TEXTFILE THEN TIMESTAMP TINYINT TO
TOUCH TRANSFORM TRIGGER TRUE TYPE UNARCHIVE UNBOUNDED UNDO
UNION UNIONTYPE UNIQUEJOIN UNLOCK UNSIGNED UPDATE USE
USING UTC UTC_TMESTAMP VIEW WHEN WHERE WHILE DIV - 同步任务命名规范
- 任务名：{源系统表名}[delta]。
说明同一database下异库同名表的任务名为{源系统表名}{tddl的appname}[_delta]。
- 任务的输出名称，即输出表的名称，需要与数据存储及生命周期管理规范保持一致。
4.3.3 建表示例
通过即席查询等客户端， 可以编写SQL语句创建所需的ODS表。为方便使用，集中提供建表语句如下。
CREATE TABLE IF NOT EXISTS s_auction
(
id STRING COMMENT '商品ID',
title STRING COMMENT '商品名称',
gmt_modified STRING COMMENT '商品最后修改日期',
price DOUBLE COMMENT '商品成交价格，单位元',
starts STRING COMMENT '商品上架时间',
minimum_bid DOUBLE COMMENT '拍卖商品起拍价，单位元',
duration STRING COMMENT '有效期，销售周期，单位天',
incrementnum DOUBLE COMMENT '拍卖价格的增价幅度',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份',
ends STRING COMMENT '销售结束时间',
quantity BIGINT COMMENT '数量',
stuff_status BIGINT COMMENT '商品新旧程度：0全新 1闲置 2二手',
auction_status BIGINT COMMENT '商品状态：0正常 1用户删除 2下架 3从未上架',
cate_id BIGINT COMMENT '商品类目ID',
cate_name STRING COMMENT '商品类目名称',
commodity_id BIGINT COMMENT '品类ID',
commodity_name STRING COMMENT '品类名称',
umid STRING COMMENT '买家umID'
)
COMMENT '商品拍卖ODS'
PARTITIONED BY (ds STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_sale
(
id STRING COMMENT '商品ID',
title STRING COMMENT '商品名称',
gmt_modified STRING COMMENT '商品最后修改日期',
starts STRING COMMENT '商品上架时间',
price DOUBLE COMMENT '商品价格，单位元',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份',
quantity BIGINT COMMENT '数量',
stuff_status BIGINT COMMENT '商品新旧程度：0全新 1闲置 2二手',
auction_status BIGINT COMMENT '商品状态：0正常 1用户删除 2下架 3从未上架',
cate_id BIGINT COMMENT '商品类目ID',
cate_name STRING COMMENT '商品类目名称',
commodity_id BIGINT COMMENT '品类ID',
commodity_name STRING COMMENT '品类名称',
umid STRING COMMENT '买家umID'
)
COMMENT '商品正常购买ODS'
PARTITIONED BY (ds STRING COMMENT '格式：YYYYMMDD')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_users_extra
(
id STRING COMMENT '用户ID',
logincount BIGINT COMMENT '登录次数',
buyer_goodnum BIGINT COMMENT '作为买家的好评数',
seller_goodnum BIGINT COMMENT '作为卖家的好评数',
level_type BIGINT COMMENT '1 一级店铺 2 二级店铺 3 三级店铺',
promoted_num BIGINT COMMENT '1 A级服务 2 B级服务 3 C级服务',
gmt_create STRING COMMENT '创建时间',
order_id BIGINT COMMENT '订单ID',
buyer_id BIGINT COMMENT '买家ID',
buyer_nick STRING COMMENT '买家昵称',
buyer_star_id BIGINT COMMENT '买家星级 ID',
seller_id BIGINT COMMENT '卖家ID',
seller_nick STRING COMMENT '卖家昵称',
seller_star_id BIGINT COMMENT '卖家星级ID',
shop_id BIGINT COMMENT '店铺ID',
shop_name STRING COMMENT '店铺名称'
)
COMMENT '用户扩展表'
PARTITIONED BY (ds STRING COMMENT 'yyyymmdd')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_biz_order_delta
(
biz_order_id STRING COMMENT '订单ID',
pay_order_id STRING COMMENT '支付订单ID',
logistics_order_id STRING COMMENT '物流订单ID',
buyer_nick STRING COMMENT '买家昵称',
buyer_id STRING COMMENT '买家ID',
seller_nick STRING COMMENT '卖家昵称',
seller_id STRING COMMENT '卖家ID',
auction_id STRING COMMENT '商品ID',
auction_title STRING COMMENT '商品标题',
auction_price DOUBLE COMMENT '商品价格',
buy_amount BIGINT COMMENT '购买数量',
buy_fee BIGINT COMMENT '购买金额',
pay_status BIGINT COMMENT '支付状态：1未付款 2已付款 3已退款',
logistics_id BIGINT COMMENT '物流订单ID',
mord_cod_status BIGINT COMMENT '物流状态：0初始状态 1接单成功 2接单超时 3揽收成功 4揽收失败 5签收成功 6签收失败 7用户取消物流订单',
status BIGINT COMMENT '状态：0订单正常 1订单不可见',
sub_biz_type BIGINT COMMENT '业务类型：1拍卖 2购买',
end_time STRING COMMENT '交易结束时间',
shop_id BIGINT COMMENT '店铺ID'
)
COMMENT '交易成功订单日增量表'
PARTITIONED BY (ds STRING COMMENT 'yyyymmdd')
LIFECYCLE 7200;
CREATE TABLE IF NOT EXISTS s_logistics_order_delta
(
logistics_order_id STRING COMMENT '物流订单ID ',
post_fee DOUBLE COMMENT '物流费用',
address STRING COMMENT '收货地址',
full_name STRING COMMENT '收货人全名',
mobile_phone STRING COMMENT '移动电话',
prov STRING COMMENT '省份',
prov_code STRING COMMENT '省份ID',
city STRING COMMENT '市',
city_code STRING COMMENT '城市ID',
logistics_status BIGINT COMMENT '物流状态：1-未发货 2-已发货 3-已收货 4-已退货 5-配货中',
consign_time STRING COMMENT '发货时间',
gmt_create STRING COMMENT '订单创建时间',
shipping BIGINT COMMENT '发货方式：1-平邮 2-快递 3-EMS',
seller_id STRING COMMENT '卖家ID',
buyer_id STRING COMMENT '买家ID'
)
COMMENT '交易物流订单日增量表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 7200;
CREATE TABLE IF NOT EXISTS s_pay_order_delta
(
pay_order_id STRING COMMENT '支付订单ID',
total_fee DOUBLE COMMENT '应支付总金额（数量*单价）',
seller_id STRING COMMENT '卖家ID',
buyer_id STRING COMMENT '买家iD',
pay_status BIGINT COMMENT '支付状态：1等待买家付款 2等待卖家发货 3交易成功',
pay_time STRING COMMENT '付款时间',
gmt_create STRING COMMENT '订单创建时间',
refund_fee DOUBLE COMMENT '退款金额(包含运费)',
confirm_paid_fee DOUBLE COMMENT '已经确认收货的金额'
)
COMMENT '交易支付订单增量表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 7200; 4.4.4 数据同步加载与处理
ODS的数据需要由各数据源系统同步、存储到Hive，才能用于进一步的数据开发。本文章在数据引入的过程中，建议遵循以下规范：
- 一个系统的源表只允许同步一次到Hive，保持表结构的一致性。
- 数据引入支持全量数据同步、实时增量数据同步（分钟或小时调度实现）两种同步方式。
- ODS层的表建议以统计日期及时间分区表的方式存储，便于管理数据的存储成本和策略控制，默认时间分区的名字为ds。
- 数据引入手动调整源表和目标表的同步字段。
- 如果源表字段在目标表中不存在，用户需手动添加目标字段，或删除源表字段。
- 如果源表字段与目标表字段不匹配，用户需先删除目标字段，然后重新添加与之匹配的字段。
4.5 维度层（DIM）
本文介绍维度层的设计原则、维度表的规范、创建维度逻辑表。
4.5.1 维度层简介
建立一致数据分析维表，可以降低数据计算口径和算法不统一风险。以维度作为建模驱动，基于每个维度的业务含义，通过定义维度及维度主键，添加维度属性、关联维度等定义计算逻辑和雪花模型，完成属性定义的过程并建立一致的数据分析维表。同时可以规范维度主子关系，子维度的属性将合并至主维度使用，进一步保证维度的一致性和便捷使用性。
4.5.2 维度表设计原则
- 尽可能生成丰富的维度属性。 例如电商公司的商品维度可能有近百个维度属性，为下游的数据统计、分析、探查提供了良好的基础。
- 尽可能多的给出包含一些富有意义的文字性描述。 属性不应该是编码，而应该是真正的文字。在维度建模中，通常是编码和文字同时存在，例如商品维度中的商品ID和商品标题、类目ID和类目名称等。ID通常用于不同表之间的关联，而名称通常用于报表标签。
- 区分数值型属性和事实。 数值型字段是作为事实还是维度属性，可以根据字段的常用用途区分。例如，若用于查询约束条件或分组统计，则是作为维度属性；若用于参与度量的计算，则是作为事实。
- 尽量沉淀出通用的维度属性。
- 通过逻辑处理得到维度属性。
- 通过多表关联得到维度属性。
- 通过单表的不同字段混合处理得到维度属性。
- 通过对单表的某个字段进行解析得到维度属性。
4.5.3 维度表规范
定义普通维度或层级维度时，严格对应维度逻辑表，不建议自定义新建维度逻辑表。此外，数据开发过程中还要定义枚举维度和虚拟维度。
说明
枚举维度指的是维度表的值可枚举，以便规范统一枚举的维度值，维度作为派生指标统计粒度时，实现数据归一汇总计算。
虚拟维度与某个字段关联后，以维度的形式作为统计粒度，定义派生指标。例如URL。
定义维度同时创建维度逻辑表。维度表名称默认前缀为dim_，层级维度表默认增加后缀_lvl{n}。举例如下：
- dim_c1（普通维度逻辑表）
- dim_c1_lvl1（层级维度逻辑表）
4.5.4 明细数据层（DWD）
基于方法论最佳实践，事实表（事实模型，又称事实逻辑表）作为数据仓库维度建模的核心，紧紧围绕着业务过程进行设计。业务过程是通过事实表的度量、引用的维度与业务过程有关属性的方式获取。
两种类型的事实表：
- 事务型事实表：用于描述业务过程，跟踪空间或时间上某点的度量事件，保存的是最原子的数据，也称为原子事实表，表名后缀一般为di。
- 周期快照型事实表：以具有规律性的、可预见的时间间隔（例如每天、每月、每年等）记录事实，一般表名后缀为df。
- 4.5.5 事实表设计原则
- 尽可能包含所有与业务过程相关的事实。设计事实表的目的是度量业务过程，所以分析哪些事实与业务过程有关，是事实表设计中至关重要的。在事实表中应该尽量包含所有与业务过程相关的事实，即使存在冗余，但是因为事实通常为数字型，带来的存储开销不会很大。
- 只选择与业务过程相关的事实。在选择事实时应该注意，只选择与业务过程有关的事实。例如，A公司的订单交易业务流程中，在设计下单这个业务过程的事实表时，不能包含支付金额这个表示支付业务过程的事实。
- 在选择维度和事实之前，必须先声明粒度。粒度（数据行数的最小单位，非统计粒度）的声明是事实表设计中不可忽视的重要一步。粒度用于确定事实表中一行所表示业务的细节层次，决定了维度模型的扩展性。在选择维度和事实之前，必须先声明粒度，且每个维度和事实必须与所定义的粒度保持一致。在事实表中，通常通过业务描述来表述粒度并定义事实表主键，但对于聚集性事实表的粒度描述（例如存在下单、支付等多个事务），可以基于多个字段拼接，形成新的字段作为事实表主键，也可以不定义主键，这样一行记录即最小粒度。
- 在同一个事实表中，不能包含多种不同粒度的事实。事实表中所有事实的粒度需要与表声明的粒度保持一致，在同一个事实表中不能有多种不同粒度的事实。
- 事实的单位要保持一致。在同一个事实表中，事实的单位应该保持一致。例如，原订单金额、 订单优惠金额、订单运费金额这三个事实，应该采用一致的计量单位，例如统一为元，以方便使用。
- 4.5.6 事实表设计方法
任何类型的事件都可以被理解为一种事务。例如，交易过程中的创建订单、买家付款，物流过程中的揽货、发货、签收，退款过程中的申请退款、申请客服介入等，都可以被理解为一种事务。事务型事实表，即针对这些过程构建的一类事实表，用以跟踪定义业务过程的个体行为，提供丰富的分析能力，作为数据仓库CDM层的明细数据。
下面以A公司的订单交易事务型事实表为例，阐述事务型事实表的一般设计过程。
- 选择业务过程。按照之前的业务流程分析，A公司的交易订单流程包含四个重要过程：创建订单、买家付款、卖家发货、确认收货，即下单、支付、发货和收货四个业务过程。这四个业务过程不仅是交易过程中的重要时间节点，而且也是下游统计分析的重点，因此A公司的交易事务事实表设计着重从这四个业务过程进行展开。为了便于进行独立的分析研究，我们应该为每个业务过程建立一个事实表。本文章中，我们选择交易成功这个业务过程，建立事务型事实表。
- 确定粒度。事实表中一条记录所表达的业务细节程度被称为粒度。通常粒度可以通过两种方式来表述：一种是维度属性组合所表示的细节程度；一种是所表示的具体业务含义（例如商品）。业务过程选定之后，就要针对业务过程确定一个粒度，即确定事务型事实表每一行所表达的细节层次。明确的粒度能确保对事实表中行的意思的理解不会产生混淆，保证所有的事实按照同样的细节层次记录。如果有字段可以表达这个粒度，可以定义为事实表的主键。应该尽量选择最细级别的粒度，以确保事实表的应用具有最大的灵活性。对于订单过程而言，每一种商品结算后都会产生一个订单，交易成功这个业务过程的粒度可以选择为订单。订单ID如果唯一，可以作为事实表主键以描述粒度。
- 确定维度。选定好业务过程并且确定粒度之后，就可以确定维度信息了，应该选择能够描述清楚业务过程所处的环境的维度信息。例如，在A公司的交易订单事务事实表设计过程中，粒度为订单，确定的维度包含：买家、卖家、商品名称、商品类目、发货地区、收货地区、订单时间等维度。
- 确定事实。作为度量业务过程的核心，事实通常为整型或浮点型的十进制数值。事实表应该包含与业务过程描述有关的所有事实，且事实的粒度要与所确定的事实表的粒度一致。例如，在下单业务过程中，需要包含商品ID、商品价格、购买数量。在支付业务过程中，需要包含支付金额、红包金额、积分金额。在收货业务过程中，需要包含确认收货金额等。
- 关联维度。在确定维度时，包含了买卖家维度、商品维度、类目维度、收发货维度等。维度建模理论建议在事实表中只保存这些维表的外键， 而A公司电商交易事务事实表在维度建模基础之上做了进一步的优化，将买卖家星级、标签、店铺名称、商品类型、商品特征、商品属性、 类目层级等维度都关联到事实表中，提高对事实表进行过滤查询、统计聚合的效率。
事实表命名规范为：fct_{业务过程缩写}[_{自定义表命名标签缩写}] _{di/df，单分区增量/全量标识}。单分区增量全量标识通常为：i表示增量，f表示全量。例如，fct_ordcrt_trip_di（A电商公司航旅机票订单下单事实表，日刷新增量）及fct_asale_itm_df（A电商商品快照事实表，日刷新全量）。
汇总数据层以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求构建公共粒度的汇总表。汇总数据层的一个表通常会对应一个统计粒度（维度或维度组合）及该粒度下若干派生指标。
聚集是指针对原始明细粒度的数据进行汇总。DWS汇总数据层是面向分析对象的主题聚集建模。在本文章中，最终的分析目标为：最近一天某个类目（例如，厨具）商品在各省的销售总额、该类目销售额Top10的商品名称、各省用户购买力分布。因此，我们可以以最终交易成功的商品、类目、买家等角度对最近一天的数据进行汇总。数据聚集的注意事项如下：
- 聚集是不跨越事实的。聚集是针对原始星形模型进行的汇总。为获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的，所以原子指标只能基于一张事实表定义，但是支持原子指标组合为衍生原子指标。
- 聚集会带来查询性能的提升，但聚集也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前存在的、已经被汇总到聚集表中的数据需要被重新调整。
此外，进行DWS层设计时还需遵循数据公用性原则。数据公用性需要考虑汇总的聚集是否可以提供给第三方使用。可以思考，基于某个维度的聚集是否经常用于数据分析中。如果答案是肯定的，就有必要把明细数据经过汇总沉淀到聚集表中。
公共汇总表命名规范：dws_统计粒度。 举例如下：
- dws_report（report汇总表）
- dws_user（user汇总表）
组成汇总表的统计指标有两种来源，具体如下：
- 按照相同统计粒度，做汇聚。派生指标确认后，生成新的汇总表。派生指标组成部分，如下图所示。
基于建模理论和业务需求，明确并规范定义统计指标，以便设计出易于业务使用的数据仓库。
规范定义是指以维度建模作为理论基础，构建总线矩阵，划分并定义数据域、业务过程、维度、原子指标、统计周期和派生指标。
在开始进行数仓模型设计前，需要完成业务调研、需求分析、构建总线矩阵（从业务数据中抽象出业务过程和维度）、明确并定义统计指标。本文章中假设已完成需求调研、业务分析和构建总线矩阵，如何明确并规范定义统计指标，快速理解如何设计数仓模型。
A电商公司，销售某品牌多种零食。
商品种类单价干果类10元/500g膨化类8元/袋饮品类15元/瓶
买家和卖家可以通过电商平台进行交易。
定义指标业务数据业务板块电商业务数据域交易域维度商品种类业务过程下单购买业务限定商品种类为干果类时间周期最近1天原子指标销售总额派生指标最近1天干果类商品销售总额