ETL 调度原理浅析及最佳实践

前言：

最早的调度应用是 unix 的 crontab 。

 

最早的 ETL 规范是 NCR 和 SPSS 一起制定的，所以 NCR ETL Automation 的方式此后一直是业界效仿的原因 (SPSS 的 climentine 严格意义上它讲不再是 etl 调度工具，而是数据挖掘工具 ) ，从原理上讲是一样的，换汤不换药，从无图形化到 c\s ，然后从 c\s 到 b\s ，它可能有更多本质变化吗 ?

1 、首先看一下 ETL 作业可能有哪些？

最典型的执行脚本， perl 脚本， shell 或 bat 脚本， java 脚本， plsql 这类封装多个或一个 sql 的脚本。

其次执行 SQL 或存储过程

可能还有一些文件操作，比如创建和删除文件或目录。在数据清洗，断点回复和日志维护的时候是非常有用的。

 

对于系统管理员来讲，最好的工具可能是 shell 和 perl 了，最短的代码完成最多功能。而且很多东西不要模块化。

 

目前 ETL 现状来看，并没有特别规范的实现，或者特别有新意的东西， SourceForge 上的开源项目貌似也寥寥无几。曾经和一个老外聊天，他说他在用 ruby 写 etl 工具，我当时就很不解，问他为什么用 ruby ？ ruby 和 perl 这样的脚本语言固化的模块是很少的，各自为自己，实现功能而已，无通用性，实非通用产品之解决方案。

 

 

2 、 ETL 调度可能的执行方式

从 NCR 的 ETL Automation 的方式来看，有 2 种，第一种是回调 shell 或 cmd 方式，第二种是通过以驱动连接的方式。典型的 perl 的 DBI::DBD ， odbc 和 jdbc 等。这里附带提一下受限的 sqlplus 猜想。

 

它的执行方式可以分为静态和动态执行。

静态执行和数据库客户端的执行是一样的，即 2.1 小节讲的方式。

动态执行与语言结合的一种执行方式，即 2.1 小节讲的方式。

2.1 、回调 shell 或 cmd 方式

为什么会出现这种方式？首先看 2 个连接例子：

 

在 cmd 下连接 oracle 数据库。

Sqlplus pcat\ mypwd@dssdb

select sysdate from dual

 

在 cmd 下连接 db2 数据库，首先进入 db2cmd 命令控制台，然后

db2 connect to dssdb user db2inst1 using mypwd

db2 select current date from sysibm.dummy1

 

这种方式可能是我们操作数据库最直接的方式。从上面 2 种不同的数据库来看，首先都是获得数据库连接，然后发送 sql 交给数据库来执行。（不止此 2 种数据库， mysql ， hsqldb ， derby ，包括 SqlServer 都可以在命令行下操作）。

 

优点：简单，直接，效率高，可以利用更多的数据库特性。

这种方式可以让 sql 预编译，静态 sql 比动态 sql 有优势的。

 

缺点：安全性比较低。我们可以看到上面的例子里用户名密码是暴露出来，这东西很可怕，尤其像移动这类的企业，对非常超级重视。

 

弥补缺点的方式：比如令牌环，比如把用户名和密码做成加密文件，用的时候再解密。

2.2 、以驱动 driver 方式开发

驱动是啥？

是一个应用（也可以说是语言级的）和数据库之间的桥。

 

举个例子《小王子》一书，被翻译成多国语言。这里的《小王子》就是数据库，翻译的人就是驱动，它兼通 2 者。

 

下面举一个 perl dbi 官方文档中的一个例子：

  

#!/usr/local/bin/perl

  

use



 DBI



;

  

use



 DBD::DB2::Constants



;

  

use



 DBD::DB2



 qw($attrib_int $attrib_char $attrib_float

                  

$attrib_date $attrib_ts)



;

  

# an extraneous example of the syntax for creating a new

  

# attribute type

  

$attrib_dec



 =



 {



 %$attrib_int



,

                 

'db2_type'



  

=>



 SQL_DECIMAL



,

                 

'SCALE'



     

=>



 2



,

                 

'PRECISION'



 =>



 31



 };

  

#$DBI::dbi_debug=9; # increase the debug output

  

# Open a connection and set LongReadLen to maximum size of column

  

$dbh



 =



 DBI



->



connect



(



"dbi:DB2:sample"



,



""



,



""



,



 {



 LongReadLen



 =>



 102400



 }



 );

  

