python数据字典查询数据库,Hive查询数据字典

从知识库中查询hive的数据字典

SELECT

D.NAME AS SCHEMA_NAME, -- 数据库名称

T.TBL_NAME AS TABLE_NAME_EN, -- 表名

P.PARAM_VALUE AS TABLE_NAME_CN,

C.INTEGER_IDX AS COLUMN_ID, -- 字段序号

C.COLUMN_NAME AS COLUMN_NAME_EN, -- 字段名

C.COMMENT AS COLUMN_NAME_CN, -- 字段备注

C.TYPE_NAME AS COLUMN_DATA_TYPE, -- 字段类型

PART_KEYS AS PART_COLUMN -- 分区字段

FROM

DBS D -- 数据库

JOIN TBLS T -- 表

ON D.DB_ID = T.DB_ID AND D.NAME='${schema_name}'

LEFT JOIN TABLE_PARAMS P -- 表属性

ON T.TBL_ID=P.TBL_ID AND P.PARAM_KEY='comment'

LEFT JOIN SDS S -- 字段与表的关联关系

ON T.SD_ID = S.SD_ID

LEFT JOIN COLUMNS_V2 C -- 字段

ON S.CD_ID = C.CD_ID

LEFT JOIN (SELECT TBL_ID,GROUP_CONCAT(PKEY_NAME) AS PART_KEYS FROM PARTITION_KEYS GROUP BY TBL_ID) K -- 字段

ON T.TBL_ID = K.TBL_ID

ORDER BY

T.SD_ID,

C.INTEGER_IDX;

SELECT

D.NAME AS SCHEMA_NAME, -- 数据库名称

T.TBL_NAME AS TABLE_NAME_EN, -- 表名

P.PARAM_VALUE AS TABLE_NAME_CN,

C.INTEGER_IDX AS COLUMN_ID, -- 字段序号

C.COLUMN_NAME AS COLUMN_NAME_EN, -- 字段名

C.COMMENT AS COLUMN_NAME_CN, -- 字段备注

C.TYPE_NAME AS COLUMN_DATA_TYPE, -- 字段类型

PART_KEYS AS PART_COLUMN -- 分区方式

FROM

DBS D -- 数据库

JOIN TBLS T -- 表

ON D.DB_ID = T.DB_ID AND D.NAME='${schema_name}'

LEFT JOIN TABLE_PARAMS P -- 表属性

ON T.TBL_ID=P.TBL_ID AND P.PARAM_KEY='comment'

LEFT JOIN SDS S -- 字段与表的关联关系

ON T.SD_ID = S.SD_ID

LEFT JOIN COLUMNS_V2 C -- 字段

ON S.CD_ID = C.CD_ID

LEFT JOIN (SELECT TBL_ID,GROUP_CONCAT(concat(PKEY_NAME,' ',PKEY_TYPE,' comment ''',PKEY_COMMENT,'''' )) AS PART_KEYS FROM PARTITION_KEYS GROUP BY TBL_ID) K -- 字段

ON T.TBL_ID = K.TBL_ID

ORDER BY

T.SD_ID,

C.INTEGER_IDX;