개요

ETL은 Extract, Transform, Load 의 약자로 데이타를 Source 에서 추출 및 가공후에 다른 저장소(DBMS, LDAP, CSV 등) 로 이동하는 과정을 지원하는 툴이다.

예로 오라클에서 data 를 추출하여 다른 오라클을 옮길경우 import/export utility 를 사용하거나 SQL*Loader 등을 사용했지만 다음과 같은 문제가 있었다.

  1. Oracle DBMS 에서 Oracle 로만 이동 가능
  2. exp 의 경우 양쪽 DB의 DATABASE Character Encoding 이 맞아야 함.
  3. 사용이 어렵고 데이타 추출후 가공하여 loading 이 어려움

ETL은 이런 어려움을 극복하고 이기종 DBMS 나 다른 저장소로 데이타를 추출/변환/로딩이 가능하게 해준다. 좋은 상용 솔루션도 많이 있다지만 써 본적은 없고 Open Source 중 많이 쓰고 쓸만한 scriptella 의 사용법을 간단하게 정리해 본다.

 

Scriptella 의 특징

  1. script 를 XML 로 작성하여 사용하므로 배우기가 쉽다.
  2. 설치와 사용이 간편하다. (설치후 etl.xml 을 작성해주고 scriptella 를 실행하면 끝)
  3. Java 로 구현되어 있고 연계를 지원하므로 script code 내에 Java 를 사용할수도 있고 Java code 에서 scriptella 를 호출할 수도 있으므로 연계와 확장이 쉬워진다.
  4. ant 의 <sql> task 를 지원한다.
  5. 다양한 Driver 를 지원한다. (CSV, LDAP, JDBC, 

 

설치 및 실행

 scriptella 사이트에서 다운로드후 압축을 해제하고 PATH 에 추가

Command line

scriptella 를 command line 에서 실행하면 현재 폴더에서 템플릿 파일(etl.xml) 을 찾아서 수행함. ETL 파일명이 다를 경우(Ex: my-etl.xml) cmd option 으로 전달

 

 

scriptella my-etl.xml
CODE

템플릿 파일이 없다면 -t 옵션으로 생성할 수 있다. 

scriptella -t
CODE

 

 

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
JAVA

 

 

 

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)

  1. http://poi.apache.org/download.html 에서 POI 다운로드
    1. wget http://apache.mirror.cdnetworks.com/poi/release/bin/poi-bin-3.9-20121203.zip
    2. poi-3.9-20121203.jar 와 poi-ooxml-3.9-20121203.jar 를 압축 해제한다.

  2. jsqlparser-0.8.0.jar 다운로드

    SQLSheet 는 jsqlparser 라는 JavaCC 기반의 SQL 파서 라이브러리를 사용하는데 한글 입출력이 불가한 문제가 있다.

     jsqlparser 에서 Unicode 사용하기 를 참고해서 소스를 수정하고 re-compile 하거나 페이지에 첨부된 파일을 다운로드한다.

  3. sqlsheet-6.5.jar 다운로드

사용

  1. 테스트를 위한 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);
    SQL

  2. 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&amp;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>
    XML
  3. scriptella -d etl.xml 실행후 report.xls 을 excel 로 열어서 확인

 

제한

  1. 한글 컬럼명은 사용할 수 없다. (쓰려면  jsqlparser 를 수정해야 한다.)
  2. INSERT 구문에서 VARCHAR2 를 입력하려면 '' 로 둘러싸야 한다.
  3. OS 의 encoding 과 DB의 encoding 이 안 맞으면 에러가 발생하니 DB 의 encoding과 OS 의 encoding 을 같게 해야 한다. 
    1. Windows 의 cmd 의 경우 euc-kr 이라 xls 로 저장시 문제가 발생할 수 있다. chcp 65001 로 UTF-8 로 encoding을 변경한다.
    2. RHEL/CentOS  Linux 의 경우 LANG 변수가 ko_KR.UTF-8 여야 한다. 아니면 export LANG=ko_KR.UTF-8 를 실행해서 locale 을 변경한다.
  4. 1

 

 

Janino

Super small Java Compiler(http://scriptella.javaforge.com/docs/api/scriptella/driver/janino/package-summary.html#package_description)

설치

  1. http://docs.codehaus.org/display/JANINO/Download 에서 다운로드, 

    scriptella-1.1 과 Janino 2.7.0 을 사용시 Missing @Override 에러가 발생하므로 Janino 2.6.2 를 사용해야 함

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

Janino as an Expression Evaluator

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

 

사용예

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

  1. DB 설정 properties 및 etl 파일 생성

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

       

       

    2. vi emp-etl.xml

      script 로 실행할 SQL 이 많을 경우 별도의 sql 파일을 작성후에 include 문을 사용할 수 있다.

      <script connection-id="emp ">
    <include href="schema.sql" />
</script>
      CODE 
      <!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>
      CODE

  2. etl  실행

    scriptella emp-etl.xml
    CODE

 

 

 

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

또는 다음처럼 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>
CODE