数据抽取

数据抽取目录

定义
数据抽取方式数据源采用关系数据库
数据源非关系数据库
展开定义
数据抽取方式 数据源采用关系数据库
数据源非关系数据库
展开编辑本段定义 数据抽取是从数据源中抽取数据的过程。编辑本段数据抽取方式数据源采用关系数据库
实际应用中，数据源较多采用的是关系数据库。从数据库中抽取数据一般有以下几种方式。 (1)全量抽取 全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数据库中抽取出来，并转换成自己的ETL工具可以识别的格式。全量抽取比较简单。 (2)增量抽取 增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增、修改、删除的数据。在ETL使用过程中。增量抽取较全量抽取应用更广。如何捕获变化的数据是增量抽取的关键。对捕获方法一般有两点要求：准确性，能够将业务系统中的变化数据准确地捕获到;性能，尽量减少对业务系统造成太大的压力，影响现有业务。目前增量数据抽取中常用的捕获变化数据的方法有： a.触发器：在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据。触发器方式的优点是数据抽取的性能较高，缺点是要求在业务数据库中建立触发器，对业务系统有一定的性能影响。 b.时间戳：它是一种基于递增数据比较的增量数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，即表的其它字段的数据发生改变时，自动更新时间戳字段的值。有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单，但对业务系统也有很大的倾入性(加入额外的时间戳字段)，特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作。另外，无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。 c.全表比对：典型的全表比对的方式是采用MD5校验码。ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定

源表中的数据是新增、修改还是删除，同时更新MD5校验码。MD5方式的优点是对源系统的倾入性较小(仅需要建立一个MD5临时表)，但缺点也是显而易见的，与触发器和时间戳方式中的主动通知不同，MD5方式是被动的进行全表数据的比对，性能较差。当表中没有主键或唯一列且含有重复记录时，MD5方式的准确性较差。 d.日志对比：通过分析数据库自身的日志来判断变化的数据。Oracle的改变数据捕获(CDC，Changed Data Capture)技术是这方面的代表。CDC 特性是在Oracle9i数据库中引入的。CDC能够帮助你识别从上次抽取之后发生变化的数据。利用CDC，在对源表进行insert、update或 delete等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。CDC体系结构基于发布者/订阅者模型。发布者捕捉变化数据并提供给订阅者。订阅者使用从发布者那里获得的变化数据。通常，CDC系统拥有一个发布者和多个订阅者。发布者首先需要识别捕获变化数据所需的源表。然后，它捕捉变化的数据并将其保存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对发布者发布的所有数据都感兴趣。订阅者需要创建一个订阅者视图来访问经发布者授权可以访问的变化数据。CDC分为同步模式和异步模式，同步模式实时的捕获变化数据并存储到变化表中，发布者与订阅都位于同一数据库中。异步模式则是基于Oracle的流复制技术。
数据源非关系数据库
ETL处理的数据源除了关系数据库外，还可能是文件，例如txt文件、excel文件、xml文件等。对文件数据的抽取一般是进行全量抽取，一次抽取前可保存文件的时间戳或计算文件的MD5校验码，下次抽取时进行比对，如果相同则可忽略本次抽取。






















数据仓库中的ETL详细的分为四个阶段：提取，传输，转换，装载。我先简单的介绍一下提取和传输的分类和方法：

一：提取

提取可以分为逻辑提取，和物理提取。

1：逻辑提取按照规模分为：完全提取，增量提取。

完全提取简单运用EXP或者全表扫描可以完成。

增量提取是提取相比上次提取增加了的数据，也可以是按照数据产生时间PATITION了的一个分区等等。Oracle's Change Data Capture 是ORACLE为增量提取提供的一个完备的机制。可以运用基于Timestamps，Partitioning，Triggers的增量提取。

2：物理提取又分为在线提取和离线提取。

在线提取

是直接连接数据库，访问数据库的表，然后提取。

离线提取是指提取数据库以外的一些文件，比如Flat file，Dump file，Redo or Archive log.Transportable tablespaces。等等。

提取的方法很多。可以用sqlplus把数据提取到FLAT file中，也可以用exp，甚至可以直接用oracle net处理。比如：

CREATE TABLE country_city AS SELECT distinct t1.country_name, t2.cust_city

FROM countries@source_db t1, customers@source_db t2

WHERE t1.country_id = t2.country_id

