基於Hive進行數倉建設的資源元數據資訊統計：Hive篇

2021 年 4 月 6 日
筆記
hive, 元數據, 大數據, 數據倉庫

在數據倉庫建設中，元數據管理是非常重要的環節之一。根據Kimball的數據倉庫理論，可以將元數據分為這三類：

技術元數據，如表的存儲結構結構、文件的路徑
業務元數據，如血緣關係、業務的歸屬
過程元數據，如表每天的行數、佔用HDFS空間、更新時間

而基於這3類元數據”搭建”起來的元數據系統，通常又會實現如下核心功能：

1. 血緣關係

如表級別/欄位級別的血緣關係，這些主要體現在我們日常的SQL和ETL任務里。

2. 大數據集群計算資源管理

針對利用不同的計算引擎如Spark/Flink/Mapreduce，可以到Yarn（也可能是其他資源管理器）上採集相關任務的使用情況。如CPU、記憶體、磁碟IO使用情況。
然後可以把這些資源使用情況繪製成圖。通過可視化介面可以直觀發現某些任務中的異常情況，以及發現某些嚴重消耗資源的表或業務，及時通知相關負責人有針對性的分析處理和優化。

3. 數據如何同步以及許可權管理等

4. Hive庫表元數據資訊統計

這裡對Hive庫表統計資訊主要是指：行數、文件數、所佔HDFS存儲大小、最後一次操作時間等。

通過持續不斷的採集這些指標，形成可視化曲線圖，數據倉庫相關人員都可以從這個圖中發現數據規律或數據品質問題。對於利用數倉進行業務開發的人員，可以通過這些曲線圖來分析業務量變化趨勢。在此基礎之上，還可以做數據品質校驗、數值分布探查等功能。

本文主要介紹如何利用Hive進行對Hive庫、分區表/非分區表相關指標的統計。

而在我們實際生產中，我們不僅可以通過如下的方式及時更新和獲取Hive元資料庫中相關表記錄的指標資訊，我們也可以參考下述相關SQL在Hive/Spark底層的執行過程，實現我們自己的一整套業務邏輯。

1. Hive元資料庫中主要涉及的元數據表

DBS：存儲Hive中所有資料庫的基本資訊，如庫ID、表ID、創建時間、用戶、表名、表的類型等。
TBS：存儲Hive表、視圖等的基本資訊，如表ID、表名、創建時間、用戶、表類型等。
TABLE_PARAMS：存儲表等的屬性資訊，表ID、PARAM_KEY（如EXTERNAL）、PARAM_VALUE（與PARAM_KEY對應的值）。
PARTITIONS：存儲Hive分區統計資訊相關的元數據，如分區ID、表ID、創建時間、分區名（partCol=partVal）等資訊。
PARTITION_PARAMS：存儲Hive分區統計資訊相關的元數據，如分區ID、PARAM_KEY（如文件數）、PARAM_VALUE（與
PARAM_KEY對應的值）。

2. Hive和Spark支援的Hive庫表元數據資訊統計

2.1 Hive

2.1.1 語法支援

默認情況下，在對Hive表進行數據insert時，會自動更新元資料庫表中的統計資訊，但主要是文件數、佔用HDFS空間大小等，不包括行數。

1）分區表

Hive分區表元數據統計資訊SQL語法需要指定到具體分區，如分區欄位或者分區名=分區值

-- 1. 統計更新tab_partition的分區欄位為dt的所有元數據資訊
analyze table tab_partition partition(dt) COMPUTE STATISTICS;

-- 2. 統計更新單個分區元數據統計資訊
analyze table tab_partition partition(dt='20200722000000') COMPUTE STATISTICS;

在Hive shell中執行analyze時，如果進行了元數據資訊統計會列印類似如下資訊：

Partition default.test_partition2{dt=20200718000000} stats: [numFiles=1, numRows=2, totalSize=418, rawDataSize=6]

2）非分區表

-- 非分區表粒度到表
analyze table tab_no_partition COMPUTE STATISTICS;

