数据仓库数据管理规范

一般使用传统的关系型数据库，如Oracle、Postgresql等。传统数据库一般部署在单机上，扩展能力差。传统数据库在较少的数据量（百GB）的情况下，处理效率、查询效率相对都比较快，但是当数据量增长到PB级时，受限于单机的处理能力，传统数据库的处理效率会快速下降。这时可以在单机上升级配置或者换用大型机，但这样做会导致成本也快速上升。

Oracle是由甲骨文公司开发的一款关系型数据库管理系统，是目前市场上占有率最高的一款关系型数据库管理系统。Oracle可以执行好、使用关闭、功能强大，可靠性高，提供高级别的数据安全控制，稳定性强。但是，Oracle对硬件的要求很高，价格比较昂贵，对于维护人员的要求也比较高。

DB2是由IBM公司开发的一种关系型数据库管理系统，可运行在UNIX、Linux、Windows、z/OS上。DB2主要应用于大型应用系统，具有较好的可伸缩性，提供了高层次的数据利用性、完整性、可恢复性，采用了数据分级技术，能够使大型机数据很方便地下载到LAN数据库服务器，拥有非常完备的查询优化器，有效提升查询性能。

不过，DB2配置复杂，命名不规范，管理命令多且不规则，大大提升了运维难度及成本。

SYBASEIQ是Sybase公司推出的特别为数据仓库设计的关系型数据库。SYBASEIQ的架构与大多数关系型数据库不同，它特别的设计用以支持大量用户并发的即席查询。其设计与执行进程优先考虑查询性能，其次是完成批量数据更新的速度。

SYBASEIQ通过列存储、革命性的位图索引方法以及动态访问技术，实现了快速的查询响应速度。由于减少了磁盘IO、对列进行并行处理、提高cache命中率等，能做到比传统的数据库查询速度提高10-1000倍。此外，由于使用了智能压缩技术与精巧的索引结构和列存储结合，相比其他数据库引擎有更好的存储效果，占用存储空间更少。

GreenPlum数据库是一个MPP分布式数据库，基于PostgreSQL数据库扩展开发，底层由多个PostgreSQL数据库实例组合衔接而成。GreenPlum数据库采用ShareNothing架构，每个底层PostgreSQL实例都有独立的CPU、内存以及磁盘，数据按行进行水平划分，不同的实例节点存储不同行的数据，只负责处理自己节点上的数据，避免了复杂的分布式锁，拥有良好的扩展性。在进行查询时，查询会根据执行计划，将计算发送到各个节点进行运算，再将结果进行汇总返回。

由于底层为PostgreSQL，支持完整的SQL2003、SQL93、SQL99标准，支持随机访问数据且支持索引，且由于采用多节点并行执行计算，可以支持千万亿资金规模的数据加载操作，支持PB级别的海量数据存储和处理。

将数据仓库搭建在开源的大数据平台hadoop上，使用hive作为数据库。由于hadoop优秀的水平扩展能力，在面对超大数据集处理（PB级）的数据处理时，能有效降底建设成本。

基本架构，可以总结为六个层，即接口层、整合层、中间层、融合层、应用层，以及规格层。

l接口层，用于接入来自多个异构数据库的数据，统一接入存储。接口层的模型，一般情况下与源系统

l整合层，对接口层接入的数据进行整合，进行数据清理，编码统一，历史状态存储。

l中间层，根据业务需求，开发出一系列业务标签。

l融合层，按照主题即将标签汇总到一起，便于后续管理及使用。

l应用层，基于汇总层的数据，进行应用开发。

l规格层，主要存储一下系统配置表、维度表。

1.设计人员

a)架构设计人员

在数据仓库建设前期，对整个数据仓库的技术选型、仓库层次架构等进行整体规划。

b)模型设计人

负责需求的分析、模型的设计。需要输出设计文档，以供开发人员进行开发。

2.开发人员

负责实现根据设计人员给出的设计。包括编码实现、脚本测试、数据稽核等。

3.部署运维人员

负责部署大数据开发平台套件，负责发布开发人员打包好的任务（代码、调度配置等），并对生产上的任务进行监控，处理异常，保障每天的数据正常生成。

