Greenplum常用语句

# 登陆
psql -h 10.10.10.10 -p 5432 -d gp_data -U username
# 查看所有schema
\dn
# 切换到名为test的schema下
set search_path to test;
# 查看当前schema下所有的表
\dt
# 查看表t_test的结构
\d t_test;
# 查看表t_test详细表信息
\d+ t_test;

# 添加非空约束
alter table test.t_test alter COLUMN f_modify_time set not null;
# 删除非空约束
alter table test.t_test alter COLUMN f_modify_time drop not null;

# 清空主表
truncate table test.t_test;
# 删除表
drop table if exists test.t_test;
# 删除外部表
drop external table if exists test.r_ext_t_test;

# 导入sql
insert into ${schema}.${tablename}
select to_date(F_create_time::text,'YYYY-MM-DD'),* from ${schema}.${ext_tablename};
# 上个月的最后一天
select date_trunc('month', now()) - interval '1 day';
select to_char((date_trunc('month', now()) - interval '1 day'),'yyyyMM');
# 当前的后一天
select date_trunc('day'  , now()) + interval '1 day';
# 本月的最后一天
select (date_trunc('month', now()) + interval '1 month - 1 day')::date;
# 本月的第一天
select (date_trunc('month', now()))::date;
# 返回上个月的年月
select to_char((date_trunc('month', date '2019-11-30') - interval '1 day'),'yyyyMM');
# 更改表名
alter table 表名 rename to 新表名
# 更改字段名
alter table 表名 rename 字段名 to 新字段名
# 删除字段
alter table t_user drop column uid;

# 新建名为open的schema，并指定所属用户为daiyuanpei_rd
CREATE SCHEMA open AUTHORIZATION daiyuanpei_rd;

# 查看schema为test下t_pay表（test.t_test） 分布键
SELECT attname FROM pg_attribute WHERE attrelid='test.t_test'::regclass
and attnum in (SELECT unnest(attrnums) FROM pg_catalog.gp_distribution_policy t
WHERE localoid='test.t_test'::regclass);
# 查看表 分区信息
SELECT pg_get_partition_def('test.t_test'::regclass,true);

# 插入外表数据到主表
insert into test.t_test select * from test.r_ext_t_test;

# 字段加注释
comment on column t_test.f_type is '1 neibu 2 waibu';
# 表添加字段
ALTER TABLE test.t_test ADD COLUMN "f_state" smallint DEFAULT 1::smallint NOT NULL;

# 查整个库的大小
select pg_size_pretty(pg_database_size('gp_data'));

# 查看单个表大小 - 主表不存数据，所以值为0
select pg_size_pretty(pg_relation_size('test.t_test'));
select pg_size_pretty(pg_relation_size('test.t_test_1_prt_m2019_9'));

#  查schema下所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_tables where schemaname='test' order by pg_relation_size(relid) desc;

# 查schema的大小 包括索引 不包括索引可用pg_relation_size
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"
from pg_tables where schemaname='test' group by 1;

# 查schema的大小 包括索引 不包括索引可用pg_relation_size
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"
from pg_tables
where schemaname in ('fn', 'open', 'test')
group by 1;

# 建表
CREATE TABLE open.t_ls_b2c (
    dt date,
    f_code character varying(128)
);
# 清空表
truncate table open.t_ls_b2c;
# 填充表
insert into open.t_ls_b2c
select
    current_date,
    F_code
from fn.t_details
where F_class=0;

