【离线数仓项目】——数仓开发流程实战

庄小焱
177 阅读45分钟

摘要

本文围绕离线数仓项目展开，重点阐述了电商业务需求分析、架构与模型设计、数仓系统性能基准以及性能相关指标优化等内容。在电商业务需求分析方面，强调了充分调研的重要性，包括了解组织架构、业务架构、各业务板块主要功能及数据需求等。架构与模型设计部分，详细介绍了技术架构选型、数仓分层设计以及各层的数据模型设计要点。数仓系统性能基准和性能相关指标优化则涉及数据同步时间、存储大小记录以及Hash Clustering等优化技巧，旨在提升数仓性能。

1. 电商业务需求分析

充分的业务调研和需求分析是数据仓库建设的基石，直接决定数据仓库能否建设成功。在数仓建设项目启动前，需要请相关的业务人员介绍具体的业务，以便明确各个团队的分析员和运营人员的需求，沉淀出相关文档。可以通过调查表和访谈等形式详细了解以下信息：

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

如下以为公司的电商业务，梳理出业务数据框架如下图所示。A公司的电商业务板块分为招商、供应链、营销和服务四个模块，每个板块的需求和数据应用都不同。在构建数据仓库之前，首先需要明确构建数据仓库的业务板块和需要具体满足的业务需求。

此外，还需要进一步了解各业务板块中已有的数据功能模块。数据功能模块通常和业务板块紧耦合，对应一个或多个表，可以作为构建数据仓库的数据源。下表展现的是一个营销业务板块的数据功能模块。

数据功能模块A公司电商营销管理 （ Y代表包含该数据功能模块，N代表不包含 ）
商品管理Y
用户管理Y
购买流程Y
交易订单Y
用户反馈Y

数据需求为最近一天某个类目（例如，厨具）商品在各省的销售总额、该类目Top10销售额商品名称和各省客户购买力分布（人均消费额） 等，用于营销分析。最终的业务需求是通过营销分析完成该类目的精准营销，提升销售总额。通过业务调研，我们将着力分析营销业务板块的交易订单数据功能模块。

1.1. 业务需求分析

在未考虑数据分析师和业务运营人员的数据需求的情况下，单纯根据业务调研结果构建的数据仓库可用性差。完成业务调研后，需要进一步收集数据使用者的需求，进而对需求进行深度的思考和分析。需求分析的途径有两种：

  • 根据与分析师和业务运营人员的沟通获知需求。
  • 对报表系统中现有的报表进行研究分析。

在需求分析阶段，需要沉淀出业务分析或报表中的指标，以及指标的定义和粒度。粒度可以作为维度的输入。建议思考下列问题，对后续的数据建模将有巨大的帮助：

  • 业务数据是根据什么（维度、粒度）汇总的，衡量标准是什么？例如，成交量是维度，订单数是成交量的度量。
  • 明细数据层和汇总数据层应该如何设计？公共维度层该如何设计？是否有公共的指标？
  • 数据是否需要冗余或沉淀到汇总数据层中？

数据分析师需要了解A公司电商业务中厨具类目的成交金额。当获知这个需求后，需要分析：根据什么（维度）汇总、汇总什么（度量）以及汇总的范围多大（粒度）。例如，类目是维度，金额是度量，范围是全表。此外，还需要思考明细数据和汇总数据应该如何设计、是否是公共层的报表及数据是否需要沉淀到汇总表中等因素。需求调研的分析产出通常是记录原子与派生指标的文档。

1.2. 分析业务过程

业务过程可以概括为一个个不可拆分的行为事件。用户的业务系统中，通过埋点或日常积累，通常已经获取了充足的业务数据。为理清数据之间的逻辑关系和流向，首先需要理解用户的业务过程，了解过程中涉及到的数据系统。

可以采用过程分析法，将整个业务过程涉及的每个环节一一列清楚，包括技术、数据、系统环境等。在分析企业的工作职责范围（部门）后，也可以借助工具通过逆向工程抽取业务系统的真实模型。可以参考业务规划设计文档以及业务运行（开发、设计、变更等）相关文档，全面分析数据仓库涉及的源系统及业务管理系统：

  • 每个业务会生成哪些数据，存在于什么数据库中。
  • 对业务过程进行分解，了解过程中的每一个环节会产生哪些数据，数据的内容是什么。
  • 数据在什么情况下会更新，更新的逻辑是什么。

业务过程可以是单个业务事件，例如交易的支付、退款等；也可以是某个事件的状态，例如当前的账户余额等；还可以是一系列相关业务事件组成的业务流程。具体取决于分析的是某些事件过去发生情况、当前状态还是事件流转效率。

选择粒度：在业务过程事件分析中，需要预判所有分析需要细分的程度和范围，从而决定选择的粒度。 识别维表、选择好粒度之后，需要基于此粒度设计维表，包括维度属性等，用于分析时进行分组和筛选。最后，需要确定衡量的指标。经过业务过程调研，我们了解到用户电商营销业务的交易订单功能模块的业务流程如下。

