【Hive实战】Hive治理方向探讨（请留意见）

Hive治理方向探讨

全篇数据已20230618分区的数据为准

Hive治理项

治理临时性质的表

1. 临时表未及时清理，或按时间维度建表（亦是分区表类）
2. 临时属性却以常规表标记。

诉求： 能够更好的识别出临时表，对超期临时表进行删除程序。

控制分区表的分区数量和分区层级

内部表的分区表数量 99,709

内部表的非分区表数量 222,555

外部表的分区表数量 7,842

外部表的非分区表数量 719

表类型分布数量

表类型	表数量	占比
MANAGED_TABLE	322264	97.28%
VIRTUAL_VIEW	446	0.13%
INDEX_TABLE	3	-
EXTERNAL_TABLE	8561	2.58%

hive分区数量分布情况

按时间（天）的维度进行计算分区数区间

时间区间	分区数区间	表数量	占比
半年（6个月）	[1,180]	29148	34.33%
1年	[181,365]	5914	6.96%
2年	[366,730]	12069	14.21%
3年	[731,1095]	12314	14.50%
5年	[1096,1825]	18476	21.76%
10年	[1826,3650]	5751	6.77%
20年	[3651,7300]	401	0.47%
30年	[7301,10950]	156	0.18%
30年以上	[10950,+∞]	687	0.81%
目前5年以上数据定义为存疑	[1826,+∞]	6995	8%

hive分区表的层级分布情况

分区层级	此层级的表数量	分区级别下的分区数
1	105475	74891729
2	2072	8241483
>2	7	2141

超过2层分区级别的分区表

TBL_NAME	OWNER	PART_LEVEL

诉求： 识别出分区的数量和分区的层级，对数据异常的分区表（分区数量超过1826个，分区层级超过2层的）进行处理。

原则上分区层级不超过2层，单个表（1级分区）的最大分区数不超过2000，单个表（2级分区）的最大分区数不超过5000。

限制建表时使用的存储格式

存储格式

• 低效存储格式（TextInputFormat、RCFileInputFormat）

• 废弃存储格式（DeprecatedLzoTextInputFormat,DeprecatedRawMultiInputFormat）

• 无格式

INPUT_FORMAT分布情况

INPUT_FORMAT类	数量	占比
com.hadoop.mapred.DeprecatedLzoTextInputFormat	183962	0.220%
com.twitter.elephantbird.mapred.input.DeprecatedRawMultiInputFormat	7889	0.009%
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat	29966343	35.902%
org.apache.hadoop.mapred.TextInputFormat	18777680	22.497%
org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat	67633	0.081%
************************************************************1	6252676	7.491%
org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat	2522173	3.022%
org.apache.hadoop.hive.ql.io.RCFileInputFormat	25427575	30.464%
org.elasticsearch.hadoop.hive.EsHiveInputFormat	3	-
org.apache.hadoop.mapred.SequenceFileInputFormat	15274	0.018%
************************************************************ 2	9643	0.012%
************************************************************ 3	2297	0.003%
************************************************************ 4	228137	0.273%
************************************************************ 5	3023	0.004%
	1982	0.002%

OUTPUT_FORMAT分布情况

OUTPUT_FORMAT类	数量	占比
org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	25443778	30.485%
org.elasticsearch.hadoop.hive.EsHiveOutputFormat	4	0.000%
org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat	67633	0.081%
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat	29966344	35.903%
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat	15556	0.019%
org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat	13356	0.016%
org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat	7889	0.009%
org.apache.hadoop.hive.ql.io.RCFileOutputFormat	25427575	30.465%
org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat	2522173	3.022%
	1982	-

分区的格式为空的内部表

TBL_NAME	OWNER	TBL_TYPE

格式为空的非分区的内部表

TBL_NAME	OWNER	TBL_TYPE

诉求：

• 控制文件格式的选项，若无特殊需求，以ORC和Parquet类的格式为主。

  数据一般按以下结构进行分层存储：

  • 贴源层：该层是将数据源中的数据直接抽取过来的，数据类型以文本为主，需要保持数据原样。数据不会发生变化，在初次清洗之后被读取的概率也不大，可以采用ORC格式文件。

  • 加工汇总层：该层是数仓的数据加工组织阶段，会做一些数据的清洗和规范化的操作，比如去除空数据、脏数据、离群值等。采用ORC能够较好支持该阶段的数据ACID需求。

  • 应用层：该层的数据是供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里。此时的数据已经具备了对外部的直接使用的能力。数据的可能具备了一定层度的结构化，而Parquet在实现复杂的嵌套结构方面，比ORC更具有优势。所以该层一般采用Parquet。

    主要考虑的因素

    • 数据的变更性
    • 数据的结构复杂性
    • 数据的读写效率性

表或分区记录的location对应的HDFS路径实际不存在

可参考里面的语句参考

诉求：这样情况的表或分区是否已停止使用，如果不使用了，就删除。若使用，则需要修正。

表级路径应是分区路径的前缀

内部分区表下的 分区不以表路径为前缀的分区数量268,651&