ETL开发综述ETL是数据仓库的后台,主要包括提取、清洗、规范、提交四个步骤,传统的数据仓库一般分为四层模型。
分层的作用:
1 .划分ETL阶段工作重点,便于管理
2 .降低开发维护成本
3 .减少需求变化带来的冲击
4 .方便跟踪数据问题
名词解释:
ODS——可操作性数据
DW——数据仓库
DM——数据集市
STG层在维度建模阶段确定了源系统,并对源系统进行了数据评估。 STG层基于CDC策略将每个源系统的数据提取到数据仓库中。 STG层主要是面向批处理的形式,基于日志信息实时同步时,可以跳过STG层直接进入ODS层。
STG作用:减少源系统压力; 支持数据备份、重新行驶; 容易追踪问题; 数据质量检查主要起到缓冲区的作用。
开发步骤:
确定CDC策略,并根据源系统的数据状态选择相应的CDC策略。
设计一个映射文档。
设计物理模型,STG物理模型通常包含源系统中的所有字段和审核字段。 例如,源系统名称、源表名称、加载时间和加载方法。 您可以移除其他约束条件,例如主键、索引和默认值。 如果源表和目标表的数据库类型不同,则建议您扩展字段的长度。 通常,目标表的数据类型选择几种常见的,长度选择几种固定长度。
希望从数据提取、STG级别到异构数据源选择ETL工具。 典型的ETL工具支持多个数据源。 STG层不进行数据转换。
加载数据,STG层通常保留7天或1月的数据
ODS层ODS层对STG层的数据进行历史归档,维护源系统的所有历史数据。 在流传送的情况下,STG层可被跳过并实时地与ODS层同步。
ODS作用:总量存储源系统数据; 支持下游系统实时查询业务的数据质量检测
开发步骤:
设计一个映射文档。
设计物理模型。 ODS物理模型通常包含源系统的所有字段和审核字段,但与源系统的最大区别在于在ODS层中添加了逻辑删除标志和增量时间戳。 许多源系统可能会物理删除数据,因此即使标记为逻辑删除,也可以在后台手动删除数据。
提取数据,ODS层从STG层提取数据,可以在相同数据平台上采用或手动编码ETL工具。
加载数据并进行数据比较,以确定是否存在物理删除情况,如果标记为删除,则将其删除。 ODS层保存着全部量的数据。
提出DW层DW层清洗、规范,量纲合一的工作空间,建立反规范的量纲模型。
数据清洗:数据清洗是发现数据质量问题、纠正数据的过程,通用方法是安装质量环
主要步骤:
定义数据质量要求,并根据业务要求和数据分析结果确定数据质量要求的优先级。
创建数据质量度量类型
提出数据质量测定结果表。 通常,异常数据处理策略是中断处理; 只做将拒绝记录放入错误日程的标记,数据继续处理
修正数据分为四个优先级。 必须用ETL处理。 最好用ETL处理; 最好在源头处理; 必须用信源处理
数据规范化
由于数据仓库的数据源是各业务系统,所以各业务系统相对封闭,在命名、取值上有其自身的特点。 规范化是指经过标准化、重复数据消除、合并、分割、合并等过程对各业务系统的数据进行统一命名、统一取值,并生成企业标准版本的数据。
主要步骤:1.数据标准化
根据数据的内容、格式、命名、计算规则等作为唯一的版本数据输出,统一各源系统的相同记述对象但可能不同的值。 例如,在性别字段中,可以使用0和1或Man和Wonen的源系统。 在映射表中统一命名为m和f。
2.删除重复数据
如果源系统具有重复数据,或者多个源系统包含同一对象的数据,则必须根据保留规则删除重复数据,以便只保留唯一的数据。
3.数据共存
对各业务系统的数据进行分割、合并、综合。 例如,如果客户编号相同,但两个源系统维护此客户的联系人,则必须根据业务规则选择保留哪个源系统的值。
维度表的提交
1 .粒度确认
维度表的粒度是表的业务主键,根据业务主键判断记录的唯一性。
2 .选择代理密钥生成器
ETL工具和数据库具有设置字段自扩展的功能。
3 .选择维表的类型
根据业务系统的实际情况选择合适的维度表类型,通常采用缓慢变化的维度类型1和类型2。
4 .增量加载维数据
维表中的每个字段都具有默认值,且不能为空。 第一次加载时需要1
条代理键为-1的默认记录,为了防止事实表查找不到代理键。5.生成代理键管道
为了生成事实表的维度代理键,一般会建一个查找维表,查找维表包含业务主键和代理键的映射关系。
事实表的提交:
1.选择事实表类型
根据业务需求选择合适的事实表类型,一般会先建最细粒度的事务事实表,根据事务事实表建周期快照或累积事实表。
2.用代理键替换主键
根据事实表中维表的业务主键关联查找维表替换成代理键,如果关联不上设置为-1。
3.增量加载事实数据
事实表的每个字段不能为空,事实表主要包含事实粒度的业务主键、维表业务主键、维表代理键、源表的主键、逻辑删除标识和事实。便于重新加载事实表和问题跟踪。由于事实表的数据量大,一般采用分区的方式进行存储。
4.提交错误事实表
把加载事实表的拒绝记录存储在错误事实表中,以便进行数据质量跟踪。
5.事实表合并
一级事实表都是根据单个业务过程建立的,为了便于分析和重用,需要把多个业务过程的事实表进行合并,形成二级事实表。
6.事实表归档
随着时间的推移,事实表中会存放大量的历史数据,如果这些数据很少再出现在统计分析中,需要把这些数据迁移到其他表中或以文件格式存储。
DM层DM层根据业务需求把DW层数据进行聚合或生成宽表。
创建聚合事实表
前台展现的数据一般都是聚合后的数据,聚合后的数据量比最细粒度的事实表小很多,查询性也有很大的提升。
创建聚合表的方法
1.增量加载,创建聚合表,增量加载聚合表。
2.聚合导航,用户通过报表分析工具,根据用户请求把基础事实表自动生成聚合数据。
3.物化视图,创建物化视图定时刷新聚合表。
创建缩小维度表
由于聚合事实表的粒度和基础事实表粒度不同,需要创建和聚合表相同粒度的维度表,这些维度表只是基础维度表的缩小版。
ETL优化1.减少磁盘I/O
关联查询的时候,尽可能把无效的数据过滤掉
只查出需要的列
大数据量尽量不要有排序
在加载数据时关闭日志
2.分区和并行
大数据量可以进行分区
查询和任务调度都可以进行并行处理
3.增量加载
4.增加索引
5.大而化小,复杂的查询可以分成多个子任务来执行。
6.重用结果集,把多个查询任务的共用数据可以单独建临时表。
扫描下方二维码
加入优质社群一起交流吧!
如若二维码过期
请添加小编微信,回复关键词:[进群/加群],
-今日互动-
ETL规范你知道了吗?欢迎文章下方留言互动
如果对你有帮助的话
❤️来个「转发朋友圈」和「在看」,是最大的支持❤️