如图这是一个非常典型的电商交易业务流程图。在该业务流程图中，有创建订单、买家付款、卖家发货、确认收货四个核心业务步骤。由于确认收货代表交易成功，我们重点分析确认收货（交易成功）步骤即可。在明确用户的业务过程之后，可以根据需要对进行分析决策的业务划分数据域。

1.3. 划分数据域

数据仓库是面向主题（数据综合、归类并进行分析利用）的应用。数据仓库模型设计除横向的分层外，通常也需要根据业务情况纵向划分数据域。数据域是联系较为紧密的数据主题的集合，是业务对象高度概括的概念，目的是便于管理和应用数据。通常，需要阅读各源系统的设计文档、数据字典和数据模型，研究逆向导出的物理数据模型。进而，可以进行跨源的主题域合并，跨源梳理出整个企业的数据域。

数据域是指面向业务分析，将业务过程或者维度进行抽象的集合。为保障整个体系的生命力，数据域需要抽象提炼，并长期维护更新。在划分数据域时，既能涵盖当前所有的业务需求，又能让新业务在进入时可以被包含进已有的数据域或扩展新的数据域。数据域的划分工作可以在业务调研之后进行，需要分析各个业务模块中有哪些业务活动。

数据域可以按照用户企业的部门划分，也可以按照业务过程或者业务板块中的功能模块进行划分。例如A公司电商营销业务板块可以划分为如下数据域，数据域中每一部分都是实际业务过程经过归纳抽象之后得出的。

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

1.4. 定义维度与构建总线矩阵

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

1.4.1. 定义维度

在划分数据域、构建总线矩阵时，需要结合对业务过程的分析定义维度。以本教程中A电商公司的营销业务板块为例，在交易数据域中，我们重点考察确认收货（交易成功）的业务过程。在确认收货的业务过程中，主要有商品和收货地点：两个维度所依赖的业务角度。从商品维度我们可以定义出以下维度的属性：

属性取值示例
商品ID（主键）1020
商品名称苹果笔记本
商品交易价格12090￥
商品新旧程度：1 全新 2 闲置 3 二手
商品类目ID10003
商品类目名称电脑
品类ID1010101
品类名称电脑
买家ID202024
商品状态0 正常 1 删除 2 下架 3 从未上架
商品所在城市上海
商品所在省份上海

从地域维度，我们可以定义出以下维度的属性：

属性取值示例
城市code102301
城市名称杭州
省份code1023
省份名称浙江

作为维度建模的核心，在企业级数据仓库中必须保证维度的唯一性。以A公司的商品维度为例，有且只允许有一种维度定义。例如，省份code这个维度，对于任何业务过程所传达的信息都是一致的。

1.4.2. 构建总线矩阵

明确每个数据域下有哪些业务过程后，即可构建总线矩阵。需要明确业务过程与哪些维度相关，并定义每个数据域下的业务过程和维度。如下所示是A公司电商板块交易功能的总线矩阵，我们定义了购买省份、购买城市、类目名称、类目ID、品牌名称、品牌ID、商品名称、商品ID、成交金额等维度。

数据域/过程一致性维度 （ Y代表包含该维度，N代表不包含 ）
购买省份购买城市类目ID类目名称品牌ID品牌名称商品ID商品名称成交金额
交易下单YYYYYYYYN
支付YYYYYYYYN
发货YYYYYYYYN
确认收货YYYYYYYYY

1.5. 明确统计指标

需求调研输出的文档中，含有原子指标与派生指标，此时需要在设计汇总层表模型前完成指标的设计。

1.5.1. 指标定义注意事项

  1. 原子指标是明确的统计口径、计算逻辑：原子指标 = 业务过程+度量。
  2. 派生指标即常见的统计指标：派生指标 = 时间周期+修饰次+原子指标。

原子指标的创建需要在业务过程定义后方才可创建。派生指标的创建一般需要在了解具体报表需求之后展开，在新建派生指标前必须新建好原子指标。

  • 原子指标、修饰类型及修饰词，直接归属在业务过程下，其中修饰词继承修饰类型的数据域。
  • 派生指标可以选择多个修饰词，由具体的派生指标语义决定。例如，支付金额为原子指标，则客单价（支付金额除以买家数）为派生指标。
  • 派生指标唯一归属一个原子指标，继承原子指标的数据域，与修饰词的数据域无关。

1.5.2. 根据业务需求确定指标

用户是电商营销部门的营销数据分析师。数据需求为最近一天厨具类目的商品在各省的销售总额、该类目Top10销售额商品名称、各省用户购买力分布（人均消费额）等，用于营销分析。根据之前的分析，我们确认业务过程为：确认收货（交易成功），而度量为商品的销售金额。因此根据业务需求，我们可以定义出原子指标：商品成功交易金额。例如派生指标为：

  • 最近一天全省厨具类目各商品销售总额
  • 最近一天全省厨具类目人均消费额（消费总额除以人数）

