1.2 Hive表和视图相关的元数据表（TBLS）

 1.3 Hive文件存储信息相关的元数据表（SDS）

1.4 Hive数据库相关的元数据表（PARTITIONS）

2 收集Hive元数据

    在使用Hive元数据做监控时要确保相应表或者分区的元数据信息已经被收集。收集元数据的方式如下

2.1 收集表的元数据

2.2 收集表的字段的元数据

2.3 收集所有分区的元数据

2.4 指定特定分区进行收集元数据

2.5 收集所有分区的列的元数据

3   Hive元数据监控案例

3.1监控普通表存储的文件的平均大小

       对于大的文件块可能导致数据在读取时产生数据倾斜，影响集群任务的运行效率。下面sql是对于大于两倍HDFS文件块大小的表：

-- 整体逻辑：通过DBS找到对应库下面的表TBLS，再通过TBLS找到每个表对应的表属性，取得totalSize和numFiles两个属性，前者表示文件大小，后者表示文件数量

SELECT

    TBL_NAME,round(avgfilesize,1) as 'fileSize(Mb)'

FROM (

    SELECT

       tp.totalSize/(1024*1024)/numFiles avgfilesize,TBL_NAME

    FROM metastore.dbs d

    INNER join  metastore.tbls t on d.DB_ID = t.DB_ID

    left join  (

       SELECT TBL_ID,

           MAX(case PARAM_KEY when 'numFiles' then PARAM_VALUE ELSE 0 END) numFiles,

           MAX(case PARAM_KEY when 'totalSize' then PARAM_VALUE ELSE 0 END ) totalSize

       from metastore.table_params

       GROUP by TBL_ID

       ) tp on t.TBL_ID = tp.TBL_ID

       where d.NAME = '要监控的库'

       and tp.numFiles  is not NULL

       and tp.numFiles > 0

    ) a where  avgfilesize > hdfs的文件块大小*2

    ORDER BY avgfilesize desc;

3.2监控分区存储的文件平均大小，大于两倍HDFS文件块大小的分区，

-- 整体逻辑：先用DBS关联TBLS表，TBLS表关联PARTITIONS表PARTITION表关联PARTITION_PARAMS

SELECT

TBL_NAME,part_name,round(avgfilesize,1) as 'fileSize(Mb)'

FROM (

    SELECT

       pp.totalSize/(1024*1024)/numFiles avgfilesize,TBL_NAME,part.PART_NAME

    FROM metastore.dbs d

    INNER join metastore.TBLS t on d.DB_ID = t.DB_ID

    INNER join metastore.PARTITIONS part  on t.TBL_ID = part.TBL_ID

    left join  (

       SELECT PART_ID,

       -- 每个表存储的文件个数

       MAX(case PARAM_KEY when 'numFiles' then PARAM_VALUE ELSE 0 END) numFiles,

       -- 文件存储的大小

       MAX(case PARAM_KEY when 'totalSize' then PARAM_VALUE ELSE 0 END ) totalSize

       from metastore.PARTITION_PARAMS

       GROUP by PART_ID

    ) pp on part.PART_ID = pp.PART_ID

    where d.NAME = '要监控的库'

    and pp.numFiles  is not NULL

    and pp.numFiles > 0

) a where  avgfilesize >hdfs的文件块大小*2

ORDER BY avgfilesize desc;

3.3监控大表不分区的表

对于大数据量的表，如果不进行分区，意味着程序在读取相同的数据时需要遍历更多的文件块，性能会下降很多。

select t.TBL_NAME ,round(totalSize/1024/1024,1) as 'fileSize(Mb)'

FROM metastore.DBS d

inner join metastore.TBLS t on d.`DB_ID` = t.`DB_ID`

inner join (

    select `TBL_ID`,max(case `PARAM_KEY` when 'totalSize' then `PARAM_VALUE` else 0 end) totalSize

    from `TABLE_PARAMS`

    group by `TBL_ID`

 

) tp on t.`TBL_ID` = tp.`TBL_ID`

left join

(

    select distinct `TBL_ID` from metastore.PARTITIONS p

) part on t.`TBL_ID` = part.`TBL_ID`

where d.`NAME` = '要监控的库'

and  part.`TBL_ID` is null

and totalSize/1024/1024/1024 > 30

ORDER BY totalSize/1024 desc;

SELECT

t.TBL_NAME '表名',d.`NAME` '库名', COUNT(part.PART_NAME) '分区数'

FROM

DBS d

INNER JOIN TBLS t on d.DB_ID = t.DB_ID

INNER join `PARTITIONS` part  on part.TBL_ID = t.TBL_ID

WHERE d.`NAME` = '要监控的库'

GROUP by  t.TBL_NAME,d.`NAME`

ORDER BY COUNT(part.PART_NAME) desc;