필자는 지금 리눅스 환경에서 sqlloader를 이용해 csv파일을 데이터베이스에 적재하려고 한다. centos를 사용하였으며 oracle 데이터베이스를 활용하였다. 근데 계속 csv 파일이 넣어지지 않는 현상이 일어나서 n시간동안 삽질해 해결한 내용을 기록한다.
한국어로 해결법이 나온 내용이 없었기에... 모두에게 도움이 되고자...

테이블 생성 쿼리

우선 데이터는 이렇게 구성되어 있다. 그렇기에 아래와 같이 쿼리를 작성하였다.

CREATE TABLE MYTERROR (
    eventid      CHAR(15),
    iyear        INTEGER,
    imonth       INTEGER,
    iday         INTEGER,
    country      INTEGER,
    country_txt  VARCHAR2(50),
    region       INTEGER,
    region_txt   VARCHAR2(50),
    provstate    VARCHAR2(50),
    city         VARCHAR2(50),
    latitude     FLOAT,
    longitude    FLOAT
);

---

tbl 파일

여러가지 시도를 통해서 만든 tbl파일은 아래와 같다.

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'myterror.csv'
INTO TABLE MYTERROR
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    eventid      CHAR(15),
    iyear        INTEGER EXTERNAL,
    imonth       INTEGER EXTERNAL,
    iday         INTEGER EXTERNAL,
    country      INTEGER EXTERNAL,
    country_txt  CHAR(50),
    region       INTEGER EXTERNAL,
    region_txt   CHAR(50),
    provstate    CHAR(50),
    city         CHAR(50),
    latitude     FLOAT EXTERNAL,
    longitude    FLOAT EXTERNAL
)

여기서 첫줄 options는 내가 있는 데이터파일이 첫행이 칼럼명을 표현하고 있기에 스킵하는 것이다. 그리고 integer뒤에 있는 external유형은 문자열 데이터를 정수로 변환하여 처리하는 것이다. 근데 만약 데이터 파일에 숫자가 아닌 문자가 포함되어 있거나 형식이 맞지 않는 경우 잘못된 변환으로 이어질 수 있다고 한다.

그런다음 sqlldr userid=hr/1234 control=myterror.ctl log=mylog.log 이 명령어를 통해 결과를 확인해보자.

---

에러

실제로 적재된 데이터를 보면
201301100013 858861618 824979756 892939312 1634879788 q 1294741553 iddle East & North Africa Diyala Baqubah 0.0000103831289379741 0.000175670546013862 이런 데이터로 정수형, 실수형 즉 숫자 데이터가 제대로 반영되지 않고 이상하게 적재되는 것을 확인할 수 있었다.

그래서 길이를 명시적으로 지정해주어 좀 더 명확성을 높이고자 integer external(4) 이렇게 모두 길이를 지정해주었더니

    2 SQL*Loader: Release 11.2.0.2.0 - Production on 화 4월 23 10:22:43 2024
      3
      4 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
      5
      6 Control File:   test.ctl
      7 Data File:      myterror.csv
      8   Bad File:     myterror.bad
      9   Discard File:  none specified
     10
     11  (Allow all discards)
     12
     13 Number to load: ALL
     14 Number to skip: 1
     15 Errors allowed: 50
     16 Bind array:     64 rows, maximum of 256000 bytes
     17 Continuation:    none specified
     18 Path used:      Conventional
     19
     20 Table MYTERROR, loaded from every logical record.
     21 Insert option in effect for this table: INSERT
     22 TRAILING NULLCOLS option in effect
     23
     24    Column Name                  Position   Len  Term Encl Datatype
     25 ------------------------------ ---------- ----- ---- ---- ---------------------
     26 EVENTID                             FIRST    15   ,       CHARACTER
     27 IYEAR                                NEXT     4   ,       CHARACTER
     28 IMONTH                               NEXT     2   ,       CHARACTER
     29 IDAY                                 NEXT     2   ,       CHARACTER
     30 COUNTRY                              NEXT    10   ,       CHARACTER
     31 COUNTRY_TXT                          NEXT    50   ,       CHARACTER
     32 REGION                               NEXT    10   ,       CHARACTER
     33 REGION_TXT                           NEXT    50   ,       CHARACTER
     34 PROVSTATE                            NEXT    50   ,       CHARACTER
     35 CITY                                 NEXT    50   ,       CHARACTER
     36 LATITUDE                             NEXT    16   ,       CHARACTER
     37 LONGITUDE                            NEXT    16   ,       CHARACTER
     38
     39 Record 1: Rejected - Error on table MYTERROR, column LONGITUDE.
     40 ORA-01722: invalid number
     41
     42 Record 2: Rejected - Error on table MYTERROR, column LONGITUDE.
     43 ORA-01722: invalid number
     44

