常用-元数据查询sql

数据字典

SELECT
        a.table_name AS 英文表名,
        b.COMMENTs AS 中文表名,
        c.column_name AS 英文字段,
        c.COMMENTs AS 中文字段,
        d.data_type AS 字段类型,
        d.data_length AS 字段长度,
        a.num_rows AS 行数 
FROM
        user_tables a
        LEFT JOIN user_tab_comments b ON a.table_name = b.table_name
        LEFT JOIN user_col_comments c ON b.table_name = c.table_name
        LEFT JOIN user_tab_columns d ON c.COLUMN_name = d.COLUMN_name 
        AND d.table_name = c.table_name
        ORDER BY a.table_name

SELECT
        A.owner 英文数据库,
        '' 中文数据库,
        a.table_name AS 英文表名,
        b.COMMENTs AS 中文表名,
        c.column_name AS 英文字段,
        c.COMMENTs AS 中文字段,
        d.data_type AS 字段类型,
        d.data_length AS 字段长度,
        a.num_rows AS 行数 
FROM
        ALL_TABLES a
        JOIN ALL_tab_comments b ON a.table_name = b.table_name
        LEFT JOIN ALL_col_comments c ON b.table_name = c.table_name
        LEFT JOIN ALL_tab_columns d ON c.COLUMN_name = d.COLUMN_name 
        AND d.table_name = c.table_name 
WHERE
        A.owner IN ( 'NE2MES_SYSTEM', 'MES_SYSTEM' ) 
ORDER BY
        A.owner,
        a.table_name

HDFS文件查询

hadoop dfs -ls /warehouse/tablespace/managed/hive/qdmedev.db 

select d.NAME,a.TBL_NAME,e.PARAM_VALUE,c.COLUMN_NAME,c.TYPE_NAME,c.COMMENT,c.INTEGER_IDX
from TBLS a
join SDS b on (a.SD_ID=b.SD_ID)
join COLUMNS_V2 c ON (c.CD_ID=b.CD_ID)
join DBS d on (a.DB_ID=d.DB_ID)
join TABLE_PARAMS e on (a.TBL_ID=e.TBL_ID and e.PARAM_KEY='comment')
order by TBL_NAME,INTEGER_IDX

-- hive元数据
select d.NAME,a.TBL_NAME,e.PARAM_VALUE,c.COLUMN_NAME,c.TYPE_NAME,c.COMMENT,c.INTEGER_IDX
from TBLS a
join SDS b on (a.SD_ID=b.SD_ID)
join COLUMNS_V2 c ON (c.CD_ID=b.CD_ID)
join DBS d on (a.DB_ID=d.DB_ID)
join TABLE_PARAMS e on (a.TBL_ID=e.TBL_ID and e.PARAM_KEY='comment')
where d.NAME = 'qdmedev'
and a.TBL_NAME in ('ods_ne1mes3_mes_stationdata_assembly',
'ods_ne1mes3_mes_stationdata_machine',
'ods_ne1mes3_mes_stationdata_machine',
'ods_ne1mes3_mes_part_storage',
'ods_ne1mes3_mes_part_storage',
'ods_ne1mes3_wp_processinstance',
'ods_dc1mes_mes_stationdata',
'ods_dq1mes_mes_stationdata',
'ods_eds2mes1_mes_stationdata',
'ods_dkmes1_mes_stationdata_assembly',
'ods_eds2mes2_mes_stationdata_assembly',
'ods_ne1mes2_mes_stationdata_assembly',
'ods_ne1mes1_mes_stationdata_assembly',
'ods_ne1mes2_mes_stationdata_machine',
'ods_ne1mes2_mes_stationdata_machine',
'ods_ne1mes2_mes_stationdata_machine',
'ods_ne1mes2_mes_stationdata_machine',
'ods_ne1mes2_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_machine',
'ods_ne1mes1_mes_stationdata_casting',
'ods_ne1mes1_mes_stationdata_casting',
'ods_ne1mes2_mes_stationdata_casting'
)
order by TBL_NAME,INTEGER_IDX