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