数仓名词解释#1. 实体

实体是指依附的主体，就是我们分析的一个对象，比如我们分析商品的销售情况，如华为手机近半年的销售量是多少，那华为手机就是一个实体；我们分析用户的活跃度，用户就是一个实体。当然实体也可以现实中不存在的，比如虚拟的业务对象，活动，会员等都可看做一个实体。

实体的存在是为了业务分析，作为分析的一个筛选的维度，拥有描述自己的属性，本身具有可分析的价值。

#2. 维度

维度就是看待问题的角度，分析业务数据，从什么角度分析，就建立什么样的维度。所以维度就是要对数据进行分析时所用的一个量，比如你要分析产品销售情况，你可以选择按商品类别来进行分析，这就构成一个维度，把所有商品类别集合在一起，就构成了维度表。

#3. 度量

度量是业务流程节点上的一个数值。比如销量，价格，成本等等。

事实表中的度量可分为三类：完全可加，半可加，不可加。

完全可加的度量是最灵活，最有用的，比如说销量，销售额等，可进行任意维度汇总；半可加的度量可以对某些维度汇总，但不能对所有维度汇总，差额是常见的半可加度量，它除了时间维度外，可以跨所有维度进行加法操作；还有一种是完全不可加的，例如：比率。对于这类非可加度量，一种好的方法是，尽可能存储非可加度量的完全可加分量，并在计算出最终的非可加事实前，将这些分量汇总到最终的结果集中。(一般比率在聚合dws后计算)#4. 粒度

粒度就是业务流程中对度量的单位，比如商品是按件记录度量，还是按批记录度量。

在数仓建设中，我们说这是用户粒度的事实表，那么表中每行数据都是一个用户，无重复用户；例如还有销售粒度的表，那么表中每行都是一条销售记录。

选择合适的粒度级别是数据仓库建设好坏的重要关键内容，在设计数据粒度时，通常需重点考虑以下因素：

要接受的分析类型、可接受的数据最低粒度和能存储的数据量；粒度的层次定义越高，就越不能在该仓库中进行更细致的分析；如果存储资源有一定的限制，就只能采用较高的数据粒度划分；数据粒度划分策略一定要保证：数据的粒度确实能够满足用户的决策分析需要，这是数据粒度划分策略中最重要的一个准则。#5. 口径

口径就是取数逻辑（如何取数的），比如要取的数是10岁以下儿童中男孩的平均身高，这就是统计的口径。

#6. 指标

指标是口径的衡量值，也就是最后的结果。比如最近七天的订单量，一个促销活动的购买转化率等。

一个指标具体到计算实施，主要有以下几部分组成：

指标加工逻辑，比如count ,sum, avg维度，比如按部门、地域进行指标统计，对应sql中的group by业务限定/修饰词，比如以不同的支付渠道来算对应的指标，微信支付的订单退款率，支付宝支付的订单退款率 。对应sql中的where。

除此之外，指标本身还可以衍生、派生出更多的指标，基于这些特点，可以将指标进行分类：

原子指标：基本业务事实，没有业务限定、没有维度。比如订单表中的订单量、订单总金额都算原子指标；

业务方更关心的指标，是有实际业务含义，可以直接取数据的指标。比如店铺近1天订单支付金额就是一个派生指标，会被直接在产品上展示给商家看。 但是这个指标却不能直接从数仓的统一中间层里取数（因为没有现成的事实字段，数仓提供的一般都是大宽表）。需要有一个桥梁连接数仓中间层和业务方的指标需求，于是便有了派生指标

派生指标：维度+修饰词+原子指标。 店铺近1天订单支付金额中店铺是维度，近1天是一个时间类型的修饰词，支付金额是一个原子指标；

维度：观察各项指标的角度； 修饰词：维度的一个或某些值，比如维度性别下，男和女就是2种修饰词。

衍生指标：比如某一个促销活动的转化率就是衍生指标，因为需要促销投放人数指标和促销订单数指标进行计算得出。#7. 标签

标签是人为设定的、根据业务场景需求，对目标对象运用一定的算法得到的高度精炼的特征标识。可见标签是经过人为再加工后的结果，如网红、白富美、萝莉。对于有歧义的标签，我们内部可进行标签区分，比如：苹果，我们可以定义苹果指的是水果，苹果手机才指的是手机。

#8. 自然键

由现实中已经存在的属性组成的键，它在业务概念中是唯一的，并具有一定的业务含义，比如商品ID，员工ID。

以数仓角度看，来自于业务系统的标识符就是自然键，比如业务库中员工的编号。

#9. 持久键

保持永久性不会发生变化。有时也被叫做超自然持久键。比如身份证号属于持久键。

自然键和持久键区别：举个例子就明白了，比如说公司员工离职之后又重新入职，他的自然键也就是员工编号发生了变化，但是他的持久键身份证号是不变的。

#10. 代理键

