仓库表分层规范
命名规则
表的命名需要遵循基本原则:层次_表名_所属项目,如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上注册
脚本开发规范
重刷或初始化历史数据规范数据同步通常采用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