SQL——ETL

1
2
3
4
5
ETL代表Extract Transform和Load
ETL将所有三个数据库功能组合到一个工具中，ETL是一个工具。
作用：从一个数据库获取数据并将其放入另一个数据库。
ETL的实现有多种方法：常用的方法有。一种是借助ETL工具(如Oracle的OWB、SQL Server 2000的DTS、SQL Server2005的SSIS服务、Informatic等)实现，另外一种是ETL工具和SQL相结合

1、数据抽取

数据抽取可以从数据库、文本文件、Web网页，WebService等地方抽取
（1）确定数据源，需要确定从哪些源系统进行数据抽取；
（2）定义数据接口，对每个源文件及系统的每个字段进行详细说明；
1、对于与存放DW的数据库系统相同的数据源处理方法
　　这一类数据源在设计上比较容易。一般情况下，DBMS(SQLServer、Oracle)都会提供数据库链接功能，在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select 语句直接访问。
　　2、对于与DW数据库系统不同的数据源的处理方法
　　对于这一类数据源，一般情况下也可以通过ODBC的方式建立数据库链接——如SQL Server和 Oracle之间。如果不能建立数据库链接，可以有两种方式完成，一种是通过工具将源数据导出成.txt或者是.xls文件，然后再将这些源系统文件导入到ODS中。另外一种方法是通过程序接口来完成。
　　3、对于文件类型数据源(.txt,.xls)，可以培训业务人员利用数据库工具将这些数据导入到指定的数据库，然后从指定的数据库中抽取。或者还可以借助工具实现。
（3）确定数据抽取的方法：是主动抽取还是由源系统推送？是增量抽取还是全量抽取？是按照每日抽取还是按照每月抽取？

二、数据的清洗转换

需要根据业务规则对异常数据进行清洗，主要将不完整数据、错误数据、重复数据进行处理，保证后续分析结果的准确性。使符合规则的数据入库，把不符合规则的数据变成符合规则的数据后入库。
不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类
(1)不完整的数据：这一类数据主要是一些应该有的信息缺失，如供应商的名称、分公司的名称、客户的区域信息缺失、业务系统中主表与明细表不能匹配等。对于这一类数据过滤出来，按缺失的内容分别写入不同Excel文件向客户提交，要求在规定的时间内补全。补全后才写入数据仓库。

(2)错误的数据：这一类错误产生的原因是业务系统不够健全，在接收输入后没有进行判断直接写入后台数据库造成的，比如数值数据输成全角数字字符、字符串数据后面有一个回车操作、日期格式不正确、日期越界等。这一类数据也要分类，对于类似于全角字符、数据前后有不可见字符的问题，只能通过写SQL语句的方式找出来，然后要求客户在业务系统修正之后抽取。日期格式不正确的或者是日期越界的这一类错误会导致ETL运行失败，这一类错误需要去业务系统数据库用SQL的方式挑出来，交给业务主管部门要求限期修正，修正之后再抽取。

(3)重复的数据：对于这一类数据——特别是维表中会出现这种情况——将重复数据记录的所有字段导出来，让客户确认并整理。
（1）空值处理：可捕获字段空值，进行加载或替换为其他含义数据，或数据分流问题库；
（2）数据标准：统一元数据、统一标准字段、统一字段类型定义；
（3）数据拆分：依据业务需求做数据拆分，如身份证号，拆分区划、出生日期、性别等；
（4）数据验证：时间规则、业务规则、自定义规则；
（5）数据替换：对于因业务因素，可实现无效数据、缺失数据的替换；
（6）数据关联：关联其他数据，保障数据完整性。

3、数据加载

将整理好的数据加载到数据仓库
加载类型：
初始加载 - 填满整个数据仓库表
增量加载 - 在需要时应用更改。
完全刷新 - 擦除一个或多个表的内容并使用新数据重新加载。

4、ETL日志与警告发送

（1）ETL日志
记录日志的目的是随时可以知道ETL运行情况，如果出错了，出错在那里。

ETL日志分为三类：

执行过程日志：是在ETL执行过程中每执行一步的记录，记录每次运行每一步骤的起始时间，影响了多少行数据，流水账形式；
是错误日志：当某个模块出错的时候需要写错误日志，记录每次出错的时间，出错的模块以及出错的信息；
日志是总体日志：只记录ETL开始时间，结束时间是否成功信息。
如果使用ETL工具，工具会自动产生一些日志，这一类日志也可以作为ETL日志的一部分。

（2）警告发送
ETL出错了，不仅要写ETL出错日志而且要向系统管理员发送警告，发送警告的方式有多种，常用的就是给系统管理员发送邮件，并附上错误信息，便于管理员排查。