技本功|Hive优化之监控（三）

时间 2021-05-11 标签技术文档数据库运维 hive 大数据 mysql

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）

元数据表字段	说明	示例数据
DB_ID	数据库ID	2
DESC	数据库描述	测试库
DB_LOCATION_URI	数据库HDFS路径	hdfs://namenode/user/hive/warehouse/hhh1234.db
NAME	数据库名	hhh1234
OWNER_NAME	数据库所有者用户名	hhh1234
OWNER_TYPE	所有者角色	USER

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

元数据表字段	说明	示例数据
TBL_ID	表ID	1
CREATE_TIME	创建时间	1436317071
DB_ID	数据库ID	2，对应DBS中的DB_ID
LAST_ACCESS_TIME	上次访问时间	1436317071
OWNER	所有者	hhh
RETENTION	保留字段	0
SD_ID	序列化配置信息	86，对应SDS表中的SD_ID
TBL_NAME	表名	hhh1234
TBL_TYPE	表类型	MANAGED_TABLE、EXTERNAL_TABLE、INDEX_TABLE、VIRTUAL_VIEW
VIEW_EXPANDED_TEXT	视图的详细HQL语句	select `hhh1234`.`pt`, `hhh1234`.`pcid` from `liuxiaowen`.`hhh1234`
VIEW_ORIGINAL_TEXT	视图的原始HQL语句	select * from lxw1234

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

元数据表字段	说明	示例数据
SD_ID	存储信息ID	1
CD_ID	字段信息ID	21，对应CDS表
INPUT_FORMAT	文件输入格式	org.apache.hadoop.mapred.TextInputFormat
IS_COMPRESSED	是否压缩	0
IS_STOREDASSUBDIRECTORIES	是否以子目录存储	0
LOCATION	HDFS路径	hdfs://namenode/hivedata/warehouse/ut.db/t_hhh
NUM_BUCKETS	分桶数量	5
OUTPUT_FORMAT	文件输出格式	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
SERDE_ID	序列化类ID	3，对应SERDES表

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

元数据表字段	说明	示例数据
PART_ID	分区ID	1
CREATE_TIME	分区创建时间
LAST_ACCESS_TIME	最后一次访问时间
PART_NAME	分区名	pt=2020-09-26
SD_ID	分区存储ID	21
TBL_ID	表ID	2

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/csdn