ETL的主要作用:数据的获取、清洗的一致性、用于展现的发布、ETL环境的管理,
在所有的设计ETL模型的时候,所有模型的逻辑设计应该已经初步完成,并了解了自己所使用的用于建立数据仓库的数据源有哪些,以及需要建设的模型ETL和源之间的映射关系的80%是可以确认的,那么在上面的基本条件准备充分的情况下,可以开始ETL的建设,需要考虑一下ETL工具的选择,对于一个大型的项目有一个ETL去管理,对后期新的需求和维护是有很大的帮助,这个是从多个项目的深切体会。
基于现在数据分析ETL可以分为如下两大类:
- .维度ETL模型;
- 指标ETL模型。
基于以上的两种模型展开项目的ETL的搭建,根据需求结合业界对于不同维度的使用情况进行处理,例如:维度的缓慢变化,站在历史看历史的需求等等,对维度的处理比较麻烦;指标处理就相对维度价简单些,根据需求和映射关系等内容可以计算或者得到想要的汇总结果。
下面结合实际项目经验:主要对维度模型的ETL建立,做详细介绍、
实际场景:
项目背景金融行业,主要是对各个证卷金融行业数据进行汇总,供决策层使用和下属的一些单位使用,按照需求需要实现站在历史看历史,那么这时候维度的缓慢变化是关注的一个重点设计。
假设数据来源源A,B两个项目,归属到C项目
- 高层规划
- 明确数据的来源只有两个项目A,B;
- 首先需要明确C项目和A,B项目维度值的映射关系,这个是由C项目的负责人提供映射关系;
- 关于指标的获取方式按照需求的要求实现,这里不做介绍;
- 对于站在历史看历史的数据,那么维度的变化是不可避免,需要考虑对变化的维度如何处理;
- 选择ETL工具
进来用来做ETL工作的工作层出不穷,比较多,选择一款合适的ETL工具,对于后续的开发维护及新需求的处理都是有很大的帮助,很不幸的是我们采用了最原始的工具sql脚本,好处,可以针对不同的需求能够处理,避免因为工具的局限性导致部分需求开发比较困难,不足,开发工作量大,并且代码管理比较繁琐。
使用ETL工具的优势:
- 便于管理,同时可以生成一些构建文档,便于维护;
- 图形关系直接转化为物理模型;
- 以最基本的经验改进性能;
- 成本小,不需要特别专业的sql开发人员;
- 未来系统的维护和改进有很大的帮助,快速,易读性强。
- 开发些常规【行业标准】
- 从每个主要的源系统获取数据;
是直接从库读取,还是文件读取,或者流的方式读入等。
- 归档回去数据或分级的数据;
在获取数据之后,在处理之前需要将数据保存,是保存最新的还是历史所有的,需要将迁移的数据做一个归档,是永久归档还是有时间限制的归档,根据具体的平台设计和将来平台的扩展性,及项目是作为其他项目的数据仓库等一些列原因去分析。
- 监控维度和特定事实表的数据质量
在做ETL过程中,要时刻注意数据质量的问题,对于一些异常数据及时发现与相关业务沟通,制定相应的处理规则,不能等用户发现在处理吗,这样项目的整个进度就会出现很难把控的形式;
- 维度变换的属性变化的管理
这一部分是ETL设计的一个难点,C项目直接在维度表存储最新的维度数据。这个是处理的比价简单的方式,个人建议一般不这么处理,缺陷,用户需要知道不同时期的维度值的分布情况,eg:2000年的时候,性别维度属性name值男、女2010年发生变化值变为男生、女人。那么按照C项目的处理方式现在维度表是有4个值,站在历史看历史,在2005年的时候通过name值为男生的时候查出来的数据是空的,这个就需要解释。而且一张维度表中这四个值,这样的设计存在不合理性。
个人觉得分为以下二种设计比较合理一些
- 在查询2010年之前维度表的值就应该只有男、女,查询之后的数据对应的是变化后的值,这个就需要考虑维度的缓慢变化,对数据加工的处理也是一个复杂的过程
- 将所有的维度进行转换,所有的维度采用最新维度,同时将变化维度做好记录和映射,在数据加工的数据,进行转换。不会出现维度字段内容冗余;
以上两种方案的实施还是需要具体参考业务的需求去设计和实施。
- 确保数据仓库和ETL系统满足系统可用性需求
将这一部分一定要文档化,以及所有使用到的ETL设计文档很重要。
- 设计数据审计子系统
数据仓库中每行应该使用相关审计信度息标记,用于描述数据如何进入系统的
- 组织过渡区
数据仓库建立或者ETL处理过程一般至少都要有2-3次的转换,用于ETL步骤以及系统恢复和附档工作。
- 按照目标表钻取数据
- 确保层次的清洗性;
- 开发的详细设计的pdm。
- 开发ETL规范文档
源到目标的映射、数据档案的报表、物理设计决策,都应该包含子啊ETL开发的文档中,具体一应该包含一下几部分:
- 每个组要源系统获取的默认策略;
- 归档策略;
- 数据质量跟踪和元数据;
- 管理维度属性变化的默认策略;
- 系统可用性需求与策略;
- 数据审计子系统的设计;
- 过渡区定位;
另外,ETL规范的下一个部分就是描述每个表的历史和增量的加载策略,具体应该包含如下细节:
- 表设计{列名,数据类型、键和约束}
- 历史数据加载参数(月数)和容量(行计数)
- 增量数据容量,对每个加载周期涉及的新的和更新的行
- 处理实时表和维度表迟到的数据。
- 加载数据频率
- 处理每一个维度属性的缓慢变换维度变化
- 表分区,例如按月
- 数据来源概述,包括讨论所有不常见的源特征
- 详细的源到目标的映射
- 源数据概要,包括每一个列的最大值和最小值,每一个列中不同值的统计及空值发生的频率
- 源数据获取策略【api,直接从数据库查询或者转存储到平面文件】
- 依赖,包括某个表在处理前必须加载那些表
- 文档化转换逻辑,这部分最好用伪代码或者图进行说明
- 避免产生错误的前台条件,ETL开始之前检查数据库的存储空间和必须检查的文件
- 清洗步骤,删除冗余的文件
- 估计ETL设计的各个环境的 难以程度
以上开发文档的拥有,便于开发人员快速开发,精准把握,而且可以很好的指导后续工作内容,和ETL的进度把控。