最近一天全省厨具类目各商品销售总额进行降序排序后取前10名的名称，即可得到该类目Top10销售额商品名称。

2. 架构与模型设计

2.1. 技术架构选型

在数据模型设计之前，需要首先完成技术架构的选型。完整的技术架构图如下图所示。其中，大数据平台的数据集成负责完成数据的采集和基本的ETL。大数据平台作为整个大数据开发过程中的离线计算引擎。大数据平台则包括数据开发、数据质量、数据安全、数据管理等在内的一系列功能。

2.2. 数仓分层设计

在阿里巴巴的数据体系中，我们建议将数据仓库分为三层，自下而上为：数据引入层（ODS，Operation Data Store）、数据公共层（CDM，Common Data Model）和数据应用层（ADS，Application Data Service）。数据仓库的分层和各层级用途如下图所示。

原始数据ODS层（Operation Data Store）：存放未经过处理的原始数据至数据仓库系统，结构上与源系统保持一致，是数据仓库的数据准备区。主要完成基础数据引入到大数据平台时记录基础数据的历史变化。

数据公共CDM层（ Common Data Model，又称通用数据模型层），包括DIM维度表、DWD和DWS，由ODS层数据加工而成。主要完成数据加工与整合，建立一致性的维度，构建可复用的面向分析和统计的明细事实表，以及汇总公共粒度的指标。

  • 公共维度层（DIM）：基于维度建模理念思想，建立整个企业的一致性维度。降低数据计算口径和算法不统一风险。公共维度层的表通常也被称为逻辑维度表，维度和维度逻辑表通常一一对应。
  • 公共汇总粒度事实层（DWS）：以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。公共汇总粒度事实层的表通常也被称为汇总逻辑表，用于存放派生指标数据。
  • 明细粒度事实层（DWD）：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理。明细粒度事实层的表通常也被称为逻辑事实表。

数据应用ADS层（Application Data Service）：存放数据产品个性化的统计指标数据。根据CDM与ODS层加工生成。

该数据分类架构在ODS层分为三部分：数据准备区、离线数据和准实时数据区。整体数据分类架构如下图所示。

从交易数据系统的数据经过大数据平台数据集成，同步到数据仓库的ODS层。经过数据开发形成事实宽表后，再以商品、地域等为维度进行公共汇总。

整体的数据流向如下图所示。其中，ODS层到DIM层的ETL（萃取（Extract）、转置（Transform）及加载（Load））处理是在大数据平台中进行的，处理完成后会同步到所有存储系统。ODS层和DWD层会放在数据中间件中，供下游订阅使用。而DWS层和ADS层的数据通常会落地到在线存储系统中，下游通过接口调用的形式使用。

2.3. 数据模型设计

2.3.1. 数据引入层（ODS）

ODS（Operational Data Store）层存放从业务系统获取的最原始的数据，是其他上层数据的源数据。业务数据系统中的数据通常为非常细节的数据，经过长时间累积，且访问频率很高，是面向应用的数据。

2.3.1.1. 数据引入层表设计

在ODS层主要包括的数据有：交易系统订单详情、用户信息详情、商品详情等。这些数据未经处理，是最原始的数据。逻辑上，这些数据都是以二维表的形式存储。虽然严格的说ODS层不属于数仓建模的范畴，但是合理的规划ODS层并做好数据同步也非常重要。使用了6张ODS表：

  • 记录用于拍卖的商品信息：ods_auction。
  • 记录用于正常售卖的商品信息：ods_sale。
  • 记录用户详细信息：ods_users_extra。
  • 记录新增的商品成交订单信息：ods_biz_order_delta。
  • 记录新增的物流订单信息：ods_logistics_order_delta。
  • 记录新增的支付订单信息：ods__pay_order_delta。
2.3.1.2. 建表示例

为方便使用，集中提供建表语句如下。更多建表信息

CREATE TABLE IF NOT EXISTS ods_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 ods_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 ods_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 ods_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 ods_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 ods_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;
2.3.1.3. 数据存储策略

为了满足历史数据分析需求，可以在ODS层表中添加时间维度作为分区字段。实际应用中，可以选择采用增量、全量存储或拉链存储的方式。

  1. 增量存储策略

以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。举例如下：

    • 1月1日，用户A访问了A公司电商店铺B，A公司电商日志产生一条记录t1。1月2日，用户A又访问了A公司电商店铺C，A公司电商日志产生一条记录t2。采用增量存储方式，t1将存储在1月1日这个分区中，t2将存储在1月2日这个分区中。
    • 1月1日，用户A在A公司电商网购买了B商品，交易日志将生成一条记录t1。1月2日，用户A又将B商品退货了，交易日志将更新t1记录。采用增量存储方式，初始购买的t1记录将存储在1月1日这个分区中，更新后的t1将存储在1月2日这个分区中。

