基於Hive進行數倉建設的資源後設資料資訊統計：Spark篇

大資料學習與分享發表於2021-04-08
HiveSpark

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

  1. 技術後設資料，如表的儲存結構結構、檔案的路徑
  2. 業務後設資料，如血緣關係、業務的歸屬
  3. 過程後設資料，如表每天的行數、佔用HDFS空間、更新時間

而基於這3類後設資料"搭建"起來的後設資料系統，通常又會實現如下核心功能：

1. 血緣關係

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

2. 大資料叢集計算資源管理

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

3. 資料如何同步以及許可權管理等

4. Hive庫表後設資料資訊統計

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

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

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

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

注意：Spark預設不統計檔案數

語法支援

1）分割槽表

Spark對Hive分割槽表後設資料統計，跟Hive原生對分割槽表的統計支援略有不同。

Spark既支援具體到分割槽的後設資料資訊統計，也支援整個表級別的後設資料資訊統計（但不會對具體分割槽做處理）

-- 統計tab_partition資料所佔HDFS空間總大小和總行數。
-- Hive目前不支援直接這樣解析分割槽表
-- 注意：執行該SQL不會處理表中具體分割槽統計資訊
analyze table tab_partition COMPUTE STATISTICS;

-- 同Hive
analyze table tab_partition partition(partCol) COMPUTE STATISTICS;

-- 同Hive
analyze table tab_partition partition(partCol='20200722000000') COMPUTE STATISTICS;

2）非分割槽表

analyze table tab_no_partition COMPUTE STATISTICS;

下面看具體示例：

1）通過Spark建立Hive表

以分割槽表testdb.test_analyze_spark為例，表剛建立時Hive後設資料庫中表TABLE_PARAMS的資訊：

+------+------------------------------------+--------------------+
|TBL_ID|                           PARAM_KEY|         PARAM_VALUE|
+------+------------------------------------+--------------------+
|  3018|                            EXTERNAL|                TRUE|
|  3018|            spark.sql.create.version|               2.4.3|
|  3018|spark.sql.sources.schema.numPartCols|                   1|
|  3018|   spark.sql.sources.schema.numParts|                   1|
|  3018|     spark.sql.sources.schema.part.0|{"type":"struct",...|
|  3018|  spark.sql.sources.schema.partCol.0|                  dt|
|  3018|               transient_lastDdlTime|          1595409374|
+------+------------------------------------+--------------------+

2）對錶testdb.test_analyze進行資料的儲存和後設資料資訊統計

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

執行上述SQL後，Hive內部會啟動一個任務進行Hive表操作的分割槽後設資料資訊統計，但是沒有numRows。如下：

+------+------------------+------+-------+----------------------+-------+--------------------+-----------+
|  NAME|          TBL_NAME|TBL_ID|PART_ID|             PART_NAME|PART_ID|           PARAM_KEY|PARAM_VALUE|
+------+------------------+------+-------+----------------------+-------+--------------------+-----------+
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|            numFiles|          1|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|           totalSize|        389|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|transient_lastDdl...| 1595409909|
+------+------------------+------+-------+----------------------+-------+--------------------+-----------+

3）連線Hive後設資料庫，查詢testdb.test_analyze_spark的後設資料統計資訊

connect jdbc where
url="jdbc:mysql://localhost:3306/hive?useUnicode=true&characterEncoding=UTF-8"
 and driver="com.mysql.jdbc.Driver"
 and user="root"
 and password="root"
 as db_1;

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

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_spark' and d.NAME='testdb' ;

4）結果

-- Spark在執行analyze table mlsql_test.test_analyze_spark partition(dt='20200721000000') COMPUTE STATISTICS; 時，會對分割槽行數進行統計：
+------+------------------+------+-------+----------------------+-------+-------------------------------+-----------+
|  NAME|          TBL_NAME|TBL_ID|PART_ID|             PART_NAME|PART_ID|                      PARAM_KEY|PARAM_VALUE|
+------+------------------+------+-------+----------------------+-------+-------------------------------+-----------+
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|                       numFiles|          1|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|   spark.sql.statistics.numRows|          1|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977| spark.sql.statistics.totalSize|        389|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|                      totalSize|        389|
|testdb|test_analyze_spark|  3018|  52977|partCol=20200721000000|  52977|          transient_lastDdlTime| 1595410238|
+------+------------------+------+-------+----------------------+-------+-------------------------------+-----------+

5）通過Spark對整個Hive分割槽表後設資料資訊的統計

-- 1. 執行：analyze table testdb.test_analyze_spark COMPUTE STATISTICS;
-- 2. Hive後設資料庫中表TABLE_PARAMS的包含的testdb.test_analyze_spark資訊：

connect jdbc where
 url="jdbc:mysql://localhost:3306/hive?useUnicode=true&characterEncoding=UTF-8"
 and driver="com.mysql.jdbc.Driver"
 and user="root"
 and password="root"
 as db_1;

-- 獲取mlsql_test的DB_ID（49）
load jdbc.`db_1.DBS` as dbs;
select DB_ID from dbs where NAME='testdb' as db;

-- 獲取test_analyze_spark的TBL_ID（3018）
load jdbc.`db_1.TBLS` as tbls;
select TBL_ID from tbls where DB_ID=49 and TBL_NAME='test_analyze_spark' as t2;

-- 獲取testdb.test_analyze_spark表級別統計資訊
load jdbc.`db_1.TABLE_PARAMS` as TABLE_PARAMS ;
select * from TABLE_PARAMS where TBL_ID=3018 ;

-- 結果
+------+------------------------------------+--------------------+
|TBL_ID|                           PARAM_KEY|         PARAM_VALUE|
+------+------------------------------------+--------------------+
|  3018|                            EXTERNAL|                TRUE|
|  3018|            spark.sql.create.version|               2.4.3|
|  3018|spark.sql.sources.schema.numPartCols|                   1|
|  3018|   spark.sql.sources.schema.numParts|                   1|
|  3018|     spark.sql.sources.schema.part.0|{"type":"struct",...|
|  3018|  spark.sql.sourc
es.schema.partCol.0|                  partCol|
|  3018|        spark.sql.statistics.numRows|                   1|
|  3018|      spark.sql.statistics.totalSize|                 389|
|  3018|               transient_lastDdlTime|          1595410958|
+------+------------------------------------+--------------------+

Hive和Spark對Hive庫表後設資料資訊統計的主要區別

  1. 對Hive表後設資料資訊統計的SQL語法支援不同如Spark支援對Hive分割槽表進行表級別的統計，但Hive需要指定到具體分割槽
  2. 對Hive表後設資料資訊統計在Hive後設資料庫中的體現不同如同樣是行數，Hive用numRows，而Spark用spark.sql.statistics.numRows
  3. Spark預設不統計檔案數，但Hive統計

Hive和Spark對Hive庫表後設資料資訊統計的區別包括但不限於以上3種區別。具體的看之前公眾號：大資料學習與分享相關文章的介紹

 

推薦文章：

Hive實現自增序列及後設資料問題​mp.weixin.qq.com

經典的SparkSQL/Hive-SQL/MySQL面試-練習題

資料湖VS資料倉儲之爭？阿里提出湖倉一體架構

 

如何有效恢復誤刪的HDFS檔案​mp.weixin.qq.com

 

Hadoop支援的壓縮格式對比和應用場景以及Hadoop native庫

SparkSQL與Hive metastore Parquet轉換

 

Spark和Spring整合處理離線資料​mp.weixin.qq.com

相關文章