就是不具有业务含义的键。代理键有许多其他的称呼：无意义键、整数键、非自然键、人工键、合成键等。

代理键就是简单的以按照顺序序列生产的整数表示。产品行的第1行代理键为1，则下一行的代理键为2，如此进行。代理键的作用仅仅是连接维度表和事实表。

#11. 退化维度

退化维度，就是那些看起来像是事实表的一个维度关键字，但实际上并没有对应的维度表，就是维度属性存储到事实表中，这种存储到事实表中的维度列被称为退化维度。与其他存储在维表中的维度一样，退化维度也可以用来进行事实表的过滤查询、实现聚合操作等。

那么究竟怎么定义退化维度呢？比如说订单id，这种量级很大的维度，没必要用一张维度表来进行存储，而我们进行数据查询或者数据过滤的时候又非常需要，所以这种就冗余在事实表里面，这种就叫退化维度，citycode这种我们也会冗余在事实表里面，但是它有对应的维度表，所以它不是退化维度。

#12. 下钻

这是在数据分析中常见的概念，下钻可以理解成增加维的层次，从而可以由粗粒度到细粒度来观察数据，比如对产品销售情况分析时，可以沿着时间维从年到月到日更细粒度的观察数据。从年的维度可以下钻到月的维度、日的维度等。

#13. 上卷

知道了下钻，上卷就容易理解了，它俩是相逆的操作，所以上卷可以理解为删掉维的某些层，由细粒度到粗粒度观察数据的操作或沿着维的层次向上聚合汇总数据。

#14. 数据集市

数据集市（Data Mart），也叫数据市场，数据集市就是满足特定的部门或者用户的需求，按照多维的方式进行存储，包括定义维度、需要计算的指标、维度的层次等，生成面向决策分析需求的数据立方体。其实就是从数据仓库中抽取出来的一个小合集。

数仓名词之间关系#1. 实体表，事实表，维度表之间的关系

在Kimball维度建模中有维度与事实，在Inmon范式建模中有实体与关系，如果我们分开两种建模方式看这些概念比较容易理解。但是目前也出现了不少混合建模方式，两种建模方式结合起来看，这些概念是不是容易记忆混乱，尤其事实表和实体表，它们之间到底有怎样区别与联系，先看下它们各自概念：

维度表：维度表可以看成是用户用来分析一个事实的窗口，它里面的数据应该是对事实的各个方面描述，比如时间维度表，地域维度表，维度表是事实表的一个分析角度。事实表：事实表其实就是通过各种维度和一些指标值的组合来确定一个事实的，比如通过时间维度，地域组织维度，指标值可以去确定在某时某地的一些指标值怎么样的事实。事实表的每一条数据都是几条维度表的数据和指标值交汇而得到的。实体表：实体表就是一个实际对象的表，实体表放的数据一定是一条条客观存在的事物数据，比如说各种商品，它就是客观存在的，所以可以将其设计一个实体表。实体表只描述各个事物，并不存在具体的事实，所以也有人称实体表是无事实的事实表。

举个例子：比如说手机商场中有苹果手机，华为手机等各品牌各型号的手机，这些数据可以组成一个手机实体表，但是表中没有可度量的数据。某天苹果手机卖了15台，华为手机卖了20台，这些手机销售数据属于事实，组成一个事实表。这样就可以使用日期维度表和地域维度表对这个事实表进行各种维度分析。

#2. 指标与标签的区别概念不同

指标是用来定义、评价和描述特定事物的一种标准或方式。比如：新增用户数、累计用户数、用户活跃率等是衡量用户发展情况的指标；

标签是人为设定的、根据业务场景需求，对目标对象运用一定的算法得到的高度精炼的特征标识。可见标签是经过人为再加工后的结果，如网红、白富美、萝莉。

构成不同

指标名称是对事物质与量两方面特点的命名；指标取值是指标在具体时间、地域、条件下的数量表现，如人的体重，指标名称是体重，指标的取值就是120斤；

标签名称通常都是形容词或形容词+名词的结构，标签一般是不可量化的，通常是孤立的，除了基础类标签，通过一定算法加工出来的标签一般都没有单位和量纲。如将超过200斤的称为大胖子。

分类不同

对指标的分类：

按照指标计算逻辑，可以将指标分为原子指标、派生指标、衍生指标三种类型；

按照对事件描述内容的不同，分为过程性指标和结果性指标；

对标签的分类：

按照标签的变化性分为静态标签和动态标签；

按照标签的指代和评估指标的不同，可分为定性标签和定量标签；

指标最擅长的应用是监测、分析、评价和建模。 标签最擅长的应用是标注、刻画、分类和特征提取。 特别需要指出的是，由于对结果的标注也是一种标签，所以在自然语言处理和机器学习相关的算法应用场景下，标签对于监督式学习有重要价值，只是单纯的指标难以做到的。而指标在任务分配、绩效管理等领域的作用，也是标签无法做到的。

