《最强最全面的数仓建设规范指南.docx》由会员分享，可在线阅读，更多相关《最强最全面的数仓建设规范指南.docx（29页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、最强最全面的数仓建设规范指南（纯干货建议收藏）数仓宝贝库2021-11-24 18:18以下文章来源于五分钟学大数据，作者园陌* 、五分神学大改据LEARN BIG DATA IN FIVE MINUTES五分钟学大数据.4.id 类：biginto5.时间：string。6.状态:string3 .数据冗余规范宽表的冗余字段要确保：1.冗余字段要使用高频，下游3个或以上使用。2.冗余字段引入不应造本钱身数据产生过多的延后。3.冗余字段和已有字段的重复率不应过大，原那么上不应超过60% ,如需要可以选择join或原表拓展。4. . NULL字段处理规范对于维度字段,需设置为-1对于指标字段，需
2、设置为0.指标口径规范保证主题域内，指标口径一致，无歧义。通过数据分层，提供统一的数据出口，统一对外输出的数据口径，防止同一指标 不同口径的情况发生。1）指标梳理指标口径的不一致使得数据使用的本钱极高，经常出现口径打架、反复核对数据 的问题。在数据治理中，我们将需求梳理到的所有指标进行进一步梳理，明确其 口径，如果存在两个指标名称相同，但口径不一致，先判断是否是进行合并，如 需要同时存在，那么在命名上必须能够区分开。2）指标管理指标管理分为原子指标维护和派生指标维护。原子指标： 选择原子指标的归属产线、业务板块、数据域、业务过程选择原子指标的统计数据来源于该业务过程下的原始数据源 录入原子指标
3、的英文名称、中文名称、概述填写指标函数 系统根据指标函数自动生成原子指标的定义表达式系统根据指标定义表达式以及数据源表生成原子指标SQL派生指标： 在原子指标的基础之上选择了一些维度或者修饰限定词。6.数据表处理规范1）增量表新增数据，增量数据是上次导出之后的新数据。1.记录每次增加的量，而不是总量；2.增量表，只报变化量，无变化不用报；3.每天一个分区。4.2）全量表每天的所有的最新状态的数据。1.全量表，有无变化，都要报；2.每次上报的数据都是所有的数据（变化的+没有变化的）；3.只有一个分区。4.3）快照表按日分区，记录截止数据日期的全量数据。1.快照表，有无变化，都要报;2.每次上报的
4、数据都是所有的数据（变化的+没有变化的）；3.一天一个分区。4.4）拉链表记录截止数据日期的全量数据。1.记录一个事物从开始，一直到当前状态的所有变化的信息；2.拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总 量；3.当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）；4.只有一个分区。5. 7.表的生命周期管理这局部主要是要通过对历史数据的等级划分与对表类型的划分生成相应的生命周 期管理矩阵。1）历史数据等级划分主要将历史数据划分PO、Pk P2、P3四个等级，其具体定义如下:PO :非常重要的主题域数据和非常重要的应用数据，具有不可恢复性，如交易、 日志、集团K
5、PI数据、IPO关联表。PI :重要的业务数据和重要的应用数据，具有不可恢复性，如重要的业务产品数 据。P2 ：重要的业务数据和重要的应用数据，具有可恢复性，如交易线ETL产生的 中间过程数据。P3 :不重要的业务数据和不重要的应用数据，具有可恢复性，如某些SNS产品 报表。2）表类型划分1.事件型流水表（增量表）事件型流水表（增量表）指数据无重复或者无主键数据，如日志。1.事件型镜像表（增量表）事件型镜像表（增量表）指业务过程性数据，有主键，但是对于同样主键的属性 会发生缓慢变化，如交易、订单状态与时间会根据业务发生变更。1.维表维表包括维度与维度属性数据，如用户表、商品表。1.Merge全
6、量表Merge全量表包括业务过程性数据或者维表数据。由于数据本身有新增的或者发生状态变更，对于同样主键的数据可能会保存多份，因此可以对这些数据根据主 键进行Merge操作，主键对应的属性只会保存最新状态，历史状态保存在前一 天分区中。例如，用户表、交易表等都可以进行Merge操作。1. ETL临时表ETL临时表是指ETL处理过程中产生的临时表数据，一般不建议保存，最多7 天。1. TT临时数据TT拉取的数据和DbSync产生的临时数据最终会流转到DS层，ODS层数据 作为原始数据保存下来，从而使得TT&DbSync上游数据成为临时数据。这类数 据不建议保存很长时间，生命周期默认设置为93天，可
7、以根据实际情况适当减 少保存天数。7.普通全量表很多小业务数据或者产品数据，BI一般是直接全量拉取，这种方式效率快，对存 储压力也不是很大，而且表保存很长时间，可以根据历史数据等级确定保存策略。 通过上述历史数据等级划分与表类型划分，生成相应的生命周期管理矩阵，如下 表所示：三、数仓各层开发规范1. ODS层设计规范同步规范：1.一个系统源表只允许同步一次；2.全量初始化同步和增量同步处理逻辑要清晰;3.以统计日期和时间进行分区存储；4.目标表字段在源表不存在时要自动填充处理。5.表分类与生命周期：1.ods流水全量表：不可再生的永久保存；日志可按留存要求；按需设置保存特殊日期数据；按需设置保
8、存特殊月份数据；1.ods镜像型全量表:推荐按天存储；对历史变化进行保存；最新数据存储在最大分区;历史数据按需保存；Ods增量数据：推荐按天存储； 有对应全量表的，建议只保存14天数据；无对应全量表的，永久保存；1. ods的etl过程中的临时表：推荐按需保存；最多保存7天；建议用完即删，下次使用再生成；BDSync非去重数据：通过中间层保存，默认用完即删，不建议保存。数据质量：1.全量表必须配置唯一性字段标识；1.对分区空数据进行监控；2.对枚举类型字段，进行枚举值变化和分布监控；3.ods表数据量级和记录数做环比监控；4.ods全表都必须要有注释；2.公共维度层设计规范1）设计准那么1.
9、一致性共维度在不同的物理表中的字段名称、数据类型、数据内容必须保持一致（历史原因不一致，要做好版本控制）1.维度的组合与拆分组合原那么：将维度与关联性强的字段进行组合，一起查询，一起展示，两个维度必须具有天然的关系，如：商品的基本属性和所属品牌。无相关性：如一些使用频率较小的杂项维度，可以构建一个集合杂项维度的特殊属性。行为维度：经过计算的度量，但下游当维度处理，例：点击量0-1000,100-1000等，可以做聚合分类。 拆分与冗余：针对重要性，业务相关性、源、使用频率等可分为核心表、扩展表。数据记录较大的维度，可以适当冗余一些子集。2）存储及生命周期管理建议按天分区。1. 3个月内最大访问
10、跨度=4天时，建议保存最近7天分区;3个月内最大访问跨度 = 12天时，建议保存最近15天分区；2. 3个月内最大访问跨度 二 30天时，建议保存最近33天分区；3个月内最大访问跨度二90天时，建议保存最近120天分区；3. 3个月内最大访问跨度 二 180天时，建议保存最近240天分区;3个月内最大访问跨度 二 300天时，建议保存最近400天分区;3. DWD明细层设计规范1）存储及生命周期管理建议按天分区。1. 3个月内最大访问跨度=4天时，建议保存最近7天分区;3个月内最大访问跨度 = 12天时，建议保存最近15天分区；2. 3个月内最大访问跨度 二 30天时，建议保存最近33天分区；
11、3个月内最大访问跨度=90天时，建议保存最近120天分区；大数据领域原创技术号，专注于大数据研究，包括Hadoop. Flink. Spark. Kafka.Hive、HBase等，深入大数据技术原理，数据仓库，数据治理，前沿大数据技术点击上方蓝字关注我们I点击上方蓝字关注我们I数仓宝贝库，带你学数据!导读：本文将全面讲解数仓建设规范，从数据模型规范，到数仓公共规范，数仓各层规范，最后到数仓命名规范，包括表命名，指标字段命名规范等！一、数据模型架构原那么数仓分层原那么1. 主题域划分原那么数据模型设计原那么二、数仓公共开发规范层次调用规范1. 数据类型规范数据冗余规范2. NULL字段处理规范
12、指标口径规范3. 数据表处理规范5.5.3个月内最大访问跨度 二 180天时，建议保存最近240天分区;6.3个月内最大访问跨度 二 300天时，建议保存最近400天分区;2）事务型事实表设计准那么基于数据应用需求的分析设计事务型事实表，结合下游较大的针对某个业务过程 和分析指标需求，可考虑基于某个事件过程构建事务型实时表；一般选用事件的发生日期或时间作为分区字段，便于扫描和裁剪；冗余子集原那么，有利于降低后续10开销；明细层事实表维度退化，减少后续使用join本钱。3）周期快照事实表周期快照事实表中的每行汇总了发生在某一标准周期，如某一天、某周、某月的 多个度量事件。粒度是周期性的，不是个体
13、的事务。通常包含许多事实，因为任何与事实表粒度一致的度量事件都是被允许的。4）累积快照事实表多个业务过程联合分析而构建的事实表，如采购单的流转环节。用于分析事件时间和时间之间的间隔周期。少量的且当前事务型不支持的，如关闭、发货等相关的统计。4. DWS公共汇总层设计规范数据仓库的性能是数据仓库建设是否成功的重要标准之一。聚集主要是通过汇总 明细粒度数据来获得改进查询性能的效果。通过访问聚集数据，可以减少数据库 在响应查询时必须执行的工作量，能够快速响应用户的查询，同时有利于减少不 同用访问明细数据带来的结果不一致问题。1）聚集的基本原那么一致性。聚集表必须提供与查询明细粒度数据一致的查询结果。
14、防止单一表设计。不要在同一个表中存储不同层次的聚集数据。聚集粒度可不同。聚集并不需要保持与原始明细粒度数据一样的粒度，聚集只关 心所需要查询的维度。2）聚集的基本步骤第一步：确定聚集维度在原始明细模型中会存在多个描述事实的维度，如日期、商品类别、卖家等，这 时候需要确定根据什么维度聚集，如果只关心商品的交易额情况，那么就可以根 据商品维度聚集数据。第二步：确定一致性上钻这时候要关心是按月汇总还是按天汇总，是按照商品汇总还是按照类目汇总，如 果按照类目汇总，还需要关心是按照大类汇总还是小类汇总。当然，我们要做的 只是了解用户需要什么，然后按照他们想要的进行聚集。第三步：确定聚集事实在原始明细模型
15、中可能会有多个事实的度量，比方在交易中有交易额、交易数量 等，这时候要明确是按照交易额汇总还是按照成交数量汇总。3）公共汇总层设计原那么除了聚集基本的原那么外，公共汇总层还必须遵循以下原那么：数据公用性。汇总的聚集会有第三者使用吗？基于某个维度的聚集是不是经常用 于数据分析中？如果答案是肯定的，那么就有必要把明细数据经过汇总沉淀到聚 集表中。不跨数据域。数据域是在较高层次上对数据进行分类聚集的抽象。如以业务区分统计周期。在表的命名上要能说明数据的统计周期，如表示最近1天，td表示截至当天，一nd表示最近N天。四、数仓命名规范1.词根设计规范词根属于数仓建设中的规范，属于元数据管理的范畴，现在把
16、这个划到数据治理 的一局部。完整的数仓建设是包含数据治理的，只是现在谈到数仓偏向于数据建 模，而谈到数据治理，更多的是关于数据规范、数据管理。表命名，其实在很大程度上是对元数据描述的一种表达，表命名规范越完善，我 们 能从表名获取到的信息就越多。比方：一局部业务是关于货架的，英文名是：rack , rack就是一个词根，那我们就在所有的表、字段等用到的地方都叫rack ,不要 叫成 别的什么。这就是词根的作用，用来统一命名，表达同一个含义。指标体系中有很多率的指标，都可以拆解成XXX+率，率可以叫rate ,那我 们所有的指标都叫做XXX + rateo词根：可以用来统一表名、字段名、主题域名
17、等等。举例：以流程图的方式来展示，更加直观和易懂，本图侧重dwm层表的命名 规 范，其余命名是类似的道理：第一个判断条件是该表的用途，是中间表、原始日志还是业务展示用的表如果该 表被判断为中间表，就会走入下一个判断条件：表是否有group操作通过是否 有group操作来判断该表该划分在dwd层还是dwm和dws层如果不是 dwd层，那么需要判断该表是否是多个行为的汇总表（即宽表）最后再分别填上 事业群、部门、业务线、自定义名称和更新频率等信息即可。分层：表的使用范围事业群和部门：生产该表或者该数据的团队业务线：说明该数据是哪个产品或者业务线相关主题域：分析问题的角度，对象实体自定义：一般会尽可
18、能多描述该表的信息，比方活跃表、留存表等更新周期：比方说天级还是月级更新数仓表的命名规范如下：1 .数仓层次：公用维度：dimDM 层:dmODS 层:odsDWD 层:dwdDWS 层:dws2 .周期/数据范围:日快照：d增量:i全量：f周：w拉链表:I非分区全量表：a.表命名规范1）常规表 常规表是我们需要固化的表，是正式使用的表，是目前一段时间内需要去维护去 完善的表。规范：分层前缀dwd I dws I ads_部门一业务域_主题域_XXX_更新周期I数据范围 业务域、主题域我们都可以用词根的方式枚举清楚，不断完善。更新周期主要的是时间粒度、日、月、年、周等。2）中间表中间表一般出现
19、在Job中，是Job中临时存储的中间数据的表，中间表的作用 域只限于当前Job执行过程中Job 一旦执行完成，该中间表的使命就完成了 , 是可以删除的（按照自己公司的场景自由选择，以前公司会保存几天 的中间表数 据，用来排查问题）。规范：mid_table_name_09 | dimtable.name是我们任务中目标表的名字，通常来说一个任务只有一个目标表。这里加上表名，是为了防止自由发挥的时候表名冲突，而末尾大家可以选择自由 发挥，起一些有意义的名字，或者简单粗暴，使用数字代替，各有优劣吧，谨慎 选择。通常会遇到需要补全维度的表，这里使用dim结尾。如果要保存历史的中间表，可以加上日期或者
20、时间戳。3）临时表临时表是临时测试的表，是临时使用一次的表，就是暂时保存下数据看看，后续 一般不再使用的表，是可以随时删除的表。规范：tmp_xxx只要加上tmp开头即可，其他名字随意，注意tmp开头的表不要用来实际使 用，只是测试验证而已。4）维度表维度表是基于底层数据，抽象出来的描述类的表。维度表可以自动从底层表抽象 出来，也可以手工来维护。规范：dim_xxx维度表，统一以dim开头，后面加上，对该指标的描述。5）手工表手工表是手工维护的表，手工初始化一次之后，一般不会自动改变，后面变更， 也是手工来维护。一般来说，手工的数据粒度是偏细的，所以暂时统一放在dwd层，后面如果有目标值或者其
21、他类型手工数据，再根据实际情况分层。规范：dwd_业务域_manual_xxx手工表，增加特殊的主题域，manual ,表示手工维护表。2 .指标命名规范1）公共规那么所有单词小写单词之间下划线分割（反例：叩pName或AppName ）可读性优于长度（词根，防止出现同一个指标，命名一致性）禁止使用sql关键字，如字段名与关键字冲突时+col数量字段后缀_cnt等标识金额字段后缀_price标识天分区使用字段dt ,格式统一（yyyymmdd或yyyy-mm-dd ）小时分区使用字段hh ,范围（00-23 ）分钟分区使用字段mi ,范围（00-59 ）布尔类型标识：is_业务,不允许出现空值
22、2）指标命名规范结合指标的特性以及词根管理规范，将指标进行结构化处理。1.基础指标词根，即所有指标必须包含以下基础词根：基础指标词根英文全称MySQL数据类型countBigintBigint金额类amoutDecimalDecimal比率/占比ratioDecimalDecimal1.2.业务修饰词，用于描述业务场景的词汇，例如trade-交易。日期修饰词，用于修饰业务发生的时间区间。日期类型日周全称dailyweekly4.聚合修饰词，对结果进行聚集操作。聚合类型平均C3累计averagewtd词根avgwtd5 .基础指标，单一的业务修饰词+基础指标词根构建基础指标，例如：交易金额-tr
23、ade_amto6 .派生指标，多修饰词+基础指标词根构建派生指标。派生指标继承基础指标的特性，例如:安装门店数量-install_poi_cnt。7 .普通指标命名规范，与字段命名规范一致，由词汇转换即可以。1.交易金额词汇转换词汇转换tradeamt参考本文档规范依据来源参考：大数据之路：阿里巴巴大数据实践7. 表的生命周期管理三.数仓各层开发规范ODS层设计规范1. 公共维度层设计规范DWD明细层设计规范2. DWS公共汇总层设计规范!1!1!数仓命名规范词根设计规范1. 表命名规范指标命名规范一、数据模型架构原那么1.数仓分层原那么优秀可靠的数仓体系，往往需要清晰的数据分层结构，即要保
24、证数据层的稳定又 要屏蔽对下游的影响，并且要防止链路过长。那么问题来了，一直在讲数仓要分 层，那数仓分几层最好？目前市场上主流的分层方式眼花缭乱，不过看事情不能只看外表，还要看到内在 的规律，不能为了分层而分层，没有最好的，只有适合的。分层是以解决当前业务快速的数据支撑为目的，为未来抽象出共性的框架并能够 赋能给其他业务线，同时为业务开展提供稳定、准确的数据支撑，并能够按照已 有的模型为新业务开展提供方向，也就是数据驱动和赋能。一个好的分层架构，要有以下好处：2. 数仓工具箱：维度建模权威指南WneData建设：美团SaaS数仓建设-END1. 清晰数据结构；数据血缘追踪；2. 减少重复开发；
25、数据关系条理化；3. 屏蔽原始数据的影响。数仓分层要结合公司业务进行，并且需要清晰明确各层职责，一般采用如下分层结构：数仓分层APRAPR数据应用层，面向不同部门，不同业务需求进行定制化开发，提供报表数据DM数据轻汇总层，建设通用性维度和指标，3明细数据，局部数据为汇总数据，主要培养DW数据明细层，对数据进行主题划分，分为当 实表和维度表，并对数据进行规范处理ODS数据源层，仅导入业务方数据，不做任何史理，相当于入大数据平台前的:三分;.数据分层架构数仓建模在哪层建设呢？我们以维度建模为例，建模是在数据源层的下一层进行建设，在上图中，就是在DW层进行数仓建模，所以DW层是数仓建设的核心层。下面
26、详细阐述下每层建设规范，和上图的分层稍微有些区别：1.数据源层：ODS (Operational Data Store)ODS层，是最接近数据源中数据的一层，为了考虑后续可能需要追溯数据问题， 因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可, 至于数据的去噪、去重、异常值处理等过程可以放在后面的DWD层来做。2 .数据仓库层：DW (Data Warehouse)数据仓库层是我们在做数据仓库时要核心设计的一层，在这里，从ODS层中获 得的数据按照主题建立各种数据模型。DW 层又细分为 DWD( Data Warehouse Detail )层、DWM( Data War
27、ehouse Middle )层和 DWS ( Data Warehouse Servce )层。1)数据明细层：DWD (Data Warehouse Detail)该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。DWD层要做的就是将数据清理、整合,规范化.脏数据、垃圾数据,规范不一致的、状态定义不一致的、命名不规范的数据都会被处理。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度i化至事实表中，减少事实表和维表的关联。另外，在该层也会做一局部的数据聚合，将相同主题的数据汇集到一张表中，提 高数据的可用性。2)数据中间层：DWM (Data WareHo
28、use Middle)该层会在DWD层的数据基础上，数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的复用性，减少重复加工。直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在 计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多 个小的中间表，然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定， 也可以去掉DWM这一层，只留DWS层，将所有的数据再放在DWS亦可。3)数据服务层：DWS (Data WareHouse Servce)DWS层为公共汇总层，会进行轻度汇总，粒度比明细数据稍粗
29、，基于DWD层 上的基础数据，整合汇总成分析某一个主题域的服务数据，一般是宽表。DWS层 应覆盖80%的应用场景。又称数据集市或宽表。按照业务划分，如主题域流量、订单、用户等，生成字段比拟多的宽表，用于提 供后续的业务查询，OLAP分析，数据分发等。一般来讲，该层的数据表会相比照拟少，一张表会涵盖比拟多的业务内容，由于 其字段较多，因此一般也会称该层的表为宽表。3 .数据应用层：APP (Application)在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、 PostgreSqk Redis等系统中供线上系统使用，也可能会存在Hive或者Druid 中供数据分析和数据挖掘
30、使用。比方我们经常说的报表数据，一般就放在这里。4 .维表层(Dimension)如果维表过多，也可针对维表设计单独一层，维表层主要包含两局部数据：高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。低基数维度数据：一般是配置表，比方枚举值对应的中文含义，或者日期维表。 数据量可能是个位数或者几千几万。2 .主题域划分原那么1）按照业务或业务过程划分业务容易理解，就是指的功能模块/业务线。业务过程：指企业的业务活动事件，如下单、支付、退款都是业务过程。不过需 要注意的是，一个业务过程是一个不可拆分的行为事件，通俗的讲，业务过程就 是企业活动中的事件。2）按
31、照数据域划分数据域是指面向业务分析，将业务过程或者维度进行抽象的集合。其中，业务过 程可以概括为一个个不可拆分的行为事件，在业务过程下，可以定义指标，维度 是指度量的环境，如买家下单事件，买家是维度。为保障整个体系的生命力，数 据域是需要抽象提炼，并且长期维护和更新的，但不轻易变动。在划分数据域时， 既能涵盖当前所有的业务需求，又能在新业务进入时无影响地被包含进已有的数 据域中和扩展新的数据域。3 .数据模型设计原那么1）高内聚、低耦合即主题内部高内聚、不同主题间低耦合。明细层按照业务过程划分主题，汇总层按照实体+活动划分不同分析主题，应用层根据应用需求划分不同应用主题。2）核心模型和扩展模型
32、要别离建立核心模型与扩展模型体系，核心模型包括的字段支持常用的核心业务，扩展 模型包括的字段支持个性化或少量应用的需要，不能让扩展模型的字段过度侵入 核心模型，以免破坏核心模型的架构简洁性与可维护性。3）公共处理逻辑下沉及单一越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现，不要让 公用的处理逻辑暴露给应用实现，不要让公共逻辑多处同时存在。4）本钱与性能平衡适当的数据冗余可换取查询和刷新性能，不宜过度冗余与数据复制。5）数据可回滚处理逻辑不变，在不同时间屡次运行数据结果确定不变。二、数仓公共开发规范.层次调用规范稳定业务按照标准的数据流向进行开发，即ODS - DWD - DWS
33、 - APPO 非稳定业务或探索性需求，可以遵循ODS- DWD- APP或者ODS- DWD - DWM -APP两个模型数据流。在保障了数据链路的合理性之后，也必须保证模型分层引用原那么：正常流向：ODS - DWD - DWM - DWS - APP ,当出现 ODS - DWD - DWS - APP这种关系时，说明主题域未覆盖全。应将DWD数据落到DWM 中，对于使用频度非常低的表允许DWD - DWSO尽量防止出现DWS宽表中使用DWD又使用(该DWD所归属主题域)DWM 的表。同一主题域内对于DWM生成DWM的表，原那么上要尽量防止，否那么会影响 ETL的效率。DWM、DWS和APP中禁止直接使用ODS的表，ODS的表只能被DWD 引用。禁止出现反向依赖，例如DWM的表依赖DWS的表。举例：1 .数据类型规范需统一规定不同的数据的数据类型，严格按照规定的数据类型执行：1.金额：double或 使用decimal(28,6)控制精度等，明确单位是分还是元。2.字符串：string。