2017년 8월 27일 일요일

GPDB transpose

Greenplum Database Transpose

1. row_data 생성
drop TABLE if exists public.row_data;
CREATE TABLE public.row_data
(
  param varchar(100),
  g_id varchar(100),
  val   numeric
)
WITH (APPENDONLY=true, compresslevel=5)
DISTRIBUTED BY (param);
truncate table public.row_data;
insert into public.row_data
select 'param_'||trim(to_char(a, '000000')) param
     , 'gid_'||trim(to_char(b, '00')) g_id
     , round((random() * 10)::numeric, 5) val
from   generate_series(1, 100) a
     , generate_series(1, 50) b
;
2.1 case when transpose
select  param
       , sum(case when g_id = 'gid_01' then val else 0 end) gid_01
       , sum(case when g_id = 'gid_02' then val else 0 end) gid_02
       , sum(case when g_id = 'gid_03' then val else 0 end) gid_03
       , sum(case when g_id = 'gid_04' then val else 0 end) gid_04
       , sum(case when g_id = 'gid_05' then val else 0 end) gid_05
       , sum(case when g_id = 'gid_06' then val else 0 end) gid_06
       , sum(case when g_id = 'gid_07' then val else 0 end) gid_07
       , sum(case when g_id = 'gid_08' then val else 0 end) gid_08
       , sum(case when g_id = 'gid_09' then val else 0 end) gid_09
       , sum(case when g_id = 'gid_10' then val else 0 end) gid_10
       , sum(case when g_id = 'gid_11' then val else 0 end) gid_11
       , sum(case when g_id = 'gid_12' then val else 0 end) gid_12
       , sum(case when g_id = 'gid_13' then val else 0 end) gid_13
       , sum(case when g_id = 'gid_14' then val else 0 end) gid_14
       , sum(case when g_id = 'gid_15' then val else 0 end) gid_15
       , sum(case when g_id = 'gid_16' then val else 0 end) gid_16
       , sum(case when g_id = 'gid_17' then val else 0 end) gid_17
       , sum(case when g_id = 'gid_18' then val else 0 end) gid_18
       , sum(case when g_id = 'gid_19' then val else 0 end) gid_19
       , sum(case when g_id = 'gid_20' then val else 0 end) gid_20
       , sum(case when g_id = 'gid_21' then val else 0 end) gid_21
       , sum(case when g_id = 'gid_22' then val else 0 end) gid_22
       , sum(case when g_id = 'gid_23' then val else 0 end) gid_23
       , sum(case when g_id = 'gid_24' then val else 0 end) gid_24
       , sum(case when g_id = 'gid_25' then val else 0 end) gid_25
       , sum(case when g_id = 'gid_26' then val else 0 end) gid_26
       , sum(case when g_id = 'gid_27' then val else 0 end) gid_27
       , sum(case when g_id = 'gid_28' then val else 0 end) gid_28
       , sum(case when g_id = 'gid_29' then val else 0 end) gid_29
       , sum(case when g_id = 'gid_30' then val else 0 end) gid_30
       , sum(case when g_id = 'gid_31' then val else 0 end) gid_31
       , sum(case when g_id = 'gid_32' then val else 0 end) gid_32
       , sum(case when g_id = 'gid_33' then val else 0 end) gid_33
       , sum(case when g_id = 'gid_34' then val else 0 end) gid_34
       , sum(case when g_id = 'gid_35' then val else 0 end) gid_35
       , sum(case when g_id = 'gid_36' then val else 0 end) gid_36
       , sum(case when g_id = 'gid_37' then val else 0 end) gid_37
       , sum(case when g_id = 'gid_38' then val else 0 end) gid_38
       , sum(case when g_id = 'gid_39' then val else 0 end) gid_39
       , sum(case when g_id = 'gid_40' then val else 0 end) gid_40
       , sum(case when g_id = 'gid_41' then val else 0 end) gid_41
       , sum(case when g_id = 'gid_42' then val else 0 end) gid_42
       , sum(case when g_id = 'gid_43' then val else 0 end) gid_43
       , sum(case when g_id = 'gid_44' then val else 0 end) gid_44
       , sum(case when g_id = 'gid_45' then val else 0 end) gid_45
       , sum(case when g_id = 'gid_46' then val else 0 end) gid_46
       , sum(case when g_id = 'gid_47' then val else 0 end) gid_47
       , sum(case when g_id = 'gid_48' then val else 0 end) gid_48
       , sum(case when g_id = 'gid_49' then val else 0 end) gid_49
       , sum(case when g_id = 'gid_50' then val else 0 end) gid_50
