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/