数据ETL

1. ETL之数据清洗

数据清洗的概念:“数据清洗确保无法辨认的数据不会进入数据仓库。无法辨认的数据将影响到数据仓库中通过联机分析处理(OLAP)、数据挖掘和关键绩效指标(KPI)所产生的报表。”数据清洗， 是整个数据分析过程中不可缺少的一个环节，其结果质量直接关系到模型效果和最终结论。

在操作中，数据清洗通常会占据分析过程的50%—80%的时间。所以数据清洗的过程就显得尤为重要,在下面的图上我们可以看到，数据清洗操作是在数据抽取完成之后，然而事实上，数据清洗的操作在整个ETL过程中都会有。比如，我们在数据抽取时会过滤掉某些字段，去除掉重复字段等；在数据加载时，我们会通过查询语法将部分不需要的信息剔除掉。这些都可以算是对数据的清洗。这样做的目的是降低我们数据清洗过程的复杂度，提高效率。

 

数据清洗的步骤大体可以分为这么几步：

1.1预处理阶段：

1.1.1 将数据导入处理工具。也就是我们数据的抽取过程，将数据导入到处理工具中。

1.1.2 看数据。这里包含两个部分：一个是看元数据，包括字段解释、数据来源、代码表等等一切描述数据的信息；再有是抽取一部分数据，使用人工查看方式，对数据本身有一个直观的了解，并且初步发现一些问题，为之后的处理做准备

1.2分析处理阶段：

1.2.1缺失值清洗：

1.2.1.1去除不需要的字段：对某些字段内容，数据分析过程可能用不上，就需要将其删除，直接delete，建议清洗的每一步都做备份，防止错删。

1.2.1.2填充缺失内容:该字段需要使用，但是某些数据上该字段缺失，这时候需要我们给上默认值，或者计算结果（均值、中位数、众数等）填充上去

1.2.1.3重新取数：如果某些字段数据非常重要，但是数据缺失又很严重，这时候就需要我们通过其他渠道重新获取数据将数据填充进去。

1.2.2格式内容清洗：

1.2.2.1时间日期、数值、全半角显示不一致等：这种问题通常与输入端有关，在整合多来源数据时也有可能遇到，将其处理成一致的某种格式即可。

1.2.2.2内容中有不该存在的字符：某些内容可能只包括一部分字符，比如身份证号是数字+字母。最典型的就是头、尾、中间的空格，也可能出现姓名中存在数字符号、身份证号中出现汉字等问题。这种情况下，需要以半自动校验半人工方式来找出可能存在的问题，并去除不需要的字符。

1.2.2.3内容与该字段应有内容不符：一般数据获取时会有前端校验，但是也不乏这些问题会出现，此时需要我们再作处理，非重要字段可以去除掉，重要字段需要我们在做缺失值处理，处理方式就是上面提到的缺失值清洗。

1.2.3逻辑错误清洗:

1.2.3.1去重:建议把去重放在格式内容清洗之后，原因举例：多个空格导致工具认为“吴亦凡”和“吴 亦凡”不是一个人，去重失败。而且，并不是所有的重复都能这么简单的去掉。

1.2.3.2去除不合理值：有人填表时候瞎填，年龄200岁，年收入100000万（估计是没看见”万“字），这种的就要么删掉，要么按缺失值处理。

1.2.3.3修正属性依赖冲突：某些字段之间是可以相互验证的，比如北京市和北京市的邮政编码，你的身份证号和你的年龄，籍贯等这时候需要我们指定一个可靠的字段，然后去除或者重构不可靠的字段。比如：身份证号字段上显示你是20岁，但是年龄字段上28岁，这是我们就以身份证上的信息为可靠字段，将年龄字段改为20岁。

1.2.4非需求数据清洗：

很简单就是把不需要的字段删除（勤备份）

1.2.5校验：

1.2.5.1数据格式校验：验证数据格式是不是都符合标准化格式。

1.2.5.2关联性校验：有时我们的数据来源可能有多个渠道，如果多个渠道获取的同一信息出现不一致的情况，需要我们重新判断调整数据值，或者去除数据。

 