from    public.row_data
group by  param
order by 1


2.2. pivot sum
select  param
        , a[01] gid_01
 , a[02] gid_02
 , a[03] gid_03
 , a[04] gid_04
 , a[05] gid_05
 , a[06] gid_06
 , a[07] gid_07
 , a[08] gid_08
 , a[09] gid_09
 , a[10] gid_10
 , a[11] gid_11
 , a[12] gid_12
 , a[13] gid_13
 , a[14] gid_14
 , a[15] gid_15
 , a[16] gid_16
 , a[17] gid_17
 , a[18] gid_18
 , a[19] gid_19
 , a[20] gid_20
 , a[21] gid_21
 , a[22] gid_22
 , a[23] gid_23
 , a[24] gid_24
 , a[25] gid_25
 , a[26] gid_26
 , a[27] gid_27
 , a[28] gid_28
 , a[29] gid_29
 , a[30] gid_30
 , a[31] gid_31
 , a[32] gid_32
 , a[33] gid_33
 , a[34] gid_34
 , a[35] gid_35
 , a[36] gid_36
 , a[37] gid_37
 , a[38] gid_38
 , a[39] gid_39
 , a[40] gid_40
 , a[41] gid_41
 , a[42] gid_42
 , a[43] gid_43
 , a[44] gid_44
 , a[45] gid_45
 , a[46] gid_46
 , a[47] gid_47
 , a[48] gid_48
 , a[49] gid_49
 , a[50] gid_50
FROM  (
  select param, pivot_sum(
     array['gid_01', 'gid_02', 'gid_03', 'gid_04', 'gid_05', 'gid_06', 'gid_07', 'gid_08', 'gid_09', 'gid_10',
           'gid_11', 'gid_12', 'gid_13', 'gid_14', 'gid_15', 'gid_16', 'gid_17', 'gid_18', 'gid_19', 'gid_20',
           'gid_21', 'gid_22', 'gid_23', 'gid_24', 'gid_25', 'gid_26', 'gid_27', 'gid_28', 'gid_29', 'gid_30',
           'gid_31', 'gid_32', 'gid_33', 'gid_34', 'gid_35', 'gid_36', 'gid_37', 'gid_38', 'gid_39', 'gid_40',
           'gid_41', 'gid_42', 'gid_43', 'gid_44', 'gid_45', 'gid_46', 'gid_47', 'gid_48', 'gid_49', 'gid_50' ]
     , g_id
     , val
     ) a
  from    public.row_data               
  group by param
 ) pvt
order by param


select  param
        , a[01] gid_01
 , a[02] gid_02
 , a[03] gid_03
 , a[04] gid_04
 , a[05] gid_05
 , a[06] gid_06
 , a[07] gid_07
 , a[08] gid_08
 , a[09] gid_09
 , a[10] gid_10
 , a[11] gid_11
 , a[12] gid_12
 , a[13] gid_13
 , a[14] gid_14
 , a[15] gid_15
 , a[16] gid_16
 , a[17] gid_17
 , a[18] gid_18
 , a[19] gid_19
 , a[20] gid_20
 , a[21] gid_21
 , a[22] gid_22
 , a[23] gid_23
 , a[24] gid_24
 , a[25] gid_25
 , a[26] gid_26
 , a[27] gid_27
 , a[28] gid_28
 , a[29] gid_29
 , a[30] gid_30
 , a[31] gid_31
 , a[32] gid_32
 , a[33] gid_33
 , a[34] gid_34
 , a[35] gid_35
 , a[36] gid_36
 , a[37] gid_37
 , a[38] gid_38
 , a[39] gid_39
 , a[40] gid_40
 , a[41] gid_41
 , a[42] gid_42
 , a[43] gid_43
 , a[44] gid_44
 , a[45] gid_45
 , a[46] gid_46
 , a[47] gid_47
 , a[48] gid_48
 , a[49] gid_49
 , a[50] gid_50
