본문 바로가기
ETL Tools/Oracle Data Integrator

ODI Subquery KM

천정현 2010. 5. 18. 14:22

1      개요

-       subquery문 ETL Job 수행

-       수행시 Temp table 미사용

-       Interface와 패키지로 구성

-       Hardcoding 최소화

 

1.1    업데이트 사항

 

V1.0

V2.0

SQL Query 저장

Database내 data로 저장

Java 변수에 저장

사전환경 구축

DB Schema 내 Oracle Procedure 생성

SQL Query 저장 Table 생성

없음

실행 로직 구현

Oracle Procedure 사용

Java logic 사용

데이터 적재 문법

Inssert Select

Select Insert

사용 KM

IKM

LKM, IKM

활용 범위

하나의 Instance 내에서만 사용 가능

(원인 : 데이터 적재 방법)

다른 Instance 간에도 활용 가능

로그

Jython Exception 을 통해 확인 가능

확인 가능

오류

CLOB Type 설정 필요

없음

 

1.2    구현할 Query

select nvl(p.name,s.name),

       nvl(P.ITEM_ID,S.ITEM_ID),

       nvl(P.YYYYMMDD,S.YYYYMMDD),

       nvl(P.FAC_NO,S.FAC_NO),

       NVL(P.QTY,S.QTY)

from (     

    select

        ITEM_ID,

        YYYYMMDD,

        FAC_NO,

        'product' name,

        sum(PRODUCT_QTY - NG_QTY) QTY

    from PRODUCTION

    group by

        ITEM_ID,

        YYYYMMDD,

        FAC_NO

        ) P,

    (   

    select

        ITEM_ID,

        YYYYMMDD,

        FAC_NO,

        'shipment' name,

        sum(SHIP_QTY) QTY

    from  SHIPMENT

    group by

        ITEM_ID,

        YYYYMMDD,

        FAC_NO

        ) S

where   (1=1)

and        P.ITEM_ID=S.ITEM_ID

and        P.YYYYMMDD=S.YYYYMMDD

and        P.FAC_NO=S.FAC_NO

2      환경 구성
* Topology 현황

2.1    필요한 KM

Import

1. KM_IKM Oracle WideTNS SQ.xml

2. KM_LKM Oracle WideTNS SQ.xml

2.2    Source Table 생성

 

CREATE TABLE S_LCD.PRODUCTION

(

           ITEM_ID            NUMBER,

           YYYYMMDD           VARCHAR2 (8),

           PRODUCT_QTY        NUMBER,

           NG_QTY             NUMBER,

           FAC_NO             NUMBER (1),

           LINE_CD            VARCHAR2 (5),

           CREATION_DATE      DATE,

           LAST_UPDATE_DATE   DATE,

           CREATED_BY         VARCHAR2 (10),

           LAST_UPDATE_BY     VARCHAR2 (10)

);

CREATE TABLE S_LCD.SHIPMENT

(

           ITEM_ID            NUMBER,

           YYYYMMDD           VARCHAR2 (8),

           SHIP_QTY           NUMBER,

           FAC_NO             NUMBER (1),

           DETPO              VARCHAR2 (5),

           CREATION_DATE      DATE,

           LAST_UPDATE_DATE   DATE,

           CREATED_BY         VARCHAR2 (10),

           LAST_UPDATE_BY     VARCHAR2 (10)

);

 

2.3    Target Table 생성

CREATE TABLE T_PRODUCTION_SHIPMENT

(

           ITEM_ID    NUMBER,

           YYYYMMDD   VARCHAR2 (8),

           FAC_NO     NUMBER,

           NAME       VARCHAR2 (8),

           QTY        NUMBER

)

 

3      인터페이스 생성

3.1    SubQuery 인터페이스 생성 (PROD)

select

        ITEM_ID,

        YYYYMMDD,

        FAC_NO,

        'product' name,

        sum(PRODUCT_QTY - NG_QTY) QTY

    from PRODUCTION

    group by

        ITEM_ID,

        YYYYMMDD,

        FAC_NO

 

3.1.1 정의

3.1.2 다이어그램

3.1.3 흐름

 * 옵션 Create List = “예” : 최초 실행하는 단계에는 Query를 저장하는 List 변수를 생성하도록 설정한다.
 - Query를 저장하는 java.util.HashMap 변수 생성하는 옵션
 - 패키지 구성시 해당 인터페이스가 최초로 실행되어야 한다.

3.2    SubQuery 인터페이스 생성 (SHIP)

select

        ITEM_ID,

        YYYYMMDD,

        FAC_NO,

        'shipment' name,

        sum(SHIP_QTY) QTY

    from  SHIPMENT

    group by

        ITEM_ID,

        YYYYMMDD,

        FAC_NO

 

3.2.1 정의

3.2.2 다이어그램

3.2.3 흐름

 * 옵션 : 아니요

3.3    MainQuery인터페이스 생성

* 생성된 SQ들로 실 타겟에 적재하는 Interface 생성

 

Select    nvl(SQ_PROD.name,SQ_SHIP.name) name ,

nvl(SQ_PROD.ITEM_ID,SQ_SHIP.ITEM_ID) item_id,

