Package 设计3:数据源的提取和使用暂存

时间:2023-12-10 23:19:20

SSIS 设计系列:

在使用SSIS Package处理海量数据时,必须精心设计Package的各个Task组件,“锱铢必较”,以最快的速度和最小的资源消耗,完成既定的数据处理任务。在处理数据源提取时,数据的大小由两个方面决定:数据行的宽度和数据行的数量,为了减少ETL运行的时间,可以从源数据的提取上做优化设计,从数据源的输入上控制数据的数量和大小,以减少数据转换的次数,内存的消耗量,以及硬盘IO的次数。

一,减少行的宽度

1,只加载需要的数据列

在Data Flow中,数据源允许加载整个Table 或View,虽然能够从数据源编辑器勾选数据列的复选框,从而过滤掉不需要的数据列,但是,唯一的问题是,数据列的过滤过程发生SSIS引擎中,换句话说,所有的列首先从数据库中被加载到SSIS 源适配器(产生大量的IO开销),然后从SSIS引擎中删除未被选中的数据列,实际上,不需要的数据列已经从数据库加载SSIS引擎中了,这部分的Disk IO消耗,可以避免。建议在数据源组件中,使用SQL Command,在select 子句中指定只加载需要的数据列。

2,在提取数据期间将数据转换为窄的数据类型

数据窄化是指将数据类型转换为可以充分表示其值的最小数据类型,例如,如果有一个数据列,数据类型是int,但是,可能的数据值只有 0 和 1,那么将数据类型转换为bit更好,在64bit系统中,每行的长度至少减少3Byte。对数据列进行窄化,相同的数据量占用更少的内存,相同内存能够容纳的数据量更多,进而每一次数据转换的数据量更多,加快数据处理的速度。

3,窄化数据值

  • 如果字符串中的两端有大量的空格,建议使用ltrim(rtrim(String_Column)),删掉字符串两端的空格;
  • 对于小数类型,如果不需要太高的精度,可以使用decimal或低进度的数据类型来标识;
  • 对于日期/时间类型,如果在数据处理中,只需要DateKey,可以将日期类型转换为int类型,而不需要加载Datetime类型,或datetime2类型。

二,减少数据行数

1,使用Where条件,限制返回的数据行数

增量更新,使用DateTime,Row_Version等字段来实现增量更新,而不是将重复的数据重复加载,增量更新会大幅度减少需要加载的数据行数量,减少ETL运行的时间。

2,使用Where条件,过滤无效的数据

过滤数据行,只加载有效的数据行,对于一些无效的数据,使用where 子句直接过滤,保证进入ETL的都是有效的数据。

三,在提取数据期间

1, 解决幻数

幻数是一个用来表示未知或NULL的数据值,在不允许为NULL的数据表中,例如,数据列使用 not null 定义,幻数是必需的。常用的幻数根据数据类型来定义,整数类型是-1,日期类型是1753-01-01,字符串类型是空字符串。

2,数据排序

在SQL Server中对数据排序,会比在SSIS中使用 sort 转换更高效。如果在ETL中需要加载有序的数据集,请在SQL Server中排序;如果ETL不需要有序的数据集,请不要都数据源进行排序。

四,处理外键

假设场景:有一个ETL系统,通过记录数据最后更新的时间,对数据进行增量更新。如果数据仓库中存在有外键关系的两个表,Group(GroupID,StudentID,GroupData) 和 Student(StudentID,StudentAlternateID,StudentData),Group表引用Student表中的StudentID字段。在导入Group数据时,如果Group表中存在一个数据行R1,其StudentAlternateID不存在于Student表,如何处理?

分析:如果不导入这行数据,那么Group表存在丢失数据的可能性。因为,Student表的数据被补齐后,除非数据行R1被再次更新,否则,数据行R1的数据不会被导入到DW中。

结论:必须将R1导入到DW。由于StudentAlternateID是Student表的业务主键,当Student表数据补齐后,能够通过业务主键匹配,可以预先把缺失的数据行主键添加到Student表中,其他数据列设置为默认值,实现的详细步骤是:

  • Step1,将StudentAlternateID导入到Student表,StudentData 设置为null(或其他缺失值),生成一个StudentID(Student表的代理键)。
  • Step2,将生成的StudentID更新到Student表中,尽管Group引用的Student数据是未知的。
  • Step3,后续Student表有更新时,如果存在StudentAlternateID,那么可以将StudentData更新为有效值。

为了区分这种数据,可以在Student表中增加一个Column:IsLateArrival bit,如果IsLateArrival=1,表示是该数据行在插入时,只能确定业务键(Alternate Key),而其他数据未知,后续,需要通过业务键来将其他数据更新为真正有效的数据。

五,使用暂存

如果SSIS Package不需要对数据执行大量的数据更新操作,那么在数据流任务中设计Package,把数据转换和处理的业务逻辑移动到数据流中,一般情况下,能够减少临时表的创建,获得较高的处理性能,但是,在数据流任务中,执行数据更新操作,只能使用一个数据流转换组件(OLE DB Command),该组件是逐行更新。如果SSIS Package需要对数据执行大量复杂的数据更新操作,使用暂存表(staging table)能够优化package设计,在SQL Server数据库中,执行大量数据的更新操作是性能最高的。

1,使用基于集合的更新操作

在大型系统中,数据更新通常是系统的bottleneck,因为SSIS引擎不能在Data Flow 中执行基于集合的更新。在Data Flow中,OLEDB Command 转换组件是逐行对数据进行更新的,对每一行数据执行更新操作,会导致低下的性能。对于存在大量更新的数据流,能够有效解决数据更新问题的解决方案是:将需要更新的数据缓存到一个暂存表(staging table),使用TSQL 语句和暂存表对目标数据进行基于集合的更新操作。

2,使用CheckPoint从错误点重启Package

SSIS的CheckPoint记录的Control Flow Task的执行结果,如果Data Flow Task中的转换发生失败,那么CheckPoint 不会保留数据状态。当重启包时,数据流将会从头开始。如果将数据存到暂存表中,那么可以从暂存数据中重新启动。做法是:从源中读取数据,将其加载到暂存表中,然后从暂存表中获取数据,并对其应用转换逻辑。

3,增加Disk IO

将数据暂存到表中,会成倍增加Disk IO,当使用暂存表临时存储数据时,该数据最终被保存到Disk中,并且需要将数据从Disk读取数据到内存。因为需要移动大量的数据,磁盘IO通常是ETL的bottleneck,所以,在不需要大量更新数据的ETL中,应该减少对暂存表的需求,使用数据流完成相同的转换操作,由于数据流主要使用内存,相比disk,内存能够更快地访问,这样,不仅能够减少Disk IO的开销,而且能够减少ETL处理的时间。