说明： 交易、日志等事务性较强的ODS表适合增量存储方式。这类表数据量较大，采用全量存储的方式存储成本压力大。此外，这类表的下游应用对于历史全量数据访问的需求较小（此类需求可通过数据仓库后续汇总后得到）。例如，日志类ODS表没有数据更新的业务过程，因此所有增量分区UNION在一起就是一份全量数据。

  1. 全量存储策略

以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到业务日期为止的全量业务数据。

  • 例如，1月1日，卖家A在A公司电商网发布了B、C两个商品，前端商品表将生成两条记录t1、t2。1月2日，卖家A将B商品下架了，同时又发布了商品D，前端商品表将更新记录t1，同时新生成记录t3。采用全量存储方式，在1月1日这个分区中存储t1和t2两条记录，在1月2日这个分区中存储更新后的t1以及t2、t3记录。

说明： 对于小数据量的缓慢变化维度数据，例如商品类目，可直接使用全量存储。

  1. 拉链存储策略

拉链存储通过新增两个时间戳字段（start_dt和end_dt），将所有以天为粒度的变更数据都记录下来，通常分区字段也是这两个时间戳字段。拉链存储举例如下。

商品start_dtend_dt卖家状态
B2016010120160102A上架
C2016010130001231A上架
B2016010230001231A下架

这样，下游应用可以通过限制时间戳字段来获取历史数据。例如，用户访问1月1日数据，只需限制start_dt<=20160101并且 end_dt>20160101。

2.3.1.4. 缓慢变化维度

大数据平台不推荐使用代理键，推荐使用自然键作为维度主键，主要原因有两点：

  1. 大数据平台是分布式计算引擎，生成全局唯一的代理键工作量非常大。当遇到大数据量情况下，这项工作就会更加复杂，且没有必要。
  2. 使用代理键会增加ETL的复杂性，从而增加ETL任务的开发和维护成本。

在不使用代理键的情况下，缓慢变化维度可以通过快照方式处理。快照方式下数据的计算周期通常为每天一次。基于该周期，处理维度变化的方式为每天一份全量快照。例如商品维度，每天保留一份全量商品快照数据。任意一天的事实表均可以取到当天的商品信息，也可以取到最新的商品信息，通过限定日期，采用自然键进行关联即可。该方式的优势主要有以下两点：

  • 处理缓慢变化维度的方式简单有效，开发和维护成本低。
  • 使用方便，易于理解。数据使用方只需要限定日期即可取到当天的快照数据。任意一天的事实快照与任意一天的维度快照通过维度的自然键进行关联即可。

该方法的弊端主要是存储空间的极大浪费。例如某维度每天的变化量占总体数据量比例很低，极端情况下，每天无变化，这种情况下存储浪费严重。该方法主要实现了通过牺牲存储获取ETL效率的优化和逻辑上的简化。请避免过度使用该方法，且必须要有对应的数据生命周期制度，清除无用的历史数据。

2.3.1.5. 数据同步加载与处理

ODS的数据需要由各数据源系统同步到大数据平台，才能用于进一步的数据开发。在使用数据集成的过程中，建议遵循以下规范：

  • 一个系统的源表只允许同步到大数据平台一次，保持表结构的一致性。
  • 数据集成提供数据同步解决方案，可以通过配置同步规则，实现离线数据全量及增量同步、增量数据实时写入、增量数据和全量数据定时自动合并写入新的全量表分区。
  • ODS层的表建议以统计日期及时间分区表的方式存储，便于管理数据的存储成本和策略控制。

2.3.2. 公共维度汇总层（DIM）

公共维度汇总层DIM（Dimension）基于维度建模理念，建立整个企业的一致性维度。

公共维度汇总层（DIM）主要由维度表（维表）构成。维度是逻辑概念，是衡量和观察业务的角度。维表是根据维度及其属性将数据平台上构建的物理化的表，采用宽表设计的原则。因此，公共维度汇总层（DIM）首先需要定义维度。

2.3.2.1. 定义维度

在划分数据域、构建总线矩阵时，需要结合对业务过程的分析定义维度。本教程以A电商公司的营销业务板块为例，在交易数据域中，我们重点考察确认收货（交易成功）的业务过程。

在确认收货的业务过程中，主要有商品和收货地点（本教程中，假设收货和购买是同一个地点）两个维度所依赖的业务角度。从商品角度可以定义出以下维度：

属性取值示例
商品ID（主键）1020
商品名称苹果笔记本
商品交易价格12090￥
商品新旧程度：1 全新 2 闲置 3 二手
商品类目ID10003
商品类目名称电脑
品类ID1010101
品类名称电脑
买家ID202024
商品状态0 正常 1 删除 2 下架 3 从未上架
商品所在城市上海
商品所在省份上海

