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

Hive治理方向探讨

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

Hive治理项

治理临时性质的表

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

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

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

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

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

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

外部表的非分区表数量 719

表类型分布数量

hive分区数量分布情况

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

hive分区表的层级分布情况

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

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

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

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

存储格式

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

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

• 无格式

INPUT_FORMAT分布情况

OUTPUT_FORMAT分布情况

分区的格式为空的内部表

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

诉求：

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

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

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

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

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

    主要考虑的因素

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

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

可参考里面的语句参考

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

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

内部分区表下的 分区不以表路径为前缀的分区数量268,651，对应的表数量 410。

诉求：内部分区表分区location与表路径进行规整。

内部表使用非内部表路径

– 898条

诉求：内部表的表路径需要使用内部路径

外部表使用内部表路径

– 6640条

诉求：外部表的表路径不能使用内部路径

表的属性个数异常

表的基础属性在8个左右

表属性个数Top20

诉求：限制表的属性个数

按时间维度规整表

1. 表的最后写入时间。（hdfs时间、最大分区的值等）
2. 表的最后读取时间。

诉求：识别出长时间不读取的表，（多长时间没有读取）进行删除流程。

附录 分析语句

表分布语句文章来源地址https://www.toymoban.com/news/detail-499007.html

-- 不同表类型数量
SELECT  TBL_TYPE
       ,COUNT(1)
FROM hive_meta.hive_meta_tbls
WHERE day = '20230618'
GROUP BY  TBL_TYPE


-- 内部表的分区表 99709
SELECT  COUNT(1)
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.TBL_TYPE = 'MANAGED_TABLE'
AND d.TBL_ID IN (
SELECT  A.TBL_ID
FROM hive_meta.hive_meta_PARTITION_KEYS A
WHERE a.day = '20230618' );

-- 内部表的非分区表 
SELECT  COUNT(1)
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.TBL_TYPE = 'MANAGED_TABLE'
AND d.TBL_ID NOT IN (
SELECT  A.TBL_ID
FROM hive_meta.hive_meta_PARTITION_KEYS A
WHERE a.day = '20230618' );

-- 外部表的为分区表 7842
SELECT  COUNT(1)
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.TBL_TYPE = 'EXTERNAL_TABLE'
AND d.TBL_ID IN (
SELECT  A.TBL_ID
FROM hive_meta.hive_meta_PARTITION_KEYS A
WHERE a.day = '20230618' );

-- 外部表的为非分区表 719
SELECT  COUNT(1)
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.TBL_TYPE = 'EXTERNAL_TABLE'
AND d.TBL_ID NOT IN (
SELECT  A.TBL_ID
FROM hive_meta.hive_meta_PARTITION_KEYS A
WHERE a.day = '20230618' );

识别分区数

-- 分区数超出某个阈值的表信息
SELECT  d.TBL_NAME
       ,c.TBL_ID
       ,d.OWNER
       ,c.PART_SUM
FROM
(
	SELECT  b.TBL_ID
	       ,COUNT(*) AS PART_SUM
	FROM hive_meta.hive_meta_partitions b
	WHERE b.day = '20230618'
	GROUP BY  b.TBL_ID
) c
LEFT JOIN hive_meta.hive_meta_tbls d
ON c.TBL_ID = d.TBL_ID	
WHERE c.PART_SUM > 10000
AND d.day = '20230618';

-- 某个分区数量区间内的表数量
SELECT  count(1)
FROM
(
	SELECT  b.TBL_ID
	       ,COUNT(*) AS PART_SUM
	FROM hive_meta.hive_meta_partitions b
	WHERE b.day = '20230618'
	GROUP BY  b.TBL_ID
) c
LEFT JOIN hive_meta.hive_meta_tbls d
ON c.TBL_ID = d.TBL_ID	
WHERE c.PART_SUM > 180 and c.PART_SUM <=365
AND d.day = '20230618';

识别分区层级

-- 分区层级超出某个阈值的表信息
SELECT  b.TBL_NAME
       ,b.TBL_ID
       ,b.OWNER
       ,c.PART_LEVEL
FROM
(
	SELECT  A.TBL_ID
	       ,COUNT(A.PKEY_NAME) AS PART_LEVEL
	FROM hive_meta.hive_meta_PARTITION_KEYS A
	WHERE a.day = '20230618'
	GROUP BY  A.TBL_ID
) c
LEFT JOIN hive_meta.hive_meta_TBLS B
ON c.TBL_ID = B.TBL_ID
WHERE b.day = '20230618'
and c.PART_LEVEL>2

