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

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

在資料倉儲建設中,後設資料管理是非常重要的環節之一。根據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

相關文章