AND t1.country_name='United States of America';

所有提取不是ETL中困难的过程。

二：传输

通过FTP或者Transportable Tablespaces（建立一个临时的表空间用来存提取出来需要传输的数据，然后EXP这个表空间）

三：转换

转换的过程是ETL最复杂，处理时间最长的过程。这个过程涉及的ORACLE知识比较多。开发人员需要知道怎样选择最有效，最便捷的技术，我将在本文详细说明。

我理解的转化过程就是，通过若干个步骤来处理转化过程中需要处理的每一个问题，而这若干步骤是通过建立若干的临时表来完成的，后一个步骤建立的临时表是在前一个步骤建立的临时表的基础上建立起来的。这样一次一次的转化，最后得到转化的结果。

1：Transformation Flow

如果你自己涉及转化的过程，你会想到什么？首先明确，咱们的目的是什么，我们有一个STAGING表，我们是要把这个表的数据添加到DW的事实表中，但是不是简单的添加，这些数据需要按照SCHEMA DESIGN的要求，把所有和维表对应的描述信息分离到维表中。这是一步，第二，我们需要考虑事实表的主键和staging表的主键一定有冲突，因为他们不是同一个SEQUENCE生成的。第三步，就是INSERT到事实表。

Transformation Flow就是按照这样的逻辑来处理的。我们可以写PL/SQL实现整个功能。

以下这个SQL可以创建一个表，大家一看就能明白它的作用了：

CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS SELECT sales_transaction_id,

product.product_id sales_product_id, sales_customer_id, sales_time_id,

sales_channel_id, sales_quantity_sold, sales_dollar_amount

FROM temp_sales_step1, product

WHERE temp_sales_step1.product_name = product.product_name;

解释一下：

一般，从数据源过来的staging表带有和维表某个字段相同或者相似的信息，比如说产品名称。我们就可以通过产品名称链接维表和staging表，SQL中WHERE中的连接就是这样做的。然后就可以把在维表中的产品名称对应的产品ID找出来，标识成为要插入的事实表中的sales_product_id。然后创建temp表把查询结果保存下来。这样就实

现了和维的主外键对接。

这个过程会衍生出一个问题。如果product_name在product中没有，就需要吗？大部分情况可能答案是需要的。那就需要做一个验证操作。咱们看看以下的代码：

CREATE TABLE temp_sales_step1_invalid NOLOGGING PARALLEL AS

SELECT * FROM temp_sales_step1 s

WHERE NOT EXISTS (SELECT 1 FROM product p WHERE p.product_name=s.product_name);

这个CTAS statement语句就可以把查询出的新的SALE记录。

咱们也可以做左链接：

CREATE TABLE temp_sales_step2 NOLOGGING PARALLEL AS

SELECT sales_transaction_id, product.product_id sales_product_id,

sales_customer_id, sales_time_id, sales_channel_id, sales_quantity_sold,

sales_dollar_amount

FROM temp_sales_step1, product

WHERE temp_sales_step1.upc_code = product.upc_code (+);

把所有在维表中没有找到product_name的记录的sales_product_id设置为空。



2：Transformation Mechanisms

Transformation在oracle大致有三种方法：

a)使用sql语句

方法一：

CREATE TABLE ... AS SELECT (CTAS) 然后INSERT /*+APPEND*/ AS SELECT。

先按照需求SELECT出来数据然后存在一张临时表中，然后从临时表取出然后插入到要load的表中。

此外（CTAS）方式使用NOLOGGING模式可以提高性能

方法二：

Transforming Data Using UPDATE

你也可以按照你的TRANSFORM规则直接用UPDATE临时表中的数据。达到转化的效果。

方法三：

Transforming Data Using MERGE

下面我先以一个例子说明：

MERGE INTO products t USING products_delta s

ON (t.prod_id=s.prod_id)

WHEN MATCHED THEN UPDATE SET

t.prod_list_price=s.prod_list_price, t.prod_min_price=s.prod_min_price

WHEN NOT MATCHED THEN INSERT (prod_id, prod_name, prod_desc, prod_subcategory,

prod_subcategory_desc, prod_category, prod_category_desc, prod_status,

prod_list_price, prod_min_price)

VALUES (s.prod_id, s.prod_name, s.prod_desc, s.prod_subcategory,

s.prod_subcategory_desc, s.prod_category, s.prod_category_desc,

s.prod_status, s.prod_list_price, s.prod_min_price);

