[kettle_pdi] insert / update 시 중복문제 Duplicate entry 해결방법

문제점

 

Caused by: java.sql.SQLException: Duplicate entry 'PK1-PK2' for key 'PRIMARY'

 

단순히 insert/update 일뿐인데....  이런 에러가 계속 발생했다.

 

 

위 그림처럼 insert/update 디자인을 사용하고 있다.

cron 으로 5분에 한번씩 주기적으로 돌리는데, 하루에 4~5회씩 위 에러가 발생

- 로컬에서는 아무리 테스트해봐도 정상동작한다. 리눅스 서버에서 실행시 발생하는 문제다.

( 지금 생각해보니, 윈도우 GUI 환경보다 리눅스의 속도가 훨씬 빨라서 생기는 문제일 수도 .. )

- merge 때문은 아니다. 단일 프로세스일 경우에도 발생

 

거의 5일정도 삽질함... 

이건 거의 찾기 힘든 에러로 확인됨.... 

 

 

추측/ 고민했던점 

아래 내용은 다 실패한 내용이다.

1. Block this step until steps finish을 사용한 부분이 있었는데, 정지가 제대로 되지 않다고 추측 ( X )

2. Block this step until steps finish을 사용한 이유는 그 전에 delete를 했어서다. delete -> block -> upsert 

를 하기 위함인데, delete commit 이 되기전에, upsert 가 발생해서 그렇게 될것으로 추천 ( 반만 맞음 )

이경우 delay 1초하니 거의 에러 발생안함.

3. upsert 시 키 값을 정확히 비교하지 않아서라고 추측 ( X ) 

아닌곳도 있었지만 대부분 키값을 정확히 조건절에 넣어둬서 문제없었음

4. upsert 시 키 값을 조건절로 걸었고, update 될 키 필드를 업데이트 될 대상 필드로 [ Y ]로 넣었기 때문으로 추측 그래서 

[ N ] 으로 변경하여, 키값은 변경하지 않도록 처리 

( 이 처리는 의미없는 처리로 보임... 하든 안하든 상관 없을 듯 )

5. commit size 가 너무 커서 그런게 아닐까. 바로바로 커밋하면 되는게 아닐까 ?

아님.. 0으로 바꾸나 1로 바꾸나 똑같음

 

아무리 생각해도 문제는 .. 하나의 디자인에서 발생함

insert/update 앞에 어떤식으로 처리를 하던 드문드문 에러가 발생 

위 테스트외에 자잘한 테스트를 수행했을떄 내린 결론은 ... 케틀 문제임.. 

하나의 디자인을 사용자가 더 쪼개서 설정할 수 가 없었다..

 

우회방안

1. Dynamic SQL row 을 사용해보라는 조언이 있었음. 이경우에 사용할게 아니다.

2. low 쿼리로, 직정 upsert 문을 작성하려고 해봤음. -> 파라미터로 넘겨야할 값이 많아서 변경할 양도 많았고, 이걸로 한다 쳤을때 100% 에러가 없다는 보장은 없음 ( 그래도 가장 가능성이 높았지만.... 파라미터가 40개씩 되다보니... 수작업으로 할 경우 휴먼에러 발생이 크다 )

3. 위에 작성한것처럼 delay 1초를 건다. ( 약간의 효과는 있음 )

 

추가의견 ( 미확실 )

- 이전버전에서는 table output으로 upsert를 해결했던걸로 기억한다.  버전이 업그레이드 되면서 해당 디자인안쓰고 

insert/ update로 upsert 하는걸로 생각됨

 

 

결론 

공식문서를 계속봐도 이해가 안갔었다. 힌트가 되는 문구를 계속봤었는데도 이게 힌트인지 확실히 알지 못햇으나.. 여러가지 삽질하다보니 이게 맞는 방식으로 보인다

https://pentaho-public.atlassian.net/wiki/spaces/EAI/pages/371558126/Insert+-+Update 

Insert - Update - Pentaho Data Integration - Pentaho Community Wiki

위 url 에서 이런 문구가 있다.

Note: Due to the extra lookup this step performs slower then a normal Table Output step. Another option is to use the Table Output step with error handling what is described in the chapter Step Error Handling. "If you put a primary key on the ID (in this case the customer ID) the insert into the table causes an error. Because of the error handling you can pass the rows in error to the update step. Preliminary tests have shown this strategy of performing upserts to be three times faster in some situations (with a low updates to inserts ratio)."

 

참고 : 추가 조회로 인해 이 단계는 일반 테이블 출력 단계보다 느리게 수행됩니다. 또 다른 옵션은 단계 오류 처리 장에 설명된 오류 처리와 함께 테이블 출력 단계를 사용하는 것입니다 . "ID(이 경우 고객 ID)에 기본 키를 넣으면 테이블에 삽입하면 오류가 발생합니다. 오류 처리로 인해 오류가 있는 행을 업데이트 단계로 전달할 수 있습니다. 예비 테스트에서 이 전략이 나타났습니다. 일부 상황에서 upsert를 3배 더 빠르게 수행할 수 있습니다(삽입 비율에 대한 업데이트가 낮음)."

 

이 문장을 봤었는데, 이게 문제가 될지 몰랐다.  -> 일반 테이블출력 단계보다 느리게 수행된다. 

즉 이말은 느리게 수행되기 때문에 

update 되어야할게, 느리게 수행되어서 update 대상인지 파악을 못하고 insert 하는 걸로 보인다.

( "ID(이 경우 고객 ID)에 기본 키를 넣으면 테이블에 삽입하면 오류가 발생합니다.) 

 

키값이 중복된다는건.... 이미 존재하는 pk 를 또 insert 해서 생기는 문제다.

update 대상인데, insert 를 하기때문에 해당 에러가 발생하는걸로 보인다.

이런 동작이 왜 일어나는지 설명이 거의 없었는데, 위 note 가 설명해주는걸로 이해하고 있다.

 

따라서 이걸 해결하기 위해 Step Error Handling 에 따라가서 보니... 에러핸들링을 해줘야한다는 내용이 있다. 

https://pentaho-public.atlassian.net/wiki/spaces/EAI/pages/370966941/.09+Transformation+Steps 

 

그래서 아이디어를 얻고 나는 이렇게 해결했다.

insert/update 를 하고, 에러시에 update 를 다시 해주는 방식으로 ... 이렇게 하니 확실히 에러가 안나는 걸 확인

위 update는 단순 업데이트만을 위한 디자인

 

문제는 ... 모든 insert/update 에 해당 작업을 반복해줘야하는게 ... 문제다..

그래도 어떻게든 해결이 되었으니 다행이지... 

 

 

 

 

 

공식문서

공식문서가 업데이트 되면서 두곳에 존재하는데, 어딘있고 어딘없어서 둘다 확인해야함

https://pentaho-public.atlassian.net/wiki/spaces/EAI/pages/386799999/Block+this+step+until+steps+finish 

Block this step until steps finish - Pentaho Data Integration - Pentaho Community Wiki

https://help.hitachivantara.com/Documentation/Pentaho/8.2/Products/Data_Integration/Transformation_Step_Reference

Transformation Step Reference