#3. 维度和指标区别与联系

维度就是数据的观察角度，即从哪个角度去分析问题，看待问题。

指标就是从维度的基础上去衡算这个结果的值。

维度一般是一个离散的值，比如时间维度上每一个独立的日期或地域，因此统计时，可以把维度相同记录的聚合在一起，应用聚合函数做累加、均值、最大值、最小值等聚合计算。

指标就是被聚合的通计算，即聚合运算的结果，一般是一个连续的值。

#4. 自然键与代理键在数仓的使用区别

数仓工具箱中说维度表的唯一主键应该是代理键而不应该是自然键。有时建模人员不愿意放弃使用自然键，因为他们希望与操作型代码查询事实表，而不希望与维度表做连接操作。然而，应该避免使用包含业务含义的多维键，因为不管我们做出任何假设最终都可能变得无效，因为我们控制不了业务库的变动。

所以数据仓库中维度表与事实表的每个连接应该基于无实际含义的整数代理键。避免使用自然键作为维度表的主键。

#5. 数据集市和数据仓库的关系

数据集市是企业级数据仓库的一个子集，他主要面向部门级业务，并且只面向某个特定的主题。为了解决灵活性和性能之间的矛盾，数据集市就是数据仓库体系结构中增加的一种小型的部门或工作组级别的数据仓库。数据集市存储为特定用户预先计算好的数据，从而满足用户对性能的需求。数据集市可以在一定程度上缓解访问数据仓库的瓶颈。

数据集市和数据仓库的主要区别：数据仓库是企业级的，能为整个企业各个部门的运行提供决策支持手段；而数据集市则是一种微型的数据仓库,它通常有更少的数据,更少的主题区域,以及更少的历史数据,因此是部门级的，一般只能为某个局部范围内的管理人员服务，因此也称之为部门级数据仓库。

总体数据规范表和字段都要写注释表字段命名：小写+下划线维度列（包含id）：string度量 : 整数用bigint, 小数用 decimal(16,4) 控制精度ads指标：整数用bigint, 小数用 decimal(16,4) 控制精度表名前要加库名., 如：dim.dim_user_l

类型建议：

金额：decimal(16,4) 控制精度。字符串：string。id类：string。时间：string。状态：string数据表处理规范#1) 增量表

新增数据，增量数据是上次导出之后的新数据。

记录每次增加的量，而不是总量；增量表，只报变化量，无变化不用报；每天一个分区。#2) 全量表

每天的所有的最新状态的数据。

全量表，有无变化，都要报；每次上报的数据都是所有的数据（变化的 + 没有变化的）；只有一个分区。#3) 快照表

按日分区，记录截止数据日期的全量数据。

快照表，有无变化，都要报；每次上报的数据都是所有的数据（变化的 + 没有变化的）；一天一个分区。#4) 拉链表

记录截止数据日期的全量数据。

记录一个事物从开始，一直到当前状态的所有变化的信息；拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总 量；当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）；只有一个分区。周期/数据范围

日快照：d

增量：i

全量：f

周：w

拉链表：l

非分区全量表：a

手工表：manual

hive离线数仓分库

hive按层分库，分为5个库：ods, dwd, dim, dws, ads。

hive建表时注意hdfs文件路径如下:

1. ods库： /data/hadoop/hive/warehouse/ods.db/erp

2. dwd库： /data/hadoop/hive/warehouse/dwd.db

3. dim库：/data/hadoop/hive/warehouse/dim.db

4. dws库：/data/hadoop/hive/warehouse/dws.db

5. ads库：/data/hadoop/hive/warehouse/ads.db

数据源层: ODS层（Operational Data Store）

数据源层主要将各个业务数据导入到大数据平台，作为业务数据的快照存储。

ODS 层是最接近数据源中数据的一层，为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可，至于数据的去噪、去重、异常值处理等过程可以放在后面的 DWD 层来做。

同步规范：

一个系统源表只允许同步一次；全量初始化同步和增量同步处理逻辑要清晰；以更新或统计日期进行分区存储；

ODS命名规范表命名规范

DB同步方式得到非去重数据：

按天增量表：ods_{源系统表名}_di按天快照全量表: ods_{源系统表名}_df按小时的增量表：ods_{源系统表名}_hi按小时快照的全量表：ods_{源系统表名}_hf

ODS ETL过程的临时表：tmp_{临时表所在过程的输出表}_{从0开始的序号} 当从不同源系统同步到一个project下表命名冲突时，后进来的表的命名加上源系统的dbname。

字段命名规范字段默认使用源系统字段名称, 转成小写+下划线字段名与关键字冲突时处理规则：加一个”_col”后缀，即：源字段名_colHive建表设计

HSQL详情参考订单域建模：订单域建模

1）ODS层原封不动地接入原始数据。

2）使用外部表EXTERNAL，数据存储格式为orc列式存储+snappy压缩。

