数据仓库开发规范

该系列包含三篇:

仓库表分层规范

分层名称含义与规则
odl日志数据层所有外部来源的数据都入到odl层
bdl基础数据层数据清洗后的规范化数据
idl接口数据层初步聚合统计表、主题宽表,必须有主键
adl应用数据层指标统计表,高度聚合统计表,必须有主键
dim字典维度层维度表,如果判断一个表为维度表的话,必须有主键
stage中间过程表复杂逻辑的中间表,命名规则stage1_bdl_user_uc、stage2_bdl_user_uc、…
tmp临时层临时表
sync同步层用于数据同步的表(有的表不适合直接同步,需要进一步加工处理后而生成的表)

命名规则

  • 表的命名需要遵循基本原则:层次_表名_所属项目,如bdl_order_eb;
  • 临时表的命名可考虑带上创建人姓名缩写信息:tmp_xxx_zhangsan、tmp_xxx_lisi、…
  • idl层宽表基本都是基于一张原表扩展出来的,因此表名以原表名+ext作为通用规范:如idl_pay_ext_class
  • 表重构与原表不兼容无法立即替换的,以原表名+v+版本号作为通用规范:如bdl_event_assign_v2_crm
  • 含有判新逻辑的表,建议使用idl_new_install开头来标识这类表
  • 对应每日全量分区表,表名需要以idl_all开头来标识这类表
  • 特征表的的表,表名需要以idl_fe开头来标识这类表
  • 针对具有每日结算的表,表名需要包含daily这类关键词来标识
  • 针对具有取最近逻辑的表,表名需要包含last关键字来标识
  • 具有拉链属性的表,表名中需要包含zipper关键字来标识这类表
  • 针对历史快照表,表名中需要包含history或者his来标识这类表

字段规范

  • 字段名小写下划线,来源于业务表的尽量保留原风格;
  • 字段类型要统一,不要混用,比如userid统一用bigint类型;

分区规范

  • 对于日期分区统一用ds作为分区字段,如果有多分区,日期分区放在最后一个;
  • 合理使用分区,避免分区数过多,对于日期分区的全量表,需定期清理分区,原则上不保留超过90天的分区
  • 宽表除日期分区ds外无其他类型分区;全量宽表原则上不提供分区字段,如有必要,可考虑另写一份his表,保留一定日期的分区数据。如 idl_xxx_eb、idl_xxx_history_eb、idl_xxx_his_eb

数据抽取规范

1、业务数据抽取

  • 统一使用同步系统工具同步数据到odl、dim层表,表字段的格式与命名保持一致。
  • 维护底层表元数据,同步系统会自动附带原表注释,如果没有注释需要给odl表增加字段和表的注释信息
  • 日志类型的表(记录添加后不再更新),必须增加ds日期分区
  • 动态变化的表,统一采用全量同步的方式。对于超大型数据表,需要经过数据开发组内分析协商后统一处理。

2、日志数据抽取

  • 使用rsync2hive命令同步。
  • 所有日志表都需要指定为外部表,防止误删表导致的原始数据丢失。

Hive SQL编写规范

  • 所有的SQL都需要排版对称,添加必要的注释。
  • 所有join必须添加on条件,即使是某些特殊场景下的笛卡儿积。
  • 原则上,不能使用变量拼接SQL的核心逻辑,这会增加SQL阅读难度。
  • 一个脚本原则上只能写一个表(多阶段处理的中间表,需要命名为stage层的表)
  • 不要使用count(*),需要使用count(1)
  • SQL中如果表有别名,字段上一定要把表别名带上
  • 表的别名不能重复
  • 少用空字符串,尽量用NULL
  • 刷数据尽量使用动态分区的方式,避免一天天的重刷
  • 字段尽量不要以数字开头,部分导出场景下不支持,例如palo就不支持字段名以数字开头
  • 尽量在底层保证数据质量,乱码、特殊字符、长度超长、主键唯一等规范,避免上层再次处理
  • hive建表尽量不要用boolean(可以使用tinyint),因为boolean导出的时候会变成字符串true和false,在导出到其它类型的数据库(例如palo)时需要做特殊处理
  • 条件关联时保证类型一致,如果a(int)=b(string)保不定哪天就突然慢了
  • 同一个字段在各个表的类型和描述应该一致,字段名也应该保持一致

数据导出规范

  • 正常情况下都统一使用同步系统同步数据到关系型数据库。
  • 同步脚本一般是bash脚本,统一存放在项目的sync目录下。
  • 原则上不能出现导出结果超大表(MySQL 1000万、Palo 1亿)的场景,如有必要需要数据开发组评审后采取对应的解决办法

作业调度规范

  • 所有标准作业都需要通过Azkaban来调度管理
  • 所有的天级别以上Hive表ETL完成后,都需要创建成功标识(HDFS路径:/user/dw_etl_task/项目/日期/表名)
  • 作业名必须和脚本名保持一致(除数据导出外,脚本名还需要和hive表名保持一致)
  • 原则上一个主题域或项目对应一个Azkaban的调度project,一个project按调度逻辑拆分多个作业流。作业流命名格式:项目名_逻辑名_flow。例如:class_xxx_flow。
  • 为了保证调度逻辑的简洁性与直观性,所有作业都封装成脚本程序执行,一个作业(job)只允许执行一个命令(command),
  • 所有的调度逻辑的新增或修改,必须手工测试并通过。
  • 监控程序会每15分钟检测一次异常或者超时作业流,各作业流的维护者或者修改者需要在第一时间内解决。
  • 如果在Azkaban中kill作业,必须把对应Hadoop中执行的Job也kill掉(hadoop job -kill xxxx)
  • 部署任务调度时候错误选项(Failure Option)里必须选择Finish All Possible,以免因一个作业错误而导致整个作业流后续作业都无法执行;

