Greenplum常用SQL查询

抽空⽹上收罗Greenplum常⽤SQL查询语句整理备忘。欢迎各位留⾔补充。都是SQL命令以及数据字典的使⽤。熟悉数据字典⾮常重要。三个重要的schema:pg_catalog,pg_toolkit,information_schema,其中information_schema 中的数据字典都在视图中⽬录

⼀、查看表某模式所有分布键信息⼆、 数据库运⾏状态查询管理

1.greenplum查询正在运⾏的sql，session2.终⽌执⾏的sql

3.查看greemplum资源队列状态4.查看greemplum资源队列锁5.查看greemplum资源队列优先级

6.查看greemplum所有连接 类似mysql SHOW PROCESSLIST7.greemplum磁盘使⽤，通过SQL查看Greenplum中⽤了多少空间8.查看greemplum节点状态9.节点故障等历史信息10.数据倾斜

11.greemplum表或索引⼤⼩ （占⽤空间）12.greemplum表和索引⼤⼩（占⽤空间）

13.greemplum查看指定数据库⼤⼩（占⽤空间）14.greemplum所有数据库⼤⼩（占⽤空间）15.查看greemplum数据分布情况三、查源数据

16.查看greemplum数据表更新时间

17.通过sql 获取greemplum表的预估数据量18.通过sql 获取greemplum获取分布键19.通过sql获取 greemplum指定表结构

20.显⽰哪些没有统计信息且可能需要ANALYZE的表21.显⽰在系统表中被标记为掉线的Segment的信息22.显⽰库中表的⼤⼩（单位G）

23.查询⼀个库中有多少表（如果有分区表不列出⼦分区）24.查询某个⽤户对某个表有什么权限25.查看分区表的信息26.导⼊数据

27.远程导⼊数据

28.导数据指定分隔符（和mysql的select into outfile很像）29.⽣成授权语句30 .给⽤户授权

31、改变postgreSQL的默认schema⼀、查看表某模式所有分布键信息

SELECT

aaa.nspname AS \"模式名\aaa.relname AS \"表名\

aaa.table_comment AS \"中⽂表明\ccc.attname AS \"分布键\"FROM (

SELECT aa.oid,

obj_description (aa.oid) AS table_comment, aa.relname, bb.localoid, bb.attrnums,

regexp_split_to_table (

array_to_string (bb.attrnums, ','), ',' ) att,

dd.nspname FROM pg_class aa --原数据信息 最重要的表！ LEFT

JOIN gp_distribution_policy bb ON bb.localoid = aa.oid --分布键表LEFT

JOIN pg_namespace dd ON dd.oid = aa.relnamespace --模式LEFT

JOIN pg_inherits hh ON aa.oid = hh.inhrelid --继承表 WHERE dd.nspname = 'dim' -- 替换成需要的模式AND hh.inhrelid IS NULL ) aaa

LEFT JOIN pg_attribute ccc ON ccc.attrelid = aaa.oidAND ccc.attnum = aaa.attWHERE

ccc.attnum > 0ORDER BYaaa.relname;

⼆、 数据库运⾏状态查询管理

1.greenplum查询正在运⾏的sql，session

-- ⽅法1：SELECT

tt.procpid, -- pid

usename user_name, -- 执⾏的⽤户 backend_start, -- 会话开始时间 query_start, -- 查询开始时间 waiting, -- 是否等待执⾏

now() - query_start AS current_query_time, -- 累计执⾏时间 now() - backend_start AS current_session_time,*/ current_query,

client_addr , datnameFROM

pg_stat_activity tt

WHERE current_query != ''ORDER BY current_query_time DESC;

-- ⽅法2（通过视图查）SELECT procpid, START,

now() - START AS lap, current_query,

-- count() over() count_num, t2.rolname,t3.rsqname, ipFROM (

SELECT

backendid,

pg_stat_get_backend_userid(S.backendid) as uid,

pg_stat_get_backend_client_addr(S.backendid) as ip, pg_stat_get_backend_pid (S.backendid) AS procpid,

pg_stat_get_backend_activity_start (S.backendid) AS START, pg_stat_get_backend_activity (S.backendid) AS current_query FROM (

SELECT

pg_stat_get_backend_idset () AS backendid ) AS S

) AS t1 left join pg_authid t2 on t1.uid=t2.oid left join pg_resqueue t3 on t2.rolresqueue=t3.oidWHERE

current_query!= ''ORDER BY lap DESC;

-- ⽅法3（限定了⾓⾊和资源队列，查当前账号正在查询的语句）SELECT rolname, rsqname, pid,

GRANTED, current_query, datnameFROM

pg_roles t1,

gp_toolkit.gp_resqueue_status t2 , pg_locks t3 ,

pg_stat_activity t4 WHERE

t1.rolresqueue = t3.objidAND t3.objid=t2.queueidand t4.procpid=t3.pid

2.终⽌执⾏的sql

select pg_terminate_backend(48988); --pid

3.查看greemplum资源队列状态

SELECT * FROM gp_toolkit.gp_resqueue_status;

4.查看greemplum资源队列锁

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

5.查看greemplum资源队列优先级

select * from gp_toolkit.gp_resq_priority_statement;

6.查看greemplum所有连接 类似mysql SHOW PROCESSLIST

select * from pg_stat_activity; -- 所有状态的连接