3）表名的命名规范为ods_源系统表名_{刷新周期标识}{单分区增量全量标识}

订单增量表：ods_order_list_di

分区原则：按照更新或统计日期进行分区

 create  table if not exists ods.ods_order_list_di (   `id` STRING COMMENT "订单ID",   `name` STRING COMMENT "顾客名称",   。。。。。。   `stock_id` STRING COMMENT "仓库id",   `package_user_id` STRING COMMENT "包装员ID",   `updated` STRING COMMENT "更新时间，如果没有传递过去，会设置成当前时间",   `shipping_company_id` STRING COMMENT "渠道id",   `collection_verify_date` STRING COMMENT "验货时间/包装时间",  `preference_date` STRING COMMENT "待优选时间",   `dept_no` STRING  COMMENT "销售分部号",  `fines_expenses` DECIMAL(16, 4)  COMMENT "罚金支出",   `other_deduction` DECIMAL(16, 4)  COMMENT "其他扣除",   `is_yellow_mark` STRING  COMMENT "黄字订单,0:否 1：是",   `is_blue_mark` STRING COMMENT "蓝字订单,0:否 1：是",   `user_order_date`  STRING COMMENT "下单时间",   `symbol` STRING COMMENT "symbol",   `site` STRING  COMMENT "站点",   `preferred_completion_date` STRING COMMENT "物流优选完成时间",   `marketing_fee` DECIMAL(16, 4) COMMENT "营销费" )comment '订单增量表' partitioned by (`dt` string) ROW format delimited fields terminated by '\t' NULL defined as '' stored as orc location '/hive/warehouse/ods.db/erp/ods_order_list_di/' tblproperties ('orc.compress' = 'snappy');

数据仓库：DW（Data Warehouse）

数据仓库层是我们在做数据仓库时要核心设计的一层，在这里，从 ODS 层中获得的数据按照主题建立各种数据模型。

DW 层又细分为 DWD（Data Warehouse Detail）层、DWM（Data WareHouse Middle）层和 DWS（Data WareHouse Servce） 层

数据明细层：DWD（Data Warehouse Detail）

该层一般保持和 ODS 层一样的数据粒度，并且提供一定的数据质量保证。DWD 层要做的就是将数据清理、整合、规范化、脏数据、垃圾数据、规范不一致的、状态定义不一致的、命名不规范的数据都会被处理。

同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。

另外，在该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性 。

事实表

事实表中的每行对应一个度量，每行中的数据是一个特定级别的细节数据，称为粒度。维度建模的核心原则之一是同一事实表中的所有度量必须具有相同的粒度。这样能确保不会出现重复计算度量的问题。

维度表一般都是单一主键，少数是联合主键，注意维度表不要出现重复数据，否则和事实表关联会出现数据发散问题。

有时候往往不能确定该列数据是事实属性还是维度属性。记住最实用的事实就是数值类型和可加类事实。所以可以通过分析该列是否是一种包含多个值并作为计算的参与者的度量，这种情况下该列往往是事实；如果该列是对具体值的描述，是一个文本或常量，某一约束和行标识的参与者，此时该属性往往是维度属性。但是还是要结合业务进行最终判断是维度还是事实。

事务型事实表基于数据应用需求的分析设计事务型事实表，结合下游较大的针对某个业务过程和分析指标需求，可考虑基于某个事件过程构建事务型实时表；一般选用事件的发生日期或时间作为分区字段，便于扫描和裁剪；冗余子集原则，有利于降低后续IO开销；明细层事实表维度退化，减少后续使用join成本。

每天一个分区，每天递增，di

周期快照事实表周期快照事实表中的每行汇总了发生在某一标准周期，如某一天、某周、某月的多个度量事件。粒度是周期性的，不是个体的事务。不会保留所有数据，只保留固定时间间隔的数据通常包含许多事实，因为任何与事实表粒度一致的度量事件都是被允许的。

每天一个分区，每天全量，df

累积快照事实表

主要用于统计一个业务体系中，多个事实环节的时间间隔类型的指标

多个业务过程联合分析而构建的事实表，如采购单的流转环节。用于分析事件时间和时间之间的间隔周期。用于跟踪业务事实的变化少量的且当前事务型不支持的，如关闭、发货等相关的统计。

每天一个分区，每天更新历史数据，每天全量，df

Hive建表设计

1）DWD层的设计依据是维度建模理论，该层存储维度模型的事实表。

2）使用外部表EXTERNAL，DWD层的数据存储格式为orc列式存储+snappy压缩。

3）DWD层表名的命名规范为dwd_数据域_表名_单分区增量全量标识

分区原则：按照事实发生的日期进行分区

维度字段放前面，统一string类型（粒度标识字段放最前面，如：订单ID，商品ID，SKU），度量字段放后面

NULL值处理：度量置0，如coalesce(labeling_fee, 0)

DWD事实表命名规范