Ø模型直观，易于业务理解、维度和事实与业务概念一一对应，而且维度不断在不同的事实之间复用，能够减少学习成本。

Ø基础宽表采用星型模型，维度模型宽，数量少，适合做各种索引，事实模型（例如指标宽表和基础宽表）数量比较多，但是字段不多。

Ø依赖关系及并行计算考虑数据源给数据时间点和能力差异问题，以数据源为单位，进行并行整合，避免接口与整合的互相等待。

Ø“日积月累”提高计算效率，采用增量+快照模式，针对事件类，采用全增量方式；针对资料类，采用快照+增量模式（收入类数据不在此列）

Ø统一维度、统一口径。整合层完成统一的维度编码，通过统一维度的整合，达到多模型在物理与逻辑上的统一。

Ø高扩展性、可伸缩性。可根据业务需要增加新的维度属性，同时保证基础宽表和指标宽表的稳定性。在统一维度ID的前提下，能够增加新的维度子表。

数据仓库基本结构，可以分为接口层(I)、整合层(T)、中间层(M)、融合层(S)、应用层(A)及规格层(D)。

接口层，核心作用为数据接入及同步。接口层的模型，一般设计为与源端一致，直接抽取源端表全表所有字段的数据。数据的同步方式，按照同步时间间隔可以分为实时同步和离线同步，按照数据同步范围可以分为增量同步及全量同步。

实时数据同步，在传统的Oracle数据库中，可以使用OGG进行实时数据同步，而在hadoop平台中，则可以利用Flume、Kafka及spark流处理实现实时同步。

对于离线同步，一般可通过FTP文件，对于hadoop平台，还可以使用sqoop工具实现批量数据导入。

核心作用：

Ø结构化：非结构化（日志）结构化处理并存储到数据仓库；

Ø累积历史、清洗：根据数据业务需求及稽核和审计要求保存历史数据、数据清洗。

通过对各业务系统的数据的标准化，统一编码，实现数据的跨平台关联，并把数据按照不同主题域进行划分整合，有效支撑后续的数据汇总工作。通过拍照和历史拉链的形式把数据历史情况和变动过程数据有效进行存储。

整合层的两大目标：数据清洗及企业级数据存储。

核心作用：

Ø组合相关和相似数据：采用明细宽表，复用关联计算，减少数据扫描；

Ø公共指标统一加工：基于规范体系构建命名规范、口径一致和算法统一的统计指标，为上层数据产品、应用和服务提供公共指标；建立逻辑汇总宽表；

Ø建立一致性维度：建立一致数据分析维度表，降低数据计算口径、算法不统一的风险。

中间层由一个一个小表组成，单个表保证只存放单纯的一类数据；若其中会影响到运行时效性则进行拆分；若计算逻辑差异较大则进行拆分。中间层遵循内容最小化、失效影响最小化、计算最小化的原则。理论所有运算逻辑均在中间层完成。

核心作用，就是将单独的一个个的标签，按主题汇聚到一起，避免后续使用时需要进行大量的关联，让使用更加方便，同时也大大提高了查询效率。

理论上，融合层没有运算逻辑，只是标签的组合。

Ø个性化指标加工：不公用性；复杂性（指数型、比值型、排名型指标）；

Ø基于应用的数据组装：大宽表集市、横标转纵表、趋势指标串。

数据应用层：指标维度汇总以及前端应用展示结果表。

规格层主要存储数据仓库系统的两类数据：

Ø系统配置数据

Ø维度表

这两类数据，可被整个数据仓库各个层级的模型引用。

中间层和汇总层很重要的作用就是围绕关键实体做一些计算和指标统计。

数据中不同分层的模型需要进行隔离，避免混淆。不同层的表存放在不同的用户（或数据库）中，分层用户（数据库）不进行开放，所有密码只掌握在模型管理员一人手中。用户名：

接口层：DWI+数据源名称

整合层：DWT

中间层：DWM

融合层：DWS

应用层：DWA

规格层：DWD

在Oracle中，通过用户来实现逻辑上的隔离，通过表空间实现空间上的隔离。表空间：

接口层：DWI_DATA_${序号}，如DWI_DATA_01

整合层：DWT_DATA_${序号}，如DWT_DATA_01

中间层：DWM_DATA_${序号}，如DWM_DATA_01