nvl(SQ_PROD.YYYYMMDD,SQ_SHIP.YYYYMMDD) yyyymmdd,

nvl(SQ_PROD.FAC_NO,SQ_SHIP.FAC_NO)  FAC_NO,

NVL(SQ_PROD.QTY,SQ_SHIP.QTY) QTY

From    SQ_PROD,

SQ_SHIP

Where   (1=1)

And      SQ_PROD.ITEM_ID=SQ_SHIP.ITEM_ID

And      SQ_PROD.YYYYMMDD=SQ_SHIP.YYYYMMDD

And      SQ_PROD.FAC_NO=SQ_SHIP.FAC_NO

 

3.3.1 정의

3.3.2 다이어그램

3.3.3 흐름

* 사용 KM :
 - LKM : LKM Oracle WideTNS SQ
 - IKM : IKM Dummy (아무작업도 하지 않는 KM)

 

4      패키지 생성

5       테스트 및 결과 확인

5.1      테스트 결과

 

5.2    로그확인

5.2.1 IF.SQ_PROD Interface SQL Query

5.2.2 IF.SQ_SHIP Interface SQL Query

5.2.3 IF.T_PROD…_SHIP… Interface SQL Query

5.2.4 Final SQL Query

** KM 설명

1. KM 명

LKM Oracle SQ WideTNS_V2.0.0.1

2. 종류

LKM

3. 목적

해당 KM은 Interface를 이용하여 서브쿼리를 구현하지 못하는 단점을 보완하기 위해 개발한 KM이다.

4. 사용 설명

해당 KM은 Subquery를 포함한 Select문을 통한 데이터 추출시에, 구현된 Subquery를 사용하여 실 Target에 적재시에 사용한다. 데이터 적재 방법은 Select, Insert 방식을 사용한다.

 

5. 구성

 

단계

대상명령

원본명령

설명

 

Generate Query

<%
String query = "select  "+ odiRef.getPop("DISTINCT_ROWS") +
 odiRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [COL_NAME]", ",\n\t", "\n", "") +
"  from  " + odiRef.getFrom() +
" where (1=1) " +
odiRef.getFilter() +
odiRef.getJoin() +
odiRef.getJrnFilter() +
odiRef.getGrpBy() +
odiRef.getHaving();
map.put(odiRef.getTargetTable("TABLE_NAME"),query);
//list.add(query);
%>

query를 생성하여 target 테이블명과 함께 리스트 변수에 삽입

 

Substitution Query

<%
Set set = map.keySet();
Object []hmKeys = set.toArray();

String innerkey = null;
String outerkey = null;

String innerQuery = null;
String outerQuery = null;

for(int i = 0; i < hmKeys.length; i++){
 outerkey = (String)hmKeys[i];
 outerQuery = (String)map.get(outerkey);
 for (int j = hmKeys.length - 1; j >= 0; j--)
 {
  innerkey = (String)hmKeys[j];
  innerQuery = (String)map.get(innerkey);
  outerQuery = outerQuery.replaceFirst("(<..snpRef.getObjectName[^\\s]*"+innerkey+"[^\\s]*>)", " (" + innerQuery + ")");
 }
 map.put(outerkey, outerQuery);
}
%>

서브쿼리 부분을 Table 명으로 찾아내어 query로 치환

 

insert data into target

insert into <%=odiRef.getTable("L", "TARG_NAME", "A")%>
(
 <%=odiRef.getColList("", "[COL_NAME]", ",\n\t", "","")%>
)
values
(
 <%=odiRef.getColList("", ":[COL_NAME]", ",\n\t", "","")%>
)

<%=map.get(snpRef.getTargetTable("TABLE_NAME"))%>

Target Table에 Data 적재

 

commit

/*commit*/

Commit

 

 

1. KM 명

IKM Oracle SQ WideTNS_V2.0

2. 종류

IKM

3. 목적

해당 KM은 Interface를 이용하여 서브쿼리를 구현하지 못하는 단점을 보완하기 위해 개발한 KM이다.

4. 사용 설명

해당 KM은 Subquery를 포함한 Select문을 통한 데이터 추출시에, Subquery부분을 구현 할 때에 사용되는 KM이다.

5. 구성

단계

대상명령

원본명령

설명

Create List

<%
import java.util.*;
//ArrayList list = new ArrayList();
HashMap map = new HashMap();
%>

query를 저장할 리스트 변수 생성

Generate Query

<%
String query = "select  "+ odiRef.getPop("DISTINCT_ROWS") +
 odiRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "\n", "") +
"  from  " + odiRef.getFrom() +
" where (1=1) " +
odiRef.getFilter() +
odiRef.getJoin() +
odiRef.getJrnFilter() +
odiRef.getGrpBy() +
odiRef.getHaving();
map.put(odiRef.getTargetTable("TABLE_NAME"),query);
//list.add(query);
%>

query를 생성하여 target 테이블명과 함께 리스트 변수에 삽입

** 보완해야 할 사항.

-      With as 절 추가

-      KM 통합화

 

Reference Documents & KMs :
ODI_SUBQUERY_KM.zip



저작자표시

'ETL Tools/Oracle Data Integrator' Related Articles

티스토리툴바