Scriptella 로 ETL(Extract, Transform, Load) 사용하기
개요
ETL은 Extract, Transform, Load 의 약자로 데이타를 Source 에서 추출 및 가공후에 다른 저장소(DBMS, LDAP, CSV 등) 로 이동하는 과정을 지원하는 툴이다.
예로 오라클에서 data 를 추출하여 다른 오라클을 옮길경우 import/export utility 를 사용하거나 SQL*Loader 등을 사용했지만 다음과 같은 문제가 있었다.
- Oracle DBMS 에서 Oracle 로만 이동 가능
- exp 의 경우 양쪽 DB의 DATABASE Character Encoding 이 맞아야 함.
- 사용이 어렵고 데이타 추출후 가공하여 loading 이 어려움
ETL은 이런 어려움을 극복하고 이기종 DBMS 나 다른 저장소로 데이타를 추출/변환/로딩이 가능하게 해준다. 좋은 상용 솔루션도 많이 있다지만 써 본적은 없고 Open Source 중 많이 쓰고 쓸만한 scriptella 의 사용법을 간단하게 정리해 본다.
Scriptella 의 특징
- script 를 XML 로 작성하여 사용하므로 배우기가 쉽다.
- 설치와 사용이 간편하다. (설치후 etl.xml 을 작성해주고 scriptella 를 실행하면 끝)
- Java 로 구현되어 있고 연계를 지원하므로 script code 내에 Java 를 사용할수도 있고 Java code 에서 scriptella 를 호출할 수도 있으므로 연계와 확장이 쉬워진다.
- ant 의 <sql> task 를 지원한다.
- 다양한 Driver 를 지원한다. (CSV, LDAP, JDBC,
설치 및 실행
scriptella 사이트에서 다운로드후 압축을 해제하고 PATH 에 추가
Command line
scriptella 를 command line 에서 실행하면 현재 폴더에서 템플릿 파일(etl.xml) 을 찾아서 수행함. ETL 파일명이 다를 경우(Ex: my-etl.xml) cmd option 으로 전달
scriptella my-etl.xml
템플릿 파일이 없다면 -t 옵션으로 생성할 수 있다.
scriptella -t
Ant task
Java
scriptella.jar 를 CLASSPATH 에 추가하고 다음 코드를 삽입
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.
SQLSheet is a JDBC driver which allows you to interact with Microsoft Excel using SQl statements. (view HOWTO)
설치
sqlsheet 와 sqlsheet 가 의존성있는 library 가 필요함.(http://code.google.com/p/sqlsheet/wiki/HowToGetDependecies)
- http://poi.apache.org/download.html 에서 POI 다운로드
- wget http://apache.mirror.cdnetworks.com/poi/release/bin/poi-bin-3.9-20121203.zip
- poi-3.9-20121203.jar 와 poi-ooxml-3.9-20121203.jar 를 압축 해제한다.
jsqlparser-0.8.0.jar 다운로드
SQLSheet 는 jsqlparser 라는 JavaCC 기반의 SQL 파서 라이브러리를 사용하는데 한글 입출력이 불가한 문제가 있다.
jsqlparser 에서 Unicode 사용하기 를 참고해서 소스를 수정하고 re-compile 하거나 페이지에 첨부된 파일을 다운로드한다.
- sqlsheet-6.5.jar 다운로드
사용
테스트를 위한 employee table 생성 및 기본 데이타 입력 (DB character set 은 utf-8 로 생성)
CREATE TABLE EMPLOYEE( empno INTEGER NOT NULL, name VARCHAR(100), job VARCHAR(100), boss INTEGER, hiredate VARCHAR(12), salary DECIMAL(7, 2), comm DECIMAL(7, 2), deptno INTEGER ); INSERT INTO EMPLOYEE VALUES (7839, '킹KING', '프레지던트PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO EMPLOYEE VALUES (7566, '죤스JONES', '매니저MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO EMPLOYEE VALUES(7788, '스코트', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20); INSERT INTO EMPLOYEE VALUES(7876, '아담스', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20); INSERT INTO EMPLOYEE VALUES(7902, '똠방각하', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
etl.xml 작성
etl.xml<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd"> <etl> <connection id="xls" url="jdbc:xls:file:report.xls" classpath="sqlsheet-6.5.jar; poi-3.9-20121203.jar; jsqlparser-0.8.0-unicode.jar;poi-ooxml-3.9-20121203.jar"> </connection> <connection id="mysql" driver="mysql" url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8" classpath="mysql-connector-java-5.1.26-bin.jar" user="user" password="pwd"> </connection> <script connection-id="xls"> CREATE TABLE SHEET1( empno INT, name VARCHAR2(100), job VARCHAR2(100), hiredate VARCHAR2(12), salary DECIMAL(7, 2) ); </script> <query connection-id="mysql"> SELECT empno , name, job, hiredate, salary from EMPLOYEE <script connection-id="xls"> INSERT INTO SHEET1 (empno, name, job, hiredate, salary) VALUES(${empno}, '${name}', '${job}', '${hiredate}', ${salary} ); </script> </query> </etl>
- scriptella -d etl.xml 실행후 report.xls 을 excel 로 열어서 확인
제한
- 한글 컬럼명은 사용할 수 없다. (쓰려면 jsqlparser 를 수정해야 한다.)
- INSERT 구문에서 VARCHAR2 를 입력하려면 '' 로 둘러싸야 한다.
- OS 의 encoding 과 DB의 encoding 이 안 맞으면 에러가 발생하니 DB 의 encoding과 OS 의 encoding 을 같게 해야 한다.
- Windows 의 cmd 의 경우 euc-kr 이라 xls 로 저장시 문제가 발생할 수 있다. chcp 65001 로 UTF-8 로 encoding을 변경한다.
- RHEL/CentOS Linux 의 경우 LANG 변수가 ko_KR.UTF-8 여야 한다. 아니면 export LANG=ko_KR.UTF-8 를 실행해서 locale 을 변경한다.
- 1
Janino
Super small Java Compiler(http://scriptella.javaforge.com/docs/api/scriptella/driver/janino/package-summary.html#package_description)
설치
http://docs.codehaus.org/display/JANINO/Download 에서 다운로드,
scriptella-1.1 과 Janino 2.7.0 을 사용시 Missing @Override 에러가 발생하므로 Janino 2.6.2 를 사용해야 함
- 압축을 풀고 3개의 jar (commons-compiler.jar, commons-compiler-jdk.jar)는 scriptella 의 lib 폴더에 복사
사용
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd"> <etl> <connection id="out" driver="janino" /> <query connection-id="out"> set("name", "John"); next(); <script> System.out.println("Processing: "+ get("name")); </script> </query> </etl>
<!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
#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 문을 사용할 수 있다.
<script connection-id="emp "> <include href="schema.sql" /> </script>
etl 실행
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>
또는 다음처럼 get("varName") 형식 사용
<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>