Page Comparison

...

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

1. DB 설정 properties 생성

   1. vi emp-etl.properties
      

      Code Block

      #Scriptella ETL Configuration Properties driver1=oracle.jdbc.driver.OracleDriver url1=jdbc:oracle:thin:@//oracleserver:1521/xe 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

       

1. etl 파일 생성
   1. 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

       

       

   2. vi emp-etl.xml

      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>

       

       

       

2. etl 

1. 2

Tips

DB에서 가져온 값을 Java code(Janino driver) 에서 사용할 경우 $ 를 붙여준다.(확인 필요)

...