{project_name}.dwd_{业务BU缩写/pub}_{数据域缩写}_{业务过程缩写}[_{自定义表命名标签缩写}]_{刷新周期标识}{单分区增量全量标识}，pub表示数据包括多个BU的数据，单分区增量全量标识：i：表示增量，f表示全量。

销售订单sku发货事务事实表（增量表）:dwd_order_sale_sku_list_di

销售域ebay(易贝) Listing（非分区全量表）：dwd_sale_ebay_listing_a

维度表DIM层（公共层）DIM命名规范

{project_name}.dim_{业务BU/pub}_{维度定义}[_{自定义命名标签}]，所谓的pub是类似与具体业务BU无关，各个bu都可以共用，例如时间维度。

dim_product_sku（ERP商品SKU维表）

dim_product_brand（ERP商品品牌信息维表）

dim_product_categroy(ERP商品发布类目维表)

dim_order_status_manual 订单状态维表(手工表)

Hive数仓DIM层建表特征

维度列（包含id）都用string类型，方便区分和join

1. DIM层的设计依据是维度建模理论，该层存储维度模型的维度表。

2. 使用外部表EXTERNAL，DIM层的数据存储格式为orc列式存储+snappy压缩。

3. DIM层表名的命名规范为dim_表名(全量表)或者拉链表标识

用户全量维表：dim_user

用户维表拉链表：erp.dim_user_l 新数据(有效数据)放9999-12-31分区,历史数据放失效日期分区

Hive数仓UDF函数规范

Hive数仓UDF函数命名规范一般遵循以下规则：

1. 函数名应该简洁明了，能够清晰表达函数的功能。

2. 函数名应该以小写字母开头，多个单词之间使用下划线分隔。

3. 函数名应该避免使用Hive关键字。

4. 函数名应该遵循公司或项目的命名规范。

5. 建立函数一般放在数据仓库层，即在Hive的数据库中创建函数。如果需要在多个项目中使用同一个函数，可以将函数放在公共库中，供多个项目共享。

udf函数放dim库里，dim是公共库，命名要带后缀_udf，如：dim.weekofyear_udf

--创建函数,jar包在hdfs上create function dim.weekofyear_udf as 'com.tomtop.hive.udf.WeekOfYearUDF' using jar 'hdfs:///dolphinscheduler/hadoop/udfs/weekofyear-udf-1.0.0.jar';--查看该函数的参数、返回值类型、函数体等信息DESCRIBE FUNCTION dim.weekofyear_udf;--使用函数SELECT dim.weekofyear_udf('2022-12-31') current_week;-- 重新加载功能RELOAD dim.weekofyear_udf;--删除自定义函数drop function if exists dim.weekofyear_udf;

DWS公共汇总层设计

通过汇总明细粒度数据来获得改进查询性能的效果。通过访问聚集数据，可以减少数据库在响应查询时必须执行的工作量，能够快速响应用户的查询。

1) 聚集的基本原则一致性。聚集表必须提供与查询明细粒度数据一致的查询结果。避免单一表设计。不要在同一个表中存储不同层次的聚集数据。聚集粒度可不同。聚集并不需要保持与原始明细粒度数据一样的粒度，聚集只关心所需要查询的维度。2) 聚集的基本步骤

第一步：确定聚集维度

在原始明细模型中会存在多个描述事实的维度，如日期、商品类别、卖家等，这时候需要确定根据什么维度聚集，如果只关心商品的交易额情况，那么就可以根据商品维度聚集数据。

第二步：确定一致性上钻

这时候要关心是按月汇总还是按天汇总，是按照商品汇总还是按照类目汇总，如果按照类目汇总，还需要关心是按照大类汇总还是小类汇总。当然，我们要做的只是了解用户需要什么，然后按照他们想要的进行聚集。

第三步：确定聚集事实

在原始明细模型中可能会有多个事实的度量，比如在交易中有交易额、交易数量等，这时候要明确是按照交易额汇总还是按照成交数量汇总。

3) 公共汇总层设计原则

除了聚集基本的原则外，公共汇总层还必须遵循以下原则：

数据公用性。汇总的聚集会有第三者使用吗？基于某个维度的聚集是不是经常用于数据分析中？如果答案是肯定的，那么就有必要把明细数据经过汇总沉淀到聚集表中。不跨数据域。数据域是在较高层次上对数据进行分类聚集的抽象。区分统计周期。在表的命名上要能说明数据的统计周期，如 _Id 表示最近1天，_td 表示截至当天，_nd 表示最近N天。DWS命名规范

{project_name}.dws_{业务BU缩写/pub}_{数据域缩写}_{数据粒度缩写}[_{自定义表命名标签缩写}]_{统计时间周期范围缩写}[_刷新周期标识][_单分区增量全量标识]。

关于统计实际周期范围缩写，缺省情况下，离线计算应该包括最近一天（_1d）,最近N天（_nd）和历史截至当天(_td)三个表，如果出现_nd的表字段过多，需要拆分之时，只允许以一个统计周期单元作为原子拆分，也就是说一个统计周期拆分一个表，比如最近7天(_1w)拆分一个表；