例子中运用MERGE的好处是：扩展维表，因为有一些从外部数据源来的产品数据可能和DW中的维 表中的产品数据有一些重叠，为了扩展维表又保证数据不重复，可以使用MERGE。

方法四：

Transforming Data Using Multitable INSERT

无条件的insert：

INSERT ALL

INTO sales VALUES (product_id, customer_id, today, 3, promotion_id,

quantity_per_day, amount_per_day)

INTO costs VALUES (product_id, today, promotion_id, 3,

product_cost, product_price)

SELECT ...FROM..

有条件的ALL in

sert：

INSERT ALL

WHEN ...THEN INTO ..TABLE VALUES(...)

WHEN ...THEN INTO ..TABLE VALUES(...) SELECT ...FROM ...;

有条件的FRIST insert：

INSERT FIRST

WHEN ...THEN INTO...

WHEN ...THEN INTO...

ELSE INTO ... SELECT...FROM...

b）使用PL/SQL

运用PL/SQL可以处理更加复杂的转化逻辑，以前我参与的邮政的数据仓库项目就是直接写 PL/SQL来完成ETL过程的。

四：装载

1:using sql*loader

sql*loader是一个很好的从FLAT文件load数据到DW中来的工具。可以处理非常复杂的LOAD过程。有自己的control file语法

2：External Tables

External Tables是对sql*loader的一个补充，提供了一些高级的功能，它使你像访问数据库里的数据一样访问外部源数据。

我有一个文章也对外部表做了一个简单的介绍：

/u/25176/showart_2036046.html

外部表和普通表有个功能缺陷是外部表不能做DML（UPDATE/INSERT/DELETE）操作，也不能在外部表上建立索引。





















Oracle数据库实时数据抽取转换技术简述－SmartE 1 信息系统需要数据新的系统架构
随着计算机应用系统的爆炸式发展，业务量迅速增加，业务种类日益复杂，企业必须管理不断增长的信息流量；随着信息量的急剧增大，核心数据的管理变得日益困难。如何安全、可靠地存储业务数据及满足未来业务数据高速增长的需要；如何有效管理日益增长的业务数据；如何实现业务数据的共享并在现有业务数据之上建立新兴的增值应用，如数据仓库、客户关系管理等，成为了各企业建立信息系统的关键所在。目前，各企业信息系统在数据管理领域存在着普遍的问题：

1.1 数据流通效率低下，企业信息孤岛现象严重
信息系统的建设主要是以应用为驱动的，是随着各种业务的逐渐建设的。当出现一类新的业务时，一般需要为该业务建设一套业务支撑系统，保存与该业务有关的主要数据。而这些数据可能同时也需要被其他应用所访问。从而自然就形成了各子系统应用在不同的纵向，管理着不同的业务单元或对象，各个子系统又是相互独立运行，导致形成了一个个“信息孤岛”。这些系统相互之间没有畅通的信息交流与共享，阻碍了企业信息化建设的整体进程。

1.2 数据报表、查询和数据共享效率低下
为了提高市场竞争力，提高客户满意度，企业需要更复杂的、更灵活的业务统计报告、需要深入的数据挖掘、需要实时的客户查询。而这些大量的统计、查询业务收到现有系统架构的制约，不得不降低效率标准、不得不推迟报告时间、

从而导致客户服务质量降低、业务发展情况报告迟缓，业务发展情况不明等问题。

1.3 企业需要新的信息架构
因此，各企业比以往任何时候相比，管理和有效使用这些信息系统的能力高低都更能决定了长期生存和发展能力，因此比以往任何时候，企业都更关注于如下领域：l 提高系统运行效率，提高业务报表、提高客户服务质量，并降低客户流失率。l 加强企业信息流通、提高企业信息的附加值、进一步挖掘企业信息价值、迅速开发和推广新业务，创造更多收入并保持竞争能力。l 提高信息系统业务连续运行能力，提高数据安全保护水平。

2 DSG SmartE提供实时数据共享解决方案
提高数据价值、加快数据共享、提高数据利用频率的最佳手段就是加快数据的流通，传统的数据流通非常复杂：

