查询所有HIVE表分区数据量

概述

• 查询HIVE表分区的数据占用和行数，用于数据治理
• 通常，每天一个分区；
  通常，今天查看昨天分区数据量
  本文日期分区字段为ymd

创建分区表

-- 创建多级分区表
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 (f1 STRING,f2 INT)
PARTITIONED BY (ymd STRING COMMENT '年月日',h INT COMMENT '时');
-- 插入数据
INSERT INTO TABLE t3 PARTITION(ymd='2022-08-08',h=8)
VALUES ('ef',9),('hh',13);
1
2
3
4
5
6
7

查看单个HIVE表分区的行数

SELECT COUNT(1) FROM 表 WHERE 分区="分区值";
1

查看单个HIVE表分区的行数和数据占用

DESC FORMATTED 表 PARTITION(分区="分区值");
1

批量查询HIVE表分区的行数和数据占用

HIVE元数据存储在MySQL，模型如下：

E-R图

批量查询HIVE表分区的行数和数据占用的SQL

SELECT
  table_name
  ,PART_NAME AS partition_name
  ,create_time
  ,num_files
  ,num_rows
  ,raw_data_size
  ,total_size
  -- ,num_files_erasure_coded
  ,transient_last_ddl_time
  ,column_stats_accurate
FROM ( -- 昨天分区
  SELECT PART_NAME,PART_ID,TBL_ID,FROM_UNIXTIME(CREATE_TIME,"%Y-%m-%d %h:%i:%s") AS create_time FROM PARTITIONS
  WHERE PART_NAME=CONCAT("ymd=",DATE_FORMAT(DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY),"%Y-%m-%d"))
)t1
INNER JOIN ( -- 库名.表名（筛选外部表）
  SELECT CONCAT(DBS.NAME,TBLS.TBL_NAME) AS table_name,TBL_ID
  FROM DBS INNER JOIN TBLS ON DBS.DB_ID=TBLS.DB_ID
  WHERE TBLS.TBL_TYPE="EXTERNAL_TABLE"
  -- OR TBLS.TBL_TYPE="MANAGED_TABLE"
)t0 ON t1.TBL_ID=t0.TBL_ID
INNER JOIN (
  SELECT
    PART_ID
    ,MAX(IF(PARAM_KEY="numFiles",PARAM_VALUE+0,NULL)) AS num_files
    ,MAX(IF(PARAM_KEY="numRows",PARAM_VALUE+0,NULL)) AS num_rows
    ,MAX(IF(PARAM_KEY="rawDataSize",PARAM_VALUE+0,NULL)) AS raw_data_size
    ,MAX(IF(PARAM_KEY="totalSize",PARAM_VALUE+0,NULL)) AS total_size
    ,MAX(IF(PARAM_KEY="numFilesErasureCoded",PARAM_VALUE+0,NULL)) AS num_files_erasure_coded
    ,MAX(IF(PARAM_KEY="transient_lastDdlTime",FROM_UNIXTIME(PARAM_VALUE,"%Y-%m-%d %h:%i:%s"),NULL)) AS transient_last_ddl_time
    ,MAX(IF(PARAM_KEY="COLUMN_STATS_ACCURATE",PARAM_VALUE,NULL)) AS column_stats_accurate
  FROM PARTITION_PARAMS
  GROUP BY PART_ID
  -- HAVING column_stats_accurate IS NOT NULL
)t2 ON t1.PART_ID=t2.PART_ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

补充

• HIVE的LODA DATA和Sqoop的hive-import写进分区，是冇计算行数的
• 建议：HIVELODA DATA或Sqoophive-import到中间表，再从中间表SELECT INSERT到ODS层
  两个好处：1、计算行数；2、合并小文件

---

车手 - Cyndi Wang