不允许拆分出来的一个表存储多个统计周期的。

对于小时表[不管是天刷新还是小时刷新], 都用_hh 来表示。

对于分钟表[不管是天刷新还是小时刷新],都用_mm来表示。

dws_erp_order_amazon_gmv_1d

dws_order_product_sale_statistic_1d 订单主题域商品销售最近1天统计

dws_order_product_sale_statistic_nd 订单主题域商品销售最近n天统计

一日统一用数字1日，便于后续的搜索或元数据管理

Hive建表设计

1）该层存储汇总数据, 对相同粒度的数据进行关联汇总。

2）使用外部表EXTERNAL，数据存储格式为orc列式存储+snappy压缩。

注意保证dws中hive表的数据和hdfs的数据保持一致，否则会影响到后续doris导数

ADS建模hive实现

ADS层在建模时，一般是一个需求一张表，同一类型的需求可以合并到一张表 需求需要什么字段，表中就设计什么字段，此外必须包含日期字段 ADS层数据量小的表无需创建分区表

doris实现（建议使用）唯一模型：UNIQUE KEY模型就建BITMAP索引

-模型

采用 Unique Key 模型，该模型可有效保证数据脚本的结果幂等性，Unique Key 模型可以完美解决上游数据重复的问题。用于统计的维度要加索引查询优化

尽量把非字符类型（如 int,date 类型、where 条件）中最常用的字段放在前排 36 个字节内，在点查表过程中可以快速过滤这些字段（毫秒级别），可以充分利用该特性进行数据表输出。

聚合模型：aggregate key模型就建rollup

在doris在dws表建立rollup实现ADS，一个需求对应一个rollup

如果业务方需要根据sku统计毛收入近一日汇总金额，可以建立一个只有 sku, gross_default_amount_1d的 Rollup：

ALTER TABLE dws_order_product_sale_statistic_1d

ADD ROLLUP rollup_sku(sku, gross_default_amount_1d);

Hive的dwd, dim, dws, ads库的表导入doris第一步：建立面向BI的doris表，建议用唯一模型唯一模型：UNIQUE KEY模型就建BITMAP索引

-模型

采用 Unique Key 模型，该模型可有效保证数据脚本的结果幂等性，Unique Key 模型可以完美解决上游数据重复的问题。用于统计的维度要加索引查询优化

尽量把非字符类型（如 int,date 类型、where 条件）中最常用的字段放在前排 36 个字节内，在点查表过程中可以快速过滤这些字段（毫秒级别），可以充分利用该特性进行数据表输出。

CREATE TABLE dim.dim_department_l (  `dt` date COMMENT 'hive分区字段',  `id` bigint COMMENT '部门ID',   `name`  varchar(50) COMMENT '部门名称',   `parent` bigint COMMENT '上级部门ID',   `level` bigint COMMENT '层级',   `id_1` bigint COMMENT '一级部门ID',   `name_1` varchar(50) COMMENT '一级部门名',   `id_2` bigint COMMENT '二级部门ID',   `name_2` varchar(50) COMMENT '二级部门名',   `id_3` bigint COMMENT '三级部门ID',   `name_3` varchar(50) COMMENT '三级部门名',   `id_4` bigint COMMENT '四级部门ID',   `name_4` varchar(50) COMMENT '四级部门名',   `id_5` bigint COMMENT '五级部门ID',   `name_5` varchar(50) COMMENT '五级部门名',   `creator` bigint COMMENT '创建人ID',   `created_date` datetime COMMENT '创建时间',   `dw_start_date` date COMMENT '生效日期',   `dw_end_date` date COMMENT '失效日期') ENGINE=OLAPUNIQUE KEY(dt,`id`)COMMENT "部门拉链维表"DISTRIBUTED BY HASH(`dt`) BUCKETS 1PROPERTIES (        "replication_allocation" = "tag.location.default: 3",        "in_memory" = "false",        "storage_format" = "V2");

聚合模型：aggregate key模型就建rollup

在doris在dws表建立rollup实现ADS，一个需求对应一个rollup

如果业务方需要根据sku统计毛收入近一日汇总金额，可以建立一个只有 sku, gross_default_amount_1d的 Rollup：

ALTER TABLE dws_order_product_sale_statistic_1d

ADD ROLLUP rollup_sku(sku, gross_default_amount_1d);

doris建表和导数注意事项： 1. doris中建表的名称和hive中表的名称应一一对应，方便识别 2. doris中表列的数量，应该和hive中表的列的数量对应 3. doris中表需要使用唯一或聚合模型，主键由表的粒度决定

第二步：doris导数，以下方法(建议使用)用datax从hdfs同步拉数据，稳定但是增量数据不方便冗余拉取建hive外部表方式，方便使用SQL冗余拉取增量数据, 但是大宽表容易报错，待进一步验证建hive外部表，用于拉取hive数仓对应表的数据