从地域维度，我们可以定义出以下维度的属性：

属性取值示例
城市code102301
城市名称杭州
省份code1023
省份名称浙江

作为维度建模的核心，在企业级数据仓库中必须保证维度的唯一性。以A公司的商品维度为例，有且只允许有一种维度定义。例如，省份code这个维度，对于任何业务过程所传达的信息都是一致的。

2.3.2.2. DIM公共维度设计维表

完成维度定义后，可以对维度进行补充，进而生成维表。维表的设计需要注意：

  • 建议维表单表信息不超过1000万条。
  • 维表与其他表进行Join时，建议使用Map Join。
  • 避免过于频繁的更新维表的数据。

在设计维表时，需要从下列方面进行考虑：

  • 维表中数据的稳定性。例如，A公司电商会员通常不会出现消亡，但会员数据可能在任何时候更新，此时要考虑创建单个分区存储全量数据。如果存在不会更新的记录，可能需要分别创建历史表与日常表。日常表用于存放当前有效的记录，保持表的数据量不会膨胀；历史表根据消亡时间插入对应分区，使用单个分区存放分区对应时间的消亡记录。
  • 维表是否需要垂直拆分。如果一个维表存在大量属性不被使用，或由于承载过多属性字段导致查询变慢，则需要考虑对字段进行拆分，创建多个维表。
  • 维表是否需要水平拆分。如果记录之间有明显的界限，可以考虑拆成多个表或设计成多级分区。
  • 核心维表的产出时间。通常有严格的要求。

设计维表的主要步骤如下：

  • 初步定义维度。保证维度的一致性。
  • 确定主维表（中心事实表，本教程中采用星型模型）。此处的主维表通常是数据引入层（ODS）表，直接与业务系统同步。例如，s_auction是与前台商品中心系统同步的商品表，此表即是主维表。
  • 确定相关维表。数据仓库是业务源系统的数据整合，不同业务系统或者同一业务系统中的表之间存在关联性。根据对业务的梳理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。以商品维度为例，根据对业务逻辑的梳理，可以得到商品与类目、卖家和店铺等维度存在关联关系。
  • 确定维度属性。主要包括两个阶段。第一个阶段是从主维表中选择维度属性或生成新的维度属性；第二个阶段是从相关维表中选择维度属性或生成新的维度属性。以商品维度为例，从主维表（s_auction）、类目、卖家和店铺等相关维表中选择维度属性或生成新的维度属性。维度属性的设计需要注意：
    • 尽可能生成丰富的维度属性。
    • 尽可能多地给出富有意义的文字性描述。
    • 区分数值型属性和事实。
    • 尽量沉淀出通用的维度属性。
2.3.2.3. 公共维度汇总层（DIM）维表规范

公共维度汇总层（DIM）维表命名规范：dim_{业务板块名称/pub}{维度定义}[{自定义命名标签}]，pub是与具体业务板块无关或各个业务板块都可公用的维度。例如，时间维度，举例如下：

  • 公共区域维表dim_pub_area
  • A公司电商板块的商品全量表dim_asale_itm
CREATE TABLE IF NOT EXISTS dim_asale_itm
(
    item_id BIGINT COMMENT '商品ID',
    item_title STRING COMMENT '商品名称',
    item_price DOUBLE COMMENT '商品成交价格_元',
    item_stuff_status BIGINT COMMENT '商品新旧程度_0全新1闲置2二手',
    cate_id BIGINT COMMENT '商品类目ID',
    cate_name STRING COMMENT '商品类目名称',
    commodity_id BIGINT COMMENT '品类ID',
    commodity_name STRING COMMENT '品类名称',
    umid STRING COMMENT '买家ID',
    item_status BIGINT COMMENT '商品状态_0正常1用户删除2下架3未上架',
    city STRING COMMENT '商品所在城市',
    prov STRING COMMENT '商品所在省份'
)
COMMENT '商品全量表'
PARTITIONED BY (ds STRING COMMENT '日期,yyyymmdd');

CREATE TABLE IF NOT EXISTS dim_pub_area
(
    buyer_id  STRING COMMENT '买家ID',
    city_code STRING COMMENT '城市code',
    city_name STRING COMMENT '城市名称',
    prov_code STRING COMMENT '省份code',
    prov_name STRING COMMENT '省份名称'
)
COMMENT '公共区域维表'
PARTITIONED BY (ds STRING COMMENT '日期分区,格式yyyymmdd')
LIFECYCLE 3600;

2.3.3. 明细粒度事实层（DWD）

明细粒度事实层DWD（Data Warehouse Detail）以业务过程驱动建模，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理。

