hive 元数据库查询表结构 - 标签：postgresql

>i 元数据库查数据字典
## 元数据库查数据字典

### postgresql

```sql
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"='s'
	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",array_to_string(ARRAY(SELECT unnest(array_agg("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";
```

### mysql

```sql
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;
```