基于Doris的OLAP查询和实时数据仓库实战

一个完整的业务方案实现离不开具体的实践，做好一个项目的核心就在于业务方案。好的业务方案的判断标准就是是否满足业务需求，是否能够合理地完成业务指标的计算。本文重点介绍零售数据仓库建设的业务方案实现。

该项目是某头部运动品牌企业旗下的一个子品牌的零售BI项目，项目内容包括主数据梳理、零售业务分析和商品库存分析。项目各交易系统积累了3年左右的历史数据，包括2000万的零售小票和6000万的库存出入明细数据，数据量不大，但是计算逻辑比较复杂，报表查询范围也比较广。

该项目主要解决的业务痛点包括以下4方面。

1)数据孤岛。随着业务快速发展，SAP系统、新零售系统、电商系统、渠道系统逐步上线，需要有一套分析体系打通各业务系统中的数据，形成完整的业务视图。

2)手工制作报表耗时耗力。底层数据没有统一的规范，报表制作成本高，耗时长。

3)企业现有分析维度单一。零售数据集成到企业微信仅局限于终端销售数据展示，缺乏全链路数据分析维度。

4)未能体现历史数据的价值。目前企业都是被动接收需求，临时提取数据，缺乏完整的商业分析思路，未能发挥历史数据的价值。

该项目的需求主要围绕零售和商品库存业务展开，主要抽取店铺的销售明细数据、店仓的进出库明细数据，搭建以店铺为中心的零售模型和以店仓为中心的库存模型。

零售模型主要包括线下门店销售和线上门店销售。线下门店销售数据主要从新零售系统抽取，用于计算店铺销售数量、销售金额、销售吊牌金额、折扣等，然后围绕店铺的考核加工有效店、同店的标识，计算同店销售金额。线上门店销售是指电商渠道销售，主要包括天猫、京东、唯品会、抖音等电商平台的销售，其数据统一来源是新电商系统。不过，该系统数据更新较晚，通常需要第二天早上6点才能采集完。

库存模型数据主要来自大仓库存、门店库存、电商库存。大仓库存和门店库存数据都来自新零售系统，包括每日仓库进出明细、每月汇总的库存状态以及仓库间调拨明细。围绕库存基础数据，我们主要关注每日库存数量、每日库存吊牌金额、每日在途库存数量、每日在途库存吊牌，以及销售数据计算齐码率、售罄率、库销比等指标。电商库存数据相对比较简单，取自新电商系统，给到的就是每日库存，可直接用于计算售罄率。

在构建零售模型、库存模型基础上，我们以业务为导向，实现移动端数据的实时呈现和PC端数据的多维分析。系统前端展现包括3种形式。

1)移动端BI报表，包括总部零售看板、区域零售看板、门店零售看板、总部商品看板等。

2)PC端自助分析报表，包括零售多维自助分析报表、销存结构多维自助分析报表、售罄率多维自助分析报表等。

3)实时销售报表，包括总部、区域、门店3个维度的当日实时销售数据分析报表。

技术层面上要求系统采用开源技术架构搭建大数据平台，满足未来五年的业务扩展需求；搭建灵活的零售数据仓库模型，以便于业务拓展和快速响应新需求。

业务层面上要求数据准确率达到99%，数据时效性达到1 min入库，页面操作响应速度快（网页响应时间为6 s，移动端响应时间为3 s），提升用户体验。

在项目选型阶段，我们先后测试和对比过Hana、Greenplum+Oracle、Hive+Kyligence（kylin商业版）解决方案，最终决定采用Hive on Spark数据加工、DataX数据同步、Doris OLAP查询的开源解决方案。同时为了满足部分零售报表实时数据查询需求，我们在系统中增加了Flink模块，完成实时数据的清洗加工，并将数据最终写入Doris，实现数据秒级查询。此外，我们还采用了集团统一购买的商业BI软件完成移动端报表、PC端自助分析报表和实时销售报表等的呈现。系统整体架构如图13-1所示。

图13-1 零售数仓系统架构

该项目抽取数据的来源库主要包括新零售系统的Oracle、EBI系统的SQL Server、导购人员信息的Oracle数据库、权限信息的MySQL数据库。ODS层抽取的接口表比较多，仅本项目涉及的接口表有100多张，所以在ODS层最重要的就是实现数据抽取流程标准化配置，简化操作。当然，标准化配置针对增量、全量两种数据抽取模式。针对数据量超过10万的表，要求必须采用增量模式，以实现稳定的数据同步。

全量数据抽取需要配置数据来源实例、数据库、表名或者查询语句以及目标数据库和目标表名，由平台自动生成建表语句和数据同步任务。

