发明内容
本发明的目的是提出一种ETL流程中异常数据检测方法,其自动化的分析ETL流程并将目的端的数据约束沿ETL流程反向传播到数据源端,然后对数据源进行异常数据检测。
本发明的技术方案可以表示为图1,具体步骤如下:
1.将ETL工具设计的ETL流程转换为ETL抽象图。ETL抽象图是一种新的数据流程模型,它包含两种结点:关系结点(relation node)和转换结点(transformation node)。关系结点表示关系记录集,包括数据源、目的端数据库及各操作的输出记录集。转换结点表示数据转换操作,包含输入记录集模式与输出记录集模式的属性映射关系及元组选择条件。ETL抽象图中的边为有向边,连接各结点,记录数据流向。
给定一个ETL流程Ω,它对应的ETL抽象图G(Ω)按照下面的步骤构建:
1)对Ω中每一个数据转换操作创建一个转换结点α,输入记录集为{I1,...,In},输出记录集为O,根据其操作语义,生成每个输入记录集模式Ik与输出记录集模式O的属性映射关系mapping(α,Ik,O)和元组选择条件filter(α,Ik,O),k取值于1...n。
mapping(α,Ik,O)的一个例子表示为:Ik<k1,k2,...,kn>->αO<o1,o2,..,on>。尖括号内为各模式的属性,表示两边属性的对应关系。左边的属性可以包含函数操作。
filter(α,Ik,O)的一个例子表示为:Ik(k1>p∧...∧kn=q)->αO。其中的括号内为元组选择条件,为built-in谓词的合取式。可以表示大多数select条件。
对于基本关系代数操作,可以参考对应的mapping和filter的生成方法,参见实施例中表1。
2)对于每个转换结点α,创建一个关系结点resultTable(α)来表示该转换操作的输出记录集,并初始化其数据约束集合rules(resultTable(α))为空。
3)根据Ω中数据流向在关系结点和转换结点之间添加有向边。
2.获取目的端的数据约束,表示为统一形式的否定约束(denial constraints):
p
k表示关系谓词,x
k表示元组,
表示多个built-in谓词(<,>,=,≤,≥,≠等)的合取式。可以表达以下常见约束类型:非空(NotNull),值域(Value Domain),函数依赖(Function Dependency),Check(SQL语句支持的约束定义关键词)等。数据约束可以从数据库管理系统自动抽取或由用户自定义。目的端可以包含多个数据约束。传播过程中各记录集的数据约束也为统一的否定约束形式。
3.以目的端数据库为起点,沿数据流的反方向,将每个转换结点的输出记录集上的数据约束反向投影到它的各输入记录集上,直到数据源为止。
如果转换结点α包含无函数的mapping和确定的filter,数据约束反向投影过程含以下步骤:
1)对于一个数据约束c,它只含有输出记录集O的属性变量,对于一个输入记录集Ik,根据mapping(α,Ik,O)中属性映射关系,使用Ik的属性变量对c中变量进行替换得到c’。
2)如果c’仍包含O的属性变量,那么c无法投影到Ik,c在该边的传播过程终止。
3)如果c’只包含Ik的属性变量,且filter(α,Ik,O)为空,添加c’到Ik的数据约束集合rules(Ik)。
4)如果filter(α,Ik,O)不为空,将filter(α,Ik,O)中的元组选择条件添加到c’的合取式中,得到c”,添加c”到Ik的数据约束集合rules(Ik)。
5)对于每个数据约束,依次向每个输入记录集执行上面反向投影操作。
如果转换结点的mapping包含函数或者filter无法确定,需要采用特定的反向投影方法,在实施例中给出了针对基本关系操作的投影方法。
4.使用得到的数据源端的数据约束集合进行数据检测,将数据约束转换为SQL语句来定位违反约束的数据,用户可以进行数据清理。
5.投影得到的每个转换结点的输出记录集的数据约束可以提供给设计者用于ETL流程质量监控。
本发明的积极效果为:
采用本发明的方法,可以在设计好ETL流程后,将目的端的数据约束需求投影到数据源端,来检测数据源端中的非法数据,用户定位到非法数据后,一方面可以对数据源进行合适的数据清理与数据过滤,一方面可以提示用户对ETL流程进行必要的修改来保证加载数据的质量。传播得到的数据约束是数据经过ETL过程后成功加载的必要条件,因此可以大大提高ETL过程的效率。
具体实施方式
下面结合附图和实施例对本发明做进一步说明。
假定一个美国公司在中国有一个分公司,在本地记录雇员和部门的信息。现在要将该分公司的数据集成到美国总部的一个数据仓库DW中。
分公司数据库包含两个表:
employee(EId,Ename,Address,DeptID,StartDate,Salary)
department(DeptId,DeptName,DirectorId,Revenue)
关系employee的每个元组记录一个雇员的信息:EId是唯一编号,主键;EName是雇员名字;Address记录雇员的住址;DeptId是雇员部门的ID,外键;StartDate是雇员来部门开始工作的日期,格式为”YYYY-MM-DD”;Salary是员工的年工资,人民币(¥)。关系department每个元组记录一个部门的信息:DeptID是部门唯一编号;DeptName是部门名称;DirectorId是部门领导的ID;Revenue是部门的年收入,人民币(¥)。
目的端数据仓库DW位于美国,包含一个表:
seniorEmpInLargeDept(EId,EName,StartDate,Salary,DeptName,Size,Source)
每个元组记录一个雇员信息,StartDate的格式为“MM-DD-YYYY”,Salary的单位是美元,Size表示部门的雇员人数,Source表示数据的来源。
来自分公司的数据都需要进行处理后才可以加载到数据仓库中。可以使用一个ETL工具设计如图2所示的ETL流程图。其中三角形表示数据处理操作。
为了描述方便,假定每一个操作X后面存在一个临时的结果集resultTable(X)。具体的操作细节如下:
A1:使用外键DeptId等值连接表employee和department
A2:使用DeptId,DeptName进行分组,求雇员的个数Size
A3:连接表resultTable(A2)和employee
A4:为表resultTable(A3)添加字段Source,并赋值“DS1”
A5:进行投影操作,得到resultTable(A5)(EId,EName,StartDate,Salary,DeptName,Size,Source)
A6:将StartDate由格式“YYYY-MM-DD”转换为“MM-DD-YYYY”
A7:将Salary由人民币转换为美元
假定目的端数据仓库DW定义了以下数据约束:
DW-IC1:所有雇员的薪水Salary最小为50,000$
DW-IC2:所有部门雇员人数最少为10
DW-IC3:Salary不能为空
对于该实施例,我们期望根据DW的数据约束,得到数据源employee和department需要满足的数据约束,可以使用本发明的方法来求解。
具体步骤如下:
1.构造ETL抽象图
ETL流程主要描述数据处理操作的顺序与操作语义。而ETL抽象图重点记录每个数据处理操作输入记录集与输出记录集属性的映射关系和元组的选择条件,根据这两个条件可以定位与输出记录集对应的数据,来得到对应的数据约束条件。
基本关系代数操作可以表达基本的关系数据操作语义,是各种数据转换函数的基础,因此我们给出这些基本操作的属性映射关系(mapping)和元组选择条件(filter)的生成方法,如表1所示。
转换结点为α,I<a1,a2,...,an>表示输入记录集,O表示输出记录集,I1,...,In表示多个输入记录集。不考虑多个输出记录集的情况。
表1、关系代数操作的mapping与filter生成方法
根据该表,构造该实施例的ETL抽象图,如图3所示。图中转换结点对应的mapping和filter为:
A1:1)employee<EId,Ename,Address,DeptId,StartDate,Salary>->A1
resultTable(A1)<EId,Ename,Address,DeptId,StartDate,Salary>
2)department<DeptId,DeptName,DirectorId,Revenue>->A1
dresultTable(A1)<DeptId,DeptName,DirectorId,Revenue>
3)filter为空
A2:1)resultTable(A1)<DeptId,DeptName,Revenue,EId>->A2
resultTable(A2)<DeptId,DeptName,Revenue,Size>,Size=COUNT(EId)
2)filter为空
A3:1)resultTable(A2)<DeptId,DeptName,Revenue,Size>->A3
resultTable(A3)<DeptId,DeptName,Revenue,Size>
2)employee<EId,Ename,Address,DeptId,StartDate,Salary>->A3
resultTable(A3)<EId,Ename,Address,DeptId,StartDate,Salary>
3)filter为空
A4:1)resultTable(A3)<EId,Ename,Address,DeptId,StartDate,Salary,DeptName,Size>->A4
resultTable(A4)<EId,Ename,Address,DeptId,StartDate,Salary,DeptName,Size>
2)filter为空
A5:1)resultTable(A4)<EId,Ename,StartDate,Salary,DeptName,Size,Source>->A5
resultTable(A5)<EId,Ename,StartDate,Salary,DeptName,Size,Source>
2)filter为空
A6:1)resultTable(A4)<EId,Ename,StartDate,Salary,DeptName,Size,Source>->A6
resultTable(A5)<EId,Ename,f(StartDate),Salary,DeptName,Size,Source>
f(“YYYY-MM-DD”)=“MM-DD-YYYY”
2)filter为空
A7:1)resultTable(A4)<EId,Ename,StartDate,Salary,DeptName,Size,Source>→>A7
resultTable(A5)<EId,Ename,StartDate,f(Salary),DeptName,Size,Source>
f(Salary)=Salary*7
2)filter为空
2.获取目的端的数据约束并转化为统一形式
根据该实施例中目的端的数据约束,将其转化为否定约束形式。不引起歧义时,为了简化表示,省略了全称量词和谓词名称。
3.数据约束反向传播
对于包含无函数mapping及确定的filter,只需要对filter转化为合取式,就可以采用发明内容中步骤3的方法进行约束反向投影。
3.0对于包含无函数mapping及确定的filter,使用发明内容步骤3方法投影。
对于包含函数的mapping和不确定的filter,因为其投影性质与具体的操作语义相关,因此我们给出聚集操作和函数操作的约束投影方法。
下面仍然使用相同的符号。数据转换操作为转换结点α,输入记录集为{I1,...,In),输出记录集为O。一个约束为c。
3.1对于聚集操作,只有一个输入记录集I,如果c中包含函数max或min的结果,在部分情况下可以投影得到约束条件。对于其他聚集操作,因无法对应到聚集的输入元组,因此都无法投影。如果c中不含聚集操作的结果,那么可以利用3.1-3投影。
3.1.1:如果转换结点α包含聚集操作min(attr),c包含 (或 ),那么将其替换为 (或 ),然后应用3.1-3。
3.1.2:如果转换结点α包含聚集操作max(attr),c包含 (或 ),那么将其替换为 (或 ),然后应用3.1-3。
3.1.3:如果c中不含聚集操作的结果,那么可以将聚集函数和分组函数去掉,转化为selection操作,使用3.0中的方法来投影。
3.2对于数值转换函数操作,只有存在反函数且有单调性才支持约束投影。
如果转换结点α包含函数操作newname=f(oldname),newname在c中出现,f存在反函数且单调,首先使用3.0中的方法进行投影,属性变量替换时使用f(oldname)替换newname,得到的约束中可能会包含函数操作符f,通过求f的反函数,根据单调性,生成等价约束。如果newname不在c中出现,直接应用3.0的方法即可。
比如得到 f单调递增,可以得到
根据输入输出的个数划分,转换操作可以分为四类:(1)一个输入记录集一个输出记录集(2)多个输入记录集一个输出记录集(3)一个输入记录集多个输出记录集(4)多个输入记录集多个输出记录集。对于多输出记录集的情况,只有在操作为“分流”时才会出现,即同样一份数据被复制为多份,发送到不同的输出表。因为是数据的复制,输出端多个表的约束可以合并起来,然后采用与(1)(2)相同的方法进行转换,因此本发明只提供(1)(2)的约束投影方法。
操作中是否包含函数对约束投影的方法影响很大,因为函数会增加投影的难度。“多输入单输出”的操作都是无函数操作,包含union,intersection,difference,product,join。
而“单输入单输出”的操作按照是否包含函数来划分,又可以分为两类:
无函数操作::selection,projection,rename,add field。
函数操作:aggregate,function application。
约束的类型也对约束投影方法产生影响,因此投影方法还要根据不同的约束类型来划分。单元组约束指只含一个关系谓词的否定约束,比如NotNull。多元组约束包含多个关系谓词,指多个元组需要同时满足的约束,比如函数依赖。
对于不能进行约束投影的操作,是因为无法定为对应的属性或元组,比如集合差操作A-B=C,我们知道C需要满足的约束c,但是无法通过否定约束形式来定位与C中数据对应的A或B中的元组,因此无法进行投影。对于连接操作,如果A或B中存在不符合A连接B条件的元组,也无法定位与结果对应的元组,约束条件无法投影。对于这些情况可以通过扩展filter表达能力的方法来实现,比如如果无法使用否定约束表示元组选择条件,可以添加一个辅助表,来记录满足条件的元组的主键key。在具体实施时可以参考。对于非关系代数操作,用户如果可以标注出mapping和filter,也可以引用3.0中的方法进行投影。
应用上面的投影方法,可以按照下面的步骤得到实施例数据源端的数据约束。
A7:
转换结点A7将Salary从RMB转换为美元,应用3.2中的方法,得到 newSalary=f(Salary)=Salary/7,f-1(newSalary)=newSlary*7,因此得到 对于其他约束,不受A7的影响。
A6:日期转换函数对各约束无影响,应用3.2中的方法
A5:投影操作,结果记录集的属性都来自于输入记录集,应用3.0中的方法
A4:Add操作不影响各约束,应用3.0中的方法
上面三步投影操作得到:
A3:连接操作,但是filter为空,应用3.0中的方法得到:
A2:聚集函数,但是约束不含聚集函数结果,应用3.1中的方法得到rules(resultTable(A1))={}
A1:连接操作,但是filter为空,应用3.0中的方法得到
rules(department)={}
4.异常数据检测
将得到的数据源的每个数据约束转换为独立的SQL语句,检测违反数据约束的数据。
对于实施例,可以采用下面的检测操作:
Select*from employee where salary<350000
Select*from employee where salary IS NULL
5.ETL流程质量控制
每个结点的数据约束可以提供给设计者,辅助其进行异常数据控制。有的数据约束无法推送到数据源,在每个数据约束传播的最后停止点进行异常数据检测,也可以提高ETL的执行效率。