7.greemplum磁盘使⽤，通过SQL查看Greenplum中⽤了多少空间

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

8.查看greemplum节点状态

select * from gp_segment_configuration tt

select * from gp_segment_configuration tt where tt.status='d'; -- 状态为down

9.节点故障等历史信息

select * from gp_configuration_history tt order by 1 desc ;

10.数据倾斜

SELECT

t1.gp_segment_id, t1.count_tatol,

round(t1.count_tatol-(AVG(t1.count_tatol) over()) ,0)FROM (

SELECT

gp_segment_id,

COUNT (*) count_tatol FROM

-- 要查的表 GROUP BY

gp_segment_id ) t1

order by 3

11.greemplum表或索引⼤⼩ （占⽤空间）

select pg_size_pretty(pg_relation_size('gp_test'));

12.greemplum表和索引⼤⼩（占⽤空间）

select pg_size_pretty(pg_total_relation_size('gp_test'));

13.greemplum查看指定数据库⼤⼩（占⽤空间）

select pg_size_pretty(pg_database_size('postgres'));

14.greemplum所有数据库⼤⼩（占⽤空间）

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

15.查看greemplum数据分布情况

select gp_segment_id,count(*) from gp_test group by gp_segment_id order by 1;

三、查源数据

16.查看greemplum数据表更新时间

SELECT *FROM

pg_stat_last_operation, pg_classWHERE

objid = oid

AND relname = 'base_common'; -- 表名

17.通过sql 获取greemplum表的预估数据量

select

relname,

reltuples::int as totalfrom

pg_classwhere

relname = 'base_common'

and relnamespace = (select oid from pg_namespace where nspname = 'positions');

18.通过sql 获取greemplum获取分布键

SELECT string_agg(att.attname,',' order by attrnums) as distribution FROM gp_distribution_policy a,pg_attribute att

WHERE a.localoid ='bi_data.schoolmate_relations'::regclass and a.localoid = att.attrelid

and att.attnum = any(a.attrnums);

19.通过sql获取 greemplum指定表结构

SELECT

attname, typname FROM

pg_attribute

INNER JOIN pg_class ON pg_attribute.attrelid = pg_class.oid INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid

INNER JOIN pg_namespace on pg_class.relnamespace=pg_namespace.oid -- WHERE

pg_attribute.attnum > 0 AND attisdropped <> 't'

AND pg_namespace.nspname='resumes' AND pg_class.relname= 'base_common'

-- and pg_class.relname ~~* any(array['%some%', '%someelse']));order by pg_attribute.attnum

20.显⽰哪些没有统计信息且可能需要ANALYZE的表

SELECT * from gp_toolkit.gp_stats_missing ;

21.显⽰在系统表中被标记为掉线的Segment的信息

SELECT * from gp_toolkit.gp_pgdatabase_invalid;

22.显⽰库中表的⼤⼩（单位G）

SELECT sotdoid,sotdsize/1024/1024/1024 as sotdsize,sotdtoastsize,sotdadditionalsize,sotdschemaname,sotdtablename from gp_toolkit.gp_size_of_table_disk order by sotdsize desc;

23.查询⼀个库中有多少表（如果有分区表不列出⼦分区）

SELECT relname from pg_class a,pg_namespace b where relname not like '%prt%' and relkind ='r' and a.relnamespace=b.oid and nspname not in ('pg_catalog','information_schema','gp_toolkit') and nspname not like '%pg_temp%';

24.查询某个⽤户对某个表有什么权限

select * from INFORMATION_SCHEMA.role_table_grants where grantee='user_name' and table_name='table';

25.查看分区表的信息

SELECT tablename,partitiontablename,partitiontype,partitionboundary from pg_partitions where tablename='table_name' order by partitionboundary desc;

26.导⼊数据

copy t1 from '/home/gpadmin/t1.txt' with delimiter '|' LOG ERRORS INTO INSERT_ERRS SEGMENT REJECT LIMIT 100;

27.远程导⼊数据

psql -h 1.1.1.1 -U user_name -d db_name -W -c \"copy tb1 from stdin with delimiter '|'\" < /home/gpadmin/tb1.txt

28.导数据指定分隔符（和mysql的select into outfile很像）

psql -d db_name -c \"select * from tb1\" -o tb1.txt -t -A -F $'/t'

29.⽣成授权语句

SELECT 'grant select on '||relname||' to user_name;' from pg_class a,pg_namespace b where relname not like '%prt%' and relkind ='r' and has_table_privilege('user_name',a.oid,'select')='f' and a.relnamespace=b.oid and nspname not in ('pg_catalo

30 .给⽤户授权

select 'grant all on SCHEMA ' || tt.autnspname || ' to tuser;' as grant_script from gp_toolkit.__gp_user_tables tt -- group by tt.autnspnameunion -- all

select 'grant all on table ' || tt.autnspname || '.' ||tt.autrelname || ' to tuser;' grant_script from gp_toolkit.__gp_user_tables tt;

31、改变postgreSQL的默认schema

-- Use this to show the current search_path-- Should return: \"$user\SHOW search_path;-- Create another schemaCREATE SCHEMA my_schema;

GRANT ALL ON SCHEMA my_schema TO my_user;-- To change search_path on a connection-levelSET search_path TO my_schema;

-- To change search_path on a database-level

ALTER database \"my_database\" SET search_path TO my_schema;