数据清洗过程中出现了很多判断问题，一条数据是不是合乎标准，是否存在错误，我们可以通过相应算法去实现。关于数据清洗的大致介绍到这，数据清洗完成过后，就进入了数据转换过程。

 

2. ETL之数据转化

数据转换的任务主要是进行不一致的数据转换、数据粒度的转换和一些商务规则的计算。将抽取出的数据进行过滤、合并、解码、翻译等转换，为数据仓库创建有效数据的过程。一旦数据抽取或选取过程完成，就要设计并确定转换规则（Business Rules）应用于已抽取出的数据。转换过程需要理解业务侧重点（Business Focus）、信息需求（Informational Needs）和目前可用的源数据（Available Sources）。

 

2.1数据类型转换：

2.1.1 增加“上下文”数据 如，时间戳  。

2.1.2 解码（Decoding）：如，将数值型的地域编码替换成地域名称

2.1.3 清洁和净化：保留字段具有特定值或特定范围的记录、引用完整性检查

 

2.2多数据源整合：

2.2.1 字段映射（Mapping）：映射操作是将读入的数据文件内容与预处理库的字段进行对应及为预处理库的字段指定数据类型的操作，在映射过程中可进行字段合并、复制、拆分、截取操作。

2.2.2 代码变换（Transposing）：—将不同数据源中的数据值规范成数据仓库数据值。例如，将原系统非英文编码转换成数据仓库英文编码；将原系统信息编码转换成数据仓库信息编码等。

2.2.3 合并（Merging）：—将两个或更多个源系统记录合并为一个输出或“目标”记录

2.2.4 派生（Derivations）：—根据源数据，利用数学公式产生数据仓库需要的数据。例如，由身份证号码计算出出生日期、性别、年龄等。

2.3数据粒度的转换：

业务系统一般存储非常明细的数据，而数据仓库中的数据是用来分析的，不需要非常明细的数据，一般情况下，会将业务系统数据按照数据仓库粒度进行聚合。

2.4商务规则的计算：

不同的企业有不同的业务规则，不同的数据指标，这些指标有的时候不是简单的加加减减就能完成，这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中，供分析使用。

3. ETL之数据加载

LOAD 加载经转换和汇总的数据到目标数据仓库中，可实现SQL或批量加载数据加载(Load) 经过数据转换生成的文件的结构与数据仓库数据表的结构完全一致，可以直接通过数据加载工具，以Bulk Load的方式加载到数据仓库中。数据加载工作以oracle数据库为例大致可以分为3步进行。

3.1 加载准备

在真正进行数据加载之前还可能需要完成以下准备工作。

3.1.1 删除数据仓库中数据表的索引，提高加载效率。主要是针对detail及fact大表，可以直接调用DBA所创建的索引维护脚本。DBA调试过数据仓库后，必须更新相应的索引维护脚本，以保证ETL能够正确删除和建立索引。

3.2 加载

Load主要完成将转换后文件的数据加载到数据仓库的表中。需要用到的加载方式有三种

3.2.1 Insert：只需要将转换后文件所有数据完全Insert到目标表中。

3.2.2 UpdAdd（Update Add）：需要对目标表同时做Update及Insert操作，根据primary key，对于已有的记录进行Update操作；对于不存在的记录做Insert的操作；对于数据量大的表，由于此操作的效率非常低可以采用先将转换后文件分割为Delet文件及Insert文件，然后先将Delete文件中的记录根据 primay key对应从数据仓库中删除，然后再从Insert文件中将所有记录全部Insert到目标表中。

3.3.3 Refresh：即将目标表的数据完全更新，一般的做法是先Truncate目标表的数据，然后再完全 Insert要加载的记录。加载过程中数据仓库关闭数据RI（Referential Integrity）管理功能，数据库的RI检查由ETL应用程序完成。

3.3 加载后

3.3.1 重新生成索引，在加载后阶段删除的索引需在此重建。该过程也是调用DBA维护的索引维护脚本。

3.3.2 文件清理：删除不需要的临时文件及临时表。 

使用装载工具或数据查询语言（SQL）时注意点：效率问题、避免违反约束条件，注意装载顺序。

 

参考：ItStar公众号