一方面是依靠数据提供方编写特定接口来提供实时数据。这就要求每个应用系统都需要为数据订阅方编写特定接口，势必造成接口种类繁多、接口复杂、难以跟上不断变化的业务需求、加大应用开发商的负担等问题；
另一方面是靠数据使用方主动从数据源去获取，这就导致了数据的抽取的延迟，经常导致数据的抽取间隔达到每天一次，这远远无法满足数据实时共享的需求。


DSG SmartE软件解决了以上问题。该软通过安装在数据源系统上的代理程序（agent）来对生产数据的变化进行实时跟踪，然后将跟踪到的数据传输到数据订阅方，根据规则对数据进行过滤、转换，根据需求方的自定义格式进行数据装载。该方式在满足数据抽取方面具有几个明显的特点：

（1）实时性SmartE采用数据跟踪和push技术，安装在数据源端的数据变化跟踪程序agent实时跟踪新的数据变化，然后将变化实时发送到数据需求端，数据的延迟可在秒级实现共享。


（2）可配置性SmartE提供了灵活的配置参数，可通过参数配置来定义需要共享的数据表(table)，共享的数据项（字段），共享的条件（满足条件的记录）。这些都可以通过参数配置，而不需要数据源针对每个数据的要求编写专用的接口。


（3）低干扰性DSG实时数据复制技术不需要通过任何数据库的引擎来获取变更数据，而是通过数据库自身的信息获取源系统上的改变并传送给目的系统，不会对生产系统造成性能影响。对生产系统的CPU资源占用<5%；


（4）零活性SmartE提供不仅提供了选择表、选择字段和选择记录的复制，并且还提供了数据的转换，例如字段名的映射、数据类型转换、数据运算等。


（5）支持多种复制结构


单向复制：从一个数据库向另一个数据库复制；

双向复制：两个数据库进行互相复制；

广播复制：由一个数据库向多个数据库复制；

集中复制：由多个数据库向一个数据库复制；

级联复制：由A数据库向B数据库复制，同时又由B数据库向C数据库进行复制。

3 DSG SmartE功能概述
DSG SmartE支持的功能包括：


（1）复制指定用户下的数据可配置需要复制的schema名字，SmartE会将该用户所有的表都复制到目标端；


（2）复制指定的表可配置只需要复制哪些表，哪些表不需要复制


（3）复制指定的数据可配置一个表中的满足条件的记录复制，不满足条件的记录不需要复制


（4）复制指定的列可配置表中需要复制的列，和不需要复制的列


（5）对列的数据进行转换

l 列映射在将源端的表复制到目标端时，将指定表指定字段复制到目标端表的不同名字段中。

l 增加列在将源端的表复制到目标端时，在表中增加一个数据列，其值可以是固定值，也可以是一个表达式的结果。

l 删除列在将源端的表复制到目标端时，从表中删除某个列。

l 列转换在将源端的表复制到目标端时，将源表的某个列的类型、长度等等进行转换。如将VARCHAR2类型转换成INT类型等等。

l 数据转换在将源端的表复制到目标端时，根据配置将源表的某个列的数值进行转换。例如将“男”、“女”转换成“0”和“1”。这种转换不涉及第三张表，也就是说这种转换是固定的。

l 数据分拆在将源端的表复制到目标端时，将源表中一个列的数据分拆到两个字段中。


（6）支持从多个数据库中的同一类型表的记录整合到一个表中去 从多个相同结构的数据库中将数据整合到一个库中，将同类的数据项集合放到一个表中去，便于作集中的业务处理和数据利用。简化应用程序的数据整合功能。


（7）支持数据分发根据业务需要，将一个表中的数据根据其数据的某个属性，把数据分发到不同的目标端，例如从河北省的集中数据库中将属于石家庄的数据分发给石家庄市。

4 DSG SmartE支持的平台
特性 描述
工作方式 Transaction-Based数据复制
支持数据库版本 Oracle 8i ～ Oracle 11g版本
支持数据类型 支持Oracle Data Type
支持复制操作类型 DML语句
可选择性复制 选择整库、user、表为复制单位
支持的硬件平台 Sun, HP, IBM, x86
支持的操作系统 Solaris, AIX, HP-UX, Linux
支持的存储系统 EMC,IBM,HDS、HP、SUN以及其它通用的SCSI/FC存储系统
支持多种复制结构 一对一，双向，一对多，多对一
复制时间间隔 可灵活设置
最大距离 没有限制
对系统性能的影响 对主系

统CPU占用率在5%。左右