2.1.2 Hive元資料庫中涉及的元數據統計資訊欄位

1）Hive分區表

-- 表級別：TABLE_PARAMS
-- Hive分區級別：PARTITION_PARAMS

numFiles：文件數
numRows：行數
totalSize：佔用HDFS空間大小
rawDataSize：原生數據大小
transient_lastDdlTime：最近一次操作時間

2）Hive非分區表

對於Hive分區表，因為最小粒度是表級別。因此，元數據統計資訊也是表級別的。

-- TABLE_PARAMS
numFiles、numRows、totalSize、rawDataSize、transient_lastDdlTime：含義同上

3. Hive

默認情況下，在對Hive表進行數據insert時，Hive會自動更新元數據統計資訊，但是不統計行數。如需獲取numRow，可以再次執行analyze SQL

1）直接通過Hive進行表的創建

以分區表testdb.test_analyze為例，表剛創建時Hive元資料庫中表TABLE_PARAMS的資訊：

+------+---------------------+-----------+
|TBL_ID|           PARAM_KEY |PARAM_VALUE|
+------+---------------------+-----------+
|  3016|            EXTERNAL |       TRUE|
|  3016|transient_lastDdlTime| 1595405772|
+------+---------------------+-----------+

2）對錶testdb.test_analyze進行數據的保存和元數據資訊統計：

insert overwrite table testdb.test_analyze partition(partCol=20200721000000) select id,name from testdb.test_partition1 where partCol=20190626000000;

analyze table testdb.test_analyze partition(partCol='20200721000000') COMPUTE STATISTICS;

3）連接Hive元資料庫，查詢testdb.test_analyze的元數據統計資訊

-- 1. 連接Hive元資料庫
connect jdbc where
url="jdbc:mysql://localhost:3306/hive?useUnicode=true&amp;characterEncoding=UTF-8"
and driver="com.mysql.jdbc.Driver"
and user="root"
and password="root"
as db_1;

-- 2. 將TABLE_PARAMS、DBS、TBLS、PARTITIONS、PARTITION_PARAMS註冊為臨時表

-- load jdbc.`db_1.TABLE_PARAMS` as TABLE_PARAMS ;
load jdbc.`db_1.DBS` as dbs;
load jdbc.`db_1.TBLS` as tbls;
load jdbc.`db_1.PARTITIONS` as partitions;
load jdbc.`db_1.PARTITION_PARAMS` as partition_params;

-- 3. 獲取testdb.test_analyze的元數據統計資訊
select d.NAME,t.TBL_NAME,t.TBL_ID,p.PART_ID,p.PART_NAME,a.*   
from tbls t   
left join dbs d  
on t.DB_ID = d.DB_ID  
left join partitions p  
on t.TBL_ID = p.TBL_ID   
left join partition_params a  
on p.PART_ID=a.PART_ID
where t.TBL_NAME='test_analyze' and d.NAME='testdb';

4）結果

-- 測試時，testdb.test_analyze只有partCol=20200721000000的分區。因此，統計資訊也只有partCol=20200721000000的

+------+------------+------+-------+----------------------+-------+--------------------+--------------------+
|  NAME|    TBL_NAME|TBL_ID|PART_ID|             PART_NAME|PART_ID|           PARAM_KEY|         PARAM_VALUE|
+------+------------+------+-------+----------------------+-------+--------------------+--------------------+
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|COLUMN_STATS_ACCU...|{"BASIC_STATS":"t...|
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|            numFiles|                   1|
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|             numRows|                   1|
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|         rawDataSize|                   3|
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|           totalSize|                 383|
|testdb|test_analyze|  3016|  52976|partCol=20200721000000|  52976|transient_lastDdl...|          1595407507|
+------+------------+------+-------+----------------------+-------+--------------------+--------------------+

下篇文章將介紹如何利用Spark進行Hive庫表元數據資訊統計，以及二者的區別。關注微信公眾號：大數據學習與分享，搶先看技術乾貨