Greenplum DB 및 스키마생성

1.      Database 생성 및 초기 구성

1)        Database 생성 

-       DB  셋업시 UTF8으로 생성되어, Database 생성시 Default는 UTF8으로 생성 됨.

$ createdb database명        ## OS 상에서 database 생성 Or $ psql                             ## DB 상에서 database 생성 CREATE DATABASE  dbname ENCODING = 'UTF8'

2)        User 생성

$ psql ## 테이블 생성 및 관리를 위한 DBAUSER CREATE ROLE dbauser  LOGIN ENCRYPTED  PASSWORD 'dbauser1234' ; ## 테이블 DML, ETL를 위한 ETL User CREATE ROLE etluser   LOGIN ENCRYPTED PASSWORD  'etluser1234' ; ## 테이블 SELECT 를 위한 OLAP User CREATE ROLE olapuser LOGIN ENCRYPTED PASSWORD  'olapuser1234';

3)        스키마 생성

-       논리적으로 object를 그룹핑한 개념. object 생성 시 반드시 하나의 schema에만 할당됨 

-       schema명.table명 으로 조회. 단, 권한처리(search path)에 의해 schema명 생략가능.

-       자신의 schema 에 생성됨. (pgAdmin에서 확인가능)

$ psql create schema pjt_dw; create schema pjt_dm; create schema pjt_ods; create schema pjt_tmp;   alter schema pjt_dw owner to dbauser; alter schema pjt_dm owner to dbauser; alter schema pjt_ods owner to dbauser;

4)        스키마 권한 부여

            -       스키마 권한 부여는 ALL 및 USAGE가 있음.

-       ALL 으로 스키마 권한 부여하면 스키마 테이블 생성 실행할 수 있음

-       USAGE로 스키마 권한 부여하면 스키마 내의 권한 있는 테이블에 대해서 DML 사용 가능

$ psql grant all on schema pjt_dw to etluser; grant all on schema pjt_dm to etluser; grant all on schema pjt_ods to etluser; grant usage on schema pjt_dw to olapuser; grant usage on schema pjt_dm to olapuser; grant usage on schema pjt_ods to olapuser;

n  권한 메트릭스 (예제)

user/schema	prj_dw	prj_dm	prj_com	prj_ods	prj_tmp	public
dbauser	all (table)	all (table)	all (table)	all (table)	all (table)	all (table)
Etluser	insert upate delete select	insert upate delete select	insert upate delete select	insert upate delete select	insert upate delete select	all (table)
olapuser	select	select	select	select	insert upate delete select	all (table)

5)        Resource Queue 생성

n  개념 및 항목 설명 

-       role 별 자원사용을 제한하여 시스템 안정성을 확보하기 위한 GP 기능

-       ACTIVE_STATEMENTS : 최대 동시작업수

-       MAX_COST : 해당 Resource Queue의 최대값, 쿼리의 Sum 값이 설정값 보다 높을 경우 Waiting 걸림

-       MIN_COST : 최소 Cost값 설정으로 해당 쿼리가 MIN_COST 보다 낮을 경우 Bypass 함.

-       PRIORITY:  RQ 별로 우선순위 가능함(MAX, HIGH, MEDIUM, LOW,MIN)

-       COST_OVERCOMM: 시스템 IDLE 이 있을 경우 MAX_COST 보다 높은 쿼리 실행 여부

n  Resource Queue 생성

CREATE RESOURCE QUEUE rqmax WITH (           ACTIVE_STATEMENTS=10,           MAX_COST=100000000.0,           MIN_COST=10000.0,           PRIORITY=MAX,           COST_OVERCOMM=FALSE);   CREATE RESOURCE QUEUE rqhigh WITH (ACTIVE_STATEMENTS=5,MIN_COST=10000.0,PRIORITY=HIGN); CREATE RESOURCE QUEUE rqmedium WITH (ACTIVE_STATEMENTS=5,MIN_COST=10000.0,PRIORITY=MEDIUM); CREATE RESOURCE QUEUE rqlow WITH (ACTIVE_STATEMENTS=5,MIN_COST=10000.0,PRIORITY=LOW); CREATE RESOURCE QUEUE rqmin WITH (ACTIVE_STATEMENTS=5,MIN_COST=10000.0,PRIORITY=MIN);

n  User 별 Resource Queue 맵핑      

ALTER ROLE dbauser RESOURCE QUEUE rqmax; ALTER ROLE olapuser RESOURCE QUEUE rqhigh; ALTER ROLE etluser RESOURCE QUEUE rqmedium;

6)        Group Role 생성 및 반영

n  Resource Queue 생성

CREATE RESOURCE QUEUE rqadhoc ACTIVE THRESHOLD 2 COST THRESHOLD 100000000.0 CREATE ROLE n0001 WITH LOGIN PASSWORD 'n0001'  RESOURCE QUEUE rqadhoc; CREATE ROLE n0002 WITH LOGIN PASSWORD 'n0002'  RESOURCE QUEUE rqadhoc;   CREATE ROLE biadhoc  ; GRANT biadhoc TO n0001, n0002; create table bmt.test (a int, b varchar(100)) ; GRANT USAGE ON SCHEMA bmt TO biadhoc; GRANT SELECT ON TABLE bmt.test  TO biadhoc; ## n0001 으로 접속한 후`에 테이블 select

n  User 별 Resource Queue 맵핑

ALTER ROLE n0001 RESOURCE QUEUE rqadhoc; ALTER ROLE n0002 RESOURCE QUEUE rqadhoc