《数据清洗》课件 第6章 数据转换与加载.pdf

数据清洗大数据应用人才培养系列教材第六章数据转换与加载6.1 数据清洗转换6.2 数据质量评估6.3 数据加载6.4 上机练习月实训 习题6.1数据清洗转换第六章数据转换与加载1.数据清洗确定数据清洗的规模，通常采取RDBMS和文本为清洗载体，常见的数据清洗范围包 含：缺失值清洗格式内容清洗逻辑错误清洗，6.1数据清洗转换第六章数据转换与加载(1).缺失值清洗在各类数据源系统中，缺失值的问题时常发生，在一定程度上，造成缺失值的原因在于 系统的不完备性和故障。具体原因较多，主要分为系统原因和人为原因确定范围计算源端数据中字段缺失值比例，之后根据缺失率和重要性分别制 定策略。对于重要性高和缺失率高的数据，可采取数据从其他渠道 补全、使用其他字段计算获取和去掉字段，并在结果中制定策略进 行清洗；对于重要性高但缺失率较低的数据，可采取计算填充、经 验或业务知识估计等策略进行清洗；对于重要性低、缺失率高的数 据，可采取去除该字段的策略进行清洗；对于重要性低且缺失率低 的数据，可以不做处理，6.1数据清洗转换第六章数据转换与加载(1).缺失值清洗确定范围重要性高特征：重要性高，缺失率低 策略：1.通过计算进行填充2.通过经验或业务知识估计低特征：重要性低，缺失率低策略：不做处理或简单填充特征：重要性高，缺失率高策略：1.尝试从其他渠道取数补全2.使用其他字段通过计算获取3.去除字段，并在结果中标明缺失率同I特征：重要性低，缺失率高策略：去除该字段低，6.1数据清洗转换第六章数据转换与加载(1).缺失值清洗去除重要性低的字段重要性低的字段，且缺失严重，可以采取将数据抽取 的结果放入一中间临时库中，在数据清洗之前，先备 份临时库数据，然后直接删除不需要的字段。填充缺失内容某些缺失值补齐采取一定的值去填充缺失项，从而使 数据完备化。通常基于统计学原理，根据决策表中其 余对象取值的分布情况来对一个空值进行填充，例如 用其属性的平均值来进行补充等，6.1数据清洗转换第六章数据转换与加载(2).格式内容清洗数据源系统若为业务系统，则该系统的数据通常由用户填 写，在用户填写数据的过程中，存在全角输入、半角输入、空格符号、错误字段格式等错误时间日期格式清洗当采取多个源端整合数据时，因源端系统的不够严谨，采取 了字符串类型作为数据的存储类型，可能在不同的源中存储 日期、时间的格式不一，导致数据多源抽取到临时表后存在 不同的日期格式，从而导致目标系统无法应用，6.1数据清洗转换第六章数据转换与加载(2).格式内容清洗全角半角清洗全角指一个字符占用两个标准字符位置，半角指一字符占用 一个标准的字符位置。在数据采集时，时常因输入法设置问 题，将字母或者数字输入存储为全角格式。故在对数据进行 ETL操作时，需要进行全角和半角转换，6.1数据清洗转换第六章数据转换与加载(2).格式内容清洗不应有的字符在源端系统中，数据采集时因人为原因可能存在一些数据不 应有的字符，例如身份证号码出现非数字和X的情况，中国 人的姓名出现西文字符、阿拉伯数字等情况。此类问题的解 决需要采取半自动+人工方式相结合进行清洗，6.1数据清洗转换第六章数据转换与加载(2).格式内容清洗重新取数某些指标非常重要又缺失率高，且存在其他数据源可以获取,可采取重新抽取不同数据源的数据进行关联对比清洗，6.1数据清洗转换第六章数据转换与加载(2).格式内容清洗内容与字段不匹配端系统同样存在数据与该数据的字段表达意义不符的现象,该类问题主要来源于源端业务系统的缺陷，6.1数据清洗转换第六章数据转换与加载(3).逻辑错误清洗排重清洗数据排重是指在数据中查找和删除重复内容，而不会影响其 保真度或完整性。数据排重需要技巧，首先一定要有信息去 识别一条数据的唯一性，也就是类似数据库中的主键，如果 唯一性都无法识别，排重也就无所依据，6.1数据清洗转换第六章数据转换与加载(3).逻辑错误清洗去除不合理值不合理数据指在业务系统中收录的部分数据存在不合理性，例如一个大学生的实际年龄不能为5岁。一个员工的年龄也 不可能超过200岁，QQ信息上好友的年龄为0岁等，导致此 类问题的原因可能是业务系统操作失误，也有可能是用户为 进行信息隐藏而故意错填数据。对于不合理的数据，在数据 采集时，若该数据不是很重要，建议直接删除，否则需要进 行人工干预或者引入更多的数据源进行关联识别，6.1数据清洗转换第六章数据转换与加载(3).逻辑错误清洗修正矛盾内容源端系统在提供数据时，存在部分信息可以相互验证的校验，例如，在某教务系统中，教师任课的编号由学期+教工号+课程代码+序号构成，则该号码能够有效地验证当前教师任 课信息中的学期信息、教师信息、课程信息等。同理，身份证 号码也能够有效验证当前人员的出生年月，从而能够推算该人 员的年龄。源端数据存在矛盾且可以利用规则判定的情况，能够通过ETL 工具的规则设置进行查找发现脏数据，从而达到更加容易 清洗的目的，6.1数据清洗转换第六章数据转换与加载2.数据校验数据检验是在数据清洗转换过程中，通过对转换的数据项增加验证约束，实现对数据转换过程的有效性验证。可能存在的数据验证方法有数据项规 则设置、数据类型检验、正则表达式约束检验、查询表检验等。对数据执 行检验后，ETL工具提供验证结果的输出。在Kettle中，可以在数据转换过程中增加数据检验(Data Validator)步骤来完成数据的有效性校验。)6.1数据清洗转换第六章数据转换与加载在Kettle中，可以在数据转换过程中增加数据检验(Data Validator)步 骤来完成数据的有效性校验。Kettle中的数据校验3日期类型验证，6.1数据清洗转换第六章数据转换与加载在Kettle中，可以在数据转换过程中增加数据检验(Data Validator)步 骤来完成数据的有效性校验。Kettle中的数据校验4日期类型验证5正则表达式验证，6.1数据清洗转换第六章数据转换与加载3.错误处理数据错误是指数据在转换过程中出现数据丢失、数据失效和数据的完整性 被破坏等问题。数据出现错误的原因五花八门，有存储设备的损坏、电磁 干扰、错误的操作、硬件的故障等。造成的后果就是会增加大量无用数据 甚至会造成系统瘫痪。因此，人们采取各种手段对数据转换进行优化，尽 可能避免错误产生，6.1数据清洗转换第六章数据转换与加载(1).转换过程错误在设计ETL过程中，存在一些设计未对转换过程进行错误处理，进而造成 ETL执行完成后，目标端的数据未能按照约定数据标准进行组织存储，从 而导致脏数据进入目标端。转换过程错误是在执行ETL过程中发生的 转换错误，该错误一旦发生，应该进入错误处理环节，终止ETL转换，保 证进入目标系统的数据干净可靠。-自GSTART 转换一麴身银0由 中止作业转建1 转换26 6DUMMY中止作业1 中止作业2，6.1数据清洗转换第六章数据转换与加载例63设置定义错误处理作为错误处理方式(1”曾加Exce输出，重命名为Exce忤昔误输出”。(2)在表输出步骤上通过右键快捷菜单选择定义错误处理命令(如图6-17所示)，打开步骤错误处理设置对话框获取变量新建节点连接 表编辑步骤编辑步骤描述日志：二步骤度量性能图 匡Metrics Preview data)0雌辘改变开始复制的数量复制到剪贴板 CTRL-C复制步骤删除步骤 DEL隐藏步骤分离步骤显示输入字段显演出字段Sniff Test During Execution费日日日志通道日志表|Metrics log table1 检查所选步骤)状态 读 写 更新 输入 输出 舍弃 错 定义错误处理Preview.对弁/分散Data Services根据目标步骤生成映射分区集群.)6.1数据清洗转换第六章数据转换与加载例63设置定义错误处理作为错误处理方式(3)设置步骤错误处理设置对话框的参数，指定目标步骤为 Exce忤昔误输出，并选中启用错误处理？复选框，指定相关的错误字段值，6.1数据清洗转换第六章数据转换与加载例63设置定义错误处理作为错误处理方式(4)指定了Exce忤昔误输出后，表输出中遇到的错误就会直接转存到 Exce输出，除了在步骤错误处理设置中指定的字段名之外，表输出 中的字段名也会一并加入Exce输出中-获取变量I回表输入Excel错误输出，6.1数据清洗转换第六章数据转换与加载(2).转换数据错误所谓数据转换，从计算机审计的需求来讲，主要包括两个方面的内容：一 是将被审计单位的数据有效地装载到审计软件所操纵的数据库中；二是明 确地标识出每张表、每个字段的具体含义及其相互之间的关系。而转换数 据错误则出现在数据转换过程中，要想实现严格的等价转换是比较困难的。两种模型在数据转换的过程中会出现各种语法和语义上的错误。命名错误：源端数据源的标识符可能是目的数据源中的保留字。格式错误：同一种数据类型可能有不同的表示方法和语义差异。结构错误：如果两种数据库之间的数据定义模型不同，如为关系模 型和层次模型，则需要重新定义实体属性和联系，以防止属性或联 系信息、的丢失。荽统输吴：不由数据库的同一种数据类型存在精度之间的差异。，6.1数据清洗转换第六章数据转换与加载(2).转换数据错误对于以上数据转换中的错误，可进行相应的处理。对于命名错误，可以先检查数据源中的保留字，建立保留字集合，对于保留字中的命名冲突，根据需要重新命名。对于格式错误，可以从数据源的驱动程序中取出相对应的数据源的 数据类型后，对一些特定的类型进行特殊的处理。对于不同数据库的同一数据类型的精度冲突，类型转换中将类型和 精度结合起来决定源端数据类型和目标数据类型的映射关系。找出 目的数据源中与源端数据源类型的精度最匹配的数据类型作为默认 的映射关系。，6.1数据清洗转换第六章数据转换与加载(3).数据错误数据错误是数据工作者需要注意的指标之一，因为数据错误能导致完全错 误的分析结果。处理数据错误的方法取决于错误出现的原因。数据输入错误：人工在数据收集、记录、输入造成的错误，可能会成为数据中的异常值。测量误差：当使用错误的测量仪器测量时，通常会出现异常值。数据处理错误：当进行数据分析时，错误的数据处理操作可能会造 成异常值。，6.1数据清洗转换第六章数据转换与加载(4).错误处理针对数据错误的处理方法是在转换环节增加数据检验，在执行数据检验过 程中，当检验错误发生时，可以采取如下方法进行错误处理：删除错误数据：如果数据错误是由于数据输入错误、数据处理错误 或数据错误数目很少造成的，可以采取直接删除错误数据的方式处 理。错i吴数据替换：类似于替换缺失值，我们也可以替换错误数据。可 以使用均值、中位数、众数替换方法。分离对待：如果数据错误的数目比较多，在统计模型中我们应该对 它们分别处理。一个处理方法是异常值一组，正常值一组，然后分 别建立模型，最后对结果进行合并。大数据应用人才培养系列教材第六章数据转换与加载6.1 数据清洗转换6.2 数据质量评估6.4 上机练习与实训习题 6.2数据质量评估第六章数据转换与加载1.数据质量评估数据质量是保证数据应用的基础，我们提出了一些数据质量的评估指标。在进行数 据质量评估时，要根据具体的数据质量评估需求对评估指标进行相应的取舍。完整性2一致性3准确性及时性 6.2数据质量评估第六章数据转换与加载2审计数据审计数据就是对被审计单位的数据进行采集、预处理以及分析，从而发现审计线 索，获得审计证据的过程。审计数据有多种不同的处理方法数据查询审计抽样统计分析数值分析大数据应用人才培养系列教材第六章数据转换与加载6数据清洗转换6.2 数据质量评值6.3 数据加载6.4 上机练习巨实训习题，6.3数据加载第六章数据转换与加载1.概念数据加载是继数据抽取和转换清洗后的一个阶段，它负责将从数据源中抽 取加工所需的数据，经过数据清洗和转换后，最终按照预定义好的数据仓 库模型，将数据加载到目标数据集市或数据仓库中去，可实现SQL或批量 加载。大多数情况下，异构数据源均可通过SQL语句进行insert、update.delete操作。而有些数据库管理系统集成了相应的批量加载方法，如SQL Server的bcp、bulk等,Oracle的sqlldr,或使用Oracle的plsql工具中的 import完成批量加载。大多数情况下会使用SQL语句，因为这样导入有 日志记录，是可回滚的。但是，批量加载操作易于使用，并且在加载大量 数据时效率较高。当异构数据源浮中类繁多，且数据仓库模型复杂时，使用专业的ETL工具 必将事半功倍。，6.3数据加载第六章数据转换与加载2.数据加载方式与数据抽取方式类似，在数据加载到目标数据集市或数据仓库过程中，分 为全量加载和增量加载。全量加载是指全表删除后再进行全部（全量）数 据加载的方式；而增量加载是指目标表仅更新源表变化（增量）的数据。增量抽取机制比较适用于以下特点的数据表：数据量巨大的目标表。源表变化数据比较规律，例如按时间序列增长或减 少。源表变化数据相对数据总量较小。目标表需要记录过期信息或者冗余信息。业务系统能直接提供增量数据。，6.3数据加载第六章数据转换与加载3.批量数据加载每种数据库都有自己的批量加载方法，Kettle为大多数DBMS如Oracle、MySQL、MS SQL Server等提供了批量加载方法。MySQL的批量加载MySQL是Kettle支持的从数据库批量加载到文件的DBMS。Kettle提供两个组件实现批量加载功能，一个是通过作业项把 文本文件批量加载到数据库，另外一个是转换里的批量加载 步骤。Oracle的批量加载Kettle的Oracle批量加载工具采用SQL loader,该组件功能 复杂，需要配置较多的参数，同时也需要设置不同种类的文件，故使用Oracle批量加载需要做复杂的准备工作和配置工作，然 而该工具健壮可靠，能够精准控制处理数据和错误数据。，6.3数据加载第六章数据转换与加载4.数据加载异常处理 如果条件允许，可利用数据中转区对运营数据进行预处理，保证集成 与加载的高效性。如果ETL的过程是主动拉取，而不是从内部推送，其可控性将 大为增强。ETL之前应制定流程化的配置管理和标准协议。关键数据标准至关重要。ETL面临的最大挑战是接收数据时其各源端数 据的异构性和低质量。将数据加载到个体数据集时，在没有一个集中化的数据库的情况下，拥有数据模板是非常重要的。大数据应用人才培养系列教材第六章数据转换与加载青洗转展_6上数据质量评值 623数据加载6.4上机练习与实训习题)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换实训原理：在ETL中，数据清洗是一个非常重要的环节，其结果质量直接关系到模 型效果和最终结论，包括缺失值清洗、格式内容清洗、逻辑错误清洗、非需求数据清洗和关联性验证等。在Kettle中，通过核心对象树 中的转换 流程 脚本 检验等对象集合来实现数据的清 洗转换实训内容：对包含客户当日检入检出(checkin/checkout)的信息数据进行清洗 转换，分别获取合格数据和不合格数据)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换实训指导：根据数据源类型，从输入中将csv文件输入对象拖曳到编辑 区，在CSV文件输入对象属性中选择CSV文件，然后设置列分 隔符 封闭符等，单击获取字段按钮获取csv文件中的字段 名)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换添加数据检验，对customerid字段和studio字段增加检验数据检验-口Simple ValidationsD报告所有错误不止第一个错误0只雄出行,指定连接错误的分隔符：.步骤名称：选择一个要编辑的检验verify_customer_id检验IS述 verify_customer_id要检验S3字段8 customerid、容误代码 invalid customer id 述 malformed customer id,must start witf检验数据类型 0数据类型 String v转换掩码 0，出相 令分组符号合;琐据的正则表达式id_.*不合法数堀的正则表达元 tTee不允许的结束字符串不允许的开始字符用允许的结束字符串允许的开始字符串最小值从其他步骤荻得允许的 要读取的步骤%只允许数值逊的敢据 最大字符串长度最“字符串长度最大值0 Help 确定(0)增加检蛉 糊除检蛉 取消(C)|verify_studio数据检验 一 口Simple Validations报告所有错误,不止第切只输出一行，指定连接错误的分隔符：|。检验描述 verify_studi。要检验的字段名 studio、错误代码 invalid studio。错iJS述 expecting stoa or gardens only。理检蛟数据类型0数据美型 String转换掩码小数点符号教据允许空?只允许数值类型的数据 最大字符国长度 最小字符串长度 最大值 G最小值 令允许的开始字符串 允许的结束字符串 6不允许的开始字符串 从其他步骤获得允许的 口要读取的步骤增加检验 热除检览 取消(C)4)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换从脚本中选择JavaScript脚本”拖入编辑区,对字段checkin和 checkou t进行格式设置。在流程中选择过滤记录拖入编辑 区，根据JavaScript代码中设置的变量字 段dat_checkin和dat_checkout,设置条 件为dat_checkin二dat_checkout。在输出中将ExcH输白拖曳到编辑 框，命名为output good rows,再在转换中添加增加常量，命名为 Add error description，指定发送true 数据给步骤的值为output good rows,指定发送false数据给步骤的值为Add error description)6.4上机练习与实训第六章数据转换与加载Excel输出n实训题:客户数据的清洗转换 Exce输出的属性字段选项卡设置 增加常量实际上类似于前面提到的定义错误处理，由于过滤记录无 法使用定义错误处理，采用增加常 量的方式添加错误字段增加常量idd error description字段步骤名称g#名称格式长度精确当前的10进制的组 值设为空串？1error_descStringcheckout time must not precede checkin.否2error_fieldStringcheckin,checkout否3error_codeStringunplausible times否确定(0)取消(C)0 Help）6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换在转换中将字段选择拖曳到编辑区，打开属性对话框，单击获取选择的字段按钮在流程中选择空操作，并将其拖曳到编辑区，选择数据 检验，右击，在弹出的快捷菜单中选择定义错误步骤命令，指定目标步骤为重命名为collect error rows的空操作，并设置错误描述列名 错误列的列名 错误编码列名，重命名和增加常量中设置的一致步骤错误处理设置 一 口错误处理步骤名Simple Validations目标步骤 collect error rows 7启用错侬理?H错误数列名|0错误描阚名 error.desc 4 令错误列的列名error_field.错误蒯咧名error_code-的最大错触-1|0允许的最大错误百分比（空=100%）-1 在计算百分比前最少要读入的行数.1I OK I Cancel）6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换添加JavaScript代码，命名为Flatten source fields,对收集的错误数据进行过滤，并生成新字段errorjow。JavaScript 代码步骤名称 Flatten source fieldsJava script 函数：口 Transform Scriptsi-口 Transform ConstantsI-口 Transform Functions/M kiput fields customerid studio checkin checkout error_desc error_field error_code/g Output fieldsPlease use the Replace vJava script:3 Script 1/如果字段名不是以“error一 百项!A语箭行中获取所有字段信息var buffer=nev java lang.StringBuffer();/年甲裕中杵亍亍到据信囱 笆与包括字为名、数据类型、长度、格式等var meta=getInputRowMeta();for(var i=0;i0)buffer.append(.);buffer.append();buffer.append(meta.getString(row,i)+);buf fer.append();var era?or_row=buffer.toString();9,21兼碘式？优做别9字段#字段名称 改名为 类型 长度 精度 替换Fieldname或Rename t。值1 error,row String 否在输入中拖曳获取系统信息到编辑区，添加转换名称 转换ID 系统日期（可变）3个系统变量，并分别制定 这3个变量的变量名。这3个变量将作为最终错误输出的3个新列42 of 44)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换添加Exce输出到编辑区，并在打开的Exce库俞出对话框中设置字段选项卡属性Excel输出也1步骤名称sa网1标im 1例件(内容施(字段获取字段 最小宽度#名称蟀格式1 transformation_nameString2 transformation_batch_.Integer03 execution_timeDateyyyy-MM-dd HH:mm:ss4 error_descString5 error_fieldString6 error_codeString7 error_rowString获取字段最小宽度0 Help确定(O)取消(C)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换整个转换最终的实现及连接Flatten source fields Get System Info output bad rows)6.4上机练习与实训第六章数据转换与加载实训题:客户数据的清洗转换执行该转换，通过Preview data查看执行结果，选中CSV file input 输入源，可以看到原始数据有9条，选中。utput bad rows,可以看 到不满足条件的记录有6条z o选中output good rows,可看到满 足条件的记录有3条，如图6-37所示。由于针对错误数据的处理中 添加了以error_开头的错误字段，并添加了3个系统变量，所以不满 足条件的记录字段个数和满足条件的字段个数不同执行结果 X执行历史 目日志：:步骤度呈 巴性能图3,Metrics Preview data()$TransPreview.FirstRows.Label O$TransPreview.LastRows.Label O$TransPr#customerid studio checkin checkout error_d1unknownstoa2010-08-21 12:00:002010-08-21 12:40:00malforr2ID_diogenes2010-08-21 06:45:252010-08-21 09:29:23expecti3ID_sokratesstoa2010-08-212010-08-21 19:22:34Date fo4ID.pythagorasstoa2010-08-21 17:27:482010-08-21Date fo5ID_pythagorasstoa2010-08-21 17:27:482010-08-21+02:00Date fo6ID_platostoa2010-08-21 05:32:222010-08-21 02:12:03checkoi执行结果。执行历史目日志 工步骤度量性能图13.Metrics Preview data$TransPreview.FirstRows.Label O$TransPreview.LastRows.Label O$TransPreview.Off.Label#customerid studiocheckincheckoutdat_checkin1 ID_aristotle2 ID_sokrates3 ID_diogenesgardens 2010-08-21 14:33:12 2010-08-21 18:12:02gardens 2010-08-21 23:00:02 2010-08-21 23:56:59stoa 2010-08-21 15:25:29 2010-08-21 19:00:412010/08/212010/08/212010/08/2114:33:12.0.23:00:02.0.15:25:29.0.dat_checkout2010/08/21 18:12:02.0.2010/08/21 23:56:59.0.2010/08/21 19:00:41.0.少霉 output bad rows 的数据(6 rows)error.descmalformed customer id,must start with ID.and have at least 4 charsexpecting stoa or gardens onlyDate format yyyy-MM-dd expectedDate format yyyy-MM-dd HH:mm:ss expectedDate format yyyy-MM-dd expectedcheckout time must not precede checkin timeerror.field customerid studio checkin checkout checkout checkin,checkouterror.code error_rowinvalid customer id invalid studio malformed date malformed date malformed date unplausible times(unknown,(stoa,2010 08-21 12:00:00,(2010-08-21 12:40:00 PD.diogenes,null,2010-08-21 06:45:25).2010-08-21 09:29:23(ID.sokrates,stoa.2010-08-21,2010-08-21 19:22:34 nD_pythagoras),stoa,(2010-08-21 17:27:48,(2010-08-21 ID.pythagoras,stoa,2010-08-21 17:27:48,2010-08-21+02:00 ID.plato.stoa,2010-08-21 05:32:22,2010 08-21 02:12:03tran$formation_name validator validator validator validator validator validatorexecutionjime2017/11/11 22:59:48.1112017/11/11 22:59:48.1132017/11/11 22:59:48.1172017/11/11 22:59:48.1202017/11/11 22:59:48.1282017/11/11 22:59:48.128大数据应用人才培养系列教材第六章数据转换与加载6数据清洗转换数据质量评值6：3数据加载6 4上现练药实训习题习题:1.什么是数据评估？数据评估的指标有哪些？2.数据检验的方法都有哪些？其各自的优缺点是什么？3.数据转换错误都有哪些？如何处理？4.结合一个实例说明数据清洗的流程包括哪些步骤，并简 要说明数据清洗的主要评价标准。5.什么是数据审计？有哪些方法？6.什么是数据排重？感谢聆听