PostgreSQL에서 오라클의 External table 처럼 사용하기. foreign data 및 copy 사용.

postgresql 에서 대용량 데이터 csv나 txt 파일을 읽어서 사용하거나 물리테이블에 적재해야 할 경우 사용가능한 방법.

*외부테이블(foreign table)을 사용할 경우(오라클의 외부테이블(oracle External table) 처럼 사용하기)

-파일 자체를 데이터로 사용가능함.

-파일을 읽어서 필요한 select 쿼리를 만들어 사용할 수 있음.

-실제 postgresql상의 물리테이블은 아님.

(예) 원본 파일정보(csv/txt) (국어점수, 영어점수, 수학점수)    => 적재대상 테이블 (국어점수, 영어점수,수학점수 , 총점) 이렇게 있을 때 외부테이블을 만든다음 적재대상 테이블에 쿼리로 삽입가능 (copy자체보다는 느리지만 원본파일을 직접 읽어 처리할 수 있음)

==> 사용법 (아주 간단함)

1) 일단 extention을 설치.

CREATE EXTENSION file_fdw;

2) 서버 생성.

CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw

3) FOREIGN TABLE 생성.

CREATE FOREIGN TABLE 성적외부테이블 (

국어 numeric(10,2),

영어 numeric(10,2),

수학 numeric(10,2),

) SERVER file_fdw_server

OPTIONS (format 'text', header 'true' ,filename '/mnt/splee/fdw_test.txt', delimiter '|', null '');

4)사용하기

-insert into 성적(국어,영어,수학) 

 select 국어, 영어, 수학, (국어+영어+수학) from 성적외부테이블;

5) 삭제하기 DROP FOREIGN TABLE if exists "테이블 명

*postgresql copy 를 사용할 경우 

 -대용량 데이터를 처리하기에 좋음. 속도빠름.

-(단점) 원본데이터와 적재대상 테이블의 스키마 정보가 일치해야 함. 

읽은 데이터를 살짝 변경해서 테이블에 적재해야 할 경우가 있을 때는 copy로 로드 후 총점 update나 새로운 테이블에 insert select 할때 총점을 계산해서 넣어야 함.

(예) 원본 파일정보(csv/txt) (국어점수, 영어점수, 수학점수)    => 적재대상 테이블 (국어점수, 영어점수,수학점수 , 총점) 이렇게 있을 때 적재대상의 총점을 구하기 위해서는 update나 다른 테이블에 계산해서 넣어야 함.

※copy 사용하여 데이터 적재

1) 적재대상 테이블 생성

CREATE TABLE 성적(

국어 numeric(10,2),

영어 numeric(10,2),

수학 numeric(10,2),

총점 numeric(10.2)

);

2)  copy 를 이용해 적재

COPY 성적(

국어, 영어, 수학        

)

FROM 'D:\시험점수.csv' DELIMITER '|' CSV HEADER encoding 'EUC-KR'  (파일구분자는 파이프, 헤더를 사용함, 인코딩은 EUC-KR)

3) 총점을 update 하거나 생성해줘야 함.