NiFi에서 Oracle timestamp를 문자열로 적재하기 — logon trigger로 세션 NLS 맞추기

NiFi로 Oracle 테이블을 다른 Oracle 테이블로 복사할 때, timestamp 컬럼은 조회 SQL에서 문자열로 바꿔 넘기고 적재할 때 Oracle이 다시 timestamp로 바꾸게 할 수 있습니다. 그런데 이렇게 구성하고 실행하면 적재가 변환 오류로 failure에 쌓입니다. 원인은 NiFi 설정이 아니라 Oracle 세션의 NLS에 있습니다.

이 글은 Sqoop을 NiFi로 대체하는 프로젝트(DataDynamics/nifi-sqoop-removal)에서 만든 Oracle→Oracle 복제 시험 Flow(ExecuteSQL + PutDatabaseRecord)를 바탕으로 합니다. 문자열 timestamp가 왜 적재되지 않는지, 그리고 logon trigger로 이 Flow의 세션에만 NLS를 맞춰 5만 건을 원본과 완전히 일치시킨 방법을 정리합니다.

시험 환경은 Cloudera CFM 4.12(NiFi 2.6)와 Oracle 23 Free(FREEPDB1)이며 2026-10-07에 실행했습니다. 예시 값은 모두 시험 환경 기준입니다.

1. 원칙: NiFi는 문자열만 나르고, 변환은 Oracle이 한다

이 Flow는 timestamp 변환 지점을 Oracle 한 곳으로 모읍니다.

1. 조회: CREATED_AT을 SQL에서 TO_CHAR로 문자열로 만듭니다. ExecuteSQL은 그 결과(VARCHAR2)를 Avro string으로 쓰고, AvroReader는 Avro 파일에 들어 있는 스키마를 그대로 씁니다. NiFi 안에서 CREATED_AT은 끝까지 "26/03/08 02:52:36.547885"라는 문자열이고, NiFi는 이 값을 해석하지 않습니다.
2. 적재: PutDatabaseRecord가 TIMESTAMP 컬럼에 문자열을 바인딩하면, Oracle이 세션의 NLS_TIMESTAMP_FORMAT으로 암묵 변환합니다.

형식은 조회 SQL의 TO_CHAR에서 직접 정하므로 NiFi의 Reader 스키마나 Timestamp Format 속성을 맞출 필요가 없습니다. 남은 조건은 하나입니다. 적재 세션의 NLS_TIMESTAMP_FORMAT이 조회 SQL의 TO_CHAR 형식과 같아야 합니다.

2. 함정: JDBC thin 드라이버로는 NLS 형식을 지정할 수 없다

Oracle JDBC thin 드라이버는 세션 NLS를 JVM locale로 정합니다. NiFi JVM locale이 en_US이면 세션의 기본 형식은 다음과 같습니다.

NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM

이 형식으로는 26/03/08 02:52:36.547885를 해석할 수 없어 적재가 변환 오류로 실패합니다. 그런데 JDBC URL이나 연결 속성으로 NLS 형식을 지정하는 방법은 없습니다. NiFi의 Connection Pool에도 "세션을 만든 직후 실행할 SQL" 같은 속성이 없습니다.

JVM locale을 바꾸는 방법도 있지만, NiFi 클러스터 전체에 영향을 주고 재시작이 필요합니다. 그래서 DB 쪽에서 이 Flow의 세션에만 NLS를 적용하기로 했습니다. 방법은 logon trigger입니다.

3. logon trigger + v$session.program으로 이 Flow의 세션만 골라내기

같은 접속 계정(NIFI_READER)을 다른 NiFi Flow도 쓰고 있었습니다. 계정 단위로 NLS를 바꾸면 그 Flow들까지 영향을 받습니다. 그래서 세션을 하나 더 구분할 표식이 필요합니다.

Oracle JDBC 드라이버는 연결 속성 v$session.program을 받으면 그 값을 로그인 정보와 함께 보내고, Oracle은 이를 v$session.program에 기록합니다. NiFi HikariCPConnectionPool의 동적 속성(dynamic property) 은 JDBC 연결 속성으로 그대로 전달되므로, Pool에 다음 속성 하나만 추가하면 됩니다.

그리고 logon trigger는 계정이 NIFI_READER이고 program이 NIFI_TMP_TEST_COPY인 세션에만 ALTER SESSION을 실행합니다.

create or replace trigger SYS.TRG_NIFI_TMP_TEST_NLS after logon on database
declare
  v_prog varchar2(100);
begin
  if sys_context('USERENV','SESSION_USER') = 'NIFI_READER' then
    select program into v_prog from v$session where sid = sys_context('USERENV','SID');
    if v_prog = 'NIFI_TMP_TEST_COPY' then
      execute immediate q'[alter session set nls_timestamp_format = 'RR/MM/DD HH24:MI:SSXFF']';
    end if;
  end if;
exception when others then null;
end;
/