FROM  (
  select param, pivot_sum(
     (select array_agg(g_id order by g_id) from   (select g_id from   public.row_data group by g_id ) a)
     , g_id
     , val
     ) a
  from    public.row_data               
  group by param
 ) pvt
order by param

2.2 madlib transpose
DROP TABLE IF EXISTS public.row_data_pivot;
SELECT madlib.pivot('public.row_data', 'public.row_data_pivot', 'param', 'g_id', 'val');
SELECT * FROM public.row_data_pivot ORDER BY param;
select *
from   public.row_data
where  param = 'param_000001'
시간: 댓글 없음:
라벨:

2017년 8월 24일 목요일

GPDB 시스템 카탈로그


1.      시스템 카탈로그


1)        시스템 카탈로그 테이블


n  참고 페이지

-      https://gpdb.docs.pivotal.io/43160/ref_guide/system_catalogs/catalog_ref.html

-      http://stephendotcarter.github.io/greenplum-syscat-ref/

-      http://stephendotcarter.github.io/greenplum-syscat-ref/diagram.html
 
스키마명테이블명설명
pg_cataloggp_configuration_historyDB 상태 이력 저장
pg_cataloggp_distribution_policy분산키 정보 저장
pg_cataloggp_segment_configurationGreenplum 시스템 구성정보
pg_catalogpg_attribute컬럼 정보
pg_catalogpg_classObject 관리 테이블(Table, Index, View 등)
pg_catalogpg_constraintConstraint 관리
pg_catalogpg_exttableExternal Table 정보
pg_catalogpg_index인덱스 관리
pg_catalogpg_language언어 관리
pg_catalogpg_namespace스키마 정보
pg_catalogpg_partition_rule파티션 구성 정보
pg_catalogpg_proc함수, 프로시저 관리
pg_catalogpg_resqueue리소스큐 관리
pg_catalogpg_stat_last_operation테이블 이력 관리
pg_catalogpg_statistic통계정보
pg_catalogpg_tablespace테이블스페이스

2)        시스템 카탈로그 뷰
  스키마명View name  설명
pg_catalogpg_cursors open cursors
pg_catalog pg_group groups of database users
pg_catalog pg_indexes indexes
pg_catalogpg_locks currently held locks
pg_catalog pg_roles database roles
pg_catalog pg_rules rules
pg_catalog pg_settings parameter settings
pg_catalog pg_shadow database users
pg_catalog pg_stats planner statistics
pg_catalog pg_stat_activity 세션 정보
pg_catalog pg_tables tables
pg_catalog pg_timezone_abbrevs time zone abbreviations
pg_catalogpg_timezone_names time zone names
pg_catalogpg_userdatabase users
pg_catalog pg_views views
  gp_toolkit gp_bloat_diag테이블 Bloat 정보 (Analyze 수행해야지만 정확한 값이 나옴)
gp_toolkitgp_stats_missins 통계 누락 된 테이블리스트 추출
3)        시스템 카탈로그 뷰
스키마명   함수명   설명
pg_catalog pg_cancel_backend(pid int) 쿼리 종료
pg_catalog pg_terminate_backend(pid int) 세션 종료
pg_catalog pg_rotate_logfile() 서버로그 로테이션
pg_catalog pg_database_size(name) 데이터베이스의 용량
pg_catalogpg_relation_size(text) 테이블 또는 인덱스의 사이즈 추출
pg_catalogpg_size_pretty(bigint)사이즈를 단위로 표시
pg_catalog pg_total_relation_size(text)인덱스, Toast 포함 사이즈

2.   권환 관리 스크립트

     1)        권한 확인

SELECT auth.rolname, cl.relkind, cln.nspname,  cl.relname
  FROM pg_class cl
      JOIN pg_namespace cln ON cl.relnamespace=cln.oid
      join pg_shdepend shd  on cl.oid = shd.objid
      join pg_authid   auth on auth.oid = shd.refobjid
 --WHERE auth.rolname = '계정 OR Group Role'
 ORDER BY 1,2,3;
 
