Greenplum-S3 연동 예제

### Greenplum에서 AWS S3 연동
1. 개요
    Greenplum에서 AWS S3연동에는 2가지 방식으로 가능합니다.
    1) Greenplum-S3 프로토콜 이용
    2) PXF를 이용한 S3 프로토콜 이용
이번에 블로그는 Greenplum-S3 연동 예제 입니다.

2. 메뉴얼
   - https://gpdb.docs.pivotal.io/6-3/admin_guide/external/g-s3-protocol.html


#### S3 생성
1. 테스트용 AWS 계정 생성
    - https://aws.amazon.com/ko/free/ 에 접속하면 5GB 미만으로 사용시 12개월 테스트 가능
2. S3 생성
   1) 버킷 만들기 : 버킷은 도메인주소 사용에 필요하여, DNS 형식으로 이름을 생성. 
       - 예시: s3-bigcrow79
   2) 리전 : 서울
       - 나중에 필요함.(서울의 경우 코드는 ap-northeast-2 임.
   3) ... 계속 계속 하면 됨.

3. The AWS Access Key Id 생성
   1) 내보안자격증명 클릭 (Identity and Access Management(IAM))
      - AWS 웹콘솔에서 오른쪽 상단 접속 ID(계정)을 클릭하면 리스트가 나오며 이중 내보안자격증명 클릭
      
   2) 보안자격증명에서 엑세스 키 및 비밀 액세스 키 발급
      - 새 엑세스키 만들기를 클릭하면 키파일 다운로드 가능
      - 액세스키 보이기하면 액세스키 ID 및 보안 액세스키 를 확인할 수 있음 (아래는 예시임. 실제 키값은 다름)
      ex) 액세스 키 ID: ZZIAJ54Z4MALHN5MQWZZ
          보안 액세스 키: zzvh78sA3cLMr+zQgJi86UU2TVWEfLl7DGfphdzz
      


#### Greenplum 셋팅
1. s3 프로토콜 사전 준비
1) s3 프로토콜 사용을 위해서 함수 생성 (각 database에 마다 생성)
     - S3 프로토콜 라이브러리를 이용하기 위함.
$ psql

=# CREATE OR REPLACE FUNCTION write_to_s3() RETURNS integer AS
   '$libdir/gps3ext.so', 's3_export' LANGUAGE C STABLE;

=# CREATE OR REPLACE FUNCTION read_from_s3() RETURNS integer AS
   '$libdir/gps3ext.so', 's3_import' LANGUAGE C STABLE;
   
2) S3 bucket 접속을 위하여 S3 프로토콜 선언
     - 각 DB에서 S3 Bucket를 접속할 수 있도록 S3 프로토콜 선언
$ psql
=# CREATE PROTOCOL s3 (writefunc = write_to_s3, readfunc = read_from_s3);

2. s3 프로토콜 설정 파일 생성 및 설정 정리
1) s3 설정 템플릿 생성
$ gpcheckcloud -t > ./mytest_s3.config   
$ cat mytest_s3.config
[default]
secret = "aws secret"
accessid = "aws access id"
threadnum = 4
chunksize = 67108864
low_speed_limit = 10240
low_speed_time = 60
encryption = true
version = 1
proxy = ""
autocompress = true
verifycert = true
server_side_encryption = ""
# gpcheckcloud config
gpcheckcloud_newline = "\n"
[gpadmin@mdw s3]$

2) 템플릿 파일 수정 - 테스트 용
$ cat /home/gpadmin/s3/s3.conf
[default]
secret = "zzvh78sA3cLMr+zQgJi86UU2TVWEfLl7DGfphdzz"
accessid = "ZZIAJ54Z4MALHN5MQWZZ"
threadnum = 4
chunksize = 67108864
[gpadmin@mdw ~]$

3) 설정파일을 모든세그먼트 노드에 복사
$ scp -r s3 sdw1:/home/gpadmin
$ scp -r s3 sdw2:/home/gpadmin
$ ssh sdw1
$ cd sr
$ ls -la /home/gpadmin/s3/s3.conf

4) 설정 확인 
$ gpcheckcloud -c "s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79 config=/home/gpadmin/s3/s3.conf [region=ap-northeast-2]"
File: greenplum-s3/s3_test.txt, Size: 19

Your configuration works well.    ### 정상일 경우 왼쪽의 메시지 출력

#### Greenplum-S3 테스트

1. S3 연동 테스트  (S3 압축 예제, Default)
$ psql
=# 
create table s3_base (id int, ts timestamp, amt int)
distributed by (id);

insert into s3_base select i, now(), i from generate_series(1, 100) i;
   
CREATE READABLE EXTERNAL TABLE S3TBL (id int, ts timestamp, amt int)
   LOCATION('s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/ config=/home/gpadmin/s3/s3.conf')
   FORMAT 'csv';   
   
CREATE WRITABLE EXTERNAL TABLE S3WRIT (id int, ts timestamp, amt int)
   LOCATION('s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/ config=/home/gpadmin/s3/s3.conf')
   FORMAT 'csv';   

insert into S3WRIT select * from s3_base;