• NIFI_READER는 NiFi가 Oracle에 접속하는 계정으로 바꿉니다.
• NIFI_TMP_TEST_COPY는 Pool의 v$session.program 값과 같아야 합니다. 시험용 이름이므로 운영에서는 APPLY_NLS_TIMESTAMP_FORMAT처럼 의미가 드러나는 이름을 권장합니다.
• exception when others then null은 trigger 오류 때문에 데이터베이스 전체 로그인이 막히는 것을 방지합니다. 대신 trigger가 실패하면 NLS가 적용되지 않습니다. 이 경우 적재가 변환 오류로 실패하므로 문제는 곧바로 드러납니다.

같은 NIFI_READER 계정을 쓰는 다른 Flow의 세션은 program이 기본값 JDBC Thin Client이므로 trigger가 건너뜁니다.

4. Connection Pool과 logon trigger의 수명주기

여기서 자연스럽게 생기는 의문이 있습니다. Pool은 연결을 재사용하는데, trigger가 매번 실행될까요?

logon trigger는 커넥션을 빌릴 때마다가 아니라, Pool이 Oracle에 물리 연결을 새로 맺어 세션이 생길 때 한 번 실행됩니다. 그때 설정한 NLS는 세션이 끝날 때까지 유지됩니다.

1. HikariCP가 물리 연결을 만들 때 드라이버가 v$session.program = NIFI_TMP_TEST_COPY를 로그인 정보와 함께 보냅니다. 따라서 trigger가 실행되는 시점에 program 값이 이미 들어 있습니다.
2. 로그인이 끝나면 trigger가 조건에 맞는 세션에만 ALTER SESSION SET NLS_TIMESTAMP_FORMAT을 실행합니다.
3. Processor가 커넥션을 빌렸다 반납해도 물리 연결은 닫히지 않고 같은 세션이 재사용됩니다. HikariCP는 반납할 때 autoCommit, 트랜잭션 격리 수준, readOnly 같은 JDBC 상태만 되돌리고, ALTER SESSION으로 바꾼 NLS는 건드리지 않습니다.
4. 연결이 수명 만료나 오류로 정리되고 Pool이 새로 만들면 새 세션에서 trigger가 다시 실행됩니다.

결과적으로 Pool 안의 모든 세션은 항상 같은 NLS를 갖습니다. 운영에서 주의할 점은 다음과 같습니다.

• trigger를 만들거나 고친 뒤에는 Controller Service를 Disable→Enable합니다. 그 전에 열려 있던 연결에는 새 trigger가 적용되지 않습니다.
• 같은 Pool을 쓰는 ExecuteSQL 세션에도 NLS가 적용됩니다. 조회 SQL은 TO_CHAR에 형식을 직접 지정하므로 영향이 없습니다.
• 누군가 같은 세션에서 ALTER SESSION으로 NLS를 다시 바꾸면 그 값이 Pool에 남아 다음 사용자에게 이어집니다. 이 Flow에는 그런 SQL이 없습니다.

5. Oracle 준비

모든 문장은 FREEPDB1에서 관리자 권한으로 실행합니다.

원천 테이블과 시험 데이터

문자열 컬럼 10개, timestamp 컬럼 1개, 행을 구분하는 ID를 가진 테이블에 5만 건을 만듭니다. CREATED_AT은 2026-01-01부터 약 280일 범위의 무작위 시각(마이크로초 포함)입니다.

create table APP.TMP_TEST (
  ID      number(10) primary key,
  COL01   varchar2(100), COL02 varchar2(100), COL03 varchar2(100), COL04 varchar2(100), COL05 varchar2(100),
  COL06   varchar2(100), COL07 varchar2(100), COL08 varchar2(100), COL09 varchar2(100), COL10 varchar2(100),
  CREATED_AT timestamp
);
 
insert /*+ append */ into APP.TMP_TEST
select level,
  'A-'||level, dbms_random.string('U',10), dbms_random.string('L',20), dbms_random.string('A',15), dbms_random.string('X',12),
  'CODE'||lpad(mod(level,100),3,'0'), dbms_random.string('U',30), '홍길동'||mod(level,1000), dbms_random.string('A',50), to_char(level,'FM00000000'),
  timestamp '2026-01-01 00:00:00' + numtodsinterval(dbms_random.value(0, 280*86400), 'SECOND')
from dual connect by level <= 50000;
commit;
 
grant select on APP.TMP_TEST to NIFI_READER;

GLOBAL TEMPORARY TABLE은 다른 세션에서 데이터가 보이지 않아 NiFi가 읽을 수 없으므로 일반 테이블로 만듭니다.

대상 테이블과 권한

원천 스키마를 데이터 없이 복제하고 기본키를 겁니다. 별도 적재 계정은 만들지 않고, NIFI_READER에 이 테이블에 한정한 쓰기 권한을 줍니다.

create table APP.TMP_TEST_COPY as select * from APP.TMP_TEST where 1=0;
alter table APP.TMP_TEST_COPY add primary key (ID);
grant select, insert, delete on APP.TMP_TEST_COPY to NIFI_READER;