-- 某个分区层级内的表数
SELECT  count(1)
FROM
(
	SELECT  A.TBL_ID
	       ,COUNT(A.PKEY_NAME) AS PART_LEVEL
	FROM hive_meta.hive_meta_PARTITION_KEYS A
	WHERE a.day = '20230618'
	GROUP BY  A.TBL_ID
) c
LEFT JOIN hive_meta.hive_meta_TBLS B
ON c.TBL_ID = B.TBL_ID
WHERE b.day = '20230618'
and c.PART_LEVEL=2

-- 某个分区层级内的分区数
SELECT  COUNT(1)
FROM hive_meta.hive_meta_PARTITIONS b
WHERE b.day = '20230618'
AND b.TBL_ID IN (
SELECT  TBL_ID
FROM
(
	SELECT  A.TBL_ID
	       ,COUNT(A.PKEY_NAME) AS PART_LEVEL
	FROM hive_meta.hive_meta_PARTITION_KEYS A
	WHERE a.day = '20230618'
	GROUP BY  A.TBL_ID
) c
WHERE c.PART_LEVEL >2 )

存储相关

-- output_FORMAT分布情况
SELECT  output_FORMAT分布情况,count(1)
FROM hive_meta.hive_meta_sds
WHERE day = '20230618' group by output_FORMAT

-- input_FORMAT分布情况
SELECT   input_FORMAT,count(1)
FROM hive_meta.hive_meta_sds
WHERE day = '20230618' group by input_FORMAT

-- 分区的格式为空的内部表
SELECT  d.*
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618' and 
AND d.TBL_ID IN (
SELECT  b.TBL_ID
FROM hive_meta.hive_meta_partitions b
WHERE b.day = '20230618'
AND b.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds AS a 
WHERE a.day = '20230618'
AND (a.output_FORMAT = '' or a.INPUT_FORMAT = '')) );

-- 格式为空的非分区的内部表
SELECT  b.*
FROM hive_meta.hive_meta_tbls b
WHERE b.day = '20230618'
AND b.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds AS a
WHERE a.day = '20230618'
AND (a.output_FORMAT = '' or a.INPUT_FORMAT = ''));

编解码信息

• 编解码表中存在不指向信息存储表的数据

SELECT  count(1)
FROM hive_meta.hive_meta_serdes a
WHERE serde_id not IN ( SELECT serde_id FROM hive_meta.hive_meta_sds b WHERE b.day = '20230618')
and a.day = '20230618'

存储路径

• 表或分区记录对应的location为空

SELECT  count(1)
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618' AND ( location is null or location = '' )

-- 查询没有存储路径的分区表
SELECT  d.*
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.TBL_ID IN (
SELECT  b.TBL_ID
FROM hive_meta.hive_meta_partitions b
WHERE b.day = '20230618'
AND b.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618' AND ( location is null or location = '' )));

-- 查询没有存储路径的表--- 结果可能是视图

SELECT  d.*
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618' and d.TBL_TYPE in ('MANAGED_TABLE','EXTERNAL_TABLE')
AND d.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618' AND ( location is null or location = '' ));

-- 不属于上述的2与3  '495136682','495137661','872437188'
SELECT  a.*
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618'
AND ( location is null or location = '' )
AND sd_id not IN (
SELECT  d.sd_id
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618'
AND ( location is null or location = '' )));


SELECT  d.*
FROM hive_meta.hive_meta_tbls d
WHERE d.day = '20230618'
AND d.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618' AND  a.sd_id in ('495136682','495137661','872437188'));

SELECT  d.*
FROM hive_meta.hive_meta_partitions d
WHERE d.day = '20230618'
AND d.sd_id IN (
SELECT  a.sd_id
FROM hive_meta.hive_meta_sds a
WHERE a.day = '20230618' AND  a.sd_id in ('495136682','495137661','872437188'));

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

SELECT  TBL_TYPE
       ,COUNT(1)
FROM hive_meta.hive_meta_TBLs a
WHERE a.day = '20230618'
GROUP BY  TBL_TYPE;

-- MANAGED_TABLE 322264
-- INDEX_TABLE 3
-- EXTERNAL_TABLE 8561
-- VIRTUAL_VIEW 446