增量数据抽取比全量数据抽取复杂很多，需要保证抽取完成以后，接口数据不丢、不重复、不多。为了保证数据不丢失，我们要求源库增加字段记录数据创建或者修改事件；为了保证数据不重复，我们汇总数据以后通过业务主键进行数据去重、合并；为了保证数据不多，我们要求源库尽可能避免DELETE操作，并且增加删除标记字段。

一般情况下，我们只需要抽取最近一天新增或者变化的数据作为增量。以p_bl_sell_hd表为例，抽取增量数据的语句如下：

当主表的字段信息不足以判断数据为新增时，可关联其他表来协助判断，并通过SQL语句来读取增量数据。这种情况在接入SAP系统时很常见，例如抽取p_bl_sell_dt表的增量数据时就需要借助p_bl_sell_hd表标记的更新时间。

增量数据抽取配置比全量数据抽取配置多了一个逻辑主键输入框，可以输入一个字段或者多个字段。在抽取到临时表增量数据以后，需要通过逻辑主键进行数据合并。假设有一张表p_bl_sell_hd，对应的逻辑主键是BILLID，那么数据合并语句如下：

这个逻辑主键可以是业务主键，也可以是一个区分数据增量的日期。在增量数据抽取时，系统会先将增量数据抽取到一个带日期分区的临时表（该临时表保存全量初始化数据和每日增量数据），然后基于前一日的快照和当日抽取的增量数据合并生成当日的全量快照。

Hadoop大数据集群是基于开源组件搭建的。该项目主要用到的组件包括HDFS、Yarn、Hive、Spark、Flink。从业务系统同步过来的数据都存储在HDFS中，也就是我们常说的ODS层。数据仓库工程师只需要开发Hive SQL脚本并上传到Git，调度平台自动拉取最新版本的SQL文件并通过Spark-shell命令提交到Spark集群执行。

该项目数据仓库模型采用行业通用的分层架构，主要分为DIM层、DWD层、DWS层。关于分层的详细介绍，12.2节已有介绍，这里就不再展开。

DIM层数据主要围绕商品、网点两个主数据进行加工。根据鞋服行业的实际情况，商品主数据以SKC为核心展开，常见的分析维度包括商品大类、商品中类、商品品类、销售年季、上市日期、上市批次、商品系列等。之所以选择SKC粒度，是因为SKC作为鞋服商品定价的最细粒度，可以保留以吊牌价计算库存吊牌金额，同时又不会因为数据量过大，给数据关联带来压力。

零售小知识

零售领域的SPU、SKC、SKU等常见概念如下。

❑SPU（Standard Product Unit，标准化产品单元）是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。该集合描述了一个产品的特性。通俗点讲，属性值、特性相同的商品可以称为一个SPU。例如，iphone4就是一个SPU，N97也是一个SPU，这与商家，产品颜色、款式、套餐无关。

❑SKU（Stock Keeping Unit，库存量单位）是库存进出计量单位，可以是以件、盒、托盘等，在鞋服类商品中使用最普遍。SKU是物理上不可分割的最小存货单元，也是销售的最细粒度。

❑SKC（Stock Keeping Color，单款单色）是鞋服领域常用的一个概念，用于进行鞋服销售和库存统计。SKC在销售分析和库存分析方向有广泛应用。

网点由店铺和仓库组成，店铺又包括线下实体门店和线上虚拟店铺，而仓库包括对接工厂的总仓、各省市的中间仓库、电商综合仓库和线下门店的储物仓。其中，线下门店的储物仓和线下门店通常采用相同的编码，以便于汇总数据到一个实体。根据不要的实体类型，网点主数据有不同的维度属性。常见的分析维度有线上线下标识、店仓标识、门店的省市区位置、门店管理层级、经营方式、加盟形式、商圈、开关店日期等。为了便于统一维度分析口径，我们在数据仓库建模时会整理所有维度的网点主数据。

DWD层数据加工主要围绕商品销售明细和商品出入库明细展开，包含销售和库存两个模块。销售模块围绕销售订单展开，关联销售明细中的商品编码、销售数量和销售金额，构建以销售订单、店铺、商品编码为核心的销售明细模型。库存模块抽取所有的商品调拨明细、出入库数据，并以尺码为粒度，构建以调拨单号、店仓代码、商品SKU为核心的出入库明细模型。此外，还衍生一个在途模型：抽取商品在调出和调入之间由于时间差产生的在途数据进行构建，需要在计算库存时算入收货方库存。

DWS层在DWD层的基础上汇总数据并根据业务逻辑计算指标。销售明细模型加工的指标有销售吊牌价、同店销售明细、有效店销售明细等，出入库明细模型加工的指标有物理库存、在途库存、财务库存，需结合库存和销售明细计算的指标有售罄率、动销率、齐码率、库销比等。具体的业务逻辑在后面的业务方案模块展开介绍。

