주소 db 만들기 - SQL developer 임포트 기능 활용 & cmd에서 ctl 파일 활용

최근 회원가입 기능 구현 수업을 진행하면서 주소 검색기능을 구현했습니다.

당시에는 강사님께서 txt파일을 csv 파일로 변환해주셨고 그 파일로 진행했습니다.

 

집에와서 똑같이 세팅하려 했더니 csv파일은 없고 txt파일의 링크만 있어서

txt 파일을 다운받아서 db 구축을 진행했습니다.

 

 

---

대략적인 진행과정입니다.

 

 

1) 데이터 파일의 인코딩 처리.

>> 원본 데이터 자체를 UTF-8형식으로 저장한 뒤에 임포트를 진행하는 편이 편합니다.

 

2) 테이블 선 생성.

>> 너무나 당연한 얘기지만 테이블을 먼저 생성해놓고, 임포트를 진행해야 합니다. 

(테이블 안 만들고 데이터 임포트 시도해서 왜 안되지 했었다는...;)

 

3) 특정 컬럼 제외 임포트.

>> roadcode 라는 사용하지 않을 컬럼이 있어서 이걸 제외해야 했습니다. 

sql developer의 import 기능을 이용할 땐 GUI에서 클릭으로 간단히 제외가 가능합니다.

ctl 파일을 제작하여 임포트할 땐 삽입하고자 하는 컬럼명만 ctl파일에 기입해줌으로써 제외가 가능합니다.

 

그럼 시작!

 

---

임포트 준비

 

1) 주소 값을 가진 덤프파일 다운

http://www.wdev.co.kr/zip/data.asp

도로명주소 우편번호

(기간이 좀 지났고 일부 주소값이 누락되는 등 완전하지 않지만 강의와 동일한 자료 사용을 위해 사용했습니다.)

 

 

2) 주소 값을 담을 테이블 생성

CREATE TABLE newzipcode (
    zipcode            VARCHAR2(7),
    sido               VARCHAR2(20),
    sigungu            VARCHAR2(30),
    yubmyundong        VARCHAR2(20),
   ri                 VARCHAR2(20),
    roadname           VARCHAR2(100),
    buildingname       VARCHAR2(100)
);

 

 

---

명령 프롬프트에서 ctl 파일을 이용한 임포트

 

1) 덤프파일 준비

 

덤프파일에 사용하지 않는 컬럼이 있는데, 이를 제외하고 임포트하는 방법은 여러가지가 있습니다.

1. 테이블 생성시 덤프파일과 동일하게 컬럼을 생성하여 임포트 후에 사용하지 않는 컬럼을 제거한다.

2. ctl 작성시 skip할 컬럼 순서에 컬럼명을 무작위로 적고 뒤에 filler 를 추가해준다. (임포트시 skip됨)

3. 덤프 파일을 수정한 뒤 임포트한다.

 

 

저는 3번으로 진행해보겠습니다.

Excel을 이용해서 덤프파일에 필요없는 컬럼을 지우겠습니다.

 

원본 덤프파일입니다. 로 구분되어있고 불필요한 RoadCode 컬럼이 존재합니다

 

Excel에서 덤프파일을 열기 했을 때 나오는 팝업입니다

 

구분자를 설정해주면 미리보기 구역에 데이터 처리 결과가 보입니다

제거할 컬럼을 선택 후 건너뜀을 선택합니다

 

RoadCode가 제거된 모습입니다. 이 상태에서 저장합니다

 

덤프파일을 열어보면 RoadCode 컬럼이 제거되어 있습니다. 구분자도 바뀌어있습니다.

 

 

2) ctl파일 준비

SQL Loader

load data
infile 'D:/lib/newzipcode.txt'
insert into table newzipcode
fields terminated by "	"
trailing nullcols
(zipcode,
sido,
sigungu,
yubmyundong,
ri,
roadname,
buildingname)

덤프파일을 입력하는 명령을 진행할 ctl 파일을 준비합니다.

메모장에서

2행에 덤프파일 경로 3행에 테이블명 4행에 구분자 괄호 안에 만들어둔 테이블의 컬럼명을 순서대로 적어줍니다.

확장자는 모든 파일로 선택 후 파일명 뒤에 .ctl로 저장합니다.

저장하는 경로는 c:\app\계정명\product\18.0.0\dbhomeXE\bin 입니다

 

 

 

3) 명령프롬프트에서 임포트

그리고 명령프롬프트로 이동합니다.

cd c:\app\계정명\product\18.0.0\dbhomeXE\bin
sqlldr  userid=c##java/bit  control=newzipcode.ctl  log=newzipcode.log

ctl파일을 저장한 경로로 이동한 뒤

명령어를 실행하면 데이터 입력이 진행됩니다.

 

 

임포트 작업 시작화면

임포트 작업 완료

임포트 완료된 모습

 

 

 

 

 

SQL developer을 이용한 임포트

 

 

최초 덤프파일의 인코딩을 바꿔줍니다

 

다른이름으로 저장을 누른 후 인코딩을 UTF-8로 바꿔 저장합니다

인코딩이 UTF-8로 변경된 모습입니다

입력할 테이블을 우클릭 후 데이터 임포트를 선택합니다

데이터 임포트를 눌렀을 때 나오는 화면

찾아보기를 눌러 덤프파일을 불러옵니다.

덤프파일의 인코딩을 UTF-8로 해뒀기 때문에 SQL developer에서도 UTF-8로 하면 한글이 깨지지 않습니다.

구분자를 선택하면 파일 내용이 구분되어 나옵니다.

 

다음 누르면 나오는 화면입니다. 다시 한 번 다음을 누릅니다.

불러올 컬럼을 선택할 수 있고, 순서도 변경할 수 있습니다.

다음 누릅니다

완료 누르면 데이터 작업이 시작됩니다

삽입 작업이 진행되는 화면

임포트 완료

임포트 완료된 모습