大数据应用人才培养系列教材 数据清洗 刘 鹏 张 燕 总主编 李法平 主编 陈潇潇 副主编.

Presentation on theme: "大数据应用人才培养系列教材 数据清洗 刘 鹏 张 燕 总主编 李法平 主编 陈潇潇 副主编."— Presentation transcript:

1 大数据应用人才培养系列教材 数据清洗 刘 鹏 张 燕 总主编 李法平 主编 陈潇潇 副主编

2 大数据应用人才培养系列教材 第八章　清洗RDBMS数据实例 8.1　准备工作 8.2　数据库数据清洗 8.3　数据脱敏处理 习题

3 本次使用的数据集来源于某校排课数据文件，文件类型为xls，数据集特点如下：
8.1 准备工作 第八章 清洗RDBMS数据实例 1．准备待清洗的数据集 本次使用的数据集来源于某校排课数据文件，文件类型为xls，数据集特点如下： 1 数据集一共包含30个字段 2 某些字段内容存在缺失值，某些字段内容出现格式错误或逻辑错误 2 教师信息表作为辅助表

4 8.1准备工作 第八章 清洗RDBMS数据实例 2．搭建操作环境 环境的搭建包括安装MySQL数据库、安装Navicat软件、安装ETL工具、用ETL工具连接数据库。本章ETL工具选择Pentaho Data Integration 6.1（也称作Kettle）。在运行Kettle前，需进行Java环境的配置。Java环境变量的配置如图8-1、图8-2所示。 存储 存储成本下降 图8-1 Java环境变量CLASSPATH与JAVA_HOME 图8-2 Java环境变量Path

5 8.1准备工作 2．搭建操作环境 kettle连接数据库的操作主要包括以下几个步骤： 下载连接包；
第八章 清洗RDBMS数据实例 2．搭建操作环境 kettle连接数据库的操作主要包括以下几个步骤： 下载连接包； 打开Spoon.bat文件，进入Spoon界面； 双击Navicat应用程序，打开MySQL数据库； 双击“转换1”下的DB连接，弹出数据库连接界面。连接类型选择MySQL，连接方式选择Native（JDBC）。根据实际情况填写连接名称、主机名称、数据库名称、端口号、用户名、密码，并单击“测试”按钮，若连接成功，则出现如图8-4所示的提示界面。。 存储 存储成本下降 图8-4 Kettle连接MySQL数据库成功

6 8.1准备工作 01 02 3．数据导入MySQL 下面介绍用Kettle工具将排课数据导入MySQL的过程（部分重要过程）：
第八章 清洗RDBMS数据实例 3．数据导入MySQL 下面介绍用Kettle工具将排课数据导入MySQL的过程（部分重要过程）： Kettle获取Excel表中的字段 01 步骤1：在“Excel输入”中，选择“工作表”选项卡，单击“获取工作表名称”按钮，选择需要导入数据的sheet表，如图8-8； 步骤2：选择“字段”选项卡，单击“获取来自头部数据的字段”按钮，得到获取字段的结果，再单击“确定”按钮，如图8-9所示； 存储 存储成本下降 将Excel表中的数据输出到MySQL表中 02 步骤1：双击“表输出”图标，打开“表输出”操作界面，设置MySQL与输出的连接； 步骤2：打开Navicat软件，在test数据库下建立名为“course_info”的表，并定义表字段名称和类型；

7 8.1准备工作 3．数据导入MySQL 步骤3：将Excel表中的字段与数据库表中的目标字段进行匹配，单击“确定”按钮；
第八章 清洗RDBMS数据实例 3．数据导入MySQL 步骤3：将Excel表中的字段与数据库表中的目标字段进行匹配，单击“确定”按钮； 步骤4：单击运行“转换”按钮，完成转换，从而实现数据的导入操作. 存储 存储成本下降 图8-8 添加工作表 图8-9 输入的Excel中字段的配置

8 大数据应用人才培养系列教材 第八章　清洗RDBMS数据实例 8.1　准备工作 8.2　数据库数据清洗 8.3　数据脱敏处理 习题