if



 (!



defined



(



$dbh



))



 {



 exit



;



 }

  

# Note in the following sequence, that the statement contains

  

# no parameter markers, which makes the execution sequence

  

# just prepare and execute.

  

$stmt



 =



 "SELECT empno, photo_format FROM emp_photo WHERE

            

photo_format = 'gif';"



;

  

$sth



 =



 $dbh



->



prepare



(



$stmt



);

  

$sth



->



execute



();

  

# $row[0] is the empno from the database and $row[1] is the

熟悉 java 的朋友可能都觉得熟悉，其实 php 连 mysql 也这么回事，殊途同归，只是方式而已。 Perl 的 oo 和 php 还真挺像的。

 

这种方式和语言结合的更加密切。他可以借助语言的 for 等来实现一些遍历操作（注：纯 SQL 是可以实现的，不过方式不太一样，希望大家别拿 plsql 说事， sql 的方式是多次关联，而不是说 plsql 的 for 等。 plsql 已经算一门语言了，不是标准 sql ）。

 

但是这里边有一个问题，通过驱动的执行的 sql 都是不能预编译，也就是他们属于动态 sql ，从效率上讲肯定是比静态 sql 低的。还有一个事要说， oracle 和 db2 都支持一种驱动式的静态编译，是一个叫 SQLJ 的东西，有兴趣的可以去看看。

 

大多数人都是不区分动态 sql 和静态 sql 的概念的，大家对效率的看法过于偏重数据库本身。不太重视 sql 本身的业务逻辑，其实不细比较也看不出来。《 the art of SQL 》是一部非常不错的书。

2.3 、 sqlplus 猜想

这里的猜测是指 cmd 下的 sqlplus ，而不是图形化客户端的那个。

 

如果大家熟悉 windows 编程，可能知道 windows 也是有一个 shell 的， dos 命令和 windows shell 是完全不一样的，我猜测 sqlplus 是直接扩展 windows shell ，屏蔽了 dos 命令，不过 f 开头的快捷键还是可以使用的。

 

这个是可以考虑用来实现作业调度的。但是问题来了，跨平台是受限的，如果只是在 windows 下调用是没有问题的。

3 、典型的 shell 或 cmd 回调方式举例

3.1 、 perl 实现方式

Perl 的实现方式其实是很优雅的。在 3.1.1 小节里会有分析。 Perl 处理 oracle ， Teradata 和 db2 的原理是一样的，下面给出 2 种实现。

3.1.1 、 perl 连接 oracle 的实例

##########################################################################

# Description : run Oracle Command ， call the sqlplus to execute sql

# Input       : String sql

# Output      : None

# Example     :

#             my $sql = "sqlplus system/sang@dssdb;

#                                              select sysdate from dual;    

#                                            ";

#                          runOracleCommand($sql);

##########################################################################

sub runOracleCommand

{

         my ($cmd)=@_;

        

         my $rc = open(SQLPLUS, "|sqlplus");

         unless ($rc) {

                   print("Could not invoke CMD command");

                   return -1;

         }

         print SQLPLUS <<ENDINOUT;

                            $cmd

ENDINOUT

         close(SQLPLUS);

         my $RET_CODE = $? >> 8 ;

         print "RET_CODE,$RET_CODE";

}

 

分析一下：

         my $rc = open(SQLPLUS, "|cmd");

         上面的语句是回调的核心。为什么说它优雅呢？ 先看它的过程，首先调用 sqlplus ，然后把 sql 传给 sqlplus ，有 sqlplus 来执行。这里面就有一个类 unix 管道的概念，后面定义的东西回调给 sqlplus ，作为 sqlplus 的输入，是不是很像呢？

         补充一下，这段程序是可以跨平台的。

         在集群的环境下，在不同的主机上，资源共享，但是 perl 环境不共享，这是当年遇到的一个问题，提醒自己复习一下。

 

3.1.2 、 perl 调 db2

这个的执行方式和上面 3.1.1 的方式是一样的，其实这是从我根据 IBM DB2 安装的 sample 里的 perl 脚本的方式，自己把 NCR ETL Automation 封装成一个 pm 中的一段。这里是节选，不能直接运行的

DB2Util.pm 这个东西我还真觉得挺帅的，比如容错处理，共享连接，出错回滚等功能是河北移动，包括华为那边人（ 2 个 OCP ，硕士）都没弄成来的，哈哈，沾沾自喜一下。

