Greenplum DDL(테이블/인덱스 생성)

1.      테이블 & 파티션 테이블

1)        Data Type

           -       Greenplum의 Character Length 는 Byte 수가 아닌 Character 수 입니다. 
           -       Date는 날짜까지만 관리되고, 시분초까지 필요할 경우 Timestamp 사용 가능합니다.

구  분	Data Type	Size Required	Range	비  고
Integer	SmallInt	2 Byte	-32,768 ~ 32,767
	Integer	4 Byte	-231 ~ 231-1
	bigint	8 Byte	-263 ~ 263-1
Numeric	Numeric(m,n)	가변적	No limit
real	Float4	4 Byte	6 decimal digits precision	variable-precision, inexact
Date	Date	4 Byte	4713 BC - 5874897 AD	YYYY-MM-DD
	Time	8 Byte	00:00:00[.000000] - 24:00:00[.000000]	HH:MM:SS.ssssss
	Timestamp	8 Byte	4713 BC - 5874897 AD	Date + Time
Character	Char(n)	1+ N Byte	1 ~ 64,000 Byte
	Varchar(n)	1+ String size
	Text	1 byte + string size	strings of any length	variable unlimited length

n  Data Type 변환 예제

구  분	Oralce	Greenplum	비  고
Numeric	number(m,n)	Numeric(m,n)
	number(n)	Numeric(n)
	number	numeric	Oracle - 유효숫자 38 자리
Date	Date	Date	YYYY-MM-DD
		Timestamp	YYYY-MM-DD HH:MM:SS.ssssss
	Timestamp	Timestamp	YYYY-MM-DD HH:MM:SS.ssssss
Character	Char(n)	Char(n)
	Varchar2(n)	Varchar(n)
	clob	text	variable unlimited length

 

2)        테이블 분산키

n  분산키 설명

            -       User 데이터 저장방식. 분산키를 통하여 각 Segement Node 에 나누어 저장
            -       분산 유형
                 . 분산키 지정 → Hash (권고)
                 . 분산키 미지정 → Round-robin

-       데이터가 각 세그먼트별로 분산이어야지만 최대한의 Performance를 낼 수 가 있음

            -       데이터를 분산하기 위한 기준되는 컬럼이며, 단일 컬럼 또는 복합 컬럼을 사용할 수 있음.

n  권고 사항

            -       cardinality가 높은 칼럼 중심으로, 1~2의 칼럼으로 생성권고
            -       분산도가 높으며, 조인이 많이 발생되는 컬럼을 분산키로 권고
            -       분산도가 높은 PK 또는 UK 컬럼이 분산키의 대상
            -       Composite 분산 컬럼의 경우 대표성을 지닌 컬럼 위주로 설정을 하며, Max 2~3개 내외를 권고
            -       PK, UK 부재로 인하여 분산 컬럼을 선택하기 어려운 경우 Randomly를 이용
            -       Update가 빈번한 컬럼은 대상에서 제외
            -       Long Fields에 대해서는 대상에서 제외

n  제약 사항

            -       Unique index 및 Primary Key는 분산키의 superset 이어야 합니다.
            -       분산키를 Randomly 으로 생성시 조인 update 실행되지 않음.

3)        Table

n  테이블 설명

-       테이블은 Heap Table과 AO(Append only) Table 2가지 유형이 있으며, 테이블 저장 방식은 Row, Column Base 지원합니다.

-       테이블 파티션은 Range, List 및 Multi-Level을 지원합니다.

-       테이블명은 64Byte 까지 지원하며, 숫자 및 예약어의 경우에는 변경이 필요합니다

-       압축 테이블에서도 update/delete 지원(GPDB 4.3.X 이상)

 

n  제약 사항

-       Truncate 및 Drop 구문은 Table owner만 실행 가능

-- Create table create table schema.GP_MASTER (   GPID       numeric not null,   GPNAME     varchar(50) not null,   REG_DATE   DATE,   STATUS_YN  CHAR(1),   AGE        numeric,   BIRTH_DATE DATE ) DISTRIBUTED BY (gpid);     -- primary key constraints alter table schema.GP_MASTER   add constraint PK_GP_MASTER primary key (GPID) ;

1. 분산키를 기준으로 데이터 분산하며 2 가지 유형 지원( hash distribution, random distribution )    - 세그먼트 별로 골고루 분포되도록 분산키 설정해야 하며, 분산도가 높은 PK, UK 컬럼을 분산키로 활용 가능 함. 2. Primary Key 생성 Syntax 는 ANSI SQL.

4)        임시 테이블 생성

