Page Comparison

...

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.

...

<!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. 1. vi emp-etl.properties

      Code Block
      #Scriptella ETL Configuration Properties driver1=oracle.jdbc.driver.OracleDriver url1=jdbc:oracle:thin:@//

oracleserver

2. 1. oraclesrv:1521/

xe

3. 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

       

etl 파일 생성vi emp
4. 1.  

   2. vi emp-etl.

properties

5. 1. xml

 

6. 1. Expand
      Code Block

#Scriptella

7. 1. <!DOCTYPE

ETL

8. 1. 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

Expand Code Block<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd"> <etl>

9. 1. 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>

10.  

    etl  실행

     

     

11. etl 

1. 2

   Code Block
   scriptella emp-etl.xml

Tips

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

<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>

...