##########################################################################

# Description : execute DB2 SQL command .this is static SQL

#               actually,we use db's command to execute SQL.

# Input       : sql

# Output      : the executed return code.

# Example     :

#                                                               my $sql="insert into mk_vsdm.a values(13);";

#                                                               ETL::run_db2cmd($sql);

# TODO        : 虽说此方法应该有返回值，但是不知道为什么没有返回来？待改。

###########################################################################

sub run_db2cmd

{

#print "execute run_db2cmd()\n";

  my $rc = open(DB2CMD, "| db2 -mtvsp-");  # 执行失败不可继续建表或视图

  # To see if DB2CMD command invoke ok?

  unless ($rc) {

    print "Could not invoke DB2CMD command/n";

                   return -1;

         }

 

print DB2CMD <<ENDINOUT;

----------------------------------------------------------

----------------------------------------------------------

CONNECT TO HEBdw USER $db_user USING $db_password;

-----------------------------------------------------------

-----------------------------------------------------------

------------------SQL 语句开始 -----------------------------

-----------------------------------------------------------

  $sql

-------------------------------------------------------

-------------------------------------------------------

CONNECT RESET;

TERMINATE;

------------------------------------------------------

----------------SQL 语句结束 -------------------------

------------------------------------------------------

ENDINOUT

 

   close(DB2CMD);

          

         my $RET_CODE = $? >> 8 ;

  if ( $RET_CODE == 0 or $RET_CODE == 1 )

  {

                   showTime();

                   myStatusLog("execute sucess");

    return $SUCCESS;

  }

  elsif ( $RET_CODE == 2 )

  {

    showTime();

                   myStatusLog("execute sucess");

    print  " 加载文件 $sql 告警 :$RET_CODE \n";

    return $SUCCESS; 

  }

    else

  {

    showTime();

    print  " 加载文件 $sql 发生错误 ! 错误代码 :$RET_CODE \n";

    return $FAILURE;

  }

};

 

3.2 、 Java 实现方式

Java 来实现这个，其实还是有优势的，它最开始设计的时候就是为了跨平台，所以用 java 来做是非常好的。而且对于 java 的效率， javaEE 应用来看，采用 java 来做是非常合适的。

 

我说 java 有优势，不是和 perl 比较，各有优点，没法去比的。 Perl 处理单个脚本绝对是王者，但是讲到 OO ，这个是 java 的优势。而且 perl 写的 cgi 脚本和 java 的 servlet/jsp 来比就差太多了，不是一个辈分的东西。

 

给出一段实际代码：

       try {

           Runtime .getRuntime ().exec( "dir" );

       } catch (IOException e) {

           e.printStackTrace();

       }

所有的对 cmd 和 shell 的调用都是这种方式。

 

其实咱公司的数据集成平台中 ETL 部分，也肯定是采用这种方式。原理肯定是一样的。 Java 在语言级屏蔽了 shell 和 cmd 的差异，省去了不少麻烦。

 

这样实现是没问题的，不过有很多问题要自己处理，其实 ant 的底层也是这么玩的，那么为什么不投机取巧呢？这个在后面的最佳实践中有说明。

 

3.3 、比较 crontab 和 perl 和 java 方式

crontab 和 perl 都是进程级别的，建一个 job 就是一个进程，华为的那边 6 台 power6 服务器，就给 ETL Automation 开了 8 个进程，不知何故，猜测是性能消耗比较大。

java 里是线程，在 java5 以后有现成的线程池可以利用，效率更高。

 

4 、作业调度时间处理方式

上面讲的实际是 ETL 中执行方式。在 ETL 调度中有 2 大部分，一部分是上说过的执行方式，另一部分是触发方式。

 

所谓触发方式，就是某个时间触发某个作业的方式。常见的有日触发方式、月触发方式和临时触发。

 

触发最重要是时间处理。最典型的应用时 unix 的 crontab 。指定要执行的 shell 和 6 个已空格来连接的时间表达式。这个表达式是通用表达式，它可以准确的表示某一天，某几天，执行多少次。它的灵活使他在简单应用或者系统管理员使用的非常多。

 

事实上，这种通用时间表达式确实是行业内的最佳实践。在 java 世界里开源工具 quartz 就有这种 cron  expression ，实现的非常相似。

 

待做

这里可以详细的解说一下表达式原理。

 

 

 

