1、第六章数据转换与加载6.1数据清洗转换6.2数据质量评估6.3数据加载习题 6.4上机练习与实训6.1数据清洗转换第六章 数据转换与加载确定数据清洗的规模，通常采取RDBMS和文本为清洗载体，常见的数据清洗范围包含：1数据清洗格式内容清洗12缺失值清洗3逻辑错误清洗第六章 数据转换与加载存储 存储成本下降6.1数据清洗转换（1）缺失值清洗在各类数据源系统中，缺失值的问题时常发生，在一定程度上，造成缺失值的原因在于系统的不完备性和故障。具体原因较多，主要分为系统原因和人为原因l 计算源端数据中字段缺失值比例，之后根据缺失率和重要性分别制定策略。对于重要性高和缺失率高的数据，可采取数据从其他渠道补
2、全、使用其他字段计算获取和去掉字段，并在结果中制定策略进行清洗；对于重要性高但缺失率较低的数据，可采取计算填充、经验或业务知识估计等策略进行清洗；对于重要性低、缺失率高的数据，可采取去除该字段的策略进行清洗；对于重要性低且缺失率低的数据，可以不做处理确定范围01第六章 数据转换与加载存储 存储成本下降6.1数据清洗转换（1）缺失值清洗确定范围01第六章 数据转换与加载6.1数据清洗转换（1）缺失值清洗02去除重要性低的字段重要性低的字段，且缺失严重，可以采取将数据抽取的结果放入一中间临时库中，在数据清洗之前，先备份临时库数据，然后直接删除不需要的字段。02填充缺失内容某些缺失值补齐采取一定的值
3、去填充缺失项，从而使数据完备化。通常基于统计学原理，根据决策表中其余对象取值的分布情况来对一个空值进行填充，例如用其属性的平均值来进行补充等第六章 数据转换与加载6.1数据清洗转换（2）格式内容清洗数据源系统若为业务系统，则该系统的数据通常由用户填写，在用户填写数据的过程中，存在全角输入、半角输入、空格符号、错误字段格式等错误l 当采取多个源端整合数据时，因源端系统的不够严谨，采取了字符串类型作为数据的存储类型，可能在不同的源中存储日期、时间的格式不一，导致数据多源抽取到临时表后存在不同的日期格式，从而导致目标系统无法应用时间日期格式清洗01第六章 数据转换与加载6.1数据清洗转换（2）格式内
4、容清洗l 全角指一个字符占用两个标准字符位置，半角指一字符占用一个标准的字符位置。在数据采集时，时常因输入法设置问题，将字母或者数字输入存储为全角格式。故在对数据进行ETL操作时，需要进行全角和半角转换全角半角清洗02第六章 数据转换与加载6.1数据清洗转换（2）格式内容清洗l 在源端系统中，数据采集时因人为原因可能存在一些数据不应有的字符，例如身份证号码出现非数字和X的情况，中国人的姓名出现西文字符、阿拉伯数字等情况。此类问题的解决需要采取半自动+人工方式相结合进行清洗不应有的字符03第六章 数据转换与加载6.1数据清洗转换（2）格式内容清洗l 某些指标非常重要又缺失率高，且存在其他数据源可
5、以获取，可采取重新抽取不同数据源的数据进行关联对比清洗重新取数04第六章 数据转换与加载6.1数据清洗转换（2）格式内容清洗l 端系统同样存在数据与该数据的字段表达意义不符的现象，该类问题主要来源于源端业务系统的缺陷内容与字段不匹配03第六章 数据转换与加载6.1数据清洗转换（3）逻辑错误清洗l 数据排重是指在数据中查找和删除重复内容，而不会影响其保真度或完整性。数据排重需要技巧，首先一定要有信息去识别一条数据的唯一性，也就是类似数据库中的主键，如果唯一性都无法识别，排重也就无所依据排重清洗01第六章 数据转换与加载6.1数据清洗转换（3）逻辑错误清洗l 不合理数据指在业务系统中收录的部分数据
6、存在不合理性，例如一个大学生的实际年龄不能为5岁。一个员工的年龄也不可能超过200岁，QQ信息上好友的年龄为0岁等，导致此类问题的原因可能是业务系统操作失误，也有可能是用户为进行信息隐藏而故意错填数据。对于不合理的数据，在数据采集时，若该数据不是很重要，建议直接删除，否则需要进行人工干预或者引入更多的数据源进行关联识别去除不合理值01第六章 数据转换与加载6.1数据清洗转换（3）逻辑错误清洗源端系统在提供数据时，存在部分信息可以相互验证的校验，例如，在某教务系统中，教师任课的编号由“学期教工号课程代码序号”构成，则该号码能够有效地验证当前教师任课信息中的学期信息、教师信息、课程信息等。同理，身
7、份证号码也能够有效验证当前人员的出生年月，从而能够推算该人员的年龄。源端数据存在矛盾且可以利用规则判定的情况，能够通过ETL工具的规则设置进行查找发现“脏”数据，从而达到更加容易清洗的目的修正矛盾内容01第六章 数据转换与加载6.1数据清洗转换2数据校验数据检验是在数据清洗转换过程中，通过对转换的数据项增加验证约束，实现对数据转换过程的有效性验证。可能存在的数据验证方法有数据项规则设置、数据类型检验、正则表达式约束检验、查询表检验等。对数据执行检验后，ETL工具提供验证结果的输出。在Kettle中，可以在数据转换过程中增加“数据检验”（Data Validator）步骤来完成数据的有效性校验。
8、6.1数据清洗转换第六章 数据转换与加载Kettle中的数据校验在Kettle中，可以在数据转换过程中增加“数据检验”（Data Validator）步骤来完成数据的有效性校验。123NULL验证设置校验规则日期类型验证6.1数据清洗转换第六章 数据转换与加载Kettle中的数据校验在Kettle中，可以在数据转换过程中增加“数据检验”（Data Validator）步骤来完成数据的有效性校验。45日期类型验证正则表达式验证第六章 数据转换与加载6.1数据清洗转换3错误处理数据错误是指数据在转换过程中出现数据丢失、数据失效和数据的完整性被破坏等问题。数据出现错误的原因五花八门，有存储设备的损坏
9、、电磁干扰、错误的操作、硬件的故障等。造成的后果就是会增加大量无用数据甚至会造成系统瘫痪。因此，人们采取各种手段对数据转换进行优化，尽可能避免错误产生第六章 数据转换与加载6.1数据清洗转换（1）转换过程错误在设计ETL过程中，存在一些设计未对转换过程进行错误处理，进而造成ETL执行完成后，目标端的数据未能按照约定数据标准进行组织存储，从而导致“脏”数据进入目标端。转换过程错误是在执行ETL过程中发生的转换错误，该错误一旦发生，应该进入错误处理环节，终止ETL转换，保证进入目标系统的数据干净可靠。第六章 数据转换与加载6.1数据清洗转换例6.3 设置“定义错误处理”作为错误处理方式（1）增加“
10、Excel输出”，重命名为“Excel错误输出”。（2）在“表输出”步骤上通过右键快捷菜单选择“定义错误处理”命令（如图6-17所示），打开“步骤错误处理设置”对话框第六章 数据转换与加载6.1数据清洗转换例6.3 设置“定义错误处理”作为错误处理方式（3）设置“步骤错误处理设置”对话框的参数，指定“目标步骤”为“Excel错误输出”，并选中“启用错误处理？”复选框，指定相关的错误字段值第六章 数据转换与加载6.1数据清洗转换例6.3 设置“定义错误处理”作为错误处理方式（4）指定了Excel错误输出后，表输出中遇到的错误就会直接转存到Excel输出，除了在“步骤错误处理设置”中指定的字段名之
11、外，表输出中的字段名也会一并加入Excel输出中第六章 数据转换与加载6.1数据清洗转换（2）转换数据错误所谓数据转换，从计算机审计的需求来讲，主要包括两个方面的内容：一是将被审计单位的数据有效地装载到审计软件所操纵的数据库中；二是明确地标识出每张表、每个字段的具体含义及其相互之间的关系。而转换数据错误则出现在数据转换过程中，要想实现严格的等价转换是比较困难的。两种模型在数据转换的过程中会出现各种语法和语义上的错误。n 命名错误：源端数据源的标识符可能是目的数据源中的保留字。n 格式错误：同一种数据类型可能有不同的表示方法和语义差异。n 结构错误：如果两种数据库之间的数据定义模型不同，如为关系
12、模型和层次模型，则需要重新定义实体属性和联系，以防止属性或联系信息的丢失。n 类型错误：不同数据库的同一种数据类型存在精度之间的差异。第六章 数据转换与加载6.1数据清洗转换（2）转换数据错误对于以上数据转换中的错误，可进行相应的处理。n 对于命名错误，可以先检查数据源中的保留字，建立保留字集合，对于保留字中的命名冲突，根据需要重新命名。n 对于格式错误，可以从数据源的驱动程序中取出相对应的数据源的数据类型后，对一些特定的类型进行特殊的处理。n 对于不同数据库的同一数据类型的精度冲突，类型转换中将类型和精度结合起来决定源端数据类型和目标数据类型的映射关系。找出目的数据源中与源端数据源类型的精度
13、最匹配的数据类型作为默认的映射关系。第六章 数据转换与加载6.1数据清洗转换（3）数据错误数据错误是数据工作者需要注意的指标之一，因为数据错误能导致完全错误的分析结果。处理数据错误的方法取决于错误出现的原因。n 数据输入错误：人工在数据收集、记录、输入造成的错误，可能会成为数据中的异常值。n 测量误差：当使用错误的测量仪器测量时，通常会出现异常值。n 数据处理错误：当进行数据分析时，错误的数据处理操作可能会造成异常值。第六章 数据转换与加载6.1数据清洗转换（4）错误处理针对数据错误的处理方法是在转换环节增加数据检验，在执行数据检验过程中，当检验错误发生时，可以采取如下方法进行错误处理：n 删
14、除错误数据：如果数据错误是由于数据输入错误、数据处理错误或数据错误数目很少造成的，可以采取直接删除错误数据的方式处理。n 错误数据替换：类似于替换缺失值，我们也可以替换错误数据。可以使用均值、中位数、众数替换方法。n 分离对待：如果数据错误的数目比较多，在统计模型中我们应该对它们分别处理。一个处理方法是异常值一组，正常值一组，然后分别建立模型，最后对结果进行合并。第六章数据转换与加载6.1数据清洗转换6.2数据质量评估6.3数据加载习题 6.4上机练习与实训大数据应用人才培养系列教材6.2数据质量评估第六章 数据转换与加载数据质量是保证数据应用的基础，我们提出了一些数据质量的评估指标。在进行数
15、据质量评估时，要根据具体的数据质量评估需求对评估指标进行相应的取舍。1数据质量评估一致性12完整性3准确性4及时性6.2数据质量评估第六章 数据转换与加载审计数据就是“对被审计单位的数据进行采集、预处理以及分析，从而发现审计线索，获得审计证据的过程”。审计数据有多种不同的处理方法2审计数据审计抽样12数据查询3统计分析4数值分析第六章数据转换与加载6.1数据清洗转换6.2数据质量评估6.3数据加载习题 6.4上机练习与实训大数据应用人才培养系列教材第六章 数据转换与加载6.3数据加载1概念数据加载是继数据抽取和转换清洗后的一个阶段，它负责将从数据源中抽取加工所需的数据，经过数据清洗和转换后，最
16、终按照预定义好的数据仓库模型，将数据加载到目标数据集市或数据仓库中去，可实现SQL或批量加载。大多数情况下，异构数据源均可通过SQL语句进行insert、update、delete操作。而有些数据库管理系统集成了相应的批量加载方法，如SQLServer的bcp、bulk等，Oracle的sqlldr，或使用Oracle的plsql工具中的import完成批量加载。大多数情况下会使用SQL语句，因为这样导入有日志记录，是可回滚的。但是，批量加载操作易于使用，并且在加载大量数据时效率较高。当异构数据源的种类繁多，且数据仓库模型复杂时，使用专业的ETL工具必将事半功倍。第六章 数据转换与加载6.3数
17、据加载2数据加载方式与数据抽取方式类似，在数据加载到目标数据集市或数据仓库过程中，分为全量加载和增量加载。全量加载是指全表删除后再进行全部（全量）数据加载的方式；而增量加载是指目标表仅更新源表变化（增量）的数据。增量抽取机制比较适用于以下特点的数据表：n 数据量巨大的目标表。n 源表变化数据比较规律，例如按时间序列增长或减少。n 源表变化数据相对数据总量较小。n 目标表需要记录过期信息或者冗余信息。n 业务系统能直接提供增量数据。第六章 数据转换与加载6.3数据加载3批量数据加载每种数据库都有自己的批量加载方法，Kettle为大多数DBMS如Oracle、MySQL、MS SQL Server
18、等提供了批量加载方法。nMySQL的批量加载MySQL是Kettle支持的从数据库批量加载到文件的DBMS。Kettle提供两个组件实现批量加载功能，一个是通过作业项把文本文件批量加载到数据库，另外一个是转换里的批量加载步骤。n Oracle的批量加载Kettle的Oracle批量加载工具采用SQL*Loader，该组件功能复杂，需要配置较多的参数，同时也需要设置不同种类的文件，故使用Oracle批量加载需要做复杂的准备工作和配置工作，然而该工具健壮可靠，能够精准控制处理数据和错误数据。第六章 数据转换与加载6.3数据加载4数据加载异常处理n 如果条件允许，可利用数据中转区对运营数据进行预处理
19、，保证集成与加载的高效性。n 如果ETL的过程是主动“拉取”，而不是从内部“推送”，其可控性将大为增强。n ETL之前应制定流程化的配置管理和标准协议。n 关键数据标准至关重要。ETL面临的最大挑战是接收数据时其各源端数据的异构性和低质量。n 将数据加载到个体数据集时，在没有一个集中化的数据库的情况下，拥有数据模板是非常重要的。第六章数据转换与加载6.1数据清洗转换6.2数据质量评估6.3数据加载习题 6.4上机练习与实训大数据应用人才培养系列教材第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换实训原理：n 在ETL中，数据清洗是一个非常重要的环节，其结果质量直接关系到
20、模型效果和最终结论，包括缺失值清洗、格式内容清洗、逻辑错误清洗、非需求数据清洗和关联性验证等。在Kettle中，通过“核心对象”树中的“转换”“流程”“脚本”“检验”等对象集合来实现数据的清洗转换实训内容：n 对包含客户当日检入检出（checkin/checkout）的信息数据进行清洗转换，分别获取合格数据和不合格数据第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换实训指导：n 根据数据源类型，从“输入”中将“CSV文件输入”对象拖曳到编辑区，在“CSV文件输入”对象属性中选择CSV文件，然后设置“列分隔符”“封闭符”等，单击“获取字段”按钮获取CSV文件中的字段名第六
21、章 数据转换与加载46.4 上机练习与实训实训题目：客户数据的清洗转换 n 添加“数据检验”，对customerid字段和studio字段增加检验第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换 从“脚本”中选择“JavaScript脚本”拖入编辑区，对字段checkin和checkout进行格式设置。n 在“流程”中选择“过滤记录”拖入编辑区，根据JavaScript代码中设置的变量字段dat_checkin和dat_checkout，设置条件为dat_checkin=dat_checkout。在“输出”中将“Excel输出”拖曳到编辑框，命名为output good
22、 rows，再在“转换”中添加“增加常量”，命名为Add error description，指定“发送true数据给步骤”的值为output good rows，指定“发送false数据给步骤”的值为Add error description第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换n“Excel输出”的属性“字段”选项卡设置n“增加常量”实际上类似于前面提到的“定义错误处理”，由于“过滤记录”无法使用“定义错误处理”，采用“增加常量”的方式添加错误字段第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换n 在“转换”中将“字段选择”拖曳到编
23、辑区，打开属性对话框，单击“获取选择的字段”按钮n 在“流程”中选择“空操作”，并将其拖曳到编辑区，选择“数据检验”，右击，在弹出的快捷菜单中选择“定义错误步骤”命令，指定“目标步骤”为重命名为collect error rows的空操作，并设置“错误描述列名”“错误列的列名”“错误编码列名”，重命名和“增加常量”中设置的一致第六章 数据转换与加载of44416.4 上机练习与实训实训题目：客户数据的清洗转换n 添加JavaScript代码，命名为Flatten source fields，对收集的错误数据进行过滤，并生成新字段error_row。n 在“输入”中拖曳“获取系统信息”到编辑区，
24、添加“转换名称”“转换ID”“系统日期（可变）”3个系统变量，并分别制定这3个变量的变量名。这3个变量将作为最终错误输出的3个新列第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换n 添加“Excel输出”到编辑区，并在打开的“Excel输出”对话框中设置“字段”选项卡属性第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换n 整个转换最终的实现及连接第六章 数据转换与加载6.4 上机练习与实训实训题目：客户数据的清洗转换n 执行该转换，通过Preview data查看执行结果，选中CSV file input输入源，可以看到原始数据有9条，选中out
25、put bad rows，可以看到不满足条件的记录有6条，。选中output good rows，可看到满足条件的记录有3条，如图6-37所示。由于针对错误数据的处理中添加了以error_开头的错误字段，并添加了3个系统变量，所以不满足条件的记录字段个数和满足条件的字段个数不同第六章数据转换与加载6.1数据清洗转换6.2数据质量评估6.3数据加载习题 6.4上机练习与实训大数据应用人才培养系列教材1什么是数据评估？数据评估的指标有哪些？2数据检验的方法都有哪些？其各自的优缺点是什么？3数据转换错误都有哪些？如何处理？4结合一个实例说明数据清洗的流程包括哪些步骤，并简要说明数据清洗的主要评价标准。5什么是数据审计？有哪些方法？6什么是数据排重？习题：感谢聆听
