实时数仓Hologres：系统表

概述

使用限制

• 表名称以hg开头的是Hologres系统表，以pg开头的是PostgreSQL系统表，简称PG系统表。PG系统表不能与业务创建的表进行关联查询，也不能将PG系统表的数据导入至业务创建的表中。

  说明

  Hologres 从V1.3.22版本开始，支持PG系统表与业务创建的表进行Join，以及将系统表数据导出到Hologres表，但需注意，如果存在Hologres不支持的数据类型，则无法导出到Hologres表，也无法与Hologres表进行Join。如果有需求请您升级实例。

• 在Hologres中，系统表中的OID字段表示表、索引、视图等关系的唯一标志符。但Postgres是单机系统，Hologres是分布式系统，有多个FE节点，每个FE节点的OID序列通常不相同。所以当查询结果中有OID时，可能会出现OID结果不一致的情况。

hologres.hg_table_properties

pg_catalog.pg_tables

pg_tables保存表的元信息，包含如下字段。

pg_catalog.pg_locks

pg_catalog.pg_class

pg_class用于保存原生Postgres的所有系统信息，包含如下字段。

hologres_statistic.hg_table_statistic

Hologres的统计信息表，其字段如下。

pg_catalog.pg_stats

pg_stats用于保存Postgres原生的统计信息，字段如下。

pg_catalog.pg_roles

pg_roles用于存放实例内角色及其权限信息，字段如下。

information_schema.role_table_grants

常用SQL

一些常见命令可以通过psql简写来实现，详情请参见psql。同时也可以根据业务情况选择使用如下提供的常见SQL。

查看表对应的Hologres索引和属性信息

SELECT * FROM hologres.hg_table_properties where table_name = '<tablename>';

tablename为表名称。

查看表/视图DDL

select hg_dump_script('<tablename>'); -- 表DDL
select hg_dump_script('<viewname>');  -- 视图DDL

create extension hg_toolkit;

查看实例的Endpoint

show hg_frontend_endpoints;

查看当前实例下的所有数据库

SELECT
    d.datname AS "Name",
    pg_catalog.pg_get_userbyid(d.datdba) AS "Owner",
    pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
    d.datcollate AS "Collate",
    d.datctype AS "Ctype",
    pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM
    pg_catalog.pg_database d
WHERE
    d.datname != 'postgres'
    AND d.datname != 'template0'
    AND d.datname != 'template1'
ORDER BY
    1;

列出当前数据库下所有User Mapping

SELECT
    um.srvname AS "Server",
    um.usename AS "User name"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server'
ORDER BY
    1,
    2;

列出当前数据库下所有Schema

SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM
    pg_catalog.pg_namespace n
WHERE
    n.nspname !~ '^pg_'
    AND n.nspname <> 'information_schema'
    AND n.nspname != 'hologres'
    AND n.nspname != 'hologres_sample'
    AND n.nspname != 'hologres_statistic'
    AND n.nspname !~ '^hg_'
    AND n.nspname !~ '^holo_'
ORDER BY
    1;

列出当前数据库下所有表、外表和视图

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看当前Schema下所有表以及表Owner（不包含系统表）

--查看当前DB下所有表（包含系统表）
SELECT * FROM pg_tables


--查看当前schema下所有表以及表owner（不包含系统表）
SELECT  n.nspname as "Schema"
        ,c.relname as "Name"
        ,CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type"
        ,pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM    pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON      n.oid = c.relnamespace
WHERE   c.relkind IN ('r','p','v','m','S','f','')
and     n.nspname <> 'pg_catalog'
and     n.nspname <> 'information_schema'
and     n.nspname !~ '^pg_toast'
and     pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
            

查看父表对应的所有子表

-- 含分区键值
SELECT  c.oid::pg_catalog.regclass
        ,c.relkind
        ,pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM    pg_catalog.pg_class c
        ,pg_catalog.pg_inherits i
WHERE   c.oid = i.inhrelid
AND     i.inhparent::pg_catalog.regclass = 'parent_table_name'::pg_catalog.regclass
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT'
;



-- 不含分区键值
SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

查看所有子表的创建时间和所属父表

select
        cn.nspname as child_schema_name,c.relname as child_table_name,
        pn.nspname as parent_schema_name,p.relname as parent_table_name,
        to_timestamp(cp.property_value::bigint) as create_time