9 01 8.2 数据库数据清洗 缺失值清洗 缺失值清洗策略如下表所示：
第八章 清洗RDBMS数据实例 缺失值清洗 缺失值清洗策略如下表所示： 01 重 要 性 缺 失 率 策 略 高 1. 尝试从其他渠道取数补全2. 使用其他字段通过计算获取 低 通过经验或业务知识统计 不做处理或者简单填充 去除字段，并在结果中标明 填充实例前提：分析导入数据库test的表course_info，发现professional（职称）字段中内容缺失率较高，达43%。结合实际情况，发现此字段的重要性较高，所以采取从其他渠道取数补全的清洗策略。本节将结合另一张教师信息表teacher_info中完整的professional字段信息，利用Kettle对course_info表中professional字段的缺失值进行填充。

10 8.2 数据库数据清洗 方法1 运行SQL脚本进行填充 第八章 清洗RDBMS数据实例 第一步 第二步
第三步 第四步 使用Kettle将teacher_info表导入test数据库 新建连接，连接数据库 编辑SQL脚本，在SQL脚本框中输入待执行的SQL语句 单击“运行转换”按钮，即可执行SQL语句

11 8.2 数据库数据清洗 方法2 运用控件进行填充 第八章 清洗RDBMS数据实例 第一步 第二步 建立表输入，输入内容为待处理内容 第三步
第四步 建立表输入，输入内容为待处理内容 建立“表输入”“过滤记录”“插入/更新”之间的连接，以便完成操作目标 设置“过滤记录”。只有当待填充表course_info中的professional字段值为空或者为空字符串时才实现填充功能，因此在过滤记录中需注明过滤条件 设置“插入/更新”选项，实现更改内容与待更改字段的映射，之后再点击运行转换

12 02 8.2 数据库数据清洗 格式内容清洗 造成格式内容错误的原因大致可分为以下两类： 1）不同的数据源
第八章 清洗RDBMS数据实例 格式内容清洗 造成格式内容错误的原因大致可分为以下两类： 1）不同的数据源 不同数据源的数据标准不一致，即使导入过程正确，也使得最后数据显示格式不一致。 2）人工错误 人工导入过程出现错误或者数据检验工作不充分，导致导入的数据存在不符合常规的内容。 02 格式内容清洗前提：何为错误类型1，错误类型2？ 在course_info表中，考核方式字段assess_method内容混入了空格，如assess_method='考 查'，将这种情况定义为“格式错误类型1”。另外，在tname（教师姓名）字段下出现了tname='20168'的情况，而经过分析发现部分tname的值等于tcode（教师编号）的值，将这种情形定义为“格式错误类型2”。

13 8.2 数据库数据清洗 1） 对“格式错误类型1”进行清洗 方法1 运行SQL脚本 新建连接，连接数据库 编辑SQL脚本
第八章 清洗RDBMS数据实例 1） 对“格式错误类型1”进行清洗 方法1 运行SQL脚本 第一步 第二步 第三步 新建连接，连接数据库 编辑SQL脚本 单击“运行转换”按钮，即可执行SQL语句

14 8.2 数据库数据清洗 1） 对“格式错误类型1”进行清洗 方法2 第一步 第二步 第三步 第四步 运用控件对“格式错误类型1”进行清洗
第八章 清洗RDBMS数据实例 1） 对“格式错误类型1”进行清洗 方法2 运用控件对“格式错误类型1”进行清洗 第一步 第二步 第三步 第四步 建立表输入，并建立数据库连接，将查询结果作为本次转换的输入流 建立“表输入”“字符串操作”“插入/更新”之间的连接 设置“字符串操作”内容，注意在Remove Special character一栏设置可移除的特殊字符为空格 设置“插入/更新”内容，实现清洗内容与待清洗内容之间的映射，再运行转换

15 8.2 数据库数据清洗 2） 对“格式错误类型2”进行清洗 方法1 运行SQL脚本对“格式错误类型2”进行清洗
第八章 清洗RDBMS数据实例 2） 对“格式错误类型2”进行清洗 格式清洗的前提：正常情况下，tname（教师姓名）一般是中文或者英文，因此需要将tname字段下不是中文或者英文的内容筛选出，之后与teacher_info（教师信息表）做关联，将读取到的正确的tname值填充到course_info表中。 方法1 运行SQL脚本对“格式错误类型2”进行清洗 第一步 第二步 第三步 新建转换 连接数据库 编辑SQL脚本，单击“运行转换”按钮，即可执行SQL语句