-- Create temporary table CREATE TEMP TABLE TEMP_GP_MASTER (   GPID NUMBER NOT NULL,   GPNAME VARCHAR(50) NOT NULL,   REG_DATE DATE,   status_yn CHAR(1) DEFAULT 'Y',   age NUMBER,   birth_date DATE )  ;

1. 임시 테이블 생성 구문    - Database 접속 세션이 유효한 상태에서만 존재하는 논리적인 테이블이므로      Schema 명을 기술할 필요 없이 바로 테이블 명 사용가능.

 

5)        CTAS (Create Table With Data) 생성문

CREATE TABLE gp_master_with [WITH (APPENDONLY=TRUE)] AS SELECT gpid, gpname, reg_date, status_yn FROM   gp_master a DISTRIBUTED BY (gpid);   ANALYZE gp_master_with;

1. 테이블 생성과 동시에 sql을 이용하여 데이터 입력구문    - with (appendonly=true) 구문은 읽기전용(select) 테이블에 한하며      Update, delete 구문 사용시 해당 옵션 적용 불가 2. 통계정보 생성

6)        압축 테이블

n  압축 설명

-       압축 방식은 QuickLZ와 glib 2가지 방식 지원하며, quickLZ는 빠른 압축이 장점이지만 압축율은 약   50~60%으로 glib 방식보다 압축율이 낮습니다.

-       Glib 방식의 압축율은 Level 1~9까지 선택가능하며, Level 1의 경우 67~70%, Level 5의 경우 약 80%입니다.

n  권고 사항

-       20GB 이상의 이력 테이블일 경우 파티션을 적용하고, 데이터가 변경 되지 않는 과거 시점의 데이터를 압축 적용

-       매일 전체 삭제하고 재 적재하는 경우 압축 테이블 적용

-       압축 방식은 glib 방식 및 Level 5 적용하는 것을 권고합니다.

n  제약 사항

-       PK 및 Unique Index 생성이 되지 않습니다.

CREATE TABLE gp_master_with ( gpid number(10),     val  number(10) ) WITH (APPENDONLY=TRUE, COMPRESSTYPE= ZLIB, COMPRESSLEVEL=1) DISTRIBUTED BY (gpid); ;

1. 테이블 생성시 압축 옵션 설정    - appendonly=true 구문은 읽기전용 테이블이며, compresslevel 은 1~9 레벨까지 있으며 권고치: 1~5.    - compresstype은 ZLIB, QUICKLZ 있으며 권고: ZLIB

 

7)        Partition Table

n  파티션 설명

-       대량 데이터 테이블에 대해 의미있는 칼럼을 기준으로 테이블을 분리하는 작업

           -       의미있는 칼럼을 where 조건으로 테이블 scna하는 경우, 쿼리 성능 향상을 목적으로 함

n  권고 사항

-       파티션의 경우 테이블당 300개 이하로 권고하며, 대상 테이블이 20GB 이상일 경우 파티션 권고합니다.(프로젝트 경험상이며, 본사 매뉴얼에는 해당 내용 없음)

-       파티션 타입은 일자의 경우 Range 파티션, 코드 값인 경우 List 파티션을 권고합니다.

-       파티션 컬럼이 문자인 경우에는 CHAR 타입보다는 VARCHAR 타입을 권고합니다.

 

n  파티션 테이블 생성 예제(파티션키: Date Type)

CREATE TABLE schema.GP_BUYING_LOG (    log_date timestamp NOT NULL,    gpid numeric NOT NULL,    prod_id  numeric(10),    buying_amt numeric(10,2) DEFAULT 0 ) DISTRIBUTED BY(gpid) PARTITION BY RANGE(log_date)       ( PARTITION p2008 START ('20080101'::TIMESTAMP) END ('20090101'::TIMESTAMP), PARTITION p2009 START ('20090101'::TIMESTAMP) END ('20100101'::TIMESTAMP), PARTITION p2010 START ('20100101'::TIMESTAMP) END ('20110101'::TIMESTAMP), DEFAULT PARTITION pother ) ;

1. 파티션 테이블 생성 구문    - Greenplum은 range, list, 복합 Partition을 지원하며, 실제 물리 파티션 테이블이 존재 2. 파티션 테이블 생성시 권고 사항    - 가능하면 파티션수는 1개 테이블당  200~300개 이하로 해주시기바랍니다.     (본사 권고 가이드에는 해당 내용 없음.)

 

n  파티션 테이블 생성 예제(파티션키: Char Type)