融合层：DWS_DATA_${序号}，如DWS_DATA_01

应用层：DWA_DATA_${序号}，如DWA_DATA_01

规格层：DWD_DATA_${序号}，如DWD_DATA_01

除仓库分层外，另外还需要一个临时用户及对应的临时表空间，用于数据整合过程中的临时表的存放。用户名为TMP，表空间为TMP_DATA_${序号}。

命名总原则：

接口层表命名规范:TI_数据实体。

非接口层表与视图命名规范：

表命名规范：T分层_分域_数据实体_账期类型

视图命名规范：V分层_分域_数据实体_账期类型

1.主键:数据实体_id。例如：prod_inst_id

2.维度属性：a_xxx。例如：a_lan

3.原子指标:i_xxx.。例如：i_3g

4.派生指标：原子指标_统计方法。例如i_3g_dev_sum,.

1.统一粒度：一个脚本只生成一张输出表

2.统一命名：表名.脚本类型后缀

接口/脚本根目录/分层/分域/脚本库表目录/具体的脚本列表

数源编码.表命名，

例如_139_user_d,_139_user_d

为了模型在oracle和DB2、informix、SYBASEIQ、HIVE等数据库之间转换方便，对字段类型的使用加以约束，尽量少使用特殊类型的字段。

对于ORACLE数据库，字段类型允许使用NUMBER，CHAR，VARCHAR2，和DATE四种类型，个别情况允许使用CLOG类型，不能采用其他类型字段。

Ø数据型字段：统一使用NUMBER(NUMBER的精度为38)，必须指定精度（precision），对于浮点型还必须指定刻度（scale），不能定义为缺省的NUMBER型。也就是定义时必须是NUMBER(p)或NUMBER(p,s)，不能只为NUMBER型。

Ø日期型字段：统一使用DATE。

Ø字符型字段：统一使用VARCHAR2或CHAR。注意，CHAR类型只能用于固定定长的字段。如一些编码等字段。对于目前一些字段尽管目前是固定长度，但以后可能变化的就采用VARCHAR2型，如电话号码，邮编之类的字段。主键，索引的字段如果是字符型就尽量采用char型。

Ø大于4000个字节的字符串，可采用CLOG类型字段。（如某些接口表存储xml数据的字段）

统一采用STRING类型。

有几点需要注意：

Ø日期类型格式统一使用“年-月-日时:分:秒”，如：’2018-09-1109:53:32’。

Ø如果HIVE建表时指定的文件格式为文本格式，并且行分隔符默认设置为文件的换行符，则字段内容中不允许出现换行符，否则HIVE在解析数据时会出现一行记录变为多行记录问题。

以ORACLE为例：

Ø对于比较大的流水（例如生产编号）字段统一使用NUMBER(12)

Ø对于静态数据中的NAME字段统一使用VARCHAR2(50)；

Ø对于一般的描述性字段统一使用VARCHAR2(255)

Ø对于金额定义为NUMBER(16,4);

Ø对于类型的字段，统一使用VARCHAR2(3)。

整合层和汇总层的表不建主键和索引，只在接口层和应用层中需要创建。

可参考如下原则：

Ø主键与索引主要是对表查询性能有利，对数据库的插入及删除性能有影响，所以在建立主键及索引前，请先考虑好表及字段的数据操作特性才决定是否要建主键及索引。

Ø对于数据量大（上千条记录以上）的表及各种字典表请在适当的字段上建立主键或索引。

Ø主键的字段不能为空；索引字段可以为空。

Ø主键及索引的字段数据不能经常改变（Update），经常改变数据的字段不能选为主键及索引。也就是说主键及索引的字段只能有非常少量的Update操作。

Ø组成主键及索引的字段数尽量不要超过3个。

Ø尽量少对长度超过20个字节字符型字段建立主键及索引，以免占用大量空间。

Ø对于数据量少（千条记录以下），同时插入及册除操作频繁的表则不能建立主键及索引，以免影响性能。

在数据仓库中，对于数据量大（千万行级）的表，需要进行分区，使查询时可以根据分区减少扫描的数据量，提高查询效率。

Ø对于每个账期跑全量数据的表，必须使用账期分区（一般为日或者月）