-- 创建外部表：Hive集群中hive_db 下的 hive_table表CREATE EXTERNAL TABLE dim.dim_department_l (  `id` bigint COMMENT '部门ID',   `name`  varchar(50) COMMENT '部门名称',   `parent` bigint COMMENT '上级部门ID',   `level` bigint COMMENT '层级',   `id_1` bigint COMMENT '一级部门ID',   `name_1` varchar(50) COMMENT '一级部门名',   `id_2` bigint COMMENT '二级部门ID',   `name_2` varchar(50) COMMENT '二级部门名',   `id_3` bigint COMMENT '三级部门ID',   `name_3` varchar(50) COMMENT '三级部门名',   `id_4` bigint COMMENT '四级部门ID',   `name_4` varchar(50) COMMENT '四级部门名',   `id_5` bigint COMMENT '五级部门ID',   `name_5` varchar(50) COMMENT '五级部门名',   `creator` bigint COMMENT '创建人ID',   `created_date` datetime COMMENT '创建时间',   `dw_start_date` date COMMENT '生效日期',   `dw_end_date` date COMMENT '失效日期',  `dt` date COMMENT 'hive分区字段') ENGINE=HIVECOMMENT "部门拉链维表"PROPERTIES (        'hive.metastore.uris' = 'thrift://ip:9083',        'database' = 'dim',        'table' = 'dim_department_l',        'hadoop.username' = 'hadoop',        'dfs.nameservices'='tTCluster',        'dfs.ha.namenodes.tTCluster'='hn01,hn02,hn03',        'dfs.namenode.rpc-address.tCluster.hn01'='ip:8020',        'dfs.namenode.rpc-address.tCluster.hn02'='ip:8020',        'dfs.namenode.rpc-address.tCluster.hn03'='ip:8020',        'dfs.client.failover.proxy.provider.tCluster'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider');SELECT * from dim.dim_department_l;

参数说明：外表列列名要于 Hive 表一一对应,列的顺序需要与 Hive 表一致,必须包含 Hive 表中的全部列,Hive 表分区列无需指定，与普通列一样定义即可。ENGINE 需要指定为 HIVEPROPERTIES 属性：

hive.metastore.uris：Hive Metastore 服务地址

database：挂载 Hive 对应的数据库名

table：挂载 Hive 对应的表名

hadoop.username: 访问hdfs用户名,当认证为simple时需要

dfs.nameservices：name service名称，与hdfs-site.xml保持一致

dfs.ha.namenodes.[nameservice ID]：namenode的id列表,与hdfs-site.xml保持一致

dfs.namenode.rpc-address.[nameservice ID].[name node ID]：Name node的rpc地址，数量与namenode数量相同，与hdfs-site.xml保持一致

dfs.client.failover.proxy.provider.[nameservice ID] ：HDFS客户端连接活跃namenode的java类，通常是"org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"

访问开启kerberos的Hive数据源，需要为Hive外表额外配置如下 PROPERTIES 属性：

hadoop.security.authentication：认证方式请设置为 kerberos，默认为simple

dfs.namenode.kerberos.principal：HDFS namenode 服务的Kerberos 主体

hadoop.kerberos.principal：设置 Doris 连接 HDFS 时使用的 Kerberos 主体

hadoop.kerberos.keytab：设置 keytab 本地文件路径

AWS_ACCESS_KEY: AWS账户的access key id.

AWS_SECRET_KEY: AWS账户的secret access key.

AWS_ENDPOINT: S3 endpoint. 例如：s3.us-east-1.amazonaws.com

AWS_REGION: AWS区域. 例如：us-east-1

注意：

若要使 Doris 访问开启kerberos认证方式的hadoop集群，需要在 Doris 集群所有运行节点上部署 Kerberos 客户端 kinit，并配置 krb5.conf，填写KDC 服务信息等。PROPERTIES 属性 hadoop.kerberos.keytab 的值需要指定 keytab 本地文件的绝对路径，并允许 Doris 进程访问该本地文件。关于HDFS集群的配置可以写入hdfs-site.xml文件中，该配置文件在fe和be的conf目录下，用户创建Hive表时，不需要再填写HDFS集群配置的相关信息。

注意：

Hive 表 Schema 变更不会自动同步，需要在 Doris 中重建 Hive 外表。当前 Hive 的存储格式仅支持 Text，Parquet 和 ORC 类型当前默认支持的 Hive 版本为 2.3.7、3.1.2，未在其他版本进行测试。后续后支持更多版本。

查询用法：

完成在 Doris 中建立 Hive 外表后，除了无法使用 Doris 中的数据模型(rollup、预聚合、物化视图等)外，与普通的 Doris OLAP 表并无区别

2.2 doris表使用SQL导数

INSERT INTO dim.dim_department_l(  `dt`,  `id`,   `name`,   `parent`,   `level`,   `id_1`,   `name_1`,   `id_2`,   `name_2`,   `id_3`,   `name_3`,   `id_4`,   `name_4`,   `id_5`,   `name_5`,   `creator`,   `created_date`,   `dw_start_date`,   `dw_end_date`)SELECT  `dt`,  `id`,   `name`,   `parent`,   `level`,   `id_1`,   `name_1`,   `id_2`,   `name_2`,   `id_3`,   `name_3`,   `id_4`,   `name_4`,   `id_5`,   `name_5`,   `creator`,   `created_date`,   `dw_start_date`,   `dw_end_date`FROM dim.hive_dim_department_l;

(建议使用)Broker Load异步的导入方式

适用场景源数据在 Broker 可以访问的存储系统中，如 HDFS。数据量在 几十到百GB 级别。

映射：定义源数据中的列。如果定义的列名和表中的列相同，则直接映射为表中的列。如果不同，则这个被定义的列可以用于之后的转换操作。

LOAD LABEL dim.dim_hr_staff_basic_l_2023_05_23(    DATA INFILE("hdfs://tTCluster/data/hadoop/hive/warehouse/dim.db/dim_hr_staff_basic_l/dt=*/*")    INTO TABLE dim_hr_staff_basic_l    COLUMNS TERMINATED BY "\t"    FORMAT AS "orc"        (id,   job_number,   name,   name_en,   gender,   staff_level,   working_place,   referee,   position_status,   entry_date,   formal_date,   contract_start_date,   contract_end_date,   residence_city,   residence_province,   date_of_birth,   marital_status,   physical_status,   education,   department_id,   dw_start_date,   dw_end_date)    COLUMNS FROM PATH AS (dt)   --    SET --    (dt=dt,--   id=id, --   job_number=job_number, --   name=name, --   name_en=name_en, --   gender=gender, --   staff_level=staff_level, --   working_place=working_place, --   referee=referee, --   position_status=position_status, --   entry_date=entry_date, --   formal_date=formal_date, --   contract_start_date=contract_start_date, --   contract_end_date=contract_end_date, --   residence_city=residence_city, --   residence_province=residence_province, --   date_of_birth=date_of_birth, --   marital_status=marital_status, --   physical_status=physical_status, --   education=education, --   department_id=department_id, --   dw_start_date=dw_start_date, --   dw_end_date=dw_end_date))WITH BROKER MY_BROKER     (       "username" = "hadoop",       "dfs.namenode.rpc-address.tCluster.hn01"  =  "ip:8020",        "dfs.ha.namenodes.tCluster"  =  "hn01,hn02,hn03",        "dfs.namenode.rpc-address.tCluster.hn03"  =  "ip:8020",        "dfs.namenode.rpc-address.tCluster.hn02"  =  "ip:8020",        "hadoop.security.authentication"  =  "simple",        "dfs.client.failover.proxy.provider.tCluster"  =  "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider",        "hive.metastore.uris"  =  "thrift://ip:9083",        "dfs.nameservices"  =  "tCluster"    )PROPERTIES(    "timeout"="1200");

查看导入状态

SHOW LOAD order by createtime desc;

1s完成导入

指标规范原子指标

派生指标

派生指标由原子指标、时间周期修饰词、若干其他修饰词组合得到。 {修饰词}_{原子指标}_{时间周期}。 时间都放在后面； 指标放中间， 开头有个修饰词，

比如 订单_毛利润_ 30d: order_profit_30d, 一看就知道 订单计算出来的30天毛利润

原子指标、修饰词，直接归属在业务过程下。派生指标可以选择多个修饰词，修饰词之间的关系为‘或’或者‘且’的关系，具体由具体的派生指标语义决定。派生指标唯一归属一个原子指标，继承原子指标的数据域、与修饰词的数据域无关。 一般而言：事务型指标和存量型指标只会唯一定位到一个业务过程，如果遇到同时有两个行为发生、需要多个修饰、生成一个派生指标的话，选择时间靠后的行为创建原子指标，另一个时间靠前的行为创建为修饰词。原子指标有确定的英文字段名、数据类型和算法说明；派生指标要继承原子指标的英文名、数据类型和算法要求。衍生指标

衍生指标在ads层算。dws可以保留计算这些指标的所有字段（原子指标和派生指标）。

公共规则所有单词小写单词之间下划线分割（反例：appName 或 AppName）可读性优于长度 (词根，避免出现同一个指标，命名一致性)禁止使用 sql 关键字，如字段名与关键字冲突时 +col数量字段后缀 _qty等标识...金额字段后缀 _price 标识比率后缀_rate天分区使用字段 dt，格式统一（yyyy-mm-dd）小时分区使用字段 hh，范围（00-23）分钟分区使用字段 mi，范围（00-59）布尔类型标识：is_{业务}，不允许出现空值实施流程