# 名为test的schema下创建分区主表 示例1
CREATE TABLE test.t_log
(
    f_id bigint,
    f_user_id bigint,
    f_sub_account_type smallint,
    f_currency smallint,
    f_enabled smallint,
    f_trans_id character varying(128),
    f_log_time timestamp without time zone,
    f_rule_code smallint,
    f_service_code smallint,
    f_balance_type smallint,
    f_amount bigint,
    f_balance_after bigint,
    f_source_ip character varying(32),
    f_log_status smallint,
    f_trans_desc character varying(255),
    f_comments character varying(64),
    f_trans_tag smallint,
    f_reserve_int_2 integer,
    f_reserve_char_1 character varying(64),
    f_reserve_char_2 character varying(64),
    f_trans_id_ext character varying(128),
    f_extra character varying(255),
    f_account_tag smallint
)
WITH (appendonly=true) DISTRIBUTED BY (f_trans_id) PARTITION BY RANGE(f_log_time) (
    PARTITION m2018 START('2018-01-01'::date) END ('2019-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2019 START('2019-01-01'::date) END ('2020-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2020 START('2020-01-01'::date) END ('2021-01-01'::date) EVERY('1 mon'::interval)
);

# 创建分区主表 示例2
CREATE TABLE test.t_info (
    f_enabled smallint DEFAULT (1)::smallint NOT NULL,
    f_id bigint DEFAULT (0)::bigint NOT NULL,
    f_user_id bigint DEFAULT (0)::bigint NOT NULL,
    f_auth_channel_before BYTEA,
    f_auth_channel_after BYTEA,
    f_true_name character varying(128),
    f_certificate_code character varying(128),
    F_certificate_type smallint,
    f_mobile character varying(128),
    f_bank_card_no character varying(128),
    f_front_bank_code integer,
    f_bank_name character varying(128),
    f_channel smallint,
    f_create_time timestamp without time zone,
    f_modify_time timestamp without time zone,
    f_reserve_int16 smallint DEFAULT (0)::smallint NOT NULL,
    f_reserve_int32 integer DEFAULT (0)::integer NOT NULL,
    f_reserve_vchar16 character varying(128),
    f_reserve_vchar32 character varying(128)
)
WITH (appendonly='true') DISTRIBUTED BY (f_id) PARTITION BY RANGE(f_create_time) (
    PARTITION m2016 START('2016-01-01'::date) END ('2017-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2017 START('2017-01-01'::date) END ('2018-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2018 START('2018-01-01'::date) END ('2019-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2019 START('2019-01-01'::date) END ('2020-01-01'::date) EVERY('1 mon'::interval),
    PARTITION m2020 START('2020-01-01'::date) END ('2021-01-01'::date) EVERY('1 mon'::interval),
    default PARTITION other
);

# 全量同步 like方式建外表
create external table fn.r_ext_t_map (like fn.t_map)
location ('gpfdist://10.10.10.10:8888/pay/gp_data/fn/t_map')
format 'TEXT' (delimiter as E'\t' null as '\N' escape 'OFF');

# 一般方式建外部表
create external table open.t_top_year_ext (
    f_recvable_id character varying(128),
    f_recvable_recv_bank_id bigint,
    f_bank_card_num character varying(128),
    f_bank_name character varying(128)
)
location ('gpfdist://10.10.10.10:8888/pay/gp_data/fn/t_top_year_ext')
format 'TEXT' (delimiter as E'\t' null as '\N' escape 'OFF');

# 查版本
select version();

# 集群所有节点
select distinct(hostname) from gp_segment_configuration;
# 集群master节点
select distinct(hostname) from gp_segment_configuration where content=-1 and role='p';
# 集群standby节点 部分集群无standby，若无不用标注
select distinct(hostname) from gp_segment_configuration where content=-1 and role='m';

# 集群表单副本计算公式
select pg_size_pretty(pg_total_relation_size('schemaname.tablename'))
# 集群库单副本计算公式
select pg_size_pretty(pg_total_relation_size('dbname'));
# 集群所有IP列表demo:
psql -d xxx -p 5432 -t -A -c 'select distinct(hostname) from gp_segment_configuration;'|xargs -i host -i {} |awk '{print $4}'
# 集群master节点ip:
psql -d xxx -p 5432 -t -A -c "select distinct(hostname) from gp_segment_configuration where content=-1 and role='p';"| xargs -i host -i {} |awk '{print $4}'
# 集群standby节点ip: (部分集群无standby,若无不用标注)
psql -d xxx -p 5432 -t -A -c "select distinct(hostname) from gp_segment_configuration where content=-1 and role='m';"| xargs -i host -i {} |awk '{print $4}'