公共汇总粒度事实层（DWS）和明细粒度事实层（DWD）的事实表作为数据仓库维度建模的核心，需紧绕业务过程来设计。通过获取描述业务过程的度量来描述业务过程，包括引用的维度和与业务过程有关的度量。度量通常为数值型数据，作为事实逻辑表的依据。事实逻辑表的描述信息是事实属性，事实属性中的外键字段通过对应维度进行关联。

事实表中一条记录所表达的业务细节程度被称为粒度。通常粒度可以通过两种方式来表述：一种是维度属性组合所表示的细节程度，一种是所表示的具体业务含义。作为度量业务过程的事实，通常为整型或浮点型的十进制数值，有可加性、半可加性和不可加性三种类型：

  • 可加性事实是指可以按照与事实表关联的任意维度进行汇总。
  • 半可加性事实只能按照特定维度汇总，不能对所有维度汇总。例如库存可以按照地点和商品进行汇总，而按时间维度把一年中每个月的库存累加则毫无意义。
  • 完全不可加性，例如比率型事实。对于不可加性的事实，可分解为可加的组件来实现聚集。

事实表相对维表通常更加细长，行增加速度也更快。维度属性可以存储到事实表中，这种存储到事实表中的维度列称为维度退化，可加快查询速度。与其他存储在维表中的维度一样，维度退化可以用来进行事实表的过滤查询、实现聚合操作等。

明细粒度事实层（DWD）通常分为三种：事务事实表、周期快照事实表和累积快照事实表，

  • 事务事实表用来描述业务过程，跟踪空间或时间上某点的度量事件，保存的是最原子的数据，也称为原子事实表。
  • 周期快照事实表以具有规律性的、可预见的时间间隔记录事实。
  • 累积快照事实表用来表述过程开始和结束之间的关键步骤事件，覆盖过程的整个生命周期，通常具有多个日期字段来记录关键时间点。当累积快照事实表随着生命周期不断变化时，记录也会随着过程的变化而被修改。
2.3.3.1. 明细粒度事实表设计原则

明细粒度事实表设计原则如下所示：

  1. 通常，一个明细粒度事实表仅和一个维度关联。
  2. 尽可能包含所有与业务过程相关的事实 。
  3. 只选择与业务过程相关的事实。
  4. 分解不可加性事实为可加的组件。
  5. 在选择维度和事实之前必须先声明粒度。
  6. 在同一个事实表中不能有多种不同粒度的事实。
  7. 事实的单位要保持一致。
  8. 谨慎处理Null值。
  9. 使用退化维度提高事实表的易用性。

明细粒度事实表整体设计流程如下图所示。

在一致性度量中已定义好了交易业务过程及其度量。明细事实表注意针对业务过程进行模型设计。明细事实表的设计可以分为四个步骤：选择业务过程、确定粒度、选择维度、确定事实（度量）。粒度主要是在维度未展开的情况下记录业务活动的语义描述。在建设明细事实表时，需要选择基于现有的表进行明细层数据的开发，清楚所建表记录存储的是什么粒度的数据。

2.3.3.2. 明细粒度事实层（DWD）规范

通常需要遵照的命名规范为：dwd_{业务板块/pub}{数据域缩写}{业务过程缩写}[_{自定义表命名标签缩写}] _{单分区增量全量标识} ，pub表示数据包括多个业务板块的数据。单分区增量全量标识通常为：i表示增量，f表示全量。例如：

  1. dwd_asale_trd_ordcrt_trip_di（A电商公司航旅机票订单下单事实表，日刷新增量）
  2. dwd_asale_itm_item_df（A电商商品快照事实表，日刷新全量）。

本教程中，DWD层主要由三个表构成：

  • 交易商品信息事实表：dwd_asale_trd_itm_di。
  • 交易会员信息事实表：dwd_asale_trd_mbr_di。
  • 交易订单信息事实表：dwd_asale_trd_ord_di。
CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di
(
    item_id              BIGINT COMMENT '商品ID',
    item_title           STRING COMMENT '商品名称',
    item_price           DOUBLE COMMENT '商品价格',
    item_stuff_status    BIGINT COMMENT '商品新旧程度_0全新1闲置2二手',
    item_prov            STRING COMMENT '商品省份',
    item_city            STRING COMMENT '商品城市',
    cate_id              BIGINT COMMENT '商品类目ID',
    cate_name            STRING COMMENT '商品类目名称',
    commodity_id         BIGINT COMMENT '品类ID',
    commodity_name       STRING COMMENT '品类名称',
    buyer_id             BIGINT COMMENT '买家ID'
)
COMMENT '交易商品信息事实表'
PARTITIONED BY (ds     STRING COMMENT '日期')
LIFECYCLE 400;

CREATE TABLE IF NOT EXISTS dwd_asale_trd_mbr_di
(
    order_id         BIGINT COMMENT '订单ID',
    bc_type          STRING COMMENT '业务分类',
    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 '日期')
LIFECYCLE 400;

