Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: include 문 추가

...

  1. script 를 XML 로 작성하여 사용하므로 배우기가 쉽다.
  2. 설치와 사용이 간편하다. (설치후 etl.xml 을 작성해주고 scriptella 를 실행하면 끝)
  3. Java 로 구현되어 있고 연계를 지원하므로 script code 내에 Java 를 사용할수도 있고 Java code 에서 scriptella 를 호출할 수도 있으므로 연계와 확장이 쉬워진다.
  4. ant 의 <sql> task 를 지원한다.
  5. 다양한 Driver 를 지원한다. (CSV, LDAP, JDBC, 

 

설치 및 실행

 scriptella 의  사이트에서 다운로드후 압축을 해제하고 PATH 에 추가

Command line

...

Code Block
languagejava
EtlExecutor.newExecutor(new File("etl.xml")).execute(); //Execute etl.xml file
EtlExecutor.newExecutor(getClass().getResource("etl.xml")).execute(); //Execute etl.xml file loaded from classpath
EtlExecutor.newExecutor(servletContext.getResource("/WEB-INF/db/init.etl.xml")).execute(); //Execute init.etl.xml file from web application WEB-INF directory

 

 

 

JDBC 이외의 Driver

xls

SQLSheet Driver Adapter for Scriptella.

...

Code Block
languagexml
titleJanino as an Expression Evaluator
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
    <description>Janino as an Expression Evaluator</description>

<connection id="janino" driver="janino" />
<query connection-id="janino">
	import org.codehaus.janino.ExpressionEvaluator;
	// Compile the expression once; relatively slow.
	ExpressionEvaluator ee = new ExpressionEvaluator(
		"c > d ? c : d",                     // expression
		int.class,                           // expressionType
		new String[] { "c", "d" },           // parameterNames
		new Class[] { int.class, int.class } // parameterTypes
	);
	 
	// Evaluate it with varying parameter values; very fast.
	Integer res = (Integer) ee.evaluate(
		new Object[] {          // parameterValues
			new Integer(10),
			new Integer(11),
		}
	);
	System.out.println("res = " + res);
</query>
</etl>

 

사용예

Oracle 의 EMP/DEPT 테이블 스키마와 데이타를 MySQL 로 옮기는 예제

  1. DB 설정 properties 및 etl 파일 생성
    1. vi emp-etl.properties

      Code Block
      #Scriptella ETL Configuration Properties
      driver1=oracle.jdbc.driver.OracleDriver
      url1=jdbc:oracle:thin:@//
  1. oracleserver
    1. oraclesrv:1521/
  2. xe
    1. sid
      user1=scott
      password1=tiger
      classpath1=d:/devel/jdbc/ojdbc6-11.2.0.4.jar;
      
      ## MySQL
      driver2=com.mysql.jdbc.Driver
      url2=jdbc:mysql://localhost:3306/etltest?useUnicode=true&characterEncoding=utf8
      user2=etltest
      password2=etltest123
      classpath2=d:/devel/jdbc/mysql-connector-java-5.1.28.jar

       

  3. etl 파일 생성vi emp
    1.  
    2. vi emp-etl.

  4. properties

     

    Code Block
    #Scriptella ETL Configuration Properties
    driver1=oracle.jdbc.driver.OracleDriver
    url1=jdbc:oracle:thin:@//oraclesrv:1521/sid
    user1=scott
    password1=tiger
    classpath1=d:/devel/jdbc/ojdbc6-11.2.0.4.jar;
    
    ## MySQL
    driver2=com.mysql.jdbc.Driver
    url2=jdbc:mysql://localhost:3306/etltest?useUnicode=true&characterEncoding=utf8
    user2=etltest
    password2=etltest123
    classpath2=d:/devel/jdbc/mysql-connector-java-5.1.28.jar

     

     vi emp-etl.xml
    1. xml

      script 로 실행할 SQL 이 많을 경우 별도의 sql 파일을 작성후에 include 문을 사용할 수 있다.

      Code Block
      <script connection-id="emp ">
          <include href="schema.sql" />
      </script>
      Expand
      Code Block
      <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
      <etl>
          <description>Scriptella ETL File Template.</description>
          <properties>
              <include href="emp-etl.properties"/> <!--Load from external properties file-->
          </properties>
      	<!-- Connection declarations -->
          <connection id="oracle" driver="${driver1}" url="${url1}" user="${user1}" password="${password1}" classpath="${classpath1}"/>
          <connection id="mysql" driver="${driver2}" url="${url2}" user="${user2}" password="${password2}" classpath="${classpath2}"/>
      	
      	<connection id="log" driver="text"/> <!-- For printing debug information on the console -->
      	<!-- EMP 테이블 생성 -->
      	<script connection-id="mysql">
      		drop table IF EXISTS emp ;
      		drop table IF EXISTS dept ;
              create table dept(
      		  deptno INTEGER,
      		  dname  varchar(14),
      		  loc    varchar(13),
      		  constraint pk_dept primary key (deptno)
      		);
      		  
      		create table emp(
      		  empno    INTEGER,
      		  ename    varchar(10),
      		  job      varchar(9),
      		  mgr      INTEGER,
      		  hiredate date,
      		  sal      decimal(7,2),
      		  comm     decimal(7,2),
      		  deptno   INTEGER,
      		  constraint pk_emp primary key (empno),
      		  constraint fk_deptno foreign key (deptno) references dept (deptno)
      		);
          </script>
      	<script connection-id="log">
              Before dept migration
          </script>
          <!-- Uncomment and modify to run a query-based transformation -->
          <query connection-id="oracle">
      		SELECT deptno, dname, loc FROM dept;
              <script connection-id="mysql">
                  INSERT INTO dept(deptno, dname, loc) VALUES (?deptno, ?dname, ?loc);
              </script>
      		<script connection-id="log">
                   DEPT - Inserted a row: $deptno, $dname, $loc
              </script>
          </query>
          <query connection-id="oracle">
      		SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM EMP;
              <script connection-id="mysql">
                  INSERT INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (?empno, ?ename, ?job,?mgr, ?hiredate, ?sal, ?comm, ?deptno);
              </script>;
      		<script connection-id="log">
                   EMP - Inserted a row: $empno, $ename, $job, $mgr, $hiredate, $sal, $comm, $deptno
              </script>
          </query>
      </etl>
  5.  

    etl  실행

     

     

  6. etl 

 

 

  1. 2
    Code Block
    scriptella emp-etl.xml

 

 

 

Tips

DB에서 가져온 값을 Java code(Janino driver) 에서 사용할 나 console 출력시에는 경우 $ 를 붙여준다.(확인 필요다른 JDBC connection 에서 사용시에는 binding 문자열인 ? 를 사용)

Code Block
languagexml
<connection id="db_in" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:orcl" classpath="ojdbc6-11.2.0.3.jar" user="scott" password="tiger">
</connection>


<connection id="text" driver="janino" />
<query connection-id="db_in">
	SELECT empno num, name, salary Salary from EMPLOYEE
	 <script connection-id="janino">
		System.out.println("empno : " + $num + ",name:" + $name + ",salary =  " + $Salary);
	 </script>
</query>

...