select usename
     , nspname || '.' || relname as relation
     , case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type
     , priv
 from pg_class
 join pg_namespace
   on pg_namespace.oid = pg_class.relnamespace
    , pg_user
    , (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')      
  and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
  and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;

 

2)        일괄 권한 부여 (Table, Function, View)


1) 테이블
$ cat priv_table.sql
select ' ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO biadmin; ' --schemaname--, tablename
from    pg_tables
where   schemaname in ('etl', 'mig', 'report', 'svc', 'tf', 'tf_rep', 'tmp', 'public', 'tdm')
and     tablename not like '%1_prt_%'
and     tableowner <> 'biadmin' ;
 
2) Function
$ cat priv_proc.sql
SELECT 'ALTER FUNCTION ' || quote_ident(ns.nspname) || '.' || quote_ident(proname) || '(' || oidvectortypes(proargtypes) || ') OWNER TO biadmin;'
FROM   pg_proc
INNER JOIN pg_namespace ns
ON (pg_proc.pronamespace = ns.oid)
inner join pg_roles r
on (pg_proc.proowner = r.oid)
WHERE ns.nspname in ('public')
 
3) View
cat priv_view.sql
select ' ALTER TABLE '||schemaname||'.'||viewname||' OWNER TO biadmin; ' qry
from    pg_views
where   schemaname in ('public')
and     viewowner <> 'biadmin' ;
 
4) 일괄 권한 부여
$ psql -AtXf priv_table.sql | psql –e
$ psql -AtXf priv_ proc.sql | psql –e
$ psql -AtXf priv_ view.sql | psql -e

3)        스키마별 Grant  (tables, views, sequences)


CREATE OR REPLACE FUNCTION schema_grant(text, text, text, text)
RETURNS integer AS
$BODY$
-- Usage: select schema_grant('role','permission','object','schema');
-- Usage: select schema_grant('role','all','%','schema');
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in ('r','v','S') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE 'GRANT ' ||$2|| ' ON ' ||$4|| '.' || obj.relname || ' TO ' ||$1;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
 

4)        스키마별 Revoke (tables, views, sequences)


CREATE OR REPLACE FUNCTION schema_revoke(text, text, text, text)
  RETURNS integer AS
$BODY$
-- Usage: select schema_revoke('role','permission','object','schema');
-- Usage: select schema_revoke('role','all','%','schema');
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in ('r','v','S') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE 'REVOKE ' ||$2|| ' ON ' ||$4|| '.' || obj.relname || ' FROM ' ||$1;
num := num + 1;
END LOOP;
RETURN num;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

 

2.      DB 관리 스크립트


1)        분산키 확인


1) 분산키 확인
select
  nspname||'.'||relname as t_name,
  string_agg(attname, ', ' order by colorder)
FROM
  (select localoid, unnest(attrnums) as colnum, generate_series(1, array_upper(attrnums, 1)) as colorder from gp_distribution_policy) d
  join pg_attribute a on (d.localoid = a.attrelid and d.colnum = a.attnum)
  join pg_class c on (d.localoid = c.oid)
  join pg_namespace n on (c.relnamespace = n.oid)
GROUP BY nspname, relname;
 
2) Randomly 분산키 확인
select count(*) cnt
from (
select schemaname, tablename
from   pg_tables
where  schemaname not in ('h_edw_db', 'h_mart_db', 'j_edw_db', 'j_mart_db', 'public')
except
 SELECT n.nspname, c.relname
   FROM gp_distribution_policy d, pg_class c, pg_namespace n
  WHERE d.localoid = c.oid
  AND   c.relnamespace = n.oid
  AND   c.relkind = 'r'::"char"
  AND   n.nspname not in ('h_edw_db', 'h_mart_db', 'j_edw_db', 'j_mart_db')
and    d.attrnums is  not null
) a
;

2)        SKEW 확인


## gpdb 4.X
 