CREATE TABLE IF NOT EXISTS dwd_asale_trd_ord_di
(
    order_id              BIGINT COMMENT '订单ID',
    pay_order_id          BIGINT COMMENT '支付订单ID',
    pay_status            BIGINT COMMENT '支付状态_1未付款2已付款3已退款',
    succ_time             STRING COMMENT '订单交易结束时间',
    item_id               BIGINT COMMENT '商品ID',
    item_quantity         BIGINT COMMENT '购买数量',
    confirm_paid_amt      DOUBLE COMMENT '订单已经确认收货的金额',
    logistics_id          BIGINT COMMENT '物流订单ID',
    mord_prov             STRING COMMENT '收货人省份',
    mord_city             STRING COMMENT '收货人城市',
    mord_lgt_shipping     BIGINT COMMENT '发货方式_1平邮2快递3EMS',
    mord_address          STRING COMMENT '收货人地址',
    mord_mobile_phone     STRING COMMENT '收货人手机号',
    mord_fullname         STRING COMMENT '收货人姓名',
    buyer_nick            STRING COMMENT '买家昵称',
    buyer_id              BIGINT COMMENT '买家ID'
)
COMMENT '交易订单信息事实表'
PARTITIONED BY (ds       STRING COMMENT '日期')
LIFECYCLE 400;

2.3.4. 公共汇总粒度事实层（DWS）

公共汇总粒度事实层DWS（Data Warehouse Summary）以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求构建公共粒度的汇总指标事实表。公共汇总层的一个表至少会对应一个派生指标。

2.3.4.1. 公共汇总事实表设计原则

聚集是指针对原始明细粒度的数据进行汇总。DWS公共汇总层是面向分析对象的主题聚集建模。最终的分析目标为：最近一天某个类目（例如：厨具）商品在各省的销售总额、该类目Top10销售额商品名称、各省用户购买力分布。因此，我们可以以最终交易成功的商品、类目、买家等角度对最近一天的数据进行汇总。数据聚集的注意事项如下：

  • 聚集是不跨越事实的。聚集是针对原始星形模型进行的汇总。为获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的。
  • 聚集会带来查询性能的提升，但聚集也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前存在的、已经被汇总到聚集表中的数据需要被重新调整。

此外，进行DWS层设计时还需遵循以下原则：

  • 数据公用性：需考虑汇总的聚集是否可以提供给第三方使用。可以思考，基于某个维度的聚集是否经常用于数据分析中。如果答案是肯定的，就有必要把明细数据经过汇总沉淀到聚集表中。
  • 不跨数据域：数据域是在较高层次上对数据进行分类聚集的抽象。数据域通常以业务过程进行分类，如交易统一划到交易域下， 商品的新增、修改放到商品域下。
  • 区分统计周期：在表的命名上要能说明数据的统计周期，如_1d 表示最近1天， td 表示截至当天，nd 表示最近N天。
2.3.4.2. 公共汇总事实表规范

公共汇总事实表命名规范：dws_{业务板块缩写/pub}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}。

  • 关于统计实际周期范围缩写，缺省情况下，离线计算应该包括最近一天（_1d）,最近N天（_nd）和历史截至当天（_td）三个表。如果出现_nd的表字段过多需要拆分时，只允许以一个统计周期单元作为原子拆分。即一个统计周期拆分一个表，例如最近7天（_1w）拆分一个表。不允许拆分出来的一个表存储多个统计周期。
  • 对于小时表[无论是天刷新还是小时刷新]，都用_hh来表示。
  • 对于分钟表[无论是天刷新还是小时刷新]，都用_mm来表示。

举例如下：

  • dws_asale_trd_byr_subpay_1d （A电商公司买家粒度交易分阶段付款一日汇总事实表）
  • dws_asale_trd_byr_subpay_td（A电商公司买家粒度分阶段付款截至当日汇总表）
  • dws_asale_trd_byr_cod_nd（A电商公司买家粒度货到付款交易汇总事实表）
  • dws_asale_itm_slr_td（A电商公司卖家粒度商品截至当日存量汇总表）
  • dws_asale_itm_slr_hh（A电商公司卖家粒度商品小时汇总表）---维度为小时
  • dws_asale_itm_slr_mm（A电商公司卖家粒度商品分钟汇总表）---维度为分钟

DWS层数据存储及生命周期管理规范请参见