数据测试规范

  • 数据开发对统计数据的准确性负责,开发流程中需要对SQL逻辑进行测试。
  • 与外部部门对接的项目,如果是走标准流程的项目,数据需要建立配套的测试表(测试表的覆盖范围视测试范围而定),对于流量/移动/订单等非常负责计算的数据暂不走测试数据。

元数据与血缘规范

  • 所有的数据表和表字段都需要有注释
  • 如果数据表有主键,需要在字段注释上加以说明,原则上idl层及以上的表都必须要有主键

接口开发规范

  • 所有接口形式都需要符合公司API规范
  • 所有数据接口统一使用APIServer
  • 所有的前后端分离接口都需要在RAP上注册

脚本开发规范

目录名称说明
odlodl同步脚本目录存放odl表加工同步脚本的目录
dimdim脚本目录存放dim表加工同步脚本的目录
bdlbdl脚本目录存放bdl表加工脚本的目录
idlidl脚本目录存放idl表加工脚本的目录
adladl脚本目录存放adl表加工脚本的目录
sync同步导出脚本目录存放仓库表同步导出脚本的目录
archive脚本归档目录用来存放已下线的归档脚本
  • 数据同步通常采用shell脚本,部分复杂同步逻辑采用python脚本;
  • 所有脚本都要有创建成功标识的逻辑,以便处理跨项目依赖,除了最后一步同步结果表的脚本;
  • HSQL需要注意排版和注释;
  • 脚本参数化,对于所使用表名和日期等参数需采用变量或者可传递参数方式;
  • ETL脚本原则上需要实现幂等性(同一个脚本同样的参数,多次执行的结果是一致的)

重刷或初始化历史数据规范

  • 建议日常ETL SQL实现中就同时支持历史数据刷新(如时间条件为一段范围,group by时间字段),只需要修改部分参数即可快速重刷数据。
  • 如果业务SQL难以支持同时刷历史的话,则需要单独提供刷新历史脚本或者使用range_run.sh逐天刷新数据。
  • 逐天刷新数据的场景,务必需要控制好并发刷新度以及对服务器的资源占用,并及时只会其他数据开发。
  • 刷新数据务必有人值守,出现过载情况需要及时停止相关任务。
  • 严禁刷数据超过零点,零点后未完成是刷数据任务必须手动终止并kill相关集群任务
  • 设计表时要支持刷历史数据,且尽量支持批量重刷历史数据
  • 归因数据谨慎重刷,因为归因数据一般N天前的数据都会固化不会改变,一旦重刷就会造成数据不一致

模型规范

  • 基本层级关系如下:odl->bdl->idl->adl,禁止低层数据表依赖同项目高层数据表;
  • bdl尽量避免跨项目依赖其他表,可考虑放在idl层操作;
  • 原则上所有odl层表应该都有对应的bdl层表,但由于沪江很多都是结构化的业务数据,因此有些数据开发可以直接使用odl层数据表,但对于日志如CT服务器日志、utrack流量日志等均有bdl层做数据清洗,并且后续开发也禁止直接依赖odl层数据表;
  • 日常任务调度不鼓励使用宽表,尽量减少表的依赖深度,以提高调度效率;
  • 当项目内调度作业过多时,应根据业务分拆作业流,不同作业流之间相互依赖的表采用wait方式解决,切勿造成同一个作业在不同的作业流当中多次执行;
  • 需要注意作业之间的循环依赖问题,同项目同一个作业流内azkaban会检查循环依赖,但是跨项目跨作业流的需要开发自己注意;
  • 修改表结构增加字段的,需要在最后追加字段,不能在中间插入字段;
  • Hive表结构修改以后,需要刷新历史分区的元数据信息,执行/home/hadoop/bin/update_schema +tablename 命令刷新;
  • 日志型数据一般都是不会变的,可以做成存量脚本。业务数据会经常变,所以尽可能在上层表关联业务数据,如果要在底表用,优先做成全量的,如果是增量,要考虑重刷数据不一致的问题

重要日常

  • 不是所有的表都能刷历史。特别是归因表和KPI考核表,刷历史导致的数据变化会让业务方觉得数据不靠谱
  • 很多时候,因为各种原因,重刷数据肯定会和当前数据不完全一致。所以如果能不动历史数据就不用动,如果刷数据要用改动最小的方式,时间范围最小去修正数据
  • 数据人对于发出去的数据要负责,因为很可能就会成为别人汇报时用到的的数据
  • 采集判断的字段和能否支持重刷历史数据,需要做好权衡(最好用同一个类同一个方法,否则就会面临数据不一致的问题)
  • 对数据逻辑严谨是数据人应有的素质