基于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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24