Hive优化之监控(四)

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;