Using suqueries in Oracle Data Integrator (ODI) interfaces for complex data integration requirements
ODI interface Subquery 구현 방법.
인터페이스에서 서브쿼리를 Temp Table을 생성하지 않고 SQL Query 관리를 통해 구현 가능함
환경 구성
Database Oracle 11g
Userid/Password test/test
구성해 볼 Query
INSERT INTO TEST.PRODUCT_SHIPMENT_DAY(
MODEL,
YYYYMMDD,
PRODUCT_QTY,
SHIPMENT_QTY )
select NVL(PROD.MODEL,SHIP.MODEL) MODEL,
nvl(PROD.YYYYMMDD,SHIP.YYYYMMDD) YYYYMMDD,
PROD.QTY PRODUCT_QTY,
SHIP.QTY SHIPMENT_QTY
from (
select SHIPMENT.MODEL MODEL,
to_char(SHIPMENT.YYYYMMDD,'yyyymmdd') YYYYMMDD,
sum(SHIPMENT.QTY) QTY
from TEST.SHIPMENT SHIPMENT
where (1=1)
And (SHIPMENT.YYYYMMDD >= to_date('20100421'||'000000','yyyymmddhh24miss')
And SHIPMENT.YYYYMMDD <= to_date('20100421'||'235959','yyyymmddhh24miss'))
Group By SHIPMENT.MODEL,
to_char(SHIPMENT.YYYYMMDD,'yyyymmdd')
) SHIP
FULL OUTER JOIN (
select PRODUCT.MODEL MODEL,
to_char(PRODUCT.YYYYMMDD,'yyyymmdd') YYYYMMDD,
SUM(PRODUCT.QTY) QTY
from TEST.PRODUCT PRODUCT
where (1=1)
And (PRODUCT.YYYYMMDD >= to_date('20100421'||'000000','yyyymmddhh24miss')
And PRODUCT.YYYYMMDD <= to_date('20100421'||'235959','yyyymmddhh24miss'))
Group By PRODUCT.MODEL,
to_char(PRODUCT.YYYYMMDD,'yyyymmdd')
) PROD
ON (SHIP.YYYYMMDD=PROD.YYYYMMDD) AND PROD.MODEL=SHIP.MODEL
where (1=1)
1. Source Table 생성
PRODUCT
CREATE TABLE TEST.PRODUCT
(
MODEL VARCHAR2 (10),
QTY NUMBER,
LINE VARCHAR2 (5),
YYYYMMDD DATE
)
SHIPMENT
CREATE TABLE TEST.SHIPMENT
(
MODEL VARCHAR2 (10),
QTY NUMBER,
LINE VARCHAR2 (5),
YYYYMMDD DATE
)
2. Target Table 생성
PRODUCT_SHIPMENT_DAY
CREATE TABLE TEST.PRODUCT_SHIPMENT_DAY
(
MODEL VARCHAR2 (10) NOT NULL,
YYYYMMDD VARCHAR2 (8) NOT NULL,
PRODUCT_QTY NUMBER,
SHIPMENT_QTY NUMBER
)
3. SubQuery 인터페이스 생성
정의 탭
이름 : IF.PROD_DAY
대상과 다른 준비영역 체크 : 모델과 같은 논리적 스키마 선택
다이어그램 탭 :
소스 설정
타겟 설정
데이터 스키마
이름 : PROD
DRAG & DROPQ방식, ‘열추가’ 명령를 통해 행 생성 및 EXPRESSION 설정
SQL에 맞게 매핑정보 설정.
select SHIPMENT.MODEL MODEL,
to_char(SHIPMENT.YYYYMMDD,'yyyymmdd') YYYYMMDD,
sum(SHIPMENT.QTY) QTY
from TEST.SHIPMENT SHIPMENT
where (1=1)
And (SHIPMENT.YYYYMMDD >= to_date('20100421'||'000000','yyyymmddhh24miss')
And SHIPMENT.YYYYMMDD <= to_date('20100421'||'235959','yyyymmddhh24miss'))
흐름 탭
첨부된 KM 사용
Create procedures 옵션 예 설정 : 초기 프로시저 생성.
- 같은 방식으로 2번째 서브쿼리를 나타내는 인터페이스 생성
select PRODUCT.MODEL MODEL,
to_char(PRODUCT.YYYYMMDD,'yyyymmdd') YYYYMMDD,
SUM(PRODUCT.QTY) QTY
from TEST.PRODUCT PRODUCT
where (1=1)
And (PRODUCT.YYYYMMDD >= to_date('20100421'||'000000','yyyymmddhh24miss')
And PRODUCT.YYYYMMDD <= to_date('20100421'||'235959','yyyymmddhh24miss'))
옵션값 기본 값 유지.
4. Main Query 인터페이스 생성
생성된 노랑 인터페이스를 사용하여 소스를 구성
SQL 매핑설정
select
NVL(PROD.MODEL,SHIP.MODEL) MODEL,
nvl(PROD.YYYYMMDD,SHIP.YYYYMMDD) YYYYMMDD,
PROD.QTY PRODUCT_QTY,
SHIP.QTY SHIPMENT_QTY
from TEST.SHIP SHIP
FULL OUTER JOIN TEST.PROD PROD
ON (SHIP.YYYYMMDD=PROD.YYYYMMDD) AND PROD.MODEL=SHIP.MODEL
where (1=1)
옵션 설정.
Commit; 예
Insert 예
5. Package 생성
SubQuery -> MainQuery 로 시나리오 진행
6. Test & 결과 확인
*** IKM Oracle Append( With as, SubQuery)
SQL Query를 table에 저장하여 실 테이블에 적재할 때 Table Name과 SubQuery 부분을 치환하여 새로운 쿼리를 생성한 후 실행.
KM 옵션 설명 :
commit : 커밋여부 설정
insert : insert작업을 할 것인지 여부 설정
- 타겟이 테이블인경우 예 설정.
- sql 구문이 오류가 발생하여 SQL Query 를 확인해야 할 경우 아니오 설정후
Debug: substituted subquery value 단계에서 확인 가능
drop meta: sql를 저장하는 테이블을 drop 할 것인지 여부 설정
create procedures : KM에서 사용하는 Oracle Procedure들을 사용할 것인지 여부 설정 ( 최초 1회 예 설정)
** Error Case
- Query 속의 FULL OUTER JOIN 문법이 알맞지 않은 경우
해결 방법 : Topology의 Oracle 기술 편집
** outer join 제거 후 실행하여 SubQuery가 잘 실행되는 지부터 확인한다.
- Insert 시 java.lang.NumberFormatException: For input string: "4294967295" 발생시
원인 : ODI내에서 CLOB 타입 지원 안하는 경우
해결책 : odiparams.bat file 수정
set ODI_ADDITIONAL_JAVA_OPTIONS="-Djava.security.policy=server.policy"
set ODI_ADDITIONAL_JAVA_OPTIONS=%ODI_ADDITIONAL_JAVA_OPTIONS% "-Doracledatabasemetadata.get_lob_precision=false"
** 보완해야 할 사항.
- With As 문 Test 중…
- Source와 Target이 다른 경우에도 사용 가능하도록 현재 IKM을 LKM과 IKM으로 분할하는 작업
KM_IKM Oracle Append(With as, SubQuery).xml