一、前言
大数据资源数据清洗、装载以及交换的过程中,有些数据可能跟我们预计的格式不相符,需要进行基于规则的匹配、检测,筛选出正常数据(匹配所有规则)、问题数据(违反一个或多个规则)以及基于某些规则的正常数据和问题数据。这种基于规则的匹配和筛选,从而可以保证数据入库前的数据质量。
数据质量的使用,需要首先进行规则的创建,这些规则可以在不同的质量检测节点中进行复用,自定义的修改;然后再进行数据字段上的检测规则的设置和修改。每个字段可以设置一个或多个规则。
每个质量检测节点根据设置的规则,可以在节点的输出配置中,根据需求配置所需的数据输出,正常数据或问题数据的输出。
质量检测节点每次运行完毕,会有一个统计信息,记录此次数据交换的批次号、该批次交换数据总数、正常数据总数、问题数据总数以及异常数据总数等信息。
二、准备工作
数据质量检测物理表及数据准备
1、创建被检测物理表TEST
CREATE TABLE "DMTEST"."TEST"
(
"TEST1" INTEGER,
"TEST2" INTEGER,
"TEST3" INTEGER) STORAGE(ON "DMTS01", CLUSTERBTR) ;
2、创建质量检测结果统计表
CREATE TABLE "DMTEST"."TESTQUALITY_STATISTICS"
(
"EXCHAGE_ID" VARCHAR2(50),
"BATCH_ID" VARCHAR2(50),
"EXCHANGE_COUNT" INTEGER,
"PROBLEM_COUNT" INTEGER,
"VALID_COUNT" INTEGER,
"EXCEPTION_COUNT" INTEGER,
"STATISTICS_TIME" DATETIME(6)) STORAGE(ON "DMTS01", CLUSTERBTR) ;
3、导入被检测表数据
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(1,1,1);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,2,2);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(2,11,3);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,12,4);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(3,13,5);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,14,6);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(4,15,7);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,16,8);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(5,17,11);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,18,12);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(6,19,13);
INSERT INTO "DMTEST"."TEST"("TEST1","TEST2","TEST3") VALUES(10,20,14);
本次使用达梦数据库来作为数据质量检测演示,以上内容创建完成后,将数据库物理表加载到达梦交换工具DMETL中,如下图:
三、数据质量规则管理
3.1 数据质量规则介绍
规则按整体类型,大致分为一致性、完整性、格式化、重复性以及自定义规则等五大类型,根据实际需求也可以进行动态扩充。现在支持如下规则:
规则 | 规则说明 |
---|---|
枚举 | 检查所选择的列值是否在枚举值范围内 |
序列 | 检查所选择的列值是否在序列范围内 |
引用数据集 | 检查所选择的列值是否在引用数据集中 |
字段长度检查 | 检查列值的内容长度是否符合规则定义长度值,比较类型有等于,不等于,大于,小于,大于等于,小于等于 |
内容大小检查 | 检测列值是内容是否服务规则定义的值,比较类型有等于,不等于,大于,小于,大于等于,小于等于,该规则按照字符串进行比较 |
内容匹配检查 | 检查列值内容中是否包含某些字符串 |
空值检查 | 检查列值为空或者不为空,在规则中,null类型的值和长度为0的字符串都会被认为是空值 |
数字范围检查 | 检查列值是否在某个数字范围中,该规则处理数字类型的值 |
Email检查 | 检查某个字段内容是否是Email值 |
身份证号码检查 | 检查列值是否是正确的身份证号码 |
邮政编码检查 | 检查列值是否是正确的邮政编码 |
日期时间格式检查 | 检查日期时间格式是否正确 |
数字格式检查 | 检查数字格式是否正确 |
列值重复检查 | 检查列值是否重复 |
正则表达式 | 检查列值是否符合某个正则表达式 |
函数表达式匹配 | 检查函数表达式的值,在使用函数表达式匹配时,首先新建一个函数表达式规则,如果需要使用数据质量检测节点上的列信息,则还需要在数据质量检测节点上加入规则时,修改表达式,加入需要的列 |
在提供的规则不足以满足质量检测的情况下,可以使用系统函数或自定义函数或函数表达式来匹配检测数据。所设置的带有函数的表达式被解析后的值必须为布尔型,函数的参数应该带有被检测的字段名。比如要检测TEST1列的绝对值是否与TEST2列的绝对值相等,可以使用系统函数abs(Number number),那么检测表达式应该为abs(TEST1) == abs(TEST2)。
3.2 规则的创建与修改
按照对字段的质量要求,创建不同的质量检测规则,创建界面如下图所示:为了检测字段的数值范围,创建了如下三个规则。
四、数据质量检测及统计
4.1 数据质量检测转换配置
配置好规则后,将添加的数据质量检测TEST表以及数据质量检测插件添加到转换中,如下图所示:
4.2 节点输出配置
在转换创建好后,活动节点右键菜单,“输出配置”点击,出现如下界面:
该节点一个配置了3个规则,输出配置出现5+3个默认输出。有5个默认输出是固定的,包括默认输出(输出所有的输入数据)、正常数据的默认输出、问题数据的默认输出(违反一个或多个规则,但只输出一次)、带有检测标记的所有输入数据的输出、带有检测标记的问题数据的输出。然后每个规则会有一个违反该规则的默认输出。
- “默认输出”输出所有的输入数据,没有利用规则检测;
- “默认输出 – 违反规则大于10”输出所有TEST2字段值违反规则“大于10”的输入数据;
- “默认输出 – 违反规则小于10”输出所有TEST3字段值违反规则“小于10”的输入数据;
- “默认输出 – 违反规则等于10”输出所有TEST1字段值违反规则“等于10”的输入数据;
- “默认输出 – 正常数据(匹配所有的规则)”输出所有TEST1字段值等于10,并且TEST2字段值大于10,并且TEST3字段值小于10的输入数据。
- “默认输出 – 问题数据(违反一个或多个规则,但只输出一次)”,只要违反了某一个规则的输入数据,只会输出一次,不管违反了多少个规则。
- “默认输出 – 所有数据(带有检测标记)”,输出所有的输入数据,但是输出数据默认增加了两个字段“IS_PROBLEM”和“DATA_ID”
- “默认输出 – 问题数据(带有检测标记)”问题数据的输出,但是输出数据默认增加了五个字段:
“BATCH_ID”、“DATA_ID”、“RULE_ID”、“RULE_NAME”、“RULE_COL_NAME”,针对每一个违法的规则,都会对应一条输出,也就是该条输入数据违反了多少条规则,就会对应多少条输出。相对于其它非质量检测节点,该节点增加:
“qualityBatchId”、“qualityDataId”、“isProblemData”、“qualityRuleName”、“qualityRuleId”以及“qualityColumnName”六个局部变量。 -
- “qualityBatchId”表示该次数据交换的批次ID。
-
- “qualityDataId”表示每条被检测数据唯一ID标识。
-
- “isProblemData”表示被检测数据是否问题数据。
-
- “qualityRuleName”表示问题数据使用的检测规则名。
-
- “qualityRuleId” 表示问题数据使用的检测规则ID。
-
- “qualityColumnName” 表示问题数据使用的规则检测在哪个字段上。
带有标记的所有数据或问题数据的输出,增加的字段值表达式对应的局部变量如下两图:
节点输出配置的字段可根据实际需求再做增加或删除。
- “qualityColumnName” 表示问题数据使用的规则检测在哪个字段上。
4.3 执行检测及检测结果统计
每批数据通过质量组件检测完毕后,会有一个统计信息,记录此次数据交换的批次号、该批次交换数据总数、正常数据总数、问题数据总数以及异常数据总数等信息。
质量统计增加了如上图所有的六个局部变量,供统计表字段值表达式使用
“qualityBatchId”表示此次数据交换的批次号。
“qualityExchangeCount”表示该批次交换数据总数。
“qualityProblemCount” 表示该批次问题数据总数。
“qualityValidCount” 表示该批次正常数据总数。
“qualityExceptionCount” 表示该批次异常数据总数。
五、元数据统计
执行完之后,在数据质量检测节点上点击鼠标右键,可以打开统计信息对话框,如下图:
统计信息对话框如下图所示:
在统计信息对话框中,可以查询汇总信息,日报信息,周报信息和月报信息。
汇总:汇总信息表示从第一次执行开始符合查询条件的所有的统计信息
日报:日报信息表示以天为单位记录的统计信息
周报:周报信息表示以周为单位记录的统计信息
月报:月报信息表示以月为单位记录的统计信息
设置好查询条件后点击查询按钮就可以查询出符合条件的统计信息,这里以汇总信息为例,查询所有的统计信息,结果如下图:
查询出来的统计信息对应于上面设置的3列的数据质量检测统计信息,表中列出了列名,规则名,检测次数,检测总数(总数据行数),匹配数量(匹配数据行数)等。
再用鼠标点击表格中的每行数据时,会在下表中显示具体的详细信息,如点击第二行,则下表的显示结果为:
详细信息可以分页显示,点击下面的分页按钮可以分页查看详细信息。
此外如果检测次数比较多,涉及数据量较大时,可以手动清除数据,如下图,选择所要清除的数据之后,点击鼠标右键会出现清除菜单,选择清除就可以清除所选择的详细信息数据。
除了手动删除,还可以自动删除数据,在数据质量检测节点的属性对话框中,选择常规属性对tab页,如下图:
选择自动删除,然后在后面的输入框中输入数值n,就会在下一次执行的时候自动删除距离执行日期n天以前的检测详细信息数据。
六、总结
根据设置的字段值过滤规则,对字段值不同的数据可做不同的处理,完成了对TEST表的数据质量的检测工作。在质量检测过程中,还可以把检测后符合不同规则的数据存放到不同的物理表中,形成高质量数据资源。通过对数据资源质量的检测治理,可大大提升整体数据质量,为挖掘数据潜在价值做好数据准备。
DMETL工具可满足多样化的需求,大家可在实际操作中进行摸索,后续也会不定期分享一些在DMETL使用过程中的实战经验。
达梦数据交换工具DMETL在大数据治理质量管理环节中起到很大的作用,对于各类异构数据库形成的数据中心是必不可少的工具。对比一些开源、收费的ETL工具,DMETL具有操作简单、性能稳定的显著特点,有兴趣的大家可以下载使用。