

Greenplum常用命令
2020-03-17 18:15:05
- 来源:
- 作者:代元培
- 类别:原创
- 浏览:1356 次
# 主表和外表都需要修改
ALTER TABLE open.t_pay ALTER f_card_mobile TYPE character varying(64);
ALTER external TABLE pay_data_ext.r_ext_t_pay ALTER f_card_mobile TYPE character varying(64);
# 添加非空约束
alter table fn.t_reserve_bank_account_channel alter COLUMN f_modify_time set not null;
# 删除非空约束
alter table fn.t_scenario_sp_map alter COLUMN f_modify_time drop not null;
# 修改外表字段类型
alter external table fn.rd_ext_t_reserve_bank_account_channel_details alter COLUMN f_org_code_ext type text;
# 修改主表字段类型
alter table fn.t_reserve_bank_account_channel_details alter COLUMN f_org_code_ext type text;
alter table open.t_pay alter COLUMN f_client_ip type bigint;
ALTER external TABLE pay_data_ext.r_ext_t_pay ALTER f_client_ip TYPE bigint;
alter table open.t_pay alter COLUMN f_log_id drop not null;
ALTER external TABLE open.r_ext_bi_data_info ALTER fr TYPE character varying(1024);
ALTER TABLE open.bi_data_info ALTER fr TYPE character varying(1024);
# 清空主表
truncate table fn.t_scenario_sp_map;
# 删除表
drop table if exists fn.t_sp_uid_type_info;
# 删除外部表
drop external table if exists pay_data_ext.r_ext_t_scenario_sp_map;
# 新建schema
CREATE SCHEMA fn AUTHORIZATION fsg_rcc_pay_platncommon_data_rd;
CREATE SCHEMA fn AUTHORIZATION gp_test_user;
# IDC 查看表 分布键
SELECT attname FROM pg_attribute WHERE attrelid='center.t_remit'::regclass
and attnum in (SELECT unnest(attrnums) FROM pg_catalog.gp_distribution_policy t
WHERE localoid='center.t_remit'::regclass);
# 查看表 分区信息
SELECT pg_get_partition_def('center.t_remit'::regclass,true);
# 删除非空约束
alter table fn.t_reserve_bank_account_channel alter COLUMN f_create_time drop not null;
truncate table fn.t_reserve_bank_account_channel;
insert into fn.t_reserve_bank_account_channel select * from fn.rd_ext_t_reserve_bank_account_channel;
# 字段加注释
comment on column t_sp_uid_type_info.f_type is '1-neibu 2-waibu 3-dajinrong 0-else';
# 表添加字段
ALTER TABLE open.t_sp_uid_type_info ADD COLUMN "f_enabled" smallint DEFAULT 1::smallint NOT NULL;
# 查看表 分布键
SELECT attname FROM pg_attribute WHERE attrelid='wallet.t_wallet_info'::regclass
and attnum in (SELECT unnest(attrnums) FROM pg_catalog.gp_distribution_policy t
WHERE localoid='wallet.t_wallet_info'::regclass);
# 查整个库的大小
select pg_size_pretty(pg_database_size('gp_pay'));
# 查看单个表大小 - 主表不存数据 值为0
select pg_size_pretty(pg_relation_size('open.dwd_wallet_info'));
select pg_size_pretty(pg_relation_size('open.t_wallet_info_1_prt_m2019_9'));
# 查schema下所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid))
from pg_stat_user_tables where schemaname='open' order by pg_relation_size(relid) desc;
# 查某一个schema的大小 包括索引 不包括索引可用pg_relation_size
select round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"
from pg_tables where schemaname='test';
# 查schema的大小 包括索引 不包括索引可用pg_relation_size
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb"
from pg_tables
where schemaname in ('card','center','cif','core','wallet','fn','open','stat')
group by 1;
# 云上gp 统计每个schema的大小 - GB
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) "GB"
from pg_tables
where schemaname in ('card','center','cif','core','wallet','fn','open','stat')
group by 1;
# 云上gp 统计如下schema的总大小 - GB
select round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) "GB"
from pg_tables
where schemaname in ('card','center','cif','core','wallet','fn','open','stat');
# 集群所有节点:
select distinct(hostname) from gp_segment_configuration;
# 集群master节点:
select distinct(hostname) from gp_segment_configuration where content=-1 and role='p';
# 集群standby节点:
select distinct(hostname) from gp_segment_configuration where content=-1 and role='m';
# 日期转时间戳
SELECT EXTRACT(epoch FROM NOW());
SELECT EXTRACT(epoch FROM CAST('2017-12-06 00:17:10' AS TIMESTAMP));
# 时间戳转日期
SELECT TO_TIMESTAMP(1512490630);
# 导入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 open.t_pay drop column f_reserve_int_1;
# 删除外表字段
ALTER external TABLE pay_data_ext.r_ext_t_pay DROP COLUMN f_reserve_int_1;
- 1
- 2
- 3
- 关键词标签: Greenplum
- 责任编辑:代元培