CREATE TABLE w_log_001 (   pdate varchar(4) NOT NULL DEFAULT to_char(current_date, 'mmdd'),   msrl varchar(12),   startdate varchar(15),   playtime numeric(5,0),   pms_code numeric(6,0) ) DISTRIBUTED BY (roomid,startdate) PARTITION BY RANGE(pdate)           (           PARTITION p0301 START ('0301'::varhcar) END ('0302'::varhcar),           PARTITION p0302 START ('0302'::varhcar) END ('0303'::varhcar),           ...... 중략 .....           PARTITION p0629 START ('0629'::varhcar) END ('0630'::varhcar),           PARTITION p0630 START ('0630'::varhcar) END ('0631'::varhcar),           DEFAULT PARTITION pother           ) ;

1. 파티션 테이블 생성시 권고 사항    - 기존에 날짜 타입의 List 파티션은 Range 파티션으로 변경 - 가능하면 파티션수는 1개 테이블당 200~300 개 이하 생성(프로젝트 경험치)

n  LIST 파티션 테이블 생성 및 압축 예제(파티션키: number)

-- GREENPLUM CREATE TABLE W_LOGIN_TRC (   PARTITION_KEY    NUMERIC(2) DEFAULT to_number(to_char(current_date,'MM'), ‘99’) NOT NULL,   SUBPARTITION_KEY NUMERIC(2) DEFAULT to_number(to_char(current_date,'DD'),‘99’) NOT NULL,   LOGIN_FROM         varchar(10),   SRL                  NUMERIC (10),   NUM_IP              NUMERIC (10),   DATE_LOGIN         TIMESTAMP,   RAW_MAC            VARCHAR(12),   RAW_GW_MAC         VARCHAR(12),   NUM_IP_CLIENTSIDE NUMERIC (10),   MAC2               varchar(12),   IS_CURRENT        int(1)                  DEFAULT 0 ) DISTRIBUTED BY (LOGIN_FROM)         PARTITION BY LIST(PARTITION_KEY)           (           PARTITION p01 values(1) with (appendonly=true, compresslevel=5),           PARTITION p02 values(2) with (appendonly=true, compresslevel=5),           .............. 중략 ...........           PARTITION p11 values(11) with (appendonly=true, compresslevel=1),           PARTITION p12 values(12) with (appendonly=true, compresslevel=1),           DEFAULT PARTITION pother           )           ;

1. 파티션 테이블 생성시 권고 사항 - 가능하면 파티션수는 1개 테이블당 200~300 개 이하 생성.(프로젝트 경험치)

 

n  파티션 테이블 Alter Command

