精华内容
下载资源
问答
  • 2021-05-07 07:22:35

    《ETL数据抽取方案简介》由会员分享,可在线阅读,更多相关《ETL数据抽取方案简介(5页珍藏版)》请在人人文库网上搜索。

    1、目目录录 ETLETL 简介简介 .4 ETLETL 抽取方案抽取方案.4 1.数据抽取.4 2.数据转换和加工.7 3.数据装载.7 4.时间戳方式 .7 5.全量删除插入方式.8 6.全量比对方式.8 . . ETL 简介 数据集成是把不同来源、格式和特点的数据在逻辑上或物理上有机 地集中,从而提供全面的数据共享,是企业商务智能、数据仓库系统的 重要组成部分。ETL 是企业数据集成的主要解决方案。 ETL 中三个字母分别代表的是 Extract、Transform、Load,即抽取、 转换、加载。 (1)数据抽取:从源数据源系统抽取目的数据源系统需要的数据; (2)数据转换:将从源数据源获。

    2、取的数据按照业务需求,转换成 目的数据源要求的形式,并对错误、不一致的数据进行清洗和加工; (3)数据加载:将转换后的数据装载到目的数据源。 ETL 作为构建数据仓库的一个环节,负责将分布的、异构数据源中 的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转 换、集成,最后加载到数据仓库或数据集市中,成为联机分析处理、数 据挖掘的基础。现在越来越多地将 ETL 应用于一般信息系统数据的迁 移、交换和同步。一个简单 ETL 流程如图 1 所示。 图 1 ETL 抽取方案 ETL 过程中的主要环节就是数据抽取、数据转换和加工、数据装载。 为了实现这些功能,ETL 工具会进行一些功能上的扩。

    3、充,例如工作流、 调度引擎、规则引擎、脚本支持、统计信息等。 1. 数据抽取 数据抽取是从数据源中抽取数据的过程。实际应用中,数据源较多 采用的是关系数据库。 从数据库中抽取数据一般有以下几种方式: 数据源 文件 其他 抽取 临时数据 转换 加载 目标 数据库 . . 1)1) 全量抽取全量抽取 全量抽取类似于数据迁移或数据复制,它将数据源中的表或视图的 数据原封不动的从数 据库中抽取出来,并转换成自己的 ETL 工具可以识别的格式。全量 抽取比较简单。 2)2) 增量抽取增量抽取 增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改 的数据。在 ETL 使用过程中,增量抽取较全量抽取应。

    4、用更广。如何捕 获变化的数据是增量抽取的关键。对捕获方法一般有两点要求:准确性, 能够将业务系统中的变化数据按一定的频率准确地捕获到;性能,不能 对业务系统造成太大的压力,影响现有业务。目前增量数据抽取中常用 的捕获变化数据的方法有: a)a) 触发器方式(又称快照式)触发器方式(又称快照式) 在要抽取的表上建立需要的触发器,一般要建立插入、修改、删除 三个触发器,每当源表中的数据发生变化,就被相应的触发器将变化的 数据写入一个临时表,抽取线程从临时表中抽取数据,临时表中抽取过 的数据被标记或删除。 优点优点:数据抽取的性能高,ETL 加载规则简单,速度快,不需要修 改业务系统表结构,可以实现。

    5、数据的递增加载。 缺点缺点:要求业务表建立触发器,对业务系统有一定的影响,容易对 源数据库构成威胁。 b)b) 时间戳方式时间戳方式 它是一种基于快照比较的变化数据捕获方式,在源表上增加一个时 间戳字段,系统中更新修改表数据的时候,同时修改时间戳字段的值。 当进行数据抽取时,通过比较上次抽取时间与时间戳字段的值来决定抽 取哪些数据。有的数据库的时间戳支持自动更新,即表的其它字段的数 据发生改变时,自动更新时间戳字段的值。有的数据库不支持时间戳的 自动更新,这就要求业务系统在更新业务数据时,手工更新时间戳字段。 优点优点:同触发器方式一样,时间戳方式的性能也比较好,ETL 系统 设计清晰,源数据。

    6、抽取相对清楚简单,可以实现数据的递增加载。 缺点缺点:时间戳维护需要由业务系统完成,对业务系统也有很大的倾 入性(加入额外的时间戳字段) ,特别是对不支持时间戳的自动更新的 数据库,还要求业务系统进行额外的更新时间戳操作;另外,无法捕获 对时间戳以前数据的 delete 和 update 操作,在数据准确性上受到了 一定的限制。 c)c) 全表删除插入方式全表删除插入方式 每次 ETL 操作均删除目标表数据,由 ETL 全新加载数据。 优点优点:ETL 加载规则简单,速度快。 . . 缺点缺点:对于维表加外键不适应,当业务系统产生删除数据操作时, 综合数据库将不会记录到所删除的历史数据,不可以。

    7、实现数据的递增加 载;同时对于目标表所建立的关联关系,需要重新进行创建。 d)d) 全表比对方式全表比对方式 全表比对的方式是 ETL 工具事先为要抽取的表建立一个结构类似的 临时表,该临时表记录源表主键以及根据所有字段的数据计算出来,每 次进行数据抽取时,对源表和临时表进行的比对,如有不同,进行 Update 操作,如目标表没有存在该主键值,表示该记录还没有,即进 行 Insert 操作。 优点优点:对已有系统表结构不产生影响,不需要修改业务操作程序, 所有抽取规则由 ETL 完成,管理维护统一,可以实现数据的递增加载, 没有风险。 。 缺点缺点:ETL 比对较复杂,设计较为复杂,速度较慢。。

    8、与触发器和时 间戳方式中的主动通知不同,全表比对方式是被动的进行全表数据的比 对,性能较差。当表中没有主键或唯一列且含有重复记录时,全表比对 方式的准确性较差。 e)e) 日志表方式日志表方式 在业务系统中添加系统日志表,当业务数据发生变化时,更新维护 日志表内容,当作 ETL 加载时,通过读日志表数据决定加载那些数据 及如何加载。 优点优点:不需要修改业务系统表结构,源数据抽取清楚,速度较快。 可以实现数据的递增加载。 缺点缺点:日志表维护需要由业务系统完成,需要对业务系统业务操作 程序作修改,记录日志信息。日志表维护较为麻烦,对原有系统有较大 影响。工作量较大,改动较大,有一定风险。 f)。

    9、f) OracleOracle 变化数据捕捉(变化数据捕捉(CDCCDC 方式)方式) 通过分析数据库自身的日志来判断变化的数据。Oracle 的改变数 据捕获(CDC,Changed Data Capture)技术是这方面的代表。CDC 特 性是在 Oracle9i 数据库中引入的。CDC 能够帮助你识别从上次抽取之 后发生变化的数据。利用 CDC,在对源表进行 insert、update 或 delete 等操作的同时就可以提取数据,并且变化的数据被保存在数据 库的变化表中。这样就可以捕获发生变化的数据,然后利用数据库视图 以一种可控的方式提供给目标系统。CDC 体系结构基于发布/订阅模型。

    10、。 发布者捕捉变化数据并提供给订阅者。订阅者使用从发布者那里获得的 变化数据。通常,CDC 系统拥有一个发布者和多个订阅者。发布者首先 需要识别捕获变化数据所需的源表。然后,它捕捉变化的数据并将其保 存在特别创建的变化表中。它还使订阅者能够控制对变化数据的访问。 订阅者需要清楚自己感兴趣的是哪些变化数据。一个订阅者可能不会对 发布者发布的所有数据都感兴趣。订阅者需要创建一个订阅者视图来访 问经发布者授权可以访问的变化数据。CDC 分为同步模式和异步模式, 同步模式实时的捕获变化数据并存储到变化表中,发布者与订阅都位于 . . 同一数据库中;异步模式则是基于 Oracle 的流复制技术。 优点优。

    11、点:提供了易于使用的 API 来设置 CDC 环境,缩短 ETL 的时间。 不需要修改业务系统表结构,可以实现数据的递增加载。 缺点缺点:业务系统数据库版本与产品不统一,难以统一实现,实现过 程相对复杂,并且需深入研究方能实现。或者通过第三方工具实现,价 格昂贵。 2. 数据转换和加工 从数据源中抽取的数据不一定完全满足目的库的要求,例如数据格 式的不一致、数据输入错误、数据不完整等等,因此有必要对抽取出的 数据进行数据转换和加工。 数据的转换和加工可以在 ETL 引擎中进行,也可以在数据抽取过 程中利用关系数据库的特性同时进行。 a)a) ETLETL 引擎中的数据转换和加工引擎中的数据转换。

    12、和加工 ETL 引擎中一般以组件化的方式实现数据转换。常用的数据转换组 件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、 数据加解密、数据合并、数据拆分等。这些组件如同一条流水线上的一 道道工序,它们是可插拔的,且可以任意组装,各组件之间通过数据总 线共享数据。同时 ETL 工具还提供了脚本支持,使得用户可以以一种 编程的方式定制数据的转换和加工行为。 相比在数据库中加工,性能较高,但不容易进行修改和清晰辨认。 b)b) 在数据库中进行数据加工在数据库中进行数据加工 关系数据库本身已经提供了强大的 SQL、函数来支持数据的加工, 如在 SQL 查询语句中添加 where 条件进。

    13、行过滤,查询中重命名字段名 与目的表进行映射,substr 函数,case 条件判断等等。 相比在 ETL 引擎中进行数据转换和加工,直接在 SQL 语句中进行 转换和加工更加简单清晰;但依赖 SQL 语句,有些数据加工通过 SQL 语 句可能无法实现,对于 SQL 语句无法处理的可以交由 ETL 引擎处理。 3. 数据装载 将转换和加工后的数据装载到目的库中通常是 ETL 过程的最后步骤。 装载数据的最佳方法取决于所执行操作的类型以及需要装入多少数据。 当目的库是关系数据库时,一般来说有两种装载方式: (1) 直接 SQL 语句进行 insert、update、delete 操作。 (2) 采用批量装载方法,sqlldr 等。 大多数情况下使用第一种方法,因为它们进行了日志记录并且是可 恢复的。但是,批量装载操作易于使用,并且在装入大量数据时效率较 高。使用哪种数据装载方法取决于业务系统的需要。

    更多相关内容
  • 4.完成了需要查询数据的数据库连接,现在配置被插入数据的库的连接,有些许不一样,这里是迁移相同表结构的数据做示范(不同的表可查部分需要迁移数据,插入到某个表字段对应即可) 新建表的插入更新,双击进去...

    1.首先打开ETL工具,创建一个转换
    在这里插入图片描述
    2.拉取一个数据库的输入出来,因为是迁移数据,首先要查询出其中一个数据库的信息
    在这里插入图片描述
    3.双击数据库输入,进行其中一个数据库的连接操作
    在这里插入图片描述

    4.完成了需要查询数据的数据库连接,现在配置被插入数据的库的连接,有些许不一样,这里是迁移相同表结构的数据做示范(不同的表可查部分需要迁移的数据,插入到某个表字段对应即可)
    新建表的插入更新,双击进去配置数据库连接
    在这里插入图片描述
    这里有个小技巧,就是这两个连接之间创建是没有中间的小箭头的,按住shift,点击鼠标左键依次点击数据库输入跟表的插入更新
    在这里插入图片描述
    这里就关联上了,如果是通表结构的迁移,可以自动进行字段匹配(后面提)
    在这里插入图片描述
    在这里插入图片描述

    然后匹配两个数据库的表字段(如果之前没有用箭头关联,需要一个一个敲!!!)
    在这里插入图片描述
    5.点击开始按钮,运行完成后下方的日志会有提示成功或失败
    在这里插入图片描述
    这样的话一次简单的两个库之间的数据迁移就完成了~

    PS:如果是需要定时任务的话,则需要建立一个JOB(工作),然后把转换文件给放入到job文件中进行统一管理,然后用shell脚本进行定时启动,简单的看下示例吧
    在这里插入图片描述
    我这边是定时更新一张表的数据,由于业务需求,这里是在第一个转换中,有一个执行sql,直接删除某表数据,然后第二个转换则是上面提到的数据迁移了,使用job统一进行管理也是很方便的。

    有问题的话欢迎加QQ872962282一起讨论噢~(备注来意就好)

    展开全文
  • 随着信息量的不断增长,企业数据仓库的数据量也随着日常生产和业务处理的增长而不断增加,这随之对...本文将着重介绍在数据仓库迁移中的ETL过程和数据加载的迁移方法,并且以真实客户迁移为例,向读者介绍了如何通过与
  • Kettle是一款国外开源的ETL工具,纯java编写,可以在Window、Linux、Unix上运行,绿色无需安装,数据抽取高效稳定。 Kettle这个ETL工具集,它允许你管理来自不同数据库的数据,通过提供一个图形化的用户环境来描述你...

    1、Kettle概念

    Kettle是一款国外开源的ETL工具,纯java编写,可以在Window、Linux、Unix上运行,绿色无需安装,数据抽取高效稳定。

    Kettle这个ETL工具集,它允许你管理来自不同数据库的数据,通过提供一个图形化的用户环境来描述你想做什么,而不是你想怎么做。

    Kettle中有两种脚本文件,transformation和job,transformation完成针对数据的基础转换,job则完成整个工作流的控制。

    2.安装与启动

    KETTLE本身是绿色安装版软件,直接对压缩包解压后即可使用,在Linux操作系统下通过.sh文件运行,在Windows操作系统下对应的是.bat文件

    spoon.bat一闪而过,无法打开的问题:内存设置

    spoon.bat修改

    if “%PENTAHO_DI_JAVA_OPTIONS%”=="" setPENTAHO_DI_JAVA_OPTIONS="-Xms512m" “-Xmx512m”"-XX:MaxPermSize=256m"

    3.常用组件

    • 数据库连接
    • sql脚本
    • 表输入(数据来源表)
    • 字段选择
    • 表输出(落新表)
    • 插入/更新

    当然kettle自带了非常多的组件可使用,目前只接触了这些。kettle也支持将文件作为输入输出等等。

    4.具体案例

    服务器上的pgsql和本地的sqlserver 之间的数据迁移简单案例

    pgsql: result user_number user_name -> sqlserver:demo_user user_id user_name

    总体结构:

    sql脚本先清除了sqlserver中demo_user表的数据

    然后表输入,选择了pgsql:result表中的所有字段

    字段选择,选择了user_number user_name并改名为user_id user_name

    表输出,选择sqlserver:demo_user

    组件之间使用 shift+鼠标左键连接
    图中下边有个独立的表输入2只是用于查看数据迁移是否生效,直接去数据库看是一样的。
    在这里插入图片描述

    4.1 数据库连接

    sqlserver需要手动下载驱动jar至kettle的lib中https://sourceforge.net/projects/jtds/files/latest/download

    这里分别是服务器中的pgsql和本地sqlserver
    在这里插入图片描述
    在这里插入图片描述

    3.2 sql脚本

    清除pgsql中的rc_user表,便于以后查看从sqlserver迁移过来的数据
    在这里插入图片描述

    3.3 表输入

    数据库连接->表->sql语句->预览

    服务器pgsql至本地sqlserver

    只迁移了user_number user_name

    pgsql: result user_number user_name -> sqlserver:demo_user user_id user_name

    在这里插入图片描述

    3.4 字段选择:

    获取选择的字段->修改或移除

    也可以直接在sql中筛选字段,这样更容易维护
    在这里插入图片描述

    3.5 表输出

    选择要作为表输出的库和表

    在这里插入图片描述

    点击执行即可完成,数据迁移。

    pgsql: result user_number user_name -> sqlserver:demo_user user_id user_name

    展开全文
  • 随着信息量的不断增长,企业数据仓库的数据量也随着日常生产和业务处理的增长而不断增加,这随之对数据仓库的性能和存储容量提出了更...本文将着重介绍在数据仓库迁移中的 ETL 过程和数据加载的迁移方法,并且以真实...

    随着信息量的不断增长,企业数据仓库的数据量也随着日常生产和业务处理的增长而不断增加,这随之对数据仓库的性能和存储容量提出了更高的要求。IBM

    DB2 数据库以其特有的数据库分区技术和数据压缩技术,为企业数据量的不断增长提供了出众的解决方案。当前,已有很多企业客户迁移到

    IBM DB2 数据库用以适应其自身不断增长的业务需要。本文将着重介绍在数据仓库迁移中的 ETL 过程和数据加载的迁移方法,并且以真实客户迁移为例,向读者介绍了如何通过与

    Teradata Automation Server 的集成来完成数据仓库的 ETL 和 LOAD 迁移。

    引言

    随着信息量的不断增长,企业数据仓库的数据量也随着日常生产和业务处理的增长而不断增加,这随之对数据仓库的性能和存储容量提出了更高的要求。IBM

    DB2 数据库以其特有的数据库分区技术和数据压缩技术,为企业数据量的不断增长提供了出众的解决方案。当前,已有很多企业客户迁移到

    IBM DB2 数据库用以适应其自身不断增长的业务需要。

    对于典型的数据仓库的迁移,迁移的工作量主要集中在对 ETL 的迁移,正如同数据仓库的建设,工作量集中在

    ETL 过程的实现一样。下文将着重介绍由 Teradata 数据仓库迁移到 DB2 中的 ETL 和加载(LOAD)迁移部分。本文以真实客户为例,详细介绍在数据仓库迁移中的

    ETL 和加载(LOAD)的迁移方法,并通过实例来进一步说明如何实现从 Teradata 到 DB2 的迁移。关于

    Teradata 数据仓库迁移的概述和 Teradata 数据仓库的数据对象迁移,请参考《从 Teradata

    迁移到 IBM DB2 数据仓库》文章一。

    Teradata 自动化调度工具简介

    对于典型的数据仓库的迁移,例如从 Teradata 到 DB2 的迁移过程,迁移的工作量主要集中在对 ETL

    的迁移,正如同数据仓库的建设,工作量集中在 ETL 过程的实现一样。

    数据仓库 ETL 过程(Extraction, Transformation and Load)是将原始数据从业务数据库或其他数据源进行抽取,转换并最终加载到用于分析的数据仓库模型中的过程。对于一般的数据仓库系统,通常我们需要进行

    ETL 转换,因为我们需要将来自于不同数据源的原始数据进行清洗,转换和聚合,将它们转换成易于进行分析的数据仓库数据。具体来讲,ETL

    的不同阶段指:

    1.取(Extract)是将数据从源数据系统抽取到目标数据仓库中,通常抽取可能会涉及到从多个源数据系统中提取数据。

    2.转换(Transform)是将已经抽取到数据仓库中根据一系列或者多个层次的规则进行转换,使它成为数据仓库模型能够接受的模式。

    3.加载(Load)是将转换后的数据最终加载到用于分析的数据模型中。

    对于 ETL 过程,其实质上是使用 SELECT,INSERT 或者 DELETE 语句将数据从最底层的原始数据表转换为数据仓库的用于分析的数据仓库模型的过程。通常,我们使用自动化工具来自动化

    ETL 过程。其意义在于,一旦我们定义了数据的 ETL 过程,那么自动化工具会在每晚或者在指定的时间内,将原始数据自动的清理并转换为数据仓库的数据格式,并最终导入到数据仓库的表中供今后分析使用。在数据仓库环境的建立过程中,ETL

    自动化工具的好坏不但在构建初期会决定数据仓库项目能否顺利进行,同时也会影响到系统的后期维护的易用性上。在

    Teradata 构建的系统中,其通常使用 Teradata 自动化工具和 Perl 脚本来完成 ETL

    过程。

    Teradata 自动化调度工具(ETL Automation)是指在 Teradata 数据仓库中,Teradata

    自动化调度工具能够让许多作业在执行条件满足时自动的去执行这些操作。这其中包括了可能需要接受一些文档来做数据加载工作的作业,或者是做一些数据整合的工作。而这些工作在执行时可能还会有一定的条件限制等等。

    50f0d9b0f8a5de3502ec00773088df37.png

    图 1. ETL Automation 工具

    在 ETL Automation 机制当中提供了图形操作界面 (GUI) 的监控程序。如上图 1 所示。透过此监控程序你可以实时看到目前在

    ETL Automation 中有哪些作业正在等待状态中 (Pending),以及有哪些作业正在执行中

    (Running)。除此之外,你还可以透过监控程序看到 ETL Automation 机制在某些状况下所产生的例外事件

    (Event) 以便你能够实时地采取对应措施。

    在《从 Teradata 迁移到 IBM DB2 数据仓库》系列一中我们已经提到,在从 Teradata

    到 DB2 的迁移过程中,我们可以继续沿用原 Teradata 系统中的 Teradata Automation

    工具。同时,也可以使用 IBM DataStage 或 SQW 来完成相应的功能。

    本文主要着重于使用继续沿用原有的 Teradata Automation 工具。因此,这就涉及到我们需要进行

    DML 迁移,即 ETL 过程中 Perl 脚本的迁移,同时完成从 Teradata 到 DB2 的数据加载并驱动

    Teradata 自动化工具进行 ETL 过程。下面我们将就这两个方面进行分别的讨论。

    DML 迁移方案

    在 Teradata 数据仓库中,其数据仓库各个层次之间的 ETL 过程是通过 Perl 脚本来进行的,如下图

    2 所示。Perl 脚本主要是用于在数据仓库内部各层次之间的数据抽取、清理和转换。同时,我们使用 Teradata

    自动化工具驱动 Perl 脚本进行工作。例如 Staging 区到中间层的 PDM 层,由 PDM 层到上层的

    BIC 层,其都是由 Perl 脚本在自动化调度工具(Automation Server)的控制下来完成各层次之间的数据抽取和加载。由于

    Perl 脚本中主要为对数据库的 INSERT,UPDATE 和 DELETE 操作,所以我们又把对 Perl

    脚本的迁移称为 DML(Database Manipulation Language)迁移。

    2808cb974f332b9ab080a6a1a65cd86e.png

    图 2. 典型数据仓库中的 ETL 过程

    对于企业在进行 DML 迁移时,遇到的主要问题是:

    1. 我们需要找到 Teradata DML 语言与 DB2 DML 语言的映射关系。对于不同的数据库厂商,其

    DML 语言在符合标准 SQL 语法的同时,总会有其细微的差别。因此,我们需要找到 Teradata 与

    DB2 数据库之间的映射关系。

    2. 对典型的 Teradata 数据仓库系统来说,其包含大量的 DML 脚本,即 Perl 脚本。

    3. 如何保证当前 Teradata 数据仓库下的 DML 脚本能够在 DB2 平台下正常使用,并且保证迁移后的脚本能够保持其原有功能。

    DML(Data Manipulation Language)脚本迁移方案

    为了保证转换后 DML 脚本转换后的可用性和一致性,我们使用如下原则进行转换:

    1.保证原有功能的正确运行

    2.DML 脚本和其他程序流程尽少改变

    3.DML 脚本或其他文件尽量少进行修改

    4.转换过程可以模式化

    原有 Teradata 系统使用 Perl 脚本的 ETL 过程根据不同的应用有以下 2 种典型方式。第一种方式是使用在

    ETL 的 Perl 脚本中嵌入 SQL 语句的方式来完成各层之间的 ETL 过程。如下图 3 所示。

    50f0d9b0f8a5de3502ec00773088df37.png

    图 3. Perl 脚本中嵌入 SQL 的方式

    第二种方式是将调用底层数据库的接口进行封装,上层的 ETL 脚本通过调用底层的 Perl 程序来执行 SQL

    语句。如下图 4 所示。

    09ed0013a23f7641306ba360f93fea10.png

    图 4. Perl 脚本调用底层函数执行

    SQL 语句

    实际上,无论是采用嵌入式的 SQL 的 Perl 脚本,还是在底层进行了函数封装。对于 DB2 的迁移来说,我们本质上还是要将

    ETL 的 Perl 脚本中和 Teradata 相关的命令和 SQL 语句, 转换为 DB2 支持的语句。

    迁移方案实现细节

    对于原 TD 系统不同调用方式,它们的程序流程也有所不同。在确定转换策略前,我们需要了解原 TD 系统中不同

    Perl 脚本和 Teradata SQL 的调用方式。

    1. 底层数据库接口进行封装

    d62cace4eaf74e3424640e6dffa6bbde.png

    图 5. Perl 脚本调用底层函数方式

    每一个 Perl 脚本会去调用 data_process.pm 中的 run_bteq_command()

    函数,如上图 5 所示。此函数会被调用用来执行外部的一个 SQL 文件。对于此种方式执行的 perl SQL,

    我们需要对 run_bteq_command 函数和相关的 SQL 文件进行修改,将 Teradata

    SQL 转为 DB2 支持的 SQL。

    2. 读取自身 Perl 脚本中的嵌入 SQL

    45dc72d46c66767039b72814e8856599.png

    图 6. Perl 脚本调用嵌入 SQL

    的方式

    由上图 6 可见,Perl 脚本会调用脚本中的 run_bteq_command 去执行嵌入在此函数中的

    SQL 语句。因此我们需要对其中的嵌入的 SQL 进行改写,将相关的 Teradata SQL 转换成

    DB2 SQL。

    由于 Teradata 的 SQL 在某些细节上和 DB2 的 SQL 应用有细微的区别,因此我们需要对这些有变化的

    SQL 进行改写,下表给出了常用的 SQL 语句在 Teradata 和 DB2 上的对应表。由于篇幅有限,

    这里仅列出那些典型的转换语句,表中的蓝色字体为 Teradata 与 DB2 所不同的 SQL 语法。

    表 1. SELECT / INSERT / UPDATE 语句对应表

    cf57ce8912c5eaca9d92874dd576c012.png

    表 2. CREATE TABLE 语句对应表

    74ce9f5547f101e6abe7464077a345cd.png

    表 3. 常用函数对应表

    cae8f378083147a29816c9a2adba10a2.png

    值得一提的是,对于在实际项目中的具体转换策略来说,即如何由 Teradata

    SQL 转换为 DB2 支持的 SQL,我们有更加具体的文档指导客户进行转换。同时, 我们也提供了针对不同调用方式的

    DB2 下的 perl SQL 模板。这里由于篇幅限制就不一一列举了。

    数据扫描加载方案介绍

    如在《从 Teradata 迁移到 IBM DB2 数据仓库》系列文章一中所提到的,数据迁移是整个数据库迁移中非常重要的一环。我们需要保证原

    Teradata 中的数据被无损的,快速的装载到 数据仓库。因此我们提供了数据扫描和加载的通用程序,来完成

    Teradata 数据库到 DB2 的数据迁移工作。本节会做详细的介绍。

    数据扫描加载的目的不同于 DML 脚本,即在上文提到的 Perl 脚本迁移。与 DML 用在处理数据仓库内部的转换和加载不同,数据扫描和加载是用于将外部数据源中的数据,

    如将业务数据库中的数据(Teradata,Oracle 中的数据)加载到数据仓库的表中,即在 ETL 过程中我们提到的数据抽取过程。如图

    2 中由 OLTP 加载到 Staging 区。同时, 我们使用扫描程序定时扫描由业务数据库中卸载的文件,并且扫描程序会根据预先设定的时间,每晚、每月或者在指定的时间驱动

    Teradata 自动化测试工具完成由业务数据库 到数据仓库的数据加载。

    实际上,对于外部数据的扫描和加载,我们也是使用的 Perl 脚本来完成外部数据的加载过程。所不同的是,在这里我们并不像

    DML 脚本那样对过去已有的 Perl 脚本进行迁移, 而是使用通用的加载(Load)和扫描(Scan)工具来进行外部数据的加载。此工具是由

    IBM 中国 Avalanche 团队开发,其用于将外部数据抽取到数据仓库内部的表中(如 Staging

    区或 PSA 区), 同时此工具可以驱动 Teradata 自动化工具进行自动的数据加载。其具有很高的模块化和可定制性,可针对不同的需求和加载方式进行快速的定制,使此加载和扫描程序能适应当前的需要。

    此工具目前已经在某电信行业的项目实践得到应用,并取得了非常好的迁移成果。

    方案介绍

    在数据仓库的某一数据层上,Teradata 将这个层面上所有的表按照指定(固定)的时间卸成文本文件传递到

    DB2 所在的系统上。对于 DB2 数据仓库系统,就等于得到了这些数据文件, 并把它们装载到结构相同的

    DB2 数据库表中。在这个层面以上的部分,则通过迁移到 DB2 环境下的 ETL 脚本,根据相互的依赖关系,经过层层处理,最后使得整个数据仓库的数据得

    到更新,支持了分析应用的运行。

    通常,对于数据仓库系统,其需要从业务系统中抽取某日或某月的业务数据,并将其以全量或者增量的方式加载到数据仓库中。简单的流程如下图

    7 所示。

    04510917f10a252ac77bd8f315dc9e51.png

    图 7. 数据扫描加载方案

    其中,业务数据库即可以是 DB2 数据库,也可以是 Teradata 数据库。其数据定期的(每天或者每月)下发到服务器上的指定目录。每天,在自动调度程序(Automation

    Server) 的调度下,通过我们的通用加载程序,将数据以增量、全量或者其他自定义的方式加载到数据仓库中。之后数据经过

    ETL 处理,最终可以通过数据仓库的展现程序对外展现信息。

    加载实现模式

    如《从 Teradata 迁移到 IBM DB2 数据仓库》系列文章一中提到的,在 ETL 工具和 Perl

    脚本迁移过程中,我们可以选择 2 种方式:

    方式一,继续沿用 Teradata Automation 工具,工作重点是迁移大量的 Perl 脚本。

    方式二,放弃 Teradata Automation 工具,使用 IBM DataStage 或 SQW,工作重点是编写新的

    ETL 脚本来实现业务逻辑。

    根据客户的具体需求和系统的实际情况,我们可以选择使用第一种方式或者第二种方式。在本节中,我们着重介绍采用第一种方式,即还延续使用

    Teradata Automation 工具的方式来做后期的加载和 ETL 过程,这样我们可以保证对原有系统进行较小的变化,即可完成从

    Teradata 到 DB2 的迁移。

    因此,对于我们的加载程序来说,它包括两个部分。

    扫描程序:它用来定期扫描从业务数据库下载的数据文件,并通知 Teradata Automation 工具何时进行加载。

    加载程序:加载程序提供了最基本的增量,全量等加载模式,可以根据需要在 Teradata Automation

    工具中进行任意配置。同时,其还提供给了客户可以自定义加载模式的方法。

    扫描程序

    根据需求,我们进行扫描程序的设计,这个程序是我们数据仓库的入口控制程序。则扫描程序会:

    1 .扫描程序会定期的去扫描数据下发后生成的标识文件。当某天或者某月的数据完成下发后,则此表示文件会生成在该下发文件的文件夹中。扫描程序会通过

    ftp 的方式去扫描各个远程服务器上的标识文件。

    2 .当扫描程序启动后,扫描程序会每天启动一次,由操作系统自动在凌晨启动。当然,此扫描程序的启动频率我们可以自行进行设置。该程序启动的时候可以将业务日期作为参数,使得该扫描程序只负责扫描当天需要装载的数据标识文件。为了安全起见,该程序在启动时会检查是否还有前一天的扫描程序在运行。如果有前一天的扫描程序在运行,则报错并退出。

    3 .当扫描程序扫描到某个数据文件标识存在,即表明此数据文件已经下发成功,则扫描程序就会通过 SOCKET

    消息通知 Teradata Automation 程序开始此数据的加载工作。这时,Teradata Automation

    会根据自身配置的加载方式,去调用加载程序将相应的数据加载到生产表中。

    加载程序

    434fc5c042eaa541990fe1c54b88ebc2.png

    图 8. 数据加载示例

    数据加载过程如上图 8 所示。在加载程序中,我们使用如下的一些原则进行设计。

    1.易扩展的数据加载模式

    在加载程序中,所有的数据加载,和数据库相关的 LOAD,INSERT,DELETE 等操作,创建临时表的操作等均进行了封装。所有通用的操作都被封装为可以被单独调用的程序。如程序

    1 所示。

    清单 1. 示例代码 Base_Load.pm

    # 加载模式可以使用的通用函数

    sub Create_Temp_Table (...)

    sub Load_Temp_Data (...);

    sub Transfer (...);

    ...

    我们只需在外部定义的不同的加载模式中,如增量加载,全量加载等实现中,调用这些最基本的函数就可以实现不同的加载模式,因此达到了加载模式的可扩展性。

    2.外部调用方式和加载控制表

    加载程序对外提供统一的调用接口 Table_Load.pl ,如 Automation

    Server 可以通过此接口命令调用不同的 Load 程序进行数据加载。对于不同的表,采用何种加载控制方式等信息,

    我们都通过维护加载控制表来进行控制和调整。

    3.出错处理和数据一致性

    加载程序可以提供出错处理和数据一致性维护。针对不同的加载方式,在加载时,首先进行数据一致性维护。当读取数据文件到临时表失败,程序会退出。加载数据到生产表中失败,

    数据一致性恢复,程序退出。

    4.源代码组织结构

    源代码组织结构如下图 9 所示。其中,table_load.pl 提供了上面我们提到的统一调用接口,table_load.pl

    会根据加载控制表中的信息来进一步的调用不同的加载模式,如在本例中, Load_w.pl 对应全量加载,Load_a.pl

    对应增量加载,以及 Load_m.pl,Load_x.pl 以及 Load_x.pl 等加载脚本,它们分别对应着拉链加载,详单加载和订单加载。这

    3 种加载方式是根据项目 的实际情况以 base_load.pm 为基础而根据客户需求定义出的特定加载方式。实际上,所有不同的加载脚本均是可以通过

    base_load.pm 中的通用函数组合调用而成。

    73347055db26d9e5e27d074f98995b85.png

    图 9. 源代码组织结构

    5.源代码示例如下所示

    清单 2. 示例代码 Table_Load.pl

    # ----- 读取控制表信息 -------

    sub Get_Table_Ctl {

    ...

    }

    #---- 根据不同加载模式,从 DAT 文件中 LOAD 信息 -----

    sub Table_Load {

    # 1. 全覆盖模式

    if ($load_type eq "W") {

    print "### Calling load_W.pl ###\n";

    $rc = system("perl load_W.pl $Target_DB_Name $Target_Usr ...");

    }

    # 2. 纯附加模式

    elsif ($load_type eq "A") {

    print "### Calling load_A.pl ###\n";

    $rc = system("perl load_A.pl $Target_DB_Name $Target_Usr ...");

    }

    # 3. 拉链模式

    elsif ($load_type eq "M") {

    print "### Calling load_M.pl ###\n";

    $rc = system("perl load_M.pl $Target_DB_Name $Target_Usr ...");

    }

    # 4. 话费详单模式

    elsif ($load_type eq "X") {

    print "### Calling load_X.pl ###\n";

    print "$Target_DB_Name $Target_Usr ..." );

    }

    # 5. 话费订单模式

    elsif ($load_type eq "D") {

    print "### Calling load_D.pl ###\n";

    $rc = system("perl load_D.pl $Target_DB_Name $Target_Usr ...");

    }

    清单 3. 示例代码 Table_A.pl

    #----------- 开始进行增量加载 --------------

    print "### START TO LOAD TABLE ${SCHEMA}.${TABNAME} AT ".&getNow()." ###\n";

    # 1. 进行加载前的数据一致性维护

    &Recover_Transfer_Load_A();

    # 2. 删除临时加载表

    &Drop_Temp_Table($DBNAME_TARGET, $USER_TARGET, ...);

    # 3. 创建临时加载表,如果失败,则记录到 Log 文件并退出

    $rc = &Create_Temp_Table($DBNAME_TARGET, $USER_TARGET, ...);

    if ($rc eq 2 || $rc eq 4 || $rc eq 8) {

    print "Failure on create temporary table ${SCHEMA_TMP}.${TABNAME_TMP}\n";

    print "Waiting for re-run current job\n";

    exit 1;

    }

    #4. 将数据文件中的数据加载到临时表中,如果失败,清理临时表并退出

    $rc = &Load_Temp_Data($DBNAME_TARGET, $USER_TARGET, ...);

    if ($rc eq 2 || $rc eq 4 || $rc eq 8) {

    &Drop_Temp_Table($DBNAME_TARGET, $USER_TARGET, ...);

    print "Failure on loading into temporary table ${SCHEMA_TMP}.${TABNAME_TMP}\n";

    print "Waiting for re-run current job\n";

    exit 1;

    }

    #5. 将临时表中的数据加载到正式的生产表中,如果失败,则对生产表数据进行一致性维护

    $rc = &Transfer($DBNAME_TARGET, $USER_TARGET, ...");

    if ($rc eq 2 || $rc eq 4 || $rc eq 8) {

    &Recover_Transfer_Load_A();

    &Drop_Temp_Table($DBNAME_TARGET, $USER_TARGET, ...);

    print "Failure on transfer data into table ${SCHEMA}.${TABNAME}\n";

    print "Waiting for re-run current job\n";

    exit 1;

    }

    #6. 对生产表执行 RUNSTATS 操作

    &Runstats($DBNAME_TARGET, $USER_TARGET, ...);

    #7. 删除临时表

    &Drop_Temp_Table($DBNAME_TARGET, $USER_TARGET, ...);

    #-------- 结束进行增量加载 ---------

    print "### FINISH TO LOAD TABLE ${SCHEMA}.${TABNAME} AT ".&getNow()." ###\n";

    exit 0;

    结束语

    本文是《从 Teradata 迁移到 IBM DB2 数据仓库》系列中的第二篇文章,本文介绍了从 Teradata

    迁移到 DB2 数据库时所需要进行的 DML 迁移方法和数据加载实现。在本文中提到的方法已经在真实的迁移项目中得到验证,并被证明是行之有效的。限于本文篇幅,本文不能对

    DML 迁移方法和数据加载进行详细的介绍,这里只能就其整体方法进行讨论,具体的细节问题留待读者进一步思考。

    展开全文
  • ETL数据抽取方案

    千次阅读 2020-10-07 14:01:15
    ETL 过程中的主要环节就是数据...全量抽取类似于数据迁移或数据复制,它将数据源中的表或视图的数据原封不动的从数 据库中抽取出来,并转换成自己的ETL 工具可以识别的格式。全量抽取比较简单。 2.增量抽取 ...
  • Hbase数据迁移方案

    2021-02-27 23:11:20
    Hbase数据迁移方案 hbase的数据迁移有几种,要求新老集群间所有机器间网络通并配有路由表或dns,查资料发现以下几种方案: 1.走hdfs数据远程拷贝 直接用hadoop distcp远程拷贝迁移出hfiles(先将原表disable下线,...
  • ETL数据清洗的案例

    2022-01-16 16:04:23
    数据清洗(ETL) Extract-Transform-Load,用来描述将数据从来源端经过抽取(Extract)、转换(Transform)、加载(Load)至目的端的过程。ETL一词较常用在数据仓库,但对象并不限于数据仓库. 在运行核心业务...
  • 数据迁移方案对比

    2021-03-16 11:14:00
    数据迁移方案对比 方案列表 迁移方案 实时性 优点 缺点 同步方式 常用场景 使用方式 使用难度 Flume 准实时 支持分布式集群部署2、支持场景多样3、数据源形式多样4、支持自定义开发,易扩展 配置繁琐 增量 1...
  • 4. 如何使用Mysql的Sakila演示数据库构建一个完整的端到端的ETL解决方案 5. 34中ETL子系统,如何使用Kettle实现这34种子系统 6. Kettle如何完成数据抽取/清洗和确认,处理维度表,加载事实表,操作OLAP立方体 .....
  • 方案一:Server Link(推荐) Mysql提供一种引擎叫FEDERATED,这种引擎允许我们链接其他数据库,然后通过sql命令复制其他库表到任意数据库。简单来说就类似于Navicat复制一个数据库的表到另一个数据库中去,这样...
  • 以前的工作迁移过oracle到Informix、oracle和SQLSERVER、oracle到mysql。 在目前的公司又因为去o的关系,做了大量的迁移工作,栽了不少坑,所以和大家交流一下在迁移的过程中的一些实践。 因为成本预算等多方面的...
  • 数据迁移总结_mysql

    2021-02-07 02:50:29
    一、写在前面的这里的数据迁移是一次性的数据迁移,即将旧系统中的数据迁移到新系统的数据库中,在迁移过程中也并没有使用任何ETL工具,只是通过sql脚本按业务类型进行的分批次迁移。在此特将工作中踩过的坑和一些...
  • 回忆一下工作这么些年来,处理数据迁移、转换的工作倒 还真的不少。但是那些工作基本上是一次性工作或者很小数据量,使用access、DTS或是自己编个小程序搞定。可是在数据仓库系统中,ETL上升到了一 定的理论高度,和...
  • 数据迁移的成功策略

    2021-03-05 21:56:25
    数据迁移是将数据从源系统传输到目标系统的过程。这是任何数据存储专业人员的核心任务。 数据迁移很重要,因为它是升级或整合服务器和存储硬件,或添加数据密集型应用程序(如数据库、数据仓库和数据湖),以及大型...
  • “结束了吗?” “不用再每天工作到凌晨两点了吗?” “不用再一遍遍核对数据了吗?” “不用再研究难读的mapping rule了吗?” “是在做梦,还是已经醒了?” ...如果你没有做过数据迁移项目,很容
  • 如果历史数据库和目标数据库是不同的数据库,可以使用ETL工具kettle进行转移,kettle作为ETL工具功能非常强大,复杂的数据迁移可以全部在kettle中进行,由于我对kettle不够熟悉,这里仅仅使用到了它跨数据库进行数据...
  • 数据迁移中有没有复杂的ETL场景 对同步延时要求 同步的数据量预估,高峰预估 在明确目标和需求后,我们采取了多节点分布式采集的方式来满足应用高峰时产生的数据量,根据当时预估高峰每小时 100GB,一天存量...
  • ETL数据处理

    千次阅读 2018-07-10 09:40:51
    ETL数据处理 推荐人 梅雨 推荐日期 7.8 完成日期 7.9 概要 ETL讲解 网址 ...type=note#/ 性质 读书笔记 备注 ETL数据处理 一、关键字分析 ETL(数据仓库技术):Extract-Transform-Loa...
  • SyncNavigator是一款功能强大的数据库同步软件,适用于SQLSERVER, MySQL,具有自动/定时同步数据、无人值守、故障自动恢复、同构/异构数据库同步、断点续传和增量同步等功能,支持Windows xp以上所有操作系统,适用...
  • 博文目录ETL Tools For EDW 主题分享一、数据仓库基础概念1、定义2、关键特性3、粒度4、核心组件5、相关拓展二、数据仓库实施步骤1、定义范围2、确定需求3、逻辑设计4、物理设计5、转载数据6、访问数据7、管理维护三...
  • ETL实现mysql之间不同表结构数据迁移

    千次阅读 2018-06-07 17:16:05
    1.如何从旧系统向新系统迁移数据(表结构变化很大) ...(典型的数据迁移需要做的事,ETL是一套理论,可以使用专业的etl工具,或者用存储过程实现) 2.QQ群Java架构/分布式 群号:468897908 (最简单的用ssis...
  • 认识ETL数据分析流程

    千次阅读 2019-07-10 16:39:26
    ETL,是英文Extract-Transform-Load的缩写,用来描述将数据从来源端经过萃取(extract)、转置(transform)、加载(load)至目的端的过程。常愈大量的数据、复杂的转换逻辑、目的端为较强运算能力的数据库,愈偏向...
  • 图重构-重复关系重构图数据分块ETL函数与过程功能介绍完整实现案例 图数据分块ETL数据ETL的一个场景是需要将上亿条上百G的原始数据构建为图数据,在内存不够用的情况下保证数据构建过程可以平稳顺利运行,需要...
  • 数据仓库 迁移 大数据 对于企业开始大数据之旅而言,将冷的或未使用的数据ETL工作负载从数据仓库卸载到Hadoop /大数据平台是一个非常普遍的起点。 像Hadoop这样的平台提供了一种经济的方式来存储数据并进行大数据...
  • Oracle BI基础之ETL数据增量抽取方案

    万次阅读 2015-12-11 10:05:52
    ETL数据增量抽取方案 一、 ETL 简介 数据集成是把不同来源、格式和特点的数据在逻辑上或物理上有机地集中,从而提供全面的数据共享,是企业商务智能、数据仓库系统的重要组成部分。ETL 是企业数据集成的主要...
  • ETL】使用kettle工具将数据从oracle迁移到mysql 1 kettle安装及运行 kettle官网下载地址 ,下载完毕后大概有80...
  • kettle-数据迁移场景

    千次阅读 2022-01-19 11:02:08
    场景描述: 大家都知道,物联网项目的数据量大,如何高效的存储处理数据是一个非常棘手的问题。...此时,kettle这个强大的ETL工具的用武之地就到了。那么kettle是如何在不同的数据库之间进行迁移的呢?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,164
精华内容 2,465
关键字:

etl数据迁移方案