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