이렇게 마지막 칼럼에서 ORA-01722: invalid number 에러가나는 것을 확인할 수 있었다.

---

해결

예상치 못한 문자나 숨겨진 특수문자가 포함되어 있나.. 해서 csv를 모두다 뒤적거렸지만 찾아볼 수 없었다... 진짜 정보의 바다를 열심히 헤엄쳐본 결과 리눅스 환경에서 파일을 다룰 때 종종 발생하는 줄바꿈 문자 오류에 대한 내용을 찾아볼 수 있었다.

리눅스에서는 줄바꿈을 \n으로 처리하지만, 다른 운영체제에서 생성된 파일을 리눅스로 가져올 경우, 줄바꿈이 \r\n으로 되어 있을 수 있다. 이러한 줄바꿈 문자 차이로 인해 SQL*Loader가 데이터를 잘못 읽어들이고, 이로 인해 데이터 유형 오류(ORA-01722: invalid number)가 발생할 수 있다.

그래서 실제로 그런가? 싶어서 확인해보았다.

1. 파일의 줄바꿈 문자 검사: myterror.csv 파일을 열어 리눅스에서 인식하는 줄바꿈 문자 \n만 사용되었는지 확인한다. :

   cat -v myterror.csv

   이 명령어는 파일 내용을 표시하면서 숨겨진 제어 문자도 보여 준다. 만약 \r 문자(표시되면 ^M)가 보인다면, 이는 Windows 스타일의 줄바꿈 문자가 포함되어 있다는 뜻이다.
   확인해본 결과
   
   진짜 이녀석이 존재했던 것이였다...!!!!!!

2. 파일의 줄바꿈 문자 변환: 파일이 Windows 스타일의 줄바꿈 문자를 사용하고 있다면, 리눅스 스타일로 변환해야 한다. dos2unix 명령어를 사용하여 파일의 줄바꿈 문자를 변환할 수 있다:

   dos2unix myterror.csv

   이 명령은 Windows 스타일의 줄바꿈 (\r\n)을 리눅스 스타일의 줄바꿈 (\n)으로 변환한다.

3. sqlloader에게 형변환 맡기기: 나는 원래 tbl파일에 데이터유형을 다 작성해놨지만 이것이 리눅스와 형태가 맞지 않을수 있기에 아래와 같이 형식을 지정해주지 않았다.

      1 OPTIONS (SKIP=1)
      2 load data
      3 infile 'myterror.csv'
      4 insert into table myterror
      5 fields terminated by ','
      6 trailing nullcols(
      7         eventid,
      8         iyear,
      9         imonth,
     10         iday,
     11         country,
     12         country_txt,
     13         region,
     14         region_txt,
     15         provstate,
     16         city,
     17         latitude,
     18         longitude
     19 )

그러고 나서 sqlldr userid=hr/1234 control=myterror.ctl log=mylog.log 명령어를 수행하고 dbeaver 에서 SELECT 쿼리를 날려보면?!?!?

ㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜㅜ 드디어 ㅜㅜㅜㅜㅜㅜㅜㅜㅜ 해결완료했다...!!!!
숫자형이 제대로 들어온 것을 확인할 수 있었다!
운영체제가 다르면 정말 여러가지로 생각해야할 것이 많음을 배운 것 같다...