近期在写项目中需要查询各种数据源的元数据信息,目前做了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'