16 8.2 数据库数据清洗 2） 对“格式错误类型2”进行清洗 方法2 第一步 第二步 第三步 第四步 运用控件对“格式错误类型2”进行清洗
第八章 清洗RDBMS数据实例 2） 对“格式错误类型2”进行清洗 方法2 运用控件对“格式错误类型2”进行清洗 第一步 第二步 第三步 第四步 建立表输入，并建立数据库连接，将查询结果作为本次转换的输入流 建立“表输入”“正则表达式”“插入/更新”之间的连接 设置“正则表达式”内容，注意在Remove Special character一栏设置可移除的特殊字符为空格 设置“插入/更新”内容，实现清洗内容与待清洗内容之间的映射，再运行转换

17 03 8.2 数据库数据清洗 逻辑错误清洗 逻辑错误数据的清洗可分为以下两类：
第一章 大数据概念与应用 逻辑错误清洗 逻辑错误数据的清洗可分为以下两类： 一是去掉重复的数据。在course_info表中存在所有字段内容都相同的情况，此为完全重复。 二是修正矛盾内容。在course_info表中存在“总学时=上机学时+实验学时+讲课学时”的关系，但是实际的数据结果并非如此。比如第238条数据：总学时为40，上机学时、实验学时、讲课学时均为0，显然不符合逻辑；第282条数据：总学时为64，上机学时为0，实验学时为0，讲课学时为40。 03

18 8.2 数据库数据清洗 1） 对“逻辑错误类型1”进行清洗 逻辑错误类型1是指字段内容重复的情况。 方法1 第一步 第二步 第三步 第四步
第八章 清洗RDBMS数据实例 1） 对“逻辑错误类型1”进行清洗 逻辑错误类型1是指字段内容重复的情况。 方法1 运行SQL脚本对“逻辑错误类型1”清洗 第一步 第二步 第三步 第四步 新建转换 连接数据库 编辑SQL脚本，删除重复的字段内容 运行转换按钮

19 8.2 数据库数据清洗 1） 对“逻辑错误类型1”进行清洗 逻辑错误类型1是指字段内容重复的情况。 方法2 第一步 第二步 第三步 第四步
第八章 清洗RDBMS数据实例 1） 对“逻辑错误类型1”进行清洗 逻辑错误类型1是指字段内容重复的情况。 方法2 运用控件对“逻辑错误类型1”进行清洗 第一步 第二步 第三步 第四步 建立表输入，明确输入流 建立“表输入”“去除重复记录”“表输出”之间的连接 设置去除重复记录。”用来比较的字段”列表填写course_info表中的所有字段 设置“表输出”

20 8.2 数据库数据清洗 2） 对“逻辑错误类型2”进行清洗 逻辑错误类型2是指字段内容存在的一些逻辑错误，例如加减乘除。 方法1 第一步
第八章 清洗RDBMS数据实例 2） 对“逻辑错误类型2”进行清洗 逻辑错误类型2是指字段内容存在的一些逻辑错误，例如加减乘除。 方法1 运用SQL对“逻辑错误类型2”进行清洗 第一步 第二步 第三步 第四步 新建转换 连接数据库 编辑SQL脚本，里面包括一些简单的字段相加结果 单击运行转换

21 8.2 数据库数据清洗 2） 对“逻辑错误类型2”进行清洗 逻辑错误类型2是指字段内容存在的一些逻辑错误，例如加减乘除。 方法2 第一步
第八章 清洗RDBMS数据实例 2） 对“逻辑错误类型2”进行清洗 逻辑错误类型2是指字段内容存在的一些逻辑错误，例如加减乘除。 方法2 运用控件对“逻辑错误类型2”进行清洗 第一步 第二步 第三步 第四步 建立表输入，确立输入流 建立“表输入”“计算器”“插入/更新”之间的连接 设置“计算器”，某一字段等于其他字段之和 设置“插入/更新”，并运行转换

22 8.2 数据库数据清洗 第八章 清洗RDBMS数据实例 非需求数据清洗 所谓非需求数据清洗，就是删除对业务不重要的字段。在进行这一操作前，备份源数据显得相当重要。Kettle中使用“字段选择”控件中的“移除”功能可以实现字段的删除操作。 04

23 大数据应用人才培养系列教材 第八章　清洗RDBMS数据实例 8.1　准备工作 8.2　数据库数据清理 8.3　数据脱敏处理 习题

