一种etl流程中异常数据检测方法

专利名称：：一种etl流程中异常数据检测方法
技术领域：
：本发明涉及一种数据检测方法，尤其涉及一种ETL流程中异常数据检测方法，属于数据仓库构建中的ETL过程领域。
背景技术：
：ETL(Extract,Transform,andLoad)是构建数据仓库的重要过程，它抽取数据源中相关数据，对数据进行转换使其满足数据仓库的需求，并将数据加载到目的端的数据仓库中。最初，ETL过程是手工编码实现，维护困难。当前，数据库和数据集成厂商提供了功能丰富的ETL工具。这些工具可以分为两类一类工具支持图形化的ETL流程设计并且提供独立的引擎执行ETL任务，代表工具有Informatica的PowerCenter，IBM的DataStage和Microsoft的SSIS;另一类工具，也叫ELT(Extract，Load,Transform),ELT支持数据源端和目的端的模式映射并且将ETL任务转换为SQL语句，存储过程交由DBMS执行，代表工具有Oracle的DataIntegrators尽管ETL工具支持ETL流程设计，但实际应用中只能依靠数据源模式来设计转换规则，数据库中很多数据并不一定符合模式，这些不符合模式的数据称为"异常数据"，异常数据会让ETL过程崩溃，或者将错误带入到数据仓库中。当前异常数据主要由人工来分析处理，所以很难定位复杂ETL流程中的所有异常数据，很难保证数据质量。首先，数据仓库周期性的从多个数据源更新大量数据，而这些数据源通常是分布式异构的，因此一些数据源有很大概率包含不一致或者冲突的信息。其次，目的端数据库会对加载的数据定义一些约束，而源端的数据经过转换后可能仍然会违反这些约束。很多数据质量工具与学术研究提出了异常数据检测和数据清理的方法，主要通过以下四种方法:数据分析(dataanalysis),数据清理(datacleansing),数据质量监控(dataqualitymonitor)和数据采样(datasampling)。数据分析通过分析数据实例来得到数据的特点和数值模式，从而帮助检测异常数据和进行模式映射。数据清理进行异常数据过滤和重复记录消除，也包含各种提高数据质量的操作。数据清理操作通常与业务紧密结合，需要用户来决策，而且只能清理用户已发现的异常数据。数据质量监控在ETL流程的各个活动及加载过程定义数据质量规则和处理策略，系统发现数据违反质量规则后，根据制定的策略来终止ETL过程，或者调用相应的数据清理操作。然而这些质量规则都需要用户来定义，多数情况并不能定义完善的质量规则来检测所有的异常数据。数据采样设计好ETL流程后，对一些样本数据进行处理，来找到异常问题，然后对ETL流程的设计进行优化。虽然这个方法在实践中经常采用，但是并不容易找到高质量的样本数据集。这些方法不能保证对大规模数据进行转换、加载后满足目的端的约束条件，而且都需要人工对ETL过程进行分析来设计数据源和目的端的映射规则。为了保证加载的数据不违反这些约束，不能期望用户设计完善的ETL过程。最好可以自动化的将异常数据过滤掉，并且越早越好。但是这个问题比想象中复杂。已有的ETL工具都不支持ETL流程上的推理，我们需要一种自动化的方法，它可以分析ETL流程和目的端的数据约束，在ETL流程中尽可能早的地方进行约束验证。
发明内容本发明的目的是提出一种ETL流程中异常数据检测方法，其自动化的分析ETL流程并将目的端的数据约束沿ETL流程反向传播到数据源端，然后对数据源进行异常数据检测。本发明的技术方案可以表示为图1，具体步骤如下1.将ETL工具设计的ETL流程转换为ETL抽象图。ETL抽象图是一种新的数据流程模型，它包含两种结点关系结点(relationnode)和转换结点(transformationnode)。关系结点表示关系记录集，包括数据源、目的端数据库及各操作的输出记录集。转换结点表示数据转换操作，包含输入记录集模式与输出记录集模式的属性映射关系及元组选择条件。ETL抽象图中的边为有向边，连接各结点，记录数据流向。给定一个ETL流程Q，它对应的ETL抽象图G(Q)按照下面的步骤构建1)对Q中每一个数据转换操作创建一个转换结点a，输入记录集为^,...，1^，输出记录集为O，根据其操作语义，生成每个输入记录集模式Ik与输出记录集模式O的属性映射关系mapping(a,Ik,O)和元组选择条件filter(a,lk,0)，k取值于1…n。mapping(a,Ik,O)的一个例子表示为Ik〈kbk2,…,kn〉一〉a0〈0h02，."0n〉。尖括号内为各模式的属性，表示两边属性的对应关系。左边的属性可以包含函数操作。filter(a,Ik,O)的一个例子表示为Ik(k^pA…Akn=q)—>aO。其中的括号内为元组选择条件，为built-in谓词的合取式。可以表示大多数select条件。对于基本关系代数操作，可以参考对应的mapping和filter的生成方法，参见实施例中表1。2)对于每个转换结点a，创建一个关系结点resultTable(a)来表示该转换操作的输出记录集，并初始化其数据约束集合mles(resultTable(a))为空。3)根据Q中数据流向在关系结点和转换结点之间添加有向边。2.获取目的端的数据约束，表示为统一形式的否定约束(denialconstraints):vj^,…^^(/;(S)A…Ag(:)A(ii)(S,…^》。A表示关系i胃i司，^表示元纟且，伊表示多个built-in谓词(<，>，=,《,》，#等)的合取式。可以表达以下常见约束类型非空(NotNull)，值域(ValueDomain),函数依赖(FunctionDependency),Check(SQL语句支持的约束定义关键词)等。数据约束可以从数据库管理系统自动抽取或由用户自定义。目的端可以包含多个数据约束。传播过程中各记录集的数据约束也为统一的否定约束形式。3.以目的端数据库为起点，沿数据流的反方向，将每个转换结点的输出记录集上的数据约束反向投影到它的各输入记录集上，直到数据源为止。如果转换结点a包含无函数的mapping和确定的filter，数据约束反向投影过程含以下步骤1)对于一个数据约束c，它只含有输出记录集O的属性变量，对于一个输入记录集Ik,根据mapping(a,Ik,O)中属性映射关系，使用Ik的属性变量对c中变量进行替换得到c'。2)如果c'仍包含O的属性变量，那么c无法投影到Ik,c在该边的传播过程终止。3)如果c'只包含Ik的属性变量，且filter(a,Ik,O)为空，添加c'到Ik的数据约束集合rules(Ik)。4)如果filter(a,Ik,O)不为空，将filter(a，Ik，O)中的元组选择条件添加到c，的合取式中，得到c"添加c"到Ik的数据约束集合mles(Ik)。5)对于每个数据约束，依次向每个输入记录集执行上面反向投影操作。如果转换结点的mapping包含函数或者filter无法确定，需要采用特定的反向投影方法，在实施例中给出了针对基本关系操作的投影方法。4.使用得到的数据源端的数据约束集合进行数据检测，将数据约束转换为SQL语句来定位违反约束的数据，用户可以进行数据清理。5.投影得到的每个转换结点的输出记录集的数据约束可以提供给设计者用于ETL流程质量监控。本发明的积极效果为采用本发明的方法，可以在设计好ETL流程后，将目的端的数据约束需求投影到数据源端，来检测数据源端中的非法数据，用户定位到非法数据后，一方面可以对数据源进行合适的数据清理与数据过滤，一方面可以提示用户对ETL流程进行必要的修改来保证加载数据的质量。传播得到的数据约束是数据经过ETL过程后成功加载的必要条件，因此可以大大提高ETL过程的效率。图l是ETL流程中数据约束传播技术方案图；图2是实施例的ETL流程图；图3是实施例的ETL抽象图。具体实施例方式下面结合附图和实施例对本发明做进一步说明。假定一个美国公司在中国有一个分公司，在本地记录雇员和部门的信息。现在要将该分公司的数据集成到美国总部的一个数据仓库DW中。分公司数据库包含两个表employee(EId,Ename,Address,DeptID,StartDate,Salary)department(Deptld，DeptName,Directorld，Revenue)关系employee的每个元组记录一个雇员的信息EId是唯一编号，主键；EName是雇员名字；Address记录雇员的住址；Deptld是雇员部门的ID，外键；StartDate是雇员来部门开始工作的日期，格式为"YYYY-MM-DD";Salary是员工的年工资，人民币(Y)。关系department每个元组记录一个部门的信息DeptID是部门唯一编号；DeptName是部门名称；Directorld是部门领导的ID;Revenue是部门的年收入，人民币(Y)。目的端数据仓库DW位于美国，包含一个表seniorEmpInLargeDept(EId,EName，StartDate,Salary,DeptName,Size,Source)每个元组记录一个雇员信息，StartDate的格式为"MM-DD-YYYY"，Salary的单位是美元，Size表示部门的雇员人数，Source表示数据的来源。来自分公司的数据都需要进行处理后才可以加载到数据仓库中。可以使用一个ETL工具设计如图2所示的ETL流程图。其中三角形表示数据处理操作。为了描述方便，假定每一个操作X后面存在一个临时的结果集resultTable(X)。具体的操作细节如下Al:使用外键D印tld等值连接表employee和departmentA2:使用Deptld，DeptName进行分组，求雇员的个数SizeA3:连接表resultTable(A2)和employeeA4:为表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:所有部门雇员人数最少为10DW-IC3:Salary不能为空对于该实施例，我们期望根据DW的数据约束，得到数据源employee和department需要满足的数据约束，可以使用本发明的方法来求解。具体步骤如下1.构造ETL抽象图ETL流程主要描述数据处理操作的顺序与操作语义。而ETL抽象图重点记录每个数据处理操作输入记录集与输出记录集属性的映射关系和元组的选择条件，根据这两个条件可以定位与输出记录集对应的数据，来得到对应的数据约束条件。基本关系代数操作可以表达基本的关系数据操作语义，是各种数据转换函数的基础，因此我们给出这些基本操作的属性映射关系(mapping)和元组选择条件(filter)的生成方法，如表1所示。转换结点为a，1<^，&2,...，&11>表示输入记录集，O表示输出记录集，Ii,...，In表示多个输入记录集。不考虑多个输出记录集的情况。表1、关系代数操作的mapping与filter生成方法<table>tableseeoriginaldocumentpage9</column></row><table>根据该表，构造该实施例的ETL抽象图，如图3所示。图中转换结点对应的mapping和filter为Al:l)employee<EId，Ename,Address，DeptId，StartDate,SaIary>~>A1resultTable(Al)<EId,Ename,Address，DeptId,StartDate，Salary>2)department<DeptId,DeptName,DirectorId，Revenue>~>A1resultTable(A1)<DeptId,DeptName，DirectorId,Revenue>3)filter为空A2:l)resultTable(Al)<DeptId,DeptName,Revenue,EId>resultTable(A2)<DeptId,DeptName,Revenue，Size>，Size-COUNT(EId)2)filter为空A3:l)resultTable(A2)<DeptId,D印tName,Revenue,Size>~~>A3resultTable(A3)<DeptId,DeptName，Revenue,Size>2)employee<EId,Ename,Address,DeptId,StartDate,Salary>—>^resultTable(A3)<EId，Ename，Address，DeptId,StartDate,Salary>3)filter为空A4:l)resultTable(A3)<EId,Ename，Address,DeptId,StartDate，Salary，DeptName,Size〉——>A4resultTable(A4)<EId,Ename,Address,DeptId,StartDate，Salary,DeptName,Size>2)filter为空A5:l)resultTable(A4)<EId,Ename,StartDate,Salary，DeptName，Size,Source>—>A5resultTable(A5)<EId，Ename，StartDate,Salary,DeptName，Size,Source>2)filter为空A6:l)resultTable(A4)<EId，Ename,StartDate,Salary,DeptName,Size,Source>—>A6resultTable(A5)<EId,Ename，f(StartDate)，Salary,DeptName，Size,Source>f("YYYY-MM-DD，，)="MM-DD-YYYY"2)filter为空A7:l)resultTable(A4)<EId，Ename,StartDate,Salary,DeptName，Size,Source〉_>A7resultTable(A5)<EId,Ename，StartDate,f(Salary),DeptName,Size,Source>f(Salary)=Salary*72)filter为空2.获取目的端的数据约束并转化为统一形式根据该实施例中目的端的数据约束，将其转化为否定约束形式。不引起歧义时，为了简化表示，省略了全称量词和谓词名称。ruleS(DW)={，(Salaiy<50000)，，(Size<10),，(Salary=Null)}3.数据约束反向传播对于包含无函数mapping及确定的filter,只需要对filter转化为合取式，就可以采用
发明内容中步骤3的方法进行约束反向投影。3.0对于包含无函数mapping及确定的filter,使用
发明内容步骤3方法投影。对于包含函数的mapping和不确定的filter,因为其投影性质与具体的操作语义相关，因此我们给出聚集操作和函数操作的约束投影方法。下面仍然使用相同的符号。数据转换操作为转换结点Ot，输入记录集为化，…，W,输出记录集为O。一个约束为c。3.1对于聚集操作，只有一个输入记录集I，如果c中包含函数max或min的结果，在部分情况下可以投影得到约束条件。对于其他聚集操作，因无法对应到聚集的输入元组，因此都无法投影。如果c中不含聚集操作的结果，那么可以利用3丄3投影。3.1.1:如果转换结点a包含聚集操作min(attr)，C包含，(min(aur)2val)(或，(min(attr)〉val))，那么将其替换为，(attrhal)(或，(attr^a1))，然后应用3丄3。3.1.2:如果转换结点(X包含聚集操作max(attr)，C包含，(max(attr)Sval)(或，(max(attr)〈val))，那么将其替换为，(attr"al)(或，(attxval))，然后应用3丄3。3.1.3:如果c中不含聚集操作的结果,那么可以将聚集函数和分组函数去掉，转化为selection操作，使用3.0中的方法来投影。3.2对于数值转换函数操作，只有存在反函数且有单调性才支持约束投影。如果转换结点a包含函数操作newname-f(oldname)，newname在c中出现，f存在反函数且单调，首先使用3.0中的方法进行投影，属性变量替换时使用f(oldname)替换newname,得到的约束中可能会包含函数操作符f，通过求f的反函数，根据单调性，生成等价约束。如果newname不在c中出现，直接应用3.0的方法即可。比如得到，(f(attr)hal)，f单调递增，可以得到，((attr)2厂'(val))。根据输入输出的个数划分，转换操作可以分为四类(l)一个输入记录集一个输出记录集(2)多个输入记录集一个输出记录集(3)—个输入记录集多个输出记录集(4)多个输入记录集多个输出记录集。对于多输出记录集的情况，只有在操作为"分流"时才会出现，即同样一份数据被复制为多份，发送到不同的输出表。因为是数据的复制，输出端多个表的约束可以合并起来，然后采用与(1)(2)相同的方法进行转换，因此本发明只提供(1)(2)的约束投影方法。操作中是否包含函数对约束投影的方法影响很大，因为函数会增加投影的难度。"多输入单输出"的操作者P是无函数操作，包含union,intersection,difference,product,join。而"单输入单输出"的操作按照是否包含函数来划分，又可以分为两类无函数操作selection,projection,rename,addfield。函数操作aggregate,fiinctionapplication。约束的类型也对约束投影方法产生影响，因此投影方法还要根据不同的约束类型来划分。单元组约束指只含一个关系谓词的否定约束，比如NotNull。多元组约束包含多个关系谓词，指多个元组需要同时满足的约束，比如函数依赖。对于不能进行约束投影的操作，是因为无法定为对应的属性或元组，比如集合差操作A-B=C,我们知道C需要满足的约束c,但是无法通过否定约束形式来定位与C中数据对应的A或B中的元组，因此无法进行投影。对于连接操作，如果A或B中存在不符合A连接B条件的元组，也无法定位与结果对应的元组，约束条件无法投影。对于这些情况可以通过扩展filter表达能力的方法来实现，比如如果无法使用否定约束表示元组选择条件，可以添加一个辅助表，来记录满足条件的元组的主键key。在具体实施时可以参考。对于非关系代数操作，用户如果可以标注出mapping和filter,也可以引用3.0中的方法进行投影。应用上面的投影方法，可以按照下面的步骤得到实施例数据源端的数据约束。A7:rules(reSUltTable(A7))=ruleS(DW)={，(Salaiy<50000)，，(Size<10),，(Salary=Null)}转换结点A7将Salary从RMB转换为美元,应用3.2中的方法,得到i(《Saiao^50000),newSalary=f(Salary)=Salary〃,f'(newSalary)二newSlar)^7,因此得至[J，(Saiary〈350000)对于其他约束，不受A7的影响。nileS(reSultTable(A6))=ruleS(DW)={，(Salary<350000),，(Size<10)，，(Salary=Null)}A6:日期转换函数对各约束无影响，应用3.2中的方法A5:投影操作，结果记录集的属性都来自于输入记录集，应用3.0中的方法A4:Add操作不影响各约束，应用3.0中的方法上面三步投影操作得到rules(resultTable(A5))=rules(resultTable(A4))=rules(resultTable(A3))={，(Salary<350000),，(Size<10),，(Salary=Null)}A3:连接操作，但是filter为空，应用3.0中的方法得到rules(resultTable(A2))={，(Size<io)}rules(employee)={，(Salary〈350000),，(Salary=Null)}A2:聚集函数，但是约束不含聚集函数结果，应用3.1中的方法得到rules(resultTable(Al))-{}Al:连接操作，但是filter为空，应用3.0中的方法得至!jrales(employee)={，(Salary<350000)，，(Salary=Null)}rules(department)={}4.异常数据检测将得到的数据源的每个数据约束转换为独立的SQL语句，检测违反数据约束的数据。对于实施例，可以采用下面的检测操作Select*fromemployeewheresalary<350000Select*fromemployeewheresalaryISNULL5.ETL流程质量控制每个结点的数据约束可以提供给设计者，辅助其进行异常数据控制。有的数据约束无法推送到数据源，在每个数据约束传播的最后停止点进行异常数据检测，也可以提高ETL的执行效率。权利要求1.一种ETL流程中异常数据检测方法，其步骤为1)将ETL工具设计的ETL流程转换为ETL抽象图；所述ETL抽象图包括关系结点和转换结点，各结点通过有向边连接，记录数据流向；所述关系结点表示关系记录集，包括数据源、目的端数据库及各操作的输出记录集；所述转换结点表示数据转换操作，包含输入记录集模式与输出记录集模式的属性映射关系及元组选择条件；2)将目的端的数据约束转换为统一形式的数据约束；3)以目的端数据库为起点，沿数据流的反方向，将目的端统一形式的数据约束反向投影到数据源端；4)使用得到的数据源端的数据约束集合进行数据检测。2.如权利要求1所述的方法，其特征在于所述数据转换操作的类型为一个输入记录集对应一个输出记录集O或多个输入记录集对应一个输出记录集O。3.如权利要求2所述的方法，其特征在于所述ETL抽象图的建立方法为1)对ETL流程Q中每一数据转换操作创建一转换结点a，其输入记录集为历，…,In〉，输出记录集为O;根据流程Q的操作语义，生成每个输入记录集模式Ik与输出记录集模式O的属性映射关系mapping(a，Ik，O)和元组选择条件filter(a,Ik,O);其中1《k《n，n为自然数；2)对于每个转换结点a，创建一个关系结点resultTable(a)来表示该转换操作的输出记录集，并初始化其数据约束集合rules(resultTable(a))为空；3)根据Q中数据流向在关系结点和转换结点之间添加有向边。4.如,权利要求3所述的方法，其特征在于通过将每个转换结点的输出记录集上的数据约束反向投影到它的各输入记录集上，进行所述反向投影I)如果所述转换结点a包含无函数的mapping和确定的filter，则a)根据mapping(a，Ik,O)中属性映射关系，使用输入记录集Ik的属性变量对约束数据c中输出记录集O的属性变量进行替换得到c';b)检査c'中的属性变量，如果c'仍包含O的属性变量，那么c无法投影到Ik，c在该边的传播过程终止；如果c'只包含Ik的属性变量，且filter(a，Ik，O)为空，添加c'到Ik的数据约束集合rules(Ik);c)如果filter(a，Ik,O)不为空，将filter(a,Ik，O)中的元组选择条件添加到c'的合取式中，得到c"添加c"到Ik的数据约束集合rules(Ik);d)对于每个数据约束，依次向每个输入记录集执行上述反向投影操作；II)如果所述转换结点a包含的mapping含有函数和不确定的filter,贝廿a)如果转换结点(X包含聚集操作min(attr)，若数据约束C包含，(min(attr^val)，则替换其为，(attrhal)，若数据约束C包含，(min(attr)〉val)，则替换其为，(attr>val);如果转换结点(X包含聚集操作max(attr)，若数据约束C包含，(max(attr)^al)则替换其为，(attrSval)，若数据约束C包含，(max(attr)〈val)则替换其为，(attKval);经上述替换后，如果C中不含聚集操作的结果，则将聚集函数和分组函数去掉，转化为selection操作，后采用I)中的方法投影；b)如果转换结点a是数值转换函数操作，同时该数值转换函数存在反函数且有单调性，则采用I)中的方法投影。5.如权利要求4所述的方法，其特征在于所述步骤II)的步骤b)中，如果对数值转换函数f投影过程中得到的c'仍包含O的属性变量，则属性变量替换时使用函数f替换对应O的属性变量,然后通过求f的反函数，根据单调性，生成等价约束。6.如权利要求4所述的方法，其特征在于所述反向投影中，对于集合差操作或连接操作，通过在filter中添加一个辅助表，用来记录满足条件的元组的主键进行反向投影。7.如权利要求1所述的方法，其特征在于所述统一形式的数据约束为否定约束；所述否定约束的类型包括非空、值域、函数依赖、SQL语句支持的约束定义关键词。8.如权利要求7所述的方法，其特征在于所述否定约束的具体形式为));其中A表示关系谓词、^表示元组、炉表示多个built-in谓词的合取式；所述built-in谓词包括<、>、=、《、》、#。9.如权利要求1所述的方法，其特征在于所述数据检测的方法为将数据源端的每个数据约束转换为独立的SQL语句，检测违反数据约束的数据。全文摘要本发明公开了一种ETL流程中异常数据检测方法，属于数据仓库构建领域。本发明的方法为1)将ETL工具设计的ETL流程转换为ETL抽象图；ETL抽象图包括关系结点和转换结点，各结点通过有向边连接，记录数据流向；关系结点包括数据源、目的端数据库及各操作的输出记录集；转换结点包含输入记录集模式与输出记录集模式的属性映射关系及元组选择条件；2)将目的端的数据约束转换为统一形式的数据约束；3)以目的端数据库为起点，将目的端的数据约束反向投影到数据源端；4)使用得到的数据源端的数据约束集合进行数据检测。本发明将目的端的数据约束需求投影到数据源端，来检测数据源端中的非法数据，大大提高ETL过程的效率。文档编号G06F17/30GK101533407SQ20091008151公开日2009年9月16日申请日期2009年4月10日优先权日2009年4月10日发明者杰刘,丹叶,虞海江,峻魏,涛黄申请人:中国科学院软件研究所