select * from S3TBL;
gpadmin=# select * from S3TBL;
 id  |             ts             | amt
-----+----------------------------+-----
   1 | 2020-02-06 20:34:05.987925 |   1
  12 | 2020-02-06 20:34:05.987925 |  12
  44 | 2020-02-06 20:34:05.987925 |  44
  72 | 2020-02-06 20:34:05.987925 |  72
  74 | 2020-02-06 20:34:05.987925 |  74
  78 | 2020-02-06 20:34:05.987925 |  78
  ...

2. S3에 데이터 저장 형태
1) 파일 형식
   - 세그먼트 인스턴스별로 하나의 파일이 생성 됨.
   - .[.gz] 
s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/03388514d.csv.gz    
s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/11x80514d.csv.gz    
s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/27xx5014d.csv.gz    
s3://s3.ap-northeast-2.amazonaws.com/s3-bigcrow79/normal/3x388514d.csv.gz    

2) 파일 형태
   - 기본적으로 gz 형태로 압축되며, Greenplum 6.3.0 기준으로는 gzib과 plain 두가지 방식으로 저장 됨.

Greenplum 4 Vs Greenplum 6의 Age 변경 사항

1. 테스트 목적
   1) Greenplum 6에서는 테이블 및 데이터베이스의 age가 서서히 올라가도록 기능 개선.
      - Greenplum 5에서 Lazy XID (less frequent xid wrap around) 기능 개선. 
      - https://gpdb.docs.pivotal.io/500/relnotes/GPDB_500_README.html
      - Greenplum 6에서는 root 파티션에서 Age 계산 제거하도록 개선
      - https://github.com/greenplum-db/gpdb/commit/44f9776071c3ecbfbcf6015f66207157916665e9

   2) Greenplum 4와 Greenplum 6 에서의 트랜잭션 age 가 어떻게 증가하는지 확인.

      - Greenplum 4 vs 6에서 테스트 진행      
      
2. 테스트 스크립트
   1) DDL
   
CREATE TABLE public.test_date (
    log_date timestamp without time zone NOT NULL,
    test_id numeric,
    count numeric DEFAULT 0
) DISTRIBUTED BY (test_id) PARTITION BY RANGE(log_date)
          (
          PARTITION p2008 START ('2008-01-01'::timestamp without time zone) END ('2009-01-01'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2008'),
          PARTITION p2009 START ('2009-01-01'::timestamp without time zone) END ('2010-01-01'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2009'),
          PARTITION p2010 START ('2010-01-01'::timestamp without time zone) END ('2011-01-01'::timestamp without time zone) WITH (tablename='test_date_1_prt_p2010'),
          DEFAULT PARTITION pother  WITH (tablename='test_date_1_prt_pother', appendonly='false')
          );   
   
create table trx ( a int)
DISTRIBUTED BY (test_id);
   
   2) 트랜잭션 발생 스크립트
      $ insert_trx.sh
for i in `seq 1 100`
do
echo $i
    psql -c "insert into aaa values ( 1 );"
done
   
   3) 테이블 Age 확인 SQL #1 (Greenplum 4 용, 마스터 인스턴스의 age 확인)
       $ cat xid.sql

SELECT  relname, relkind, relstorage, age(relfrozenxid)
FROM  pg_class c
where relname like 'test_date%'
ORDER BY 4 DESC;   
   
   3) 테이블 Age 확인 SQL #2 (Greenplum 6 용, 모든 인스턴스의 age 확인)
      select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;
   

3. 테스트 수행
   1) Age 확인 SQL 수행 - 2번 트랜잭션 발생 스크립트를 수행하는 동안 계속 실행 수행
      $ while true; do date; psql -f xid.sql ; sleep 2; done
      $ select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;
   2) 트랜잭션 발생 ( Insert )    

4. 테스트 로그
   1) Greenplum 4
### Session #1
[gpadmin@mdw4 ~]$ while true; do date; psql -f xid.sql ; sleep 2; done
2020. 02. 04. (화) 10:19:00 KST
Timing is on.
 coalesce |        relname         | relkind | relstorage | age
----------+------------------------+---------+------------+-----
 public   | test_date              | r       | h          |  31
 public   | test_date_1_prt_p2008  | r       | h          |  30
 public   | test_date_1_prt_p2009  | r       | h          |  29
 public   | test_date_1_prt_p2010  | r       | h          |  28
 public   | test_date_1_prt_pother | r       | h          |  27
(5 rows)

Time: 7.489 ms
2020. 02. 04. (화) 10:19:02 KST
Timing is on.
 coalesce |        relname         | relkind | relstorage | age       
----------+------------------------+---------+------------+-----
 public   | test_date              | r       | h          |  33
 public   | test_date_1_prt_p2008  | r       | h          |  32
 public   | test_date_1_prt_p2009  | r       | h          |  31
 public   | test_date_1_prt_p2010  | r       | h          |  30
 public   | test_date_1_prt_pother | r       | h          |  29
(5 rows)

Time: 4.359 ms
===> Idle 한 상태에서도 Age 가 증가

./insert_trx.sh 수행 후 결과
....
....