24 8.3 数据脱敏处理 第八章 清洗RDBMS数据实例 数据脱敏（Data Masking）是指用随机字符或数据隐藏原始数据的过程。一般需要进行数据脱敏处理的数据包括个人识别数据、个人敏感数据和商业敏感数据等。Kettle数据加密包括4种类型：对称加密、PGP加密流、PGP解密流、生成密钥。本节主要介绍DES加密、PGP加密流。

25 01 8.3 数据脱敏处理 DES加密 第一步 第二步 第三步 第四步
第八章 清洗RDBMS数据实例 DES加密 DES（Data Encryption Standard）是对称加密的一种实现，即加密、解密运算所使用的密钥是相同的。下面介绍对course_info表中tcode字段进行DES加密。 01 第一步 第二步 第三步 第四步 建立表输入，确立输入流，将tcode的类型转换为binary 建立“表输入”“对称加密”“Excel输出”之间的连接 自定义对称加密信息 设置Excel表输出，并单击运行转换

26 02 8.3 数据脱敏处理 PGP加密流 第一步 第二步 第三步 第四步
第八章 清洗RDBMS数据实例 PGP加密流 PGP（Pretty Good Privacy）加密属于非对称加密，在使用Kettle的PGP加密流之前，需要安装Gpg 4win软件生成密钥。 02 第一步 第二步 第三步 第四步 下载安装Gpg4win软件，创建证书以及个人信息、选择认证格式等 创建密钥 建立表输入，确定输入流 建立“表输入”“PGP加密流”“Excel输出”之间的连接

27 8.3 数据脱敏处理 第八章 清洗RDBMS数据实例 PGP加密流 PGP（Pretty Good Privacy）加密属于非对称加密，在使用Kettle的PGP加密流之前，需要安装Gpg 4win软件生成密钥。 03 第五步 第六步 编辑PGP加密流控件，要求结合之前gpg创建时的信息 设置Excel表输出，并运行转换

28 8.3 数据脱敏处理 第八章 清洗RDBMS数据实例 字符加密 数据脱敏除了进行加密外，还可用字符代替数据的部分内容。为了更好地说明问题，我们选用teacher_info表中的i_card（身份证号）、phone（电话）、b_card（银行卡号）进行脱敏处理。 脱敏规则如下：身份证号的脱敏规则为用“*”替代月、日；手机号码的脱敏规则为用“*”替代第4～7位内容；银行卡号的脱敏规则为用“*”替代第7～15位的内容，具体操作步骤如下所示： 04

29 05 8.3 数据脱敏处理 字符加密 第一步 第二步 第三步 第四步 建立表输入，确立输入流
第八章 清洗RDBMS数据实例 字符加密 05 第一步 第二步 第三步 第四步 建立表输入，确立输入流 建立“表输入”“字符串替换”“插入/更新”之间的连接 设置“字符串替换”，并在“使用...”替换一栏参照上述脱敏规则进行填写 设置插入/更新，并运行转换

30 大数据应用人才培养系列教材 第八章　清洗RDBMS数据实例 8.1　准备工作 8.2　数据库数据清洗 8.3　数据脱敏处理 习题

31 习题： 1．练习使用Kettle软件对数据库缺失值进行清洗。 2．练习使用Kettle软件对数据库格式错误内容进行清洗。
5．大数据预处理的方法有哪些？ 6．练习使用Kettle软件对数据库中的敏感数据进行脱敏处理，包括加密以及特殊字符替换两种方法。

32 AIRack人工智能实验平台 ——一站式的人工智能实验平台 DeepRack深度学习一体机 ——开箱即用的AI科研平台 BDRack大数据实验平台——一站式的大数据实训平台

33 云创公众号推荐 刘鹏看未来 云计算头条 中国大数据 深度学习世界 云创大数据订阅号 云创大数据服务号 高校大数据与人工智能
微信号：lpoutlook 云计算头条 微信号：chinacloudnj 中国大数据 微信号：cstorbigdata 深度学习世界 微信号：dl-world 云创大数据订阅号 微信号：cStor_cn 云创大数据服务号 微信号：cstorfw 高校大数据与人工智能 微信号：data_AI

34 手机APP推荐 我的PM2.5 随时随地准确 查看身边的 PM2.5值 同声译 支持26种语言 互译的实时翻 译软件 我的南京
云创大数据为路 况大数据应用提 供技术支持 科技头条 汇聚前沿资讯 的科技情报站

35 网站推荐 万物云 智能硬件大数据免费托管平台 环境云 环境大数据开放共享平台

36 感谢聆听