from pg_inherits i
left join pg_class p on p.oid=i.inhparent
left join pg_namespace pn on pn.oid = p.relnamespace
left join pg_class c on c.oid=i.inhrelid
left join pg_namespace cn on cn.oid = c.relnamespace
left join hologres.hg_table_properties cp on cp.property_key='create_time' and cp.table_namespace=pn.nspname and cp.table_name = c.relname;

查看所有外部表以及外部表对应的MaxCompute表

SELECT  n.nspname
        ,c.relname
        ,s.srvname
        ,pg_catalog.array_to_string(
            ARRAY(
                SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(ftoptions)
            )
            ,', '
        )
FROM    pg_catalog.pg_foreign_table f
        ,pg_catalog.pg_foreign_server s
        ,pg_catalog.pg_class c
        ,pg_catalog.pg_namespace n
WHERE   s.oid = f.ftserver
and     c.oid = f.ftrelid
and     c.relnamespace = n.oid
and     n.nspname not in ('hologres', 'hologres_statistic', 'pg_catalog', 'pg_toast')
;
            

列出当前数据库下所有视图

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('v', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看表被哪些view依赖

select * from information_schema.view_table_usage where table_name = '<table_name>';

查看表或者表字段的注释

• 查看指定表中字段的注释：

  SELECT a.attname as Column,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
    a.attnotnull as "Nullable",
    pg_catalog.col_description(a.attrelid, a.attnum) as "Description"
  FROM pg_catalog.pg_attribute a
  WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = '<schema.tablename>'::regclass::oid
  ORDER BY a.attnum;

  其中schema.tablename为{Schema名称}.{表名称}。

• 查看表的注释并且包含表的Owner等相关信息。

  SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
    pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
    pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
    pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r','p','v','m','S','f','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
  ORDER BY 1,2;
  
  
  --返回结果示例
                          List of relations
   Schema | Name | Type  |      Owner       |  Size   | Description
  --------+------+-------+------------------+---------+-------------
   public | a    | table | 1365937xxxx | xxxx bytes | abcdef
  (1 row)

• 只查看指定表的注释信息：

  select pg_catalog.obj_description('<tablename>'::regclass::oid, 'pg_class') as "Description";
  
  --返回结果示例
  Description
  ------------
  abcdef

  其中tablename为指定表名称。

列出当前数据库下所有用户和角色

SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY (
        SELECT
            b.rolname
        FROM
            pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE
            m.member = r.oid) AS memberof,
    r.rolreplication,
    r.rolbypassrls
FROM
    pg_catalog.pg_roles r
WHERE
    r.rolname !~ '^pg_'
ORDER BY
    1;

列出当前数据库下所有EXTENSIONS

SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname != 'hg_admin_cmd'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'holo_funcs'
    AND e.extname != 'holo_link'
    AND e.extname != 'holo_system_admin'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'query_log'
    AND e.extname != 'plpgsql'
ORDER BY
    1;

查看某个账号的对应权限

SELECT * FROM pg_roles where rolname='<uid>'

查看当前实例下所有的用户以及权限示例

SELECT  r.rolname
        ,r.rolsuper
        ,r.rolinherit
        ,r.rolcreaterole
        ,r.rolcreatedb
        ,r.rolcanlogin
        ,r.rolconnlimit
        ,r.rolvaliduntil
        ,ARRAY(
            SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid
        ) as memberof
        ,r.rolreplication
        ,r.rolbypassrls
FROM    pg_catalog.pg_roles r
WHERE   r.rolname !~ '^pg_'
ORDER BY 1;

查看一个用户有权限的所有表

SELECT current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]) THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            WHEN c.relkind = 'f'::"char" THEN 'FOREIGN'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type,
        CASE
            WHEN (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) OR (c.relkind = ANY 
(ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) 
& 8) = 8 THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_insertable_into,
        CASE
            WHEN t.typname IS NOT NULL THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_typed,
    NULL::character varying::information_schema.character_data AS commit_action
   FROM pg_namespace nc
     JOIN pg_class c ON nc.oid = c.relnamespace
     LEFT JOIN (pg_type t
     JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND 
NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role('<USERID>', c.relowner, 'USAGE'::text)
 OR has_table_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
 OR has_any_column_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

查看一张表有权限的所有用户

select rolname from pg_roles where has_table_privilege(rolname, '<schemaname>.<tablename>',
 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER');