技本功|Hive优化之监控（三）
Hive是大数据领域常用的组件之一，主要是大数据离线数仓的运算，关于Hive的性能调优在日常工作和面试中是经常涉及的一个点，因此掌握一些Hive调优是必不可少的技能。影响Hive效率的主要有数据倾斜、数据冗余、job的IO以及不同底层引擎配置情况和Hive本身参数和HiveSQL的执行等因素。本文主要是从监控运维的角度对Hive进行整体性能把控，通过对hive元数据监控，提前发现Hive表的不合理处及可优化点，将被动运维转化为主动运维。
1 Hive元数据简介
Hive元数据一般会存储在关系数据库中，mysql是最常见的选择，这里介绍的就是Hive元数据就是存储在myslq中的，本次会介绍几张主要的元数据表，DBS、TBLS、SDS、PARTITIONS
1.1 Hive数据库相关的元数据表（DBS）
1.2 Hive表和视图相关的元数据表（TBLS）
1.3 Hive文件存储信息相关的元数据表（SDS）
1.4 Hive数据库相关的元数据表（PARTITIONS）
2 收集Hive元数据
在使用Hive元数据做监控时要确保相应表或者分区的元数据信息已经被收集。收集元数据的方式如下
2.1 收集表的元数据
analyze table 表名 compute statistic;
2.2 收集表的字段的元数据
analyze table 表名 compute statistic for columns;
2.3 收集所有分区的元数据
analyze table 表名 partition(分区列) compute statistic;
2.4 指定特定分区进行收集元数据
analyze table 表名 partition(分区列=分区值) compute statistic;
2.5 收集所有分区的列的元数据
analyze table 表名 partition(分区列) compute statistic for columns;
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**;
3.4监控表分区的数量
了解表的分区数量，在做全表join时如果一个表数量不大，分区很多，可以考虑分区合并等优化手段
**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**;
结语：
Hive元数据的监控主要目的就是对Hive中表情况的整体把控，这里主要介绍了大数据块、不分区表、表分区这几个指标的监控，当然还有很多，比如hive的小文件、表的数据存储格式等等，对这些信息的长期监控，最好可以和grafana这些结合展示，这对整个数仓的稳定运行至关重要。后面我们还会出Hive SQL调优相关的文章，敬请期待。
更多业务信息请查看云掣官网https://www.dtstack.com/dtsmart/
