...
- script 를 XML 로 작성하여 사용하므로 배우기가 쉽다.
- 설치와 사용이 간편하다. (설치후 etl.xml 을 작성해주고 scriptella 를 실행하면 끝)
- Java 로 구현되어 있고 연계를 지원하므로 script code 내에 Java 를 사용할수도 있고 Java code 에서 scriptella 를 호출할 수도 있으므로 연계와 확장이 쉬워진다.
- ant 의 <sql> task 를 지원한다.
- 다양한 Driver 를 지원한다. (CSV, LDAP, JDBC,
설치 및 실행
scriptella 의 사이트에서 다운로드후 압축을 해제하고 PATH 에 추가
Command line
...
Code Block | ||||
---|---|---|---|---|
| ||||
<!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 로 옮기는 예제
- DB 설정 properties 및 etl 파일 생성
vi emp-etl.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
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>
etl 실행
Code Block scriptella emp-etl.xml
...