数据仓库的原理（数据仓库开发规范）

• 表的命名需要遵循基本原则:层次_表名_所属项目,如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命令同步。

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

• 所有的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上注册

• 重刷或初始化历史数据规范数据同步通常采用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考核表,刷历史导致的数据变化会让业务方觉得数据不靠谱

• 很多时候,因为各种原因,重刷数据肯定会和当前数据不完全一致。所以如果能不动历史数据就不用动,如果刷数据要用改动最小的方式,时间范围最小去修正数据

• 数据人对于发出去的数据要负责,因为很可能就会成为别人汇报时用到的的数据

• 采集判断的字段和能否支持重刷历史数据,需要做好权衡(最好用同一个类同一个方法,否则就会面临数据不一致的问题)

• 对数据逻辑严谨是数据人应有的素质

本文作者:CIO之家的朋友 来源:CIO之家的朋友们

CIO之家 www.ciozj.com imciow