CREATE TABLE IF NOT EXISTS dws_asale_trd_byr_ord_1d
(
    buyer_id                BIGINT COMMENT '买家id',
    buyer_nick              STRING COMMENT '买家昵称',
    mord_prov               STRING COMMENT '收货人省份',
    cate_id                 BIGINT COMMENT '商品类目id',
    cate_name               STRING COMMENT '商品类目名称',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '买家粒度所有交易最近一天汇总事实表'
PARTITIONED BY (ds         STRING COMMENT '分区字段YYYYMMDD')
LIFECYCLE 36000;

CREATE TABLE IF NOT EXISTS dws_asale_trd_itm_ord_1d
(
    item_id                 BIGINT COMMENT '商品ID',
    item_title               STRING COMMENT '商品名称',
    cate_id                 BIGINT COMMENT '商品类目id',
    cate_name               STRING COMMENT '商品类目名称',
    mord_prov               STRING COMMENT '收货人省份',
    confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天订单已经确认收货的金额总和'
)
COMMENT '商品粒度交易最近一天汇总事实表'
PARTITIONED BY (ds         STRING COMMENT '分区字段YYYYMMDD')
LIFECYCLE 36000;

2.4. 数据分层模型调用规范

ADS应用层优先调用数据仓库公共层数据。如果已经存在CDM层数据，不允许ADS应用层跨过CDM中间层从ODS层重复加工数据。CDM中间层应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他数据层次提供数据服务。同时，ADS应用层也需积极配合CDM中间层进行持续的数据公共建设的改造。避免出现过度的ODS层引用、不合理的数据复制和子集合冗余。总体遵循的层次调用原则如下：

  • ODS层数据不能直接被应用层任务引用。如果中间层没有沉淀的ODS层数据，则通过CDM层的视图访问。CDM层视图必须使用调度程序进行封装，保持视图的可维护性与可管理性。
  • CDM层任务的深度不宜过大（建议不超过10层）。
  • 一个计算刷新任务只允许输出一个表，特殊情况除外。
  • 如果多个任务刷新输出一个表（不同任务插入不同的分区），大数据平台需要建立一个虚拟任务，依赖多个任务的刷新和输出。通常，下游应该依赖此虚拟任务。
  • CDM汇总层优先调用CDM明细层，可累加指标计算。CDM汇总层尽量优先调用已经产出的粗粒度汇总层，避免大量汇总层数据直接从海量的明细数据层中计算得出。
  • CDM明细层累计快照事实表优先调用CDM事务型事实表，保持数据的一致性。
  • 有针对性地建设CDM公共汇总层，避免应用层过度引用和依赖CDM层明细数据。

3. 数仓系统性能基准

大数据平台性能表现优劣，主要取决的表设计是否符合规范。为方便衡量大数据平台表的性能表现，建议在优化性能之前首先建立性能基准。

在优化表前后测试系统性能时，需要记录每张表的数据同步时间、占用存储大小以及查询性能的详细信息。

测试项测试值
数据同步时间无
占用存储大小无
查询执行时间无
查询费用预估无

3.1. 记录数据同步时间

在执行数据同步任务后，可以在运维中心 ****> ****周期实例页面右键查看用户任务运行时间，如下图所示。

3.2. 记录占用存储大小

可以使用describe命令查看全表或表中某个分区占用物理存储的大小。

4. 数仓性能相关指标优化

针对数仓的性能优化，主要是针对表和数据分布的优化。

4.1. Hash Clustering

Hash Clustering表的优势在于可以实现Bucket Pruning优化、Aggregation优化以及存储优化。在创建表时，使用clustered by指定Hash Key后，大数据平台将对指定列进行Hash运算，按照Hash值分散到各个Bucket里。Hash Key值的选择原则为选择重复键值少的列。如何转化为Hash Clustering表：

ALTER TABLE table_name [CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS]

ALTER TABLE语句适用于存量表，在增加了新的聚集属性之后，新的分区将做Hash Clustering存储。

创建完Hash Clustering表后，可以使用INSERT OVERWRITE语句将源表转化为Hash Clustering表。

说明： Hash Clustering表存在以下限制：

  • 不支持INSERT INTO语句，只能通过INSERT OVERWRITE来添加数据。
  • 不支持直接使用tunnel upload命令将数据导入到range cluster表，因为tunnel上传的数据是无序的。

4.2. 表的其他优化技巧

此外，还可以利用下列技巧完成表的优化：

  • 中间表的利用：适用于数据量非常大，下游任务很多的表。
  • 拆表：适用于个别字段产出极慢的情况，可以将字段拆分为单独的表。
  • 合表：随着数仓的发展，针对业务重叠或重复的表，可以进行任务和数据合并。
  • 拉链表：合理利用拉链表能减少的存储消耗。
  • 利用大数据平台表的特殊功能：

4.3. 结果验证

完成数仓的优化后，需要对结果进行评估验证，确认优化的有效性。如果在优化过程中改变了表结构，需要删除原有的表，并根据优化策略新建表和分区。本教程中提供的测试数据也需要进行对应的结构调整，方便完成数据的导入。

在重新创建表并导入数据后，需要重新测试数仓性能。可以通过下列表格记录相关数据，并与性能基准进行比对。

测试项测试值
数据同步时间
占用存储大小
查询执行时间
查询费用预估

5. 博文参考

  • 《阿里巴巴大数据实战》
  • 《数仓系统开发实战》
avatar
文章
阅读
粉丝