Ø若全量数据账期分区后，分区数据量仍然太大，可以考虑使用二级分区。二级分区的字段选择需根据实际情况灵活选择，在运营商数仓中，通常使用地市来进行二级分区。

Ø对于每个账期跑增量数据的表，一般不直接使用账期做分区，以免数据在物理存储中分散，导致数据库存储碎屏等问题。这时可以通过账期上级，来合并账期增量数据。如：日账期增量数据，可以使用月份来做分区字段，表字段中保留日账期字段即可。

1)增量模型

增量模型（一般为实时接口模型、增量接口模型、拉链表、日志表等）由于只存储一份全量的数据，一般情况不需要进行数据清理。但是，如果在业务上，历史数据已经不再需要，可以根据实际情况进行归档或清理。

2)全量模型

由于每个账期计算全量的数据，数据量大且重复数据多，必须根据业务需求制定数据清理规则。资料类数据一般保留每月最后1天和最近1个月数据。

特别地，对于应用层应用模型，大原则为小数据级汇总数据不做清理、大数据明细级数据日表保留最近100天数据和月最后1天、大数据明细级数据月表保留最近2年。

3)规格数据

规格类数据，数据量较小，且变化很少，不需要清理数据。

4)临时数据

临时数据无生命周期，可以随时删除，系统不做任何保证。

历史拉链表由于再数据整合时，需要对数据进行删除操作，而HIVE是不支持对表进行数据删除操作的，所以一般只在传统数据库（如Oracle）中出现。

历史拉链表时间分区使用LIST类型分区，分区字段为END_MONTH。子分区使用LIST类型分区，分区字段为LAN_ID。分区的增加在脚本中实现，分区默认不进行自动清理。

拍照表每账期生成一次全量数据，不涉及数据的删除操作，所以在HIVE中和传统数据库中都可以存在。通常，拍照表为一个账期的全量数据，写入账期的分区中。在hive中，建表时指定分区即可。而在传统数据库，如Oracle中则还有选择使用范围分区还是列表分区，建议均使用列表分区。

拍表时间分区使用RANGE类型分区，分区字段为DAY_ID/MON_ID。子分区使用LIST类型分区，分区字段为LAN_ID。分区的增加通过在创建表时指定自动分区，分区清理根据业务需要进行自动清理配置。

1、需求分析

在进行开发之前，先对开发的需求进行分析。这一步需要明确需求中需要生成什么样的数据（业务口径、描述），数据应该如何生成、从哪些表取数、取什么字段等（技术口径）。

2、脚本开发

在对需求进行分析之后，将业务整合逻辑转化为代码，写到脚本之中，以便后续重复执行。

3、脚本测试

脚本开发完成后，在开发环境运行测试，保证脚本正常运行。并对生成的数据进行初步验证。

4、调度配置

脚本在开发环境测试通过后，配置开发环境调度任务、血缘依赖、周期依赖、运行周期等。

5、数据稽核

配置调度后，可以从生产环境中导入数据到开发环境，从调度中跑出正式数据进行核对，验证调度配置是否正确（血缘依赖是否完整、账期依赖是否正确等），并确保生产的数据符合业务需求。

6、部署发布

在开发环境核对数据符合要求后，将脚本代码提交到正式环境，将调度任务发布到正式环境。

a)不同的项目根据不同的情况，对脚本的开发语言可以不同。一般情况下，在Oracle中进行数据开发一般使用存储过程，而在hadoop上进行hive脚本开发则一般使用shell脚本。

a)对于存储过程，统一使用大写编码。

b)对于shell，SQL使用大写，其他采用小写。

a)脚本中用到的正式表、临时表，都要通过变量来使用。

b)在存储过程中，所有变量统一大写。

c)在shell中，所有SQL中用到的变量都使用大写（包括正式表、临时表变量，以及其他账期、本地网等变量），而SQL之外的，使用在shell脚本本身的变量则使用小写。

在脚本开通需要写明注释，至少包含以下几个部分：脚本名称、脚本功能描述、开发人员、开发日期、入参、脚本修改历史。

a)SQL语句统一用大写书写，避免大小写混搭。

b)SQL语句需要格式化，关键字右对齐，字段左对齐，相似内容上下保持一致，字段列表逗号统一打在左边，列表带有字段注释；