-- 不易表路径为前缀的分区情况
SELECT  *
FROM
(
	SELECT  m.*
	       ,n.TBL_NAME
	       ,n.location AS tablelocation
           ,n.owner
	FROM
	(
		SELECT  a.TBL_ID
		       ,a.PART_ID
		       ,b.location
		       ,a.sd_id
		FROM hive_meta.hive_meta_partitions a, hive_meta.hive_meta_sds b
		WHERE a.sd_id = b.sd_id
		AND a.day = '20230618'
		AND b.day = '20230618' 
	) m
	LEFT JOIN
	(
		SELECT  c.TBL_ID
		       ,c.TBL_NAME
		       ,c.sd_id
               ,c.owner
		       ,d.location
		FROM hive_meta.hive_meta_TBLS c, hive_meta.hive_meta_sds d
		WHERE c.sd_id = d.sd_id
		AND d.day = '20230618'
		AND c.day = '20230618'  and c.TBL_TYPE='MANAGED_TABLE'
	) n
	ON m.TBL_ID = n.TBL_ID
) t
WHERE t.location not like concat(t.tablelocation, '%');
-- all 807946 
-- MANAGED_TABLE 268651

• 内部表使用非内部表路径。

SELECT  c.TBL_ID
       ,c.TBL_NAME
       ,c.sd_id
       ,d.location
       ,c.owner
FROM hive_meta.hive_meta_TBLS c, hive_meta.hive_meta_sds d
WHERE c.sd_id = d.sd_id
AND d.day = '20230618'
AND c.day = '20230618'
AND c.TBL_TYPE = 'MANAGED_TABLE'
and d.location not like concat('%','/hive/warehouse/', '%');
-- 898条

• 外部表使用内部表路径

SELECT  c.TBL_ID
       ,c.TBL_NAME
       ,c.sd_id
       ,d.location
       ,c.TBL_TYPE
       ,c.owner
FROM hive_meta.hive_meta_TBLS c, hive_meta.hive_meta_sds d
WHERE c.sd_id = d.sd_id
AND d.day = '20230618'
AND c.day = '20230618'
AND c.TBL_TYPE != 'MANAGED_TABLE'
AND d.location like concat('%', '/hive/warehouse/', '%');
-- 6640条

表属性

SELECT  e.TBL_ID
       ,e.OWNER
       ,e.TBL_NAME
       ,d.param_count
       ,e.TBL_TYPE
FROM
(
	SELECT  c.TBL_ID
	       ,COUNT(*) AS param_count
	FROM
	(
		SELECT  a.TBL_ID
		       ,b.PARAM_KEY
		FROM hive_meta.hive_meta_tbls a
		LEFT JOIN hive_meta.hive_meta_table_params b
		ON a.TBL_ID = b.TBL_ID
		WHERE a.day = '20230618'
		AND b.day = '20230618' 
	) c
	GROUP BY  c.TBL_ID
) d, hive_meta.hive_meta_tbls e
WHERE e.day = '20230618' and e.TBL_ID=d.TBL_ID
AND d.param_count > 100 

分区属性

-- 表下的分区
SELECT  e.TBL_ID
       ,e.OWNER
       ,e.TBL_NAME
       ,d.TART_NAME
       ,e.TBL_TYPE
       ,c.PART_ID
       ,c.PART_param_count
FROM
(
	SELECT  a.PART_ID
	       ,COUNT(*) AS PART_param_count
	FROM hive_meta.hive_meta_partitions a
	LEFT JOIN hive_meta.hive_meta_partition_params b
	ON a.PART_ID = b.PART_ID
	WHERE a.day = '20230505'
	AND b.day = '20230505'
	GROUP BY  a.PART_ID
) c, hive_meta.hive_meta_partitions d, hive_meta.hive_meta_tbls e
WHERE d.day = '20230618'
AND e.day = '20230618'
AND c.PART_ID = d.PART_ID
AND d.TBL_ID = e.TBL_ID
-- 分区
SELECT  COUNT(1)
FROM
(
	SELECT  a.PART_ID
	       ,COUNT(*) AS PART_PAR_SUM
	FROM hive_meta.hive_meta_partitions a
	LEFT JOIN hive_meta.hive_meta_partition_params b
	ON a.PART_ID = b.PART_ID
	WHERE a.day = '20230618'
	AND b.day = '20230618'
	GROUP BY  a.PART_ID
) c
WHERE PART_PAR_SUM > 0
AND PART_PAR_SUM <= 10;

附录 hive元数据数仓表

hive元数据数仓表hive元数据存储库对应表

到了这里，关于【Hive实战】Hive治理方向探讨（请留意见）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！