## gpdb 3.X or 전수 분석
$ cat skew_base.sql
select 'SELECT '''||a.schemaname||'.'||a.tablename||''' tb_nm, Avg(Row_Count), max(Row_Count),(1-(Avg(Row_Count)/Max(Row_Count)))*100 as Skew_Pct FROM (SELECT gp_segment_id, count(*) as Row_Count FROM '||a.schemaname||'.'||a.tablename||' GROUP BY gp_segment_id ) A;' qry
from   pg_tables a
       left outer join pg_partitions b
       on a.tablename = b.tablename
       left outer join pg_class c
       on a.tablename = c.relname
where  b.tablename is null
and    relstorage <> 'x'
group by a.schemaname, a.tablename
order by 1
$ psql -AXtf skew_base.sql > skew_all.sql
 
# Background 실행
$ nohup psql -ef skew_all.sql > skew_rslt.log 2>&1 &
 
# 50만건 이상이면서, 분산도가 5% 이상인 리스트 확인 방법
$ grep "|" skew_rslt.log | grep -v tb_nm | awk -F"|" '$4>10 {print$0}' | awk -F"|" '$3>25000 {print$0}'

3)        사이즈 확인(DB / Table / 파티션별)


## gpdb 4.X
1) 테이블 사이즈
SELECT relname as name
      , sotdsize as size
      , sotdtoastsize as toast
      , sotdadditionalsize as other
  FROM gp_size_of_table_disk as sotd, pg_class
 WHERE sotd.sotdoid=pg_class.oid
 ORDER BY relname;
 
2) 사이즈 관련 View
select * from gp_toolkit.gp_size_of_all_table_indexes
select * from gp_toolkit.gp_size_of_database
select * from gp_toolkit.gp_size_of_index
select * from gp_toolkit.gp_size_of_partition_and_indexes_disk
select * from gp_toolkit.gp_size_of_schema_disk
select * from gp_toolkit.gp_size_of_table_and_indexes_disk
select * from gp_toolkit.gp_size_of_table_and_indexes_licensing
select * from gp_toolkit.gp_size_of_table_disk
select * from gp_toolkit.gp_size_of_table_uncompressed
select * from gp_toolkit.gp_disk_free
 
1) DB 사이즈
select pg_size_pretty(pg_database_size('database명'));
 
2) TB 사이즈
-- 인덱스 포함
SELECT pg_size_pretty(pg_total_relation_size('faa.d_airlines'));
 
-- 인덱스 제외
SELECT pg_size_pretty(pg_relation_size('faa.d_airlines'));
 
-- 쿼리
select schemaname, tb_nm, mb
from   (
SELECT schemaname, SPLIT_PART(st.relname, '_1', 1)  tb_nm
     , round(sum(pg_relation_size(st.relid) )/1024/1024::int) MB
  FROM pg_stat_all_tables st
  JOIN pg_class cl
    on cl.oid=st.relid
 WHERE schemaname like 'bmt%'
 group by 1,2
 ) a
order by 1,2 ;
 
3) Partition size
select schemaname, tb_pt_nm, mb
from   (
SELECT schemaname, st.relname  tb_pt_nm
       , round(sum(pg_relation_size(st.relid) )/1024/1024::int) MB
  FROM pg_stat_all_tables st
  JOIN pg_class cl
    on cl.oid=st.relid
 WHERE schemaname  like 'hcn%'
 group by 1,2
 ) a
order by 1,2 ;
 
4) 테이블 사이즈(view)
create view v_table_size                                                                                                                                    
as                                                                                                                                                               
SELECT schemaname , SPLIT_PART(st.relname, '_1', 1) tablename                                                                                                                        
     , (sum(pg_relation_size(st.relid)                                                                                                                          
     + CASE WHEN cl.reltoastrelid = 0 THEN 0                                                                                                                     
            ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)
       END                                                                                                                                                       
     + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0))/1024/1024)::int::char(20)|| 'MB'  AS size           
  FROM pg_stat_all_tables st JOIN pg_class cl on cl.oid=st.relid                                                                                                
 WHERE schemaname = 'bmt'                                                                                                                                        
 group by 1                                                                                                                                                     
 order by 1;

4)        통계 정보 및 실행 일자 확인


1) 통계 정보 확인
select nspname, relname, attname, a.*
from   pg_statistic a, pg_class b, pg_attribute c, pg_namespace d
where  a.starelid = b.oid
and    c.attrelid = b.oid
and    a.staattnum = c.attnum
and    d.oid = b.relnamespace
and    relname =’테이블명’
order by 1,2,3,4,5,6;
 
2) 통계 정보 실행 일자 확인
Select * from pg_stat_operations
where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM')
order by statime;

5)        테이블/Object Operation 일자 확인


1) 통계 정보 확인
SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time
FROM pg_stat_operations
WHERE objname='cust';
 
2) 통계 정보 실행 일자 확인
Select * from pg_stat_operations
where schemaname='SCHEMA NAME '
and actionname in ('ANALYZE','VACUUM')
order by statime;

3.      유용한 스크립트

    1)        코멘트 달기

/* 코멘트 달기 */
COMMENT ON TABLE t_tf_blog_top_blog_dd IS '트래픽_블로그_';
COMMENT ON COLUMN t_tf_blog_top_blog_dd.sum_dt IS '요약일';
 

2)        테이블 목록 (테이블 Comment 확인)


SELECT
      n.nspname,
      lower(c.relname) tb_nm,
      pg_catalog.col_description(a.attrelid, 0)
FROM pg_catalog.pg_attribute a
     LEFT JOIN pg_catalog.pg_class c     ON a.attrelid = c.oid
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum = 1
--AND NOT a.attisdropped
--AND pg_catalog.pg_table_is_visible(c.oid)
--AND c.relname like 't_tf%'
--and n.nspname = 'tf'
and c.relname not like '%_1_prt_p%'
ORDER BY 1, 2, 3
 
 

3)        테이블 정의서  (테이블/컬럼 Comment 확인)


SELECT
      n.nspname schema_nm,
      upper(c.relname) tb_nm,
      pg_catalog.col_description(a.attrelid, 0) as entity_nm,
      a.attnum,
      upper(a.attname) col_nm,
      pg_catalog.col_description(a.attrelid, a.attnum) att_cmt,
      replace(replace(pg_catalog.format_type(a.atttypid, a.atttypmod), 'character varying', 'varchar'), 'without time zone', '') data_tp
FROM pg_catalog.pg_attribute a
     LEFT JOIN pg_catalog.pg_class c     ON a.attrelid = c.oid
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum > 0
--AND NOT a.attisdropped
--AND pg_catalog.pg_table_is_visible(c.oid)
--AND c.relname like 't_tf%'
--and n.nspname = 'public'
ORDER BY 1, 2, 3, 4

4)        컬럼 정보확인 (분산키, PK 여부, NULL 여부)


select    c.relname as 테이블명
     ,    row_number() over (partition by c.relname order by a.attnum) as 컬럼ID
     ,    a.attname as 컬럼명
     ,    format_type(a.atttypid, a.atttypmod) as 데이터타입
     ,    case when d.attrnums is not null then 'Y' else 'N' end as 분산키여부
     ,    case when i.indrelid is not null then 'Y' else 'N' end as PK여부
     ,    case when a.attnotnull or (t.typtype = 'd'::"char" and t.typnotnull) then 'N' else 'Y' end as NULL여부
  from    pg_class c
  join    pg_namespace n on c.relnamespace = n.oid
  join    pg_attribute a on c.oid = a.attrelid and a.attnum >= 0
  join    pg_type t on a.atttypid = t.oid
  left    outer join pg_partition_rule pr on c.oid = pr.parchildrelid
  left    outer join gp_distribution_policy d on c.oid = d.localoid and a.attnum = ANY (d.attrnums)
  left    outer join pg_index i on a.attrelid = i.indrelid and a.attnum = ANY(i.indkey) and i.indisunique
where    c.relkind = 'r'
   and    pr.parchildrelid is null -- 파티션 child 테이블 제외
   and    n.nspname = '스키마명'
order by 1,2;
 

5)        압축 테이블 리스트 보기


SELECT NSPNAME AS SCHEMANAME,
       RELNAME AS TABLENAME,
       CASE C.RELKIND
          WHEN 'x' THEN           'external table'
          WHEN 'r' THEN           'table'
          WHEN 'v' THEN           'view'
          WHEN 'i' THEN           'index'
          WHEN 'S' THEN           'sequence'
          WHEN 's' THEN           'special'
        END AS "Type",
       CASE C.RELSTORAGE
         WHEN 'h' THEN          'heap'
         WHEN 'a' THEN          'append only'
         WHEN 'v' THEN          'none'
         WHEN 'c' THEN          'append only columnar'
       END AS "Storage"
  FROM PG_CLASS C, PG_NAMESPACE N
 WHERE C.RELKIND IN ('r')
   AND C.OID NOT IN (SELECT PARCHILDRELID FROM PG_PARTITION_RULE)
   AND C.RELNAMESPACE = N.OID
   AND N.NSPNAME NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
   AND C.RELSTORAGE = 'a'
 ORDER BY 1, 2, 3

 

6)            테이블 /파티션별 사이즈/압축모드/압축옵션

select *
from  (
 SELECT a.schemaname AS schema_nm, a.tb_nm, a.tb_pt_nm, b.partitionname, a.tb_kb, a.tb_tot_kb, Storage, reloptions
   FROM ( SELECT st.schemaname
                , split_part(st.relname::text, '_1_prt_'::text, 1) AS tb_nm
                , st.relname AS tb_pt_nm
                , round(sum(pg_relation_size(st.relid)) / 1024::bigint::numeric) AS tb_kb
                , round(sum(pg_total_relation_size(st.relid)) / 1024::bigint::numeric) AS tb_tot_kb
                , CASE cl.RELSTORAGE
                       WHEN 'h' THEN          'heap'
                       WHEN 'a' THEN          'append only'
                       WHEN 'v' THEN          'none'
                       WHEN 'c' THEN          'append only columnar'
                       END AS Storage
                , cl.reloptions
           FROM pg_stat_all_tables st
      JOIN pg_class cl ON cl.oid = st.relid
     WHERE st.schemaname !~~ 'pg_temp%'::text AND st.schemaname <> 'pg_toast'::name AND cl.relkind <> 'i'::"char"
     GROUP BY 1,2,3,6,7
     ) a
     LEFT OUTER JOIN pg_partitions b
     on    a.schemaname = b.schemaname
     and   a.tb_pt_nm   = b.partitiontablename
     where (a.schemaname, a.tb_pt_nm ) not in (select schemaname, tablename from pg_partitions)
) b
where  schema_nm not in ('gp_toolkit', 'err', 'information_schema', 'pg_catalog', 'public' )
and    schema_nm <> 'upload'
order by 1, 2, 3
select
     n.nspname as schema_name,
     t.relname as table_name,
     a.attname as column_name,
     'btree'::varchar(100) as index_type,
     i.relname as index_name,
     ix.indisunique unique_yn,
     ix.indisprimary pk_yn    
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a,
pg_roles r,
pg_namespace n
where 1=1
and  t.oid = ix.indrelid
and  r.oid = t.relowner
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and t.relnamespace = n.oid
and  n.nspname  not in ('gp_toolkit', 'err', 'information_schema', 'pg_catalog', 'public' , 'upload')
--and t.relname = 'a'
and ix.indisunique = 't'
--and ix.indisprimary = 'f'
order by
t.relname,
i.relname;
 



 
시간: 댓글 없음:
라벨:
피드 구독하기: 글 (Atom)

Greenplum Backup & Restore

Greenplum에서는 gpbackup과 gprestore를 이용해서 대량의 데이터를 병렬로 백업/병렬로 복구를 지원하고 있습니다. Full 백업이외에도 incremental 백업을 지원하고 있습니다.  - incremental 백업시에는 반드시 사전...

  • Greenplum에서 리소스 관리
    보통 분석 시스템에서 항상 나오는 이야기가 배치는 빠른데, 일반 조회성 쿼리의 성능을 맞출 수 있느냐라는 질문을 많이 받습니다. 전사에서 보는 일별 현황이 있고, 배치도 수행하고, 머신러링 형태로 분석하는 것이 동시에 수행될 때 어느 업무가 중...
  • Greenplum 소개
    1. Greenplum 소개 Greenplum은 대용량 분석 작업에 최적화된 병렬처리(MPP) 기반의 오픈소스 데이터 플랫폼입니다. Greenplum 처음 프로젝트에서는 데이터웨어하우스를 목적으로 시작되었습니다. 국내 시장에서 ...
  • Greenplum DDL(테이블/인덱스 생성)
    1.       테이블 & 파티션 테이블 1)         Data Type            -        Greenplum 의 Character Length 는 Byte 수가 아닌 Character ...