c)每一段SQL前面，都要有对该段SQL的功能作用的描述。SQL中的较为关键的关联、转换、过滤等也应写上注释。如：

d)表与表之间关联使用关键字（INNERJOIN/LEFTJOIN/RIGHTJOIN），禁止使用表列表的方式；

e)INSERT必须列出具体字段列表，字段每一个字段一行，逗号放字段左边，字段后带字段注释FF1B

f)SELECT查询必须列出具体字段列表，字段每一个字段一行，逗号放字段左边，字段后带AS别名；

g)将数据通过INSERT语句写入目标表数据不带复杂逻辑，以免长时间锁表（Oracle）。若有复杂逻辑，需在前面把复杂逻辑导入中间临时表，然后由临时表直接INSERT入结果表。

h)对于表分区字段，在Oracle中，分区字段在表的字段列表中，而在hive中表字段不能作为分区字段，所以另外设置一个分区字段，字段名字为P_${表字段}。如日全量模型，一般以DAY_ID作为日账期字段，同时作为分区字段，则在hive表中，分区字段名为P_DAY_ID。

a)在数据开发完成后，后续运维可能由于不同原因需要重跑脚本以修正数据，所以所有开发的脚本，为程序的健壮性、后续的可维护性，需要支持重跑数据。

b)脚本支持重跑的功能，就是在写入当前账期产生的数据前，需要将目标表还原到当前账期数据写入前的状态。

c)一般来说，对于通过账期分区的表，可以在写入数据前直接把当前账期的分区数据清空，然后再写入；而对于非分区表，则只能通过delete语句先删除当前账期原来生成的数据，然后再写入新账期的数据。

a)所有脚本都应记录运行日志。日志在调试、测试脚本，后续运维监控查错时起巨大作用，帮助快速定位问题。

b)使用存储过程时，可以通过将动态SQL写入到一个运行日志表的形式记录运行日志。

c)使用shell脚本时，则可以将标准输出、标准错误输出重定向到一个文件中。

a)脚本编码格式统一为UTF-8

b)每个脚本只有一个目标表，即每个脚本只对一个正式表的数据进行写入、删除、更新等操作。

a)按照模型的分层，每层应有一个特定的目录，用来存放该层的脚本。每一层的脚本就放在对应层级里面。

b)除了模型脚本目录，还应有公用的目录以及日志目录，分别用于存放公用脚本以及脚本运行日志。脚本运行日志目录，应按照脚本模型目录层级及账期组织目录。

a)脚本需要进行版本管理，每次提交修改时都要写清楚本次修改人、修改时间，修改了哪些地方、功用等。

b)脚本每次从开发环境发布到生产环境时，应该先把脚本提交。

c)脚本进行版本管理，可以在必要时进行版本回退，避免错误提交以致无法恢复。

b)开发接口前，需从接口列表中确认该接口数据是否已经接入，若没有才进行开发，避免接口重复接入。

a)任务的配置，需要规律化，以便于后续运维监控。

b)可以通过模型分层、账期类型（日、月、实时等）、主题域等层级进行目录层级划分，对应的脚本放到对应的目录中。

c)每个重要的任务都要配置告警，在指定条件下（如超时未到、运行报错等）想指定的负责人发信息进行告警（邮件、短信等方式）。

a)依赖配置主要分为两点，血缘依赖以及账期依赖。

b)如果一个任务依赖了上游的任务生成的数据，则该任务需要等待上游任务都运行完成之后才能运行。所以配置任务时，需要把上游的依赖都配置好，以免出现依赖的上游任务还没运行完成，任务就开始运行，导致空跑数据。

c)配置血缘依赖时，要十分注意所依赖的数据的账期，一般情况写为当前依赖当前账期，但也会出现当前账期依赖上一账期、下一账期、上月账期等。

a)任务从开发环境发布到测试环境时，先把脚本提交，然后再导出配置好的任务及脚本给予部署发布人员到生产环境部署发布。

b)配置好的任务导出时，应支持导出任务的所有上下文信息（包含新的目录、流程、节点、依赖、调度时间、调度周期等信息）。

c)任务在导入生产环境时，调度工具应检查任务上下文是否具备。调度工具也应支持版本管理，以免导入任务后出现生产故障却无法恢复。