greenplum(四)greenplum 常用数据库管理语句,sql工具

转载 2018年04月17日 14:13:33

原文地址:https://blog.csdn.net/you_xian/article/details/78549756


在greenplum 使用过程中积累的一些常用查询语句,整理出来备忘。欢迎各位留言补充。都是SQL命令以及数据字典的使用。熟悉数据字典非常重要。三个重要的schema:pg_catalog,pg_toolkit,information_schema,其中information_schema 中的数据字典都在视图中,并且这个schema中提供了大量的操作数据字典的函数值得研究。

一 数据库运行状态查询管理

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 , datname
FROM
    pg_stat_activity tt
 WHERE      current_query != '<IDLE>'
ORDER BY current_query_time DESC;


-- 方法2(通过视图查)
SELECT
    procpid,
  START,
  now()   - START AS lap,
    current_query,
  -- count() over() count_num,
  t2.rolname,t3.rsqname,
  ip
FROM
    (
        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.oid
WHERE
    current_query!= '<IDLE>'
ORDER BY lap DESC;

-- 方法3(限定了角色和资源队列,查当前账号正在查询的语句)
SELECT
    rolname,
    rsqname,
    pid,
    GRANTED,
    current_query,
    datname
FROM
    pg_roles  t1,
    gp_toolkit.gp_resqueue_status t2 ,
    pg_locks t3 ,
    pg_stat_activity t4
WHERE
    t1.rolresqueue = t3.objid
AND t3.objid=t2.queueid
and 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
           <tablename>  -- 要查的表
        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_class
WHERE
    objid = oid
AND relname = 'base_common'; -- 表名

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

select
    relname,
    reltuples::int as total
from
    pg_class
where
    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

同时可以参考:

【greenplum】greenplum 数据字典实践--通过sql脚本查询表结构,拼装建表语句

【greenplum】 获取表结构,实现类似mysql show create table 功能

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_catalog','information_schema','gp_toolkit') and nspname not like '%pg_temp%';

30 .给用户授权

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

【greenplum】greenplum 常用数据库管理语句,sql工具

在greenplum 使用过程中SQL命令以及数据字典非常重要。三个重要的schema:pg_catalog,pg_toolkit,information_schema,其中information_s...
  • you_xian
  • you_xian
  • 2017-11-16 12:20:21
  • 1140

Greenplum常用sql语句

1. 查看表使用空间 SELECT      *   FROM         PUBLIC.dba_segments   WHERE         owner   LIKE   'owb...
  • ningjieshuijing
  • ningjieshuijing
  • 2010-07-27 15:27:00
  • 5024

greenplum 常用SQL(不断更新)

首先通过pg_stat_activity 查出你要取消的进程号 select procpid from pg_stat_activity ; pg_cancel_backend 用来取消一个进程 ...
  • rgb_rgb
  • rgb_rgb
  • 2012-09-27 16:19:54
  • 3238

Greenplum SQL语句开发

Sql开发中最基本的规范要求: 1.代码行清晰、整齐、层次分明、结构性强,易于阅读; 2.代码中应具备必要的注释以增强代码的可读性和可维护性; 3.代码应充分考虑执行效率,保证代码的高效性; ...
  • u012564911
  • u012564911
  • 2017-03-09 14:32:40
  • 645

GreenPlum常用数据库命令

  • 2012年11月28日 11:24
  • 260KB
  • 下载

greenplum sql使用实例【未完待续】

CREATE DATABASECREATE DATABASE name [ [WITH] [OWNER [=] dbowner] [TEMPLATE [=] ...
  • micklf
  • micklf
  • 2016-08-17 13:24:44
  • 773

【greenplum】greenplum 数据字典实践--通过sql脚本查询表结构,拼装建表语句

通过pg_catalog 数据字典用sql 查询greemplum的表结构,构建自定义的数据字典库。可以自己拼装成建表语句,例如:将greemplum转换成mysql建表语句等。...
  • you_xian
  • you_xian
  • 2017-12-21 16:09:14
  • 375

GREENPLUM介绍之数据库管理(三)

与其它关系型数据库一样,二维表同样是GP中最重要的存储数据对象。只不过为了更好的支持数据仓库海量数据的访问,GP在表这个层面为我们提供了更多更好的选项。 从数据存储方式上看,GP的表可以分成面向行存...
  • sptoor
  • sptoor
  • 2012-05-16 15:19:36
  • 3910

mysql 和greenplum的语法区别

greenplum 基于postgresql关系型数据库,语言基本和mysql相似. 插入/更新/删除------>同mysql 1. 字段查询 gp保留关键字如name  s...
  • CZJ2015
  • CZJ2015
  • 2016-11-08 16:47:39
  • 1371

来自阿里的greenplum数据库经验谈

  • 2016年05月15日 12:04
  • 348KB
  • 下载
收藏助手
不良信息举报
您举报文章:greenplum(四)greenplum 常用数据库管理语句,sql工具
举报原因:
原因补充:

(最多只允许输入30个字)