5 、给出作业调度的最佳实践（例子待完成）

我这个身份写这个，说实话，底气不足。像洪永这样的大先生，我是肯定比不过的，不过能抛砖引玉，对我，对公司来讲也都是好事。

 

5.1 、 Ant 来定义任务

Ant 是 java 里著名的自动化构建工具。它通过 build.xml 来保存 target 信息，然后在命令行下，输入 ant + target 名称，通过这种方式来执行 target 。

Target 是什么东西？

分解来看，它本身是一个 task ，所以他的配置可能没有外参，但大多是有内参的。

 

 

1 、区分几个核心概念

    -- 【 task 】

    -- 【 Target 】

    -- 【 Job 】

 

5.1.1 、任务 -task

 

这是 ant 里面的概念，其实一个任务就是完成一件事，可以使用 ant 的默认 task ，

当然 ant 的 task 是可以定义的，所以给我们提供了足够的扩展性

比如：建立一个目录，删除一个文件，执行一个脚本，执行一段 SQL

 

5.1.2 、目标 -target

在 Ant 中，一个 target 由多个 task 组成，它指定的是一连串的 task 。

可以说一个 target 是一个流程，可以完成多个任务

 

例如：执行脚本的时候需要一个文件夹，执行完成的时候我想移动日志文件

<target>

    <task name=1>

       创建文件夹

    </task>

    <task name=2>

       执行脚本

    </task>

    <task name=3>

       移动日志文件

    </task>

</target>

 

说明： ant 执行 build.xml 中的就是 target ，只不过 target 还有其他配置，所以才有 job 层的抽象

 

5.1.3 、作业 -job

作业有 2 点需要实现： job dependency 和 job stream 。

这是根据 NCR ETL Automation 的思想考虑的。 NCR 和 SPASS 合订的规范也应该是这样的

很明显 job 是由一个到多个 target 实现，

出于上面考虑：作业划分为实作业和虚作业

    - 实作业：即所谓的 target ，而且是单个 target ，详见 1.2 节

    - 虚作业：即我们真正调用的作业

 

    本小节重点是虚作业。

    在定义 target 的时候，它的内部参数有个 depends ，它的值是多个 target 名称 , 以逗号分隔。

    下例来自 tomcat 的 sample 应用：

      <target name="dist " depends="compile,javadoc "

               description="Create binary distribution">

   

        <!-- 在虚作业里，这部分 task 是可选的 -->

           <mkdir   dir ="${dist.home}/docs "/>

           <copy    todir ="${dist.home}/docs ">

             <fileset dir ="${docs.home}"/>

           </copy>

      

           <jar jarfile ="${dist.home}/${app.name}-${app.version}.war"

                basedir ="${build.home}"/>

        <!-- end define task-->

      </target>

    如上所示： depends 解决我们的 job depends ，原因是执行 dist 的时候会按顺序调用 complie 和 javadoc 。

    job depends ： compile 和 javadoc -- 此 2 个 target 。

    job Stream ： 这个是按 depends 顺序的， compile 在前，然后是 javadoc 。

   

    <!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

5.1.4 、三者之间关系

                             -task （ 1..n ）

             --target （ 1..n ） -[ 

      -- 实作业 -|

作业 -| 

      -- 虚作业 -|

              --target （ 1..n ） -[

                                -task （ 1..n ）

     

     

      job  (0..n)  job  (1..n)   target   (1..n)   task

     

     

     

 

更明确点讲，虚作业是我们在调度过程中所谓的作业。

而实作业是 target 。虚作业是带了依赖关系的 target 。

依赖关系可以确定依赖还可以确定调度顺序，所以有依赖关系的才是我们在调度过程中所谓的作业 .

Task 是 ant 的概念，一个 target 由多个 task 组成，这些 task 可以随意扩展，也可以随意定义。这给我们的系统提供足够的可扩展空间。

 

2 、区分 build.xml 中的属性与模板

【 target1 】

<target name="help">

  <exec executable="cmd">

    <arg value="/c"/>

    <arg value="ant.bat"/>

    <arg value="-p"/>

  </exec>

</target>

 

 

 

Co

评论

2 楼 kitleer 2014-11-03  

原理比较浅显易懂。
最佳实践有些晦涩。
unix java php xml

采用TASKCTL来实践会不会容易些？

1 楼 que1 2011-03-10  

学习了，哈哈，ETL还是蛮有意思的。