2020. 02. 04. (화) 10:19:55 KST
Timing is on.
 coalesce |        relname         | relkind | relstorage | age         
----------+------------------------+---------+------------+-----
 public   | test_date              | r       | h          | 305
 public   | test_date_1_prt_p2008  | r       | h          | 304
 public   | test_date_1_prt_p2009  | r       | h          | 303
 public   | test_date_1_prt_p2010  | r       | h          | 302
 public   | test_date_1_prt_pother | r       | h          | 301
(5 rows)
===> trx 테이블에 DML 실행시에 타 테이블에서 Age 증가

### Session #2
[gpadmin@mdw4 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 |  22
             1 |  22
             2 |  22
             3 |  22
             4 |  22
             5 |  22
             6 |  22
             7 |  22
             8 |  22
             9 |  22
(10 rows)

[gpadmin@mdw4 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 |  24
             1 |  24
             2 |  24
             3 |  24
             4 |  24
             5 |  24
             6 |  24
             7 |  24
             8 |  24
             9 |  24
(10 rows)

./insert_trx.sh 수행 후 결과
...

gpadmin@mdw4 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 | 162
             1 | 160
             2 | 160
             3 | 160
             4 | 160
             5 | 160
             6 | 158
             7 | 160
             8 | 160
             9 | 160
(10 rows)
### trx 모든 인스턴스에서 골고루 age 가 증가 됨을 확인

### Session #3.  ---트랜잭션 발생
[gpadmin@mdw4 ~]$ ./insert_trx.sh
1
INSERT 0 1
2
INSERT 0 1
...
INSERT 0 1
100
INSERT 0 1
[gpadmin@mdw4 ~]$
   

   2) Greenplum 6
   
### Session #1
[gpadmin@mdw6 ~]$ while true; do date; psql -f xid.sql ; sleep 2; done
2020. 02. 04. (화) 10:28:00 KST
 coalesce |        relname         | relkind | relstorage |    age
----------+------------------------+---------+------------+------------
 public   | test_date              | r       | h          | 2147483647
 public   | test_date_1_prt_p2008  | r       | h          |          7
 public   | test_date_1_prt_p2009  | r       | h          |          7
 public   | test_date_1_prt_p2010  | r       | h          |          7
 public   | test_date_1_prt_pother | r       | h          |          7
(5 rows)

2020. 02. 04. (화) 10:28:02 KST
 coalesce |        relname         | relkind | relstorage |    age
----------+------------------------+---------+------------+------------
 public   | test_date              | r       | h          | 2147483647
 public   | test_date_1_prt_p2008  | r       | h          |          7
 public   | test_date_1_prt_p2009  | r       | h          |          7
 public   | test_date_1_prt_p2010  | r       | h          |          7
 public   | test_date_1_prt_pother | r       | h          |          7
(5 rows)   
....
./insert_trx.sh 수행 후 로그 

2020. 02. 04. (화) 10:28:21 KST
 coalesce |        relname         | relkind | relstorage |    age
----------+------------------------+---------+------------+------------
 public   | test_date              | r       | h          | 2147483647
 public   | test_date_1_prt_p2008  | r       | h          |          8
 public   | test_date_1_prt_p2009  | r       | h          |          8
 public   | test_date_1_prt_p2010  | r       | h          |          8
 public   | test_date_1_prt_pother | r       | h          |          8
(5 rows)
==> root 파티션의 age는 2^31 -1 으로 고정 됨.

### Session #2
[gpadmin@mdw6 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 |   1
             1 |   1
             2 |   1
             3 |   1
             4 |   1
             5 |   1
(6 rows)

[gpadmin@mdw6 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 |   1
             1 |   1
             2 |   1
             3 |   1
             4 |   1
             5 |   1
(6 rows)
....
./insert_trx.sh 수행 후 로그 

[gpadmin@mdw6 ~]$ psql -c "select gp_segment_id, age(relfrozenxid)  from gp_dist_random('pg_class') where relname = 'trx' order by 1;"
 gp_segment_id | age
---------------+-----
             0 |   1  #### 마스터 인스턴스의 age는 증가 없음.
             1 |   1
             2 |   1
             3 |   1
             4 | 101  #### 트랜잭션이 발생한 특정 세그먼트의 Table age만 증가
             5 |   1
(6 rows)
===> 마스터에서는 age가 올라가지 않고, 해당 세그먼트에만 Age가 증가 됨.

[gpadmin@mdw6 ~]$


5. 결론
   1) Greenplum 4
       - 트랜잭션을 일으키지 않는 테이블도 자동적으로 Age 가 증가.
       - Root 파티션도 Age 가 증가 됨.
       - 마스터 인스턴스의 Age와 각 세그먼트 인스턴스의 Age도 함께 증가.

   2) Greenplum 6
       - 트랜잭션을 일으키는 테이블에 한해서만 Age 가 증가.
       - Root 파티션의 Age는 항상 2^31 -1 값으로 셋팅 
         ## Root 파티션에 대해서는 Age 줄일 필요 없음.
       - 트랜잭션이 테이블의 해당 세그먼트에 대해서만 Age 상승.