HIVE注释修改、元数据管理

1、概述

• 元数据（Metadata）：描述数据的数据
  例如学生信息表中，小黄和15是数据，student_name和age是元数据

元数据分类	说明	示例
技术元数据	数仓的设计和管理人员 用于开发和管理数仓时用的数据	数据源信息、数据转换的描述、源数据到目的数据的映射
业务元数据	能体现业务逻辑	表和字段的业务描述

• 元数据管理：注释管理、血缘分析、元数据分类、元数据查询…
  目的：帮助开发人员快速理解业务和查找字段，降低开发的运维成本

2、HIVE注释管理

• 在本文，HIVE元数据信息存到MySQL
  在数仓建设中，建库建表时要添加注释（COMMENT）
  对于一些历史遗留的缺乏注释的表，要对其添加注释

• 解决HIVE中文注释乱码：
  https://yellow520.blog.csdn.net/article/details/115960751

2.1、DDL

2.1.1、创建时添加注释

建库时添加注释

CREATE DATABASE b0 COMMENT '测试库';
USE b0;

建表时添加注释

CREATE TABLE t0(
    c0  INT     COMMENT '字段注释0',
    c1  STRING  COMMENT '字段注释1'
)COMMENT "表注释"
PARTITIONED BY (
    p0 STRING COMMENT "分区注释0",
    p1 INT COMMENT "分区注释1"
);

创建视图时添加注释

CREATE VIEW v0(
    c0  COMMENT '列注释0',
    c1  COMMENT '列注释1')
COMMENT '视图注释'
AS
SELECT c0,c1 FROM t0;

2.1.2、修改注释

修改表注释

ALTER TABLE t0 SET TBLPROPERTIES('comment'='修改表注释');

修改字段注释ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 数据类型 COMMENT '修改列注释';

ALTER TABLE t0 CHANGE COLUMN c1 c1 STRING COMMENT '修改列注释';

修改库和分区的注释可以去MySQL那边改（去HIVE官网找了一圈，暂时没找到😭）

-- 修改库注释（2个参数：库名、库注释）
UPDATE `DBS` SET `DESC`='修改库注释' WHERE `NAME`='b0';

-- 修改分区注释（4个参数：库名、表名、分区名、分区注释）
UPDATE `PARTITION_KEYS` SET `PKEY_COMMENT`='修改分区注释'
WHERE `TBL_ID` IN (
  SELECT t.TBL_ID
  FROM `DBS`b
  LEFT JOIN `TBLS`t ON b.`DB_ID`=t.`DB_ID`
  WHERE b.`NAME`='b0' AND t.`TBL_NAME`='t0'
)AND `PKEY_NAME`='p0';

2.2、在MySQL查看HIVE元数据

切换到存储HIVE元数据的库

USE metastore;

ER图

2.2.1、查看库

SELECT
    `DB_ID`,            -- 库ID
    `DESC`,             -- 库注释
    `DB_LOCATION_URI`,  -- 库在HDFS的位置
    `NAME`              -- 库名
FROM `DBS`;

2.2.2、查看表

查看表和表注释

SELECT
	b.`NAME`,               -- 库名
	t.`TBL_NAME`,           -- 表名
	t.`TBL_TYPE`,           -- 表类型
	c.`PARAM_VALUE`,        -- 表注释
	t.`VIEW_EXPANDED_TEXT`  -- 视图的SQL
FROM `DBS` b
LEFT JOIN `TBLS` t ON b.`DB_ID`=t.`DB_ID`
LEFT JOIN (
	SELECT `TBL_ID`,`PARAM_VALUE`
	FROM `TABLE_PARAMS`
	WHERE `PARAM_KEY`='comment'
)c ON t.`TBL_ID`=c.`TBL_ID`;

2.2.3、查看列

列注释：COLUMNS_V2的COMMENT

SELECT
    t.`TBL_NAME`,     -- 表名
    c.`COLUMN_NAME`,  -- 字段名
    c.`COMMENT`,      -- 字段注释
    c.`TYPE_NAME`     -- 字段的数据类型
FROM `TBLS`t
LEFT JOIN `SDS`s ON s.`SD_ID`=t.`SD_ID`
LEFT JOIN `COLUMNS_V2`c ON s.`CD_ID`=c.`CD_ID`;

2.2.4、查看分区

SELECT
    t.`TBL_NAME`,      -- 表名
    p.`PKEY_NAME`,     -- 分区名
    p.`PKEY_COMMENT`,  -- 分区注释
    p.`PKEY_TYPE`      -- 分区的数据类型
FROM `PARTITION_KEYS`p
LEFT JOIN `TBLS`t ON t.`TBL_ID`=p.`TBL_ID`;

2.2.5、查看全部

SELECT
    b.`NAME`,            -- 库名
    b.`DESC`,            -- 库注释
    t.`TBL_NAME`,        -- 表名
    t.`TBL_TYPE`,        -- 表类型
    tc.`PARAM_VALUE`,    -- 表注释
    c.`COL_NAME`,        -- 字段名
    c.`COL_COMMENT`,     -- 字段注释
    c.`DATA_TYPE`,       -- 数据类型
    c.`COL_TYPE`         -- 字段类型（列or分区）
-- 库
FROM `DBS` b
-- 表
INNER JOIN `TBLS` t ON b.`DB_ID`=t.`DB_ID`
-- 表注释
LEFT JOIN (
    SELECT `TBL_ID`,`PARAM_VALUE`
    FROM `TABLE_PARAMS`
    WHERE `PARAM_KEY`='comment'
    )tc ON t.`TBL_ID`=tc.`TBL_ID`
-- 字段注释
LEFT JOIN(
    (
        -- 分区注释
        SELECT
            `TBL_ID`,                         -- 表ID
            `PKEY_NAME`      `COL_NAME`,      -- 分区名
            `PKEY_COMMENT`   `COL_COMMENT`,   -- 分区注释
            `PKEY_TYPE`      `DATA_TYPE`,     -- 数据类型
            'partition'         `COL_TYPE`    -- 类型
        FROM `PARTITION_KEYS`
        )UNION ALL(
        -- 列注释
        SELECT
            t.`TBL_ID`       `TBL_ID`,        -- 表ID
            c.`COLUMN_NAME`  `COL_NAME`,      -- 字段名
            c.`COMMENT`      `COL_COMMENT`,   -- 字段注释
            c.`TYPE_NAME`    `DATA_TYPE`,     -- 字段的数据类型
            'column'      `COL_TYPE`          -- 类型
        FROM (
            SELECT b.`TBL_ID`,s.`CD_ID` FROM `TBLS`b
            INNER JOIN `SDS`s ON s.`SD_ID`=b.`SD_ID`
        )t
        LEFT JOIN `COLUMNS_V2`c ON t.`CD_ID`=c.`CD_ID`
    )
)c ON t.`TBL_ID`=c.`TBL_ID`;