hive/mysql/oracle/postgresql元数据信息查询

近期在写项目中需要查询各种数据源的元数据信息，目前做了hive/mysql/oracle/postgresql四中数据源的支持：

  表名查询

1、 mysql
    jdbc连接`information_schema`

    select
        TABLE_NAME
    from TABLES
    where TABLE_SCHEMA='dbName'

  2、hive
    hive元数据存储在mysql库中

    select 
        t1.TBL_NAME
    from TBLS t1
    LEFT JOIN DBS t2 on t1.DB_ID = t2.DB_ID
    t2.`NAME`='dbName'

    
 3、oracle表名查询
    

    select
        TABLE_NAME
    from all_tables
    where owner='dbName'

    
 4、postgresql表名查询
    默认模式`pulic`

   postGresql的元数据库信息可查看：https://www.postgresql.org/docs/10/static/catalogs.html

    select
        tablename
    from pg_tables
    schemaname='public'

字段名查询

1、mysql
  jdbc连接`information_schema `

select
    COLUMN_NAME, COLUMN_COMMENT
from COLUMNS
where TABLE_SCHEMA='dbName' and TABLE_NAME='tableName'

2、hive    
    

select 
     t1.COLUMN_NAME
     ,t1.`COMMENT` 
 from COLUMNS_V2 t1 
 left join TBLS t2 on t2.TBL_ID=t1.CD_ID
 left join DBS t3 on t3.DB_ID=t2.DB_ID and t3.`NAME`='stg'
 where t2.TBL_NAME='tableName'

    
 3、 oracle
  

    SELECT 
        COLUMN_NAME,COMMENTS 
    FROM all_COL_COMMENTS
    WHERE owner = 'dbName'  and TABLE_NAME='tableName' 

    
4、postgresql,在默认pulibc模式下
    

    select 
        t1.attname
        ,t4.description
    from pg_attribute t1
    left join pg_class t2 on t1.attrelid=t2.oid
    left join pg_namespace t3 on t2.relnamespace=t3.oid
    left join pg_description t4 on t1.attrelid=t4.objoid and t1.attnum = t4.objsubid
    where t2.relminmxid=1 and t3.nspname='public' and t1.attnum>0 and t1.attisdropped='f'             
    and t2.relname='tableName'