按照分层逻辑，数据仓库的简化模型如图13-2所示。

图13-2 数据仓库的简化模型

该项目的集市层数据加工依然是通过Hive完成的，数据加工好以后通过全量或者增量模式同步到Doris。Hive数据加工过程和仓库层数据加工过程类似，集市层主要是增加了同期计算，并关联了维度数据，进行了一些维度收敛，缩少数据查询规模。

数据同步到Doris是通过Routine Load来实现的。这里不得不夸一下Doris，Doris支持基于Key字段的数据删除操作，因此可以先删除数据再插入，以便实现数据增量同步。前文说到，电商业务的数据通常第二天6点以后才可以抽取完，且系统数据必须分成两条链路加工，但是报表需要组合在一起查看，所以需要在Doris中合并数据。因此每次抽取数据时需要先执行删除操作，典型示例如下：

其中，导入语句中load_parallelism参数可以根据数据量大小进行调整，通常需要同步的数据记录数小于1000万时采用1个并行处理任务即可；超过1000万的数据记录数可以根据可接受时长来调整并行处理任务。

当然，Broker Load也有不方便的地方，因为数据加载是异步的，需要不断轮询才能确保数据加载完成。读者可以参考5.3节Broker Load的内容，通过Python代码实现数据轮询。如果有新的应用场景，建议采用DataX抽取数据的方式进行数据同步。

在离线批处理基础上，针对销售数据，我们还有实时分析需求。销售数据主要来自Oracle数据库和SQL Server数据库。我们可通过Debezium强大的日志解析功能，读取两个库的CDC日志到Kafka，然后由Flink实时计算平台完成数据的清洗并关联离线批处理好的维度数据，写入Doris。Doris中的目标表采用Unique模型，取多个字段组合作为主键字段，以防数据重复。实时数据加工流程如图13-3所示。

图13-3 实时数据加工流程

Debezium是一个用来捕获数据库数据变更操作的分布式服务。Debezium以事件流的形式记录每张表的行级变更，然后应用以事件产生顺序读取事件变更记录。目前，Debezium支持的数据源有MySQL、MongoDB、PostgreSQL、Oracle、SQL Server、DB2等。由于该项目对接的数据库包含Oracle和SQL Server，所以只能选择Debezium组件。

Flink实时计算逻辑采用Flink SQL实现，关键代码如下：

早期的Doris不支持Flink直接写入数据，需要通过KafKa中转，因此在Flink加工数据之后，还需要在Doris中创建ROUTINE LOAD任务来实时消费KafKa中的数据并写入Doris内部表。

仓库层的数据加工主要采取Hive SQL脚本实现，该项目采用Git管理开发代码，但是Git不支持自动发布代码，所以需要手动触发代码发布。其实，笔者更推荐使用SVN管理SQL代码。如果代码管理工具是SVN，我们可以通过配置hook文件，以及Linux系统的Crontab命令，实现自动发布代码。

一个完整的业务方案实现离不开具体的实践，做好一个项目的核心就在于业务方案。好的业务方案的判断标准就是是否满足业务需求，是否能够合理地完成业务指标的计算。本节重点介绍零售数据仓库建设的业务方案实现。

零售流水指标主要指线下门店销售产生的明细数据分析指标。部分指标包含线上电商平台销售数据。该项目针对线下销售主要抽取多品牌新零售系统产生的销售订单数据，针对线上销售抽取EBI系统获取的日销售汇总数据。零售流水指标含义及其计算逻辑如表13-1所示。

表13-1 零售流水指标含义及其计算逻辑

除了上述指标，还有针对每个指标的日、周、月、年等不同周期的同环比，其中金额类和数量类指标的同环比一般等于（本期-同期）/同期，而比例类指标是本期数据减去同期数据，例如周折扣率环比=本周折扣率-上周折扣率。

有效店、同店是渠道分析的两个核心指标，主要针对线下业务。所谓“店”，是指商场或者街边的实体门店。说到渠道指标，这里需要先解释几个业务名词。

❑新开店：开店所在月份期间都称为新开店。❑改造店：改造开始日至改造完成日期间都称为改造店。

❑不变店：新开店从开店日所在月的下一个月份后都称为不变店。❑关闭店：关闭日及以后均称为关闭店。

❑有效店：在某个自然月，如果一间店铺的新开和关闭都不在该月，且这个月没有改造、月度零售流水大于0，则该店铺视为有效店。

❑同店：店铺在某个月以及去年的同月都是有效店，则视为同店。

以上指标中“店”的统计范围也有规定，指纳入渠道管理系统统一管理、有唯一编码的独立、正常经营店铺（不含临时网点及临时特卖网点）。

基于以上定义产生的渠道原子指标及衍生指标的定义及其计算逻辑如表13-2所示。

表13-2 渠道原子指标及衍生指标的定义及其计算逻辑

其中，最重要的店铺类型为有效店和同店，由此衍生的有效店及同店指标定义及其计算逻辑如表13-3所示。

表13-3 有效店及同店指标定义及其计算逻辑

库存及齐码率是零售行业数据分析的一个重要方向。众所周知，商品库存是占用资金和成本的。对于线下门店来说，最小化库存来保持商品的快速流通，这样才可以利润最大化。消费者喜欢什么商品、购买什么商品，这是商家不能决定的，但是商家可以决定摆设什么商品，促销和推广什么商品，这里面就涉及库存的盘点和调拨。

根据库存业务逻辑，财务库存=物理库存+在途库存=期初库存+出入库日汇总+在途库存。而构建库存模型最核心的任务就是计算出每一天的财务库存。为此，我们构建了3个业务模型，分别是每日仓库进出库存明细、在途库存的每日收发货明细、汇总到月末的期末结转库存。在此基础上，进一步获得“仓库+SKC”粒度的每日库存状态。

齐码率是基于财务库存计算的衍生指标，用于衡量一个门店畅销商品的码数是否完整。齐码率一般按照品牌、商品大类、年龄段指定3～7个尺码为主码组（也有客户以最畅销的3～4个尺码为主码组），判断主码组的尺码是否存在三连码或者四连码。

基于库存和齐码率的衍生指标定义及其计算逻辑如表13-4所示。

表13-4 基于库存和齐码率的衍生指标定义及其计算逻辑

商品库销比和售罄率是分析商品的去化情况和畅销情况的指标。月度库销比=（月初库存吊牌金额+月末库存吊牌金额）/2/当月销售吊牌金额；商品售罄率=商品累计零售吊牌金额/商品累计到货吊牌金额。

由于门店之间的调拨过程非常复杂，存在各种退货或者返仓情况，因此我们根据对业务的理解，可以将累计到货吊牌金额简化成一个更简单的公式：累计到货吊牌金额=期末库存吊牌金额+期末累计销售吊牌。

通过指标的定义，我们可以看出，库销比反映的是商品流通情况，库销比越小，门店商品流通率越高；售罄率反馈的是商品的畅销情况，售罄率越高，说明客户对该商品的喜爱程度越高。这两个指标都是把门店销售和门店库存结合起来进行分析的。库销比和售罄率相关指标的定义及其计算逻辑如表13-5所示。

表13-5 库销比和售罄率相关指标的定义及其计算逻辑

该项目在项目组成员的高效配合下，于2021年6月底顺利完成。本次项目任务清单如表13-6所示。

表13-6 项目任务清单

本次项目交付的报表包括零售、渠道、商品、目标4个模块的移动端报表和R01-渠道零售分析、R02-零售运营分析、R03-任意时间段销存结构分析、R04-售罄率分析4张自助分析表。移动端报表是按照实时、日、周、月、年等不同的分析粒度汇总数据，实时报表统计当日数据，日报统计最近93个自然日数据，周报统计最近52周数据，月报统计最近24个月数据，年报统计最近5年数据。PC端报表统计最近2年的数据，用于自助分析。基于该需求背景，我们设计移动端报表按照宽表模型构建，PC端报表按照星型模型构建，最终实现了报表查询性能和灵活度的完美统一——移动BI软件在3s内完成查询（实际上95%以上的SQL语句执行在1 s内完成），PC端满足任意维度和任意时间区间的报表查询，并在30 s内呈现结果（实际是90%以上的查询在5秒内完成）。

总体来说，本次项目完美地实现了业务需求，超预期达成最初制定的目标。这一切既归功于项目组团队的精诚合作和务实精神，也得益于项目需求管控到位和Doris平台强大的性能。

在这个项目中，Doris仅承担了OLAP查询功能，和Kylin的功能非常类似。正好我们在前面一个数量级类似的供应链项目中使用Kyligence（Kylin的商业版）作为OLAP查询引擎，这里对比一下Doris和Kyligence优劣势，如表13-7所示。

表13-7 Doris和Kyligence对比

通过对比，我们可以看到，作为OLAP领域冉冉升起的新星，Doris在很多方面具有优势。事实上，很多企业已经在用Doris替换Kylin。

参考：Doris实时数仓实战：https://weread.qq.com/web/reader/e3432570813ab8029g019a69

​