-- Sample : Single Partition date column CREATE TABLE sales ( id int,   date date,   amt decimal(10,2) ) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( PARTITION  Oct08 START (date '2008-10-01') INCLUSIVE END(date '2008-11-01') EXCLUSIVE with (appendonly=true, compresslevel=9) ,   PARTITION Nov08 START (date '2008-11-01') INCLUSIVE END(date '2008-12-01') EXCLUSIVE with (appendonly=true, compresslevel=5),   PARTITION Dec08 START (date '2008-12-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE with (appendonly=true, compresslevel=1),   PARTITION Jan09 START (date '2009-01-01') INCLUSIVE END(date '2009-02-01') EXCLUSIVE,   DEFAULT PARTITION extra );   --Sample : Single Partition Weekly CREATE TABLE customer ( c_customer_id        INT, week_number          INT, . . . ) DISTRIBUTED BY (c_customer_id) PARTITION BY RANGE ( week_number ) (START (200701) END (200752) INCLUSIVE) ;   -- Sample : Add Partition ALTER TABLE sales        ADD PARTITION [파티션명] START (date '2009-02-01') INCLUSIVE        END (date '2009-03-01')  EXCLUSIVE;   -- Sample : Add Default Partition ALTER TABLE sales       ADD DEFAULT PARTITION OTHER;   ALTER TABLE sales ADD PARTITION [파티션명] START (date '2009-02-01') INCLUSIVE END   (date '2009-03-01') EXCLUSIVE;   -- Sample : Rename Partition ALTER TABLE sales       RENAME PARTITION 파티션명 RENAME TO NEW_파티션명; ALTER TABLE sales       RENAME PARTITION FOR (date '2008-01-01') TO JAN08;   -- Sample : DROP Partition ALTER TABLE sales       DROP PARTITION Oct08 ; ALTER TABLE sales       DROP PARTITION FOR (date ‘2008-12-01’);   -- Sample : TRUNCATE Partition ALTER TABLE sales       TRUNCATE PARTITION Oct08 ; ALTER TABLE sales       TRUNCATE PARTITION FOR (date ‘2008-12-01’);   -- Sample : Split Partition ALTER TABLE sales       SPLIT PARTITION FOR ('2008-01-01') AT ('2008-01-16')          INTO (PARTITION jan081to15,                PARTITION jan0816to31);   ALTER TABLE sales SPLIT DEFAULT PARTITION START ('2009-01-01') INCLUSIVE END ('2009-02-01') EXCLUSIVE INTO (PARTITION jan09, PARTITION other);

 

n  파티션 이동 예제

# T_tf_trf_log_old의 특정파티션을 t_tf_trf_log로 옮기는 예제   create table t_tf_trf_log_old (  guid varchar(10),  log_tm varchar(10) ) with (appendonly=true, compresslevel=1) distributed by (guid) partition by range(log_tm) (    partition p20100101 start ('20100101') end ('20100102'),    partition p20100102 start ('20100102') end ('20100103'),    partition p20100103 start ('20100103') end ('20100104'),    default partition pother  ) ;   create table t_tf_trf_log (  guid varchar(10),  log_tm varchar(10) ) with (appendonly=true, compresslevel=1) distributed by (guid) partition by range(log_tm) (    partition p20100101 start ('20100101') end ('20100102'),    partition p20100102 start ('20100102') end ('20100103'),    partition p20100103 start ('20100103') end ('20100104'),    default partition pother  ) ;   # 옮기고자하는 테이블을 임시로 생성 create table tmp_log (like t_tf_trf_log);   # 검증하기 위함 insert into t_tf_trf_log_old values ('asdf', '20100101');   #옮기고자 하는 파티션과 임시 테이블간의 이동 alter table  t_tf_trf_log_old exchange partition p20100101 with table tmp_log;   # 임시 테이블과 옮기고자하는 파티션과의 이동 alter table  t_tf_trf_log exchange partition p20100101 with table tmp_log;

2.      VIEW

-       빈번하고 복잡한 쿼리에 대해서 적용 가능 함.

-       Greenplum에서의 View는 물리적인 디스크 공간을 사용하지 않음

-       View 안에서 order by 와 같이 Sort operation는 무시 됨.

n  VIEW 생성 예제

# T_tf_trf_log_old의 특정파티션을 t_tf_trf_log로 옮기는 예제   create table t_tf_trf_log_old (  guid varchar(10),  log_tm varchar(10) )   CREATE VIEW topten    AS SELECT name, rank, gender, year    FROM names, rank    WHERE rank < ’11’ AND names.id=rank.id; SELECT * FROM topten ORDER BY year, rank;

 

3.      INDEX

n  Index 설명

-       지원 되는 인덱스 유형은 B-Tree, Bitmap, R-tree, Hash, GiST 입니다.

-       PK 생성시 자동으로 인덱스가 생성이 됩니다.

n  권고 사항

-       인덱스를 생성하더라도 대용량 쿼리시에는 Index를 사용하지 않는 경우가 있기 때문에 Explain를     실행해서 사용여부를 확인하고 Optimizer가 Index를 사용하지 않을 경우 삭제하고 인덱스 생성을    최소화를 권고합니다.

-       대용량 데이터를 적재하는 경우는 index를 삭제하고 데이터 적재 후에 다시 재생성 합니다.

-       Primary Key를 생성하는 경우는 대용량 데이터를 적재한 뒤에 alter table add primary key형태로  생성하고, 삭제시는 alter table drop constraint pkey_name 으로 합니다

-       Unique Index 생성시 분산키를 포함하여야 합니다.

n  제약 사항

-       Single Item를 조회할 경우에는 B-Tree 인덱스 사용을 권고합니다.

-       Cardinality가 낮는 경우 Bitmap 인덱스 사용을 권고합니다.

create table schema.GP_MASTER (   GPID       numeric not null,   GPNAME     varchar(50) not null,   REG_DATE   DATE,   STATUS_YN  CHAR(1),   AGE        numeric,   BIRTH_DATE DATE ) DISTRIBUTED BY (gpid);     -- primary key constraints alter table schema.GP_MASTER   add constraint PK_GP_MASTER primary key (GPID) ;   -- Btree index Create index ix_gp_master_gpname on schema.gp_master on (gpname);     -- Bitmap index Create bitmap index ixb_gp_master_status_yn on schema.gp_master on (status_yn);

4.      SEQUENCE

-       Sequence를 이용하여 자동으로 일련번호를 생성할 수 있습니다.

## Sequence 생성  ‘myseq’ CREATE SEQUENCE myseq START 101;   ## Sequence 를 이용해서 next value를 value: INSERT INTO distributors        VALUES (nextval('myseq'), 'acme');   ## sequence reset SELECT setval('myseq', 201);   ## 아래와 같이는 실행되지 않음 INSERT INTO product        VALUES (setval('myseq', 201), 'gizmo');