元数据库查询hive、doris表结构导出excel

 

---hive导出数据字典
SELECT 
    DBS.NAME AS `Database`,
    TBLS.TBL_NAME AS `Table`,
    TABLE_PARAMS.PARAM_VALUE   AS `Table_Comment`, 
    COLUMNS_V2.COLUMN_NAME AS `Field`,
    COLUMNS_V2.TYPE_NAME AS `Type`,
    COLUMNS_V2.COMMENT AS `Colume_Comment`
FROM 
    DBS
left JOIN 
    TBLS ON DBS.DB_ID = TBLS.DB_ID 
left join TABLE_PARAMS
    on TBLS.TBL_ID = TABLE_PARAMS.TBL_ID  and TABLE_PARAMS.PARAM_KEY ='comment'
left JOIN 
    COLUMNS_V2 ON TBLS.TBL_ID = COLUMNS_V2.CD_ID 
ORDER BY 
    `Database`, 
    `Table`, 
    COLUMNS_V2.INTEGER_IDX ;



--- Doris导出数据字典 
select
 c.TABLE_SCHEMA as '数据库',
 c.TABLE_NAME as '表名',
t.TABLE_COMMENT as '描述',
 c.COLUMN_NAME as '字段名称',
 c.COLUMN_COMMENT as '字段描述',
 c.COLUMN_TYPE as '数据类型  ',
 IF(kcu.ORDINAL_POSITION is not NULL,'√',' ' ) AS '主键',
 IF(c.IS_NULLABLE='YES','√',' ' ) AS '为空',
 COLUMN_COMMENT as '取值说明'
 from information_schema.columns c
 LEFT JOIN information_schema.TABLES t on  t.TABLE_NAME = c.TABLE_NAME
 left join information_schema.key_column_usage kcu 
 on   c.TABLE_NAME = kcu.TABLE_NAME and c.COLUMN_NAME = kcu.COLUMN_NAME
 where c.TABLE_SCHEMA = 'ads'