Greenplum 데이터 로딩 및 언로딩 방법

Greenplum에서 데이터를 로드하고 언로드하는 주요 방법들을 살펴보겠습니다.

INSERT
COPY
외부 테이블
gpload

이번 예제에서는 member_change 테이블을 사용하여 각 방법을 설명합니다.

CREATE TABLE public.member_change (
    member_id VARCHAR(64),
    phone_number VARCHAR(64),
    action CHAR(1),
    insert_date DATE
) WITH (APPENDONLY=TRUE, COMPRESSLEVEL=5)
DISTRIBUTED BY (member_id);

1. INSERT

INSERT INTO public.member_change VALUES('mem006', '13100000006', 'I', DATE '2011-12-03');
INSERT INTO public.member_change VALUES('mem002', '13100000002', 'D', DATE '2011-12-03');
INSERT INTO public.member_change VALUES('mem003', '13100000003', 'U', DATE '2011-12-03');

2. COPY

-- 데이터 로드
COPY public.member_change FROM '/home/gpadmin/member_history_init.dat' WITH DELIMITER ','
LOG ERRORS INTO error_member_change SEGMENT REJECT LIMIT 2 ROWS;

-- 데이터 언로드
COPY (SELECT * FROM member_change WHERE insert_date LIKE '2013%') TO '/data/export/tb_cp_02_2013.out';

member_history_init.dat 파일 내용:

mem001,13100000001,2011-12-01,3000-12-31,C,I,2011-12-01
mem002,13100000002,2011-12-01,3000-12-31,C,I,2011-12-01
mem003,13100000003,2011-12-01,3000-12-31,C,I,2011-12-01
mem004,13100000004,2011-12-01,3000-12-31,C,I,2011-12-01
mem005,13100000005,2011-12-01,3000-12-31,C,I,2011-12-01

3. 외부 테이블

외부 테이블은 Greenplum의 세그먼트 인스턴스 리소스를 활용하여 병렬적으로 데이터를 로드하거나 언로드할 수 있습니다.

읽기 전용 외부 테이블: 데이터 로드용, 수정 불가능
쓰기 가능한 외부 테이블: 데이터 언로드용, 테이블에서 레코드 선택 후 파일이나 명령 파이프라인 등에 출력

3.1 프로토콜 종류

gpfdist: gzip 또는 bzip2 압축 지원, 파일 패턴 매칭 가능
gpfdists: gpfdist의 보안 버전
file: 외부 파일이 세그먼트 호스트에 위치해야 함
gphdfs: HDFS에서 파일을 병렬로 읽음

3.2 gpfdist 사용법

(1) gpfdist 시작:

nohup gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log &
ps -ef | grep gpfdist
wget http://gpfdist_hostname:port/filename

(2) 데이터 쓰기를 위한 외부 테이블 생성:

CREATE EXTERNAL TABLE ext_tb_gf01(id INT, name TEXT)
LOCATION (
    'gpfdist://mdw:8081/*.txt',
    'gpfdist://mdw:8082/*.txt'
)
FORMAT 'TEXT' (DELIMITER '|' NULL '')
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 2;

(3) 데이터 읽기를 위한 쓰기 가능한 외부 테이블 생성:

CREATE WRITABLE EXTERNAL TABLE ext_tb_gf04 (LIKE tb_cp_02)
LOCATION ('gpfdist://mdw:8081/tb_cp_02.out')
FORMAT 'TEXT' (DELIMITER '|' NULL ' ')
DISTRIBUTED BY (id);

INSERT INTO ext_tb_gf04 SELECT * FROM tb_cp_02;

(4) OS 명령형 웹 외부 테이블 정의:

DROP EXTERNAL TABLE IF EXISTS public.ext_tb_wb01;
CREATE EXTERNAL WEB TABLE ext_tb_wb01(output TEXT)
EXECUTE 'hostname'
FORMAT 'TEXT';

(5) URL 형 웹 외부 테이블 정의:

CREATE EXTERNAL WEB TABLE ext_tb_wb02 (name TEXT, date DATE, amount FLOAT4, category TEXT, description TEXT)
LOCATION (
    'http://intranet.company.com/expenses/marketing/file.csv',
    'http://intranet.company.com/expenses/eng/file.csv')
FORMAT 'CSV' (HEADER);

4. gpload

gpload은 외부 테이블을 사용한 데이터 로딩 과정을 간편하게 관리할 수 있는 도구입니다. 먼저 gpload 제어 파일인 gpload.yml을 작성합니다.

---
VERSION: 1.0.0.1
DATABASE: test
USER: gpadmin
HOST: localhost
PORT: 5432
GPLOAD:
  INPUT:
    - SOURCE:
        LOCAL_HOSTNAME:
          - master
        PORT: 8888
        FILE:
          - /home/gpadmin/data/member_change.dat
    - COLUMNS:
        - member_id: VARCHAR(64)
        - phone_number: VARCHAR(20)
        - action: CHAR(1)
        - insert_date: DATE
    - FORMAT: text
    - DELIMITER: ','
    - ERROR_LIMIT: 2
    - ERROR_TABLE: public.member_err
  OUTPUT:
    - TABLE: public.member_change
    - MODE: INSERT
  SQL:
    - BEFORE: "TRUNCATE TABLE public.member_change"
    - AFTER: "ANALYZE public.member_change"

gpload 실행 결과:

gpload -f gpload.yml

2018-05-18 20:53:10|INFO|gpload 세션 시작 2018-05-18 20:53:10
2018-05-18 20:53:10|INFO|gpfdist 시작 -p 8888 -P 8889 -f "/home/gpadmin/data/member_change.dat" -t 30
2018-05-18 20:53:11|INFO|실행 시간: 0.85 초
2018-05-18 20:53:11|INFO|삽입된 행 수          = 1
2018-05-18 20:53:11|INFO|업데이트된 행 수         = 0
2018-05-18 20:53:11|INFO|데이터 포맷팅 오류 = 0
2018-05-18 20:53:11|INFO|gpload 성공

태그: Greenplum 데이터 로딩 데이터 언로딩 외부 테이블 gpload

9월 23일 14:28에 게시됨

괴물 클럽