1 前言
ETL,是英文 Extract-Transform-Load 的缩写,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。ETL是构建数据仓库的重要一环,用户从数据源抽取出所需的数据,经过数据清洗,最终按照预先定义好的数据仓库模型,将数据加载到数据仓库中去。本文档讲述的就是抽取的过程。
木匠要建造房屋首先要做的事情就是将森林里的树木砍伐下来,运载到自己的仓库中以备使用,这一过程实际上就是我们所说的数据抽取。伐木、运输只是一个过程,具体用什么工具和方案就由我们自己决定,数据抽取也是一样,可以用不同的方法来完成这一过程,比如dblink、kettle……当然不同的方法也有不同的优势和劣势,并不能说什么方法更好,要根据具体的业务情况来决定用什么方法。下文主要讲述了dblink和kettle。
2 DBlink
2.1 DBlink是什么
dblink(Database Link)数据库链接顾名思义就是数据库的链接 ,就像电话线一样,是一个通道,当我们要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
2.2 创建dblink
2.2.1 方法一:通过PL/SQL创建
找到对象窗口的【database links】->右键【新建】
按照如图所示设置:
设置完成点击应用
2.2.2 方法二:用sql语句
新建sql窗口,执行一下语句
create public database link dblinkNAME
connect to userName identified by password
using 'IP:1521/orcl';
说明:dblinkNAME是自己取的连接名,类似表名
connect to 填写要连接的数据库的账号,如:SYSTEM
identified by 后面接该账号密码
IP 填写要连接数据库IP
2.3 抽取语法
2.3.1 全量抽取
create table 表名
as
select 要抽取表字段
from 要抽取的表名@dblink名字;
注释:dblink名字:就是上面自己取的连接名
2.3.2 增量抽取
insert into 表名(表字段)
select 表字段
from 要抽取的表名@dblink名字;
注释:此过程是建立在表结构已经建好的情况下
3 Kettle
3.1 Kettle是什么
Kettle是一款国外开源的ETL工具,纯java编写,可以在Window、Linux、Unix上运行,绿色无需安装,数据抽取高效稳定。
Kettle这个ETL工具集,它允许你管理来自不同数据库的数据,通过提供一个图形化的用户环境来描述你想做什么,而不是你想怎么做。
它里面有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制。
3.2 Kettle特点
- 开源,免费使用
- 跨平台,支持windows和Linux
- 简单易用,解压即可使用
- 支持管理不同的数据源类型,S3,DB,HDFS等
-
支持定时功能,可定时执行,自动执行
3.3 Kettle组成
Kettle有四个工具组成,分为Spoon,Pan,Kitchen,Carte工作。
Spoon: 为一个界面工具,主要可以通过界面来设计ETL的转换过程。
Pan:相对与Job而言,是单个转换过程,运行批量运行由Spoon设计的转换过程。
Kitchen:运行批量运行有Cherf设计的任务。
Carte 主要为一个WEB端容器,允许远程请求HTTP进行监控、启动、停止在Carte服务上运行的job和trans
3.4 运行Spoon
1、找到安装目录下Spoon.bat
3.5 新建转换
转换只能进行单表抽取
3.5.1 步骤一:新建转换
3.5.2 步骤二:创建表输入
在左侧树形菜单栏找到【输入】
3.5.3 步骤三:创建表输出
3.5.4 步骤四:更改表输入信息
点击【确定】保存。
3.5.5 步骤五:更改表输出信息
3.6 新建作业
可以进行多表抽取
步骤一:新建【作业】
步骤二:按照先后顺序双击
步骤三:双击转换,连接到之前的转换
执行完成效果图如下:
4 Dblink与kettle比较
Database links是在oracle中的名称,在其他数据库中名称可能不同,但都有相似的功能,就不一一介绍。这种方法局限性是:它只能对相同数据库进行操作。比如只能将一个oracle数据库中的数据抽取到另一个oracle数据库中,而不能将mysql中的数据抽取到oracle中。但是,正是因为它只能进行同种数据库的抽取,所以它的抽取速度特别快,用kettle抽取需要6分钟的数据,用dblink只需几秒中就完成。
与dblink相比较,kettle的优势就是能进行不同数据库之间的数据抽取,且它还支持定时功能,可定时执行,自动执行。但是对于同类数据库的抽取速度就远不如dblink了。
5 数据抽取规则与范围
在前言中提到,数据抽取可以看作伐木、运输的过程。运输我们已经明白了,那么,伐木又是什么?对建造房屋而言,我们需要的只是树木的主干,它的枝叶是需要被丢弃的。我们可以在运输之前就把枝叶去除,也可以把木头运到自己的仓库,在打造木头的形状时再进行裁剪。但是很明显,在运输之前去除枝叶可以提高运输效率,节约成本。同理可知,在数据抽取过程中,我们一般不采取select * ……的形式,对于一些空数据,没有价值的数据,在抽取之前我们就应该舍弃,但是在这过程中,就要求我们对自己要抽取的数据有一定的了解,对于某些现阶段用不到,将来可能用到的数据也要保留下来,这就需要结合具体业务来分析了。
对于枝叶我们应该丢弃,对于数量我们却应该保证,总不能盖房子过程中木材不够了,结果盖出来的房子连人都进不去吧?所以,一般来说,我们抽取的是全量数据,必须将完整数量的数据全部抽取出来,这样得到到数据分析才比较精准。总而言之,我们要保证数据抽取的高质量、高性能、完整性。