그다음 3절의 logon trigger를 만듭니다.

6. NiFi 구성

루트 PG 아래에 TEST_TMP_TEST_COPY PG를 만들고, 접속 정보는 공통 Parameter Context의 ORACLE.* 파라미터를 씁니다. Flow는 Processor 2개와 실패 보관용 Funnel 하나로 끝납니다.

Controller Service

ExecuteSQL은 Record Writer 없이 결과를 Avro로 쓰고 스키마를 Avro 파일 안에 넣습니다. 그래서 Reader에 스키마를 따로 적을 필요가 없습니다. 컬럼 타입은 ExecuteSQL이 JDBC 메타데이터로 정하므로, TO_CHAR 결과는 자연스럽게 string이 됩니다.

ExecuteSQL - TMP_TEST

SELECT ID, COL01, COL02, COL03, COL04, COL05, COL06, COL07, COL08, COL09, COL10,
       TO_CHAR(CREATED_AT, 'RR/MM/DD HH24:MI:SSXFF') AS CREATED_AT
  FROM APP.TMP_TEST

• TO_CHAR의 형식은 trigger가 설정하는 NLS_TIMESTAMP_FORMAT과 문자 하나까지 같아야 합니다.
• CREATED_AT은 26/03/21 03:58:04.706016처럼 나옵니다. 컬럼이 TIMESTAMP(6)이라 소수부는 6자리입니다.
• 입력 연결이 없는 Processor이므로 클러스터의 모든 노드에서 실행되지 않도록 Primary node로 둡니다.

PutDatabaseRecord - TMP_TEST_COPY

연결

7. 실행

1. Controller Service 두 개를 Enable합니다(trigger를 만든 뒤에 Enable해야 합니다).
2. PutDatabaseRecord를 Start합니다.
3. ExecuteSQL에서 Run Once를 실행합니다.
4. PG queue가 0이고 Funnel 앞 queue가 비어 있으면 완료입니다. 시험 환경에서는 FlowFile 5개(1만 건씩)가 수 초 안에 처리됐습니다.

다시 실행할 때는 truncate table APP.TMP_TEST_COPY를 먼저 합니다. ID가 기본키라 비우지 않으면 중복 키 오류로 failure에 쌓입니다.

8. 검증

select count(*) from APP.TMP_TEST_COPY;                                                   -- 50000
select count(*) from (select * from APP.TMP_TEST      minus select * from APP.TMP_TEST_COPY); -- 0
select count(*) from (select * from APP.TMP_TEST_COPY minus select * from APP.TMP_TEST);      -- 0
 
-- trigger가 이 Flow 세션에만 적용되는지 확인
select program, count(*) from v$session where username = 'NIFI_READER' group by program;

양방향 MINUS가 모두 0이므로 CREATED_AT의 마이크로초까지 포함해 컬럼 값 전체가 원본과 같습니다. v$session을 보면 같은 계정의 다른 세션 16개는 기본 program으로 남아 있고, 이 Flow의 세션 1개만 trigger 대상이었습니다.

9. 주의 사항

• 적재가 Oracle 세션 NLS에 의존합니다. trigger가 없거나 Pool의 v$session.program이 빠지면, 세션 기본 형식(DD-MON-RR HH.MI.SSXFF AM)으로 문자열을 변환하지 못해 failure에 쌓입니다. 값이 조용히 틀어지는 것이 아니라 실패로 드러나는 것은 장점입니다.
• RR은 두 자리 연도를 0049 → 20xx, 5099 → 19xx로 해석합니다. 1950년 이전이나 2050년 이후 값이 있으면 TO_CHAR와 NLS 모두 YYYY 형식을 써야 합니다.
• 시험이 끝나면 NIFI_READER에 준 APP.TMP_TEST_COPY 쓰기 권한을 회수합니다.

10. 정리

drop trigger SYS.TRG_NIFI_TMP_TEST_NLS;
drop table APP.TMP_TEST_COPY purge;
drop table APP.TMP_TEST purge;

NiFi에서는 TEST_TMP_TEST_COPY PG의 Processor를 Stop하고 queue를 비운 뒤, Controller Service를 Disable하고 PG를 삭제합니다.

마치며

문자열로 timestamp를 적재하는 구조에서는 세션 NLS가 조회 SQL과 적재 세션 사이의 계약입니다. TO_CHAR 형식과 NLS_TIMESTAMP_FORMAT이 같으면 NiFi는 값을 해석하지 않고 문자열로 나르기만 하면 되고, 변환은 Oracle 안에서 끝납니다.

JDBC thin 드라이버로 NLS를 지정할 수 없다는 제약은 logon trigger와 v$session.program 조합으로 풀었습니다. 이 방식은 같은 계정을 쓰는 다른 Flow에 영향을 주지 않으면서 특정 Connection Pool의 세션에만 NLS를 적용합니다. 전체 Flow 정의와 상세 문서는 nifi-sqoop-removal 저장소의 docs/appendix-b-oracle-copy-putdatabaserecord.md에 있습니다.