查询所有HIVE表分区数据量

文章目录

概述

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

创建分区表

-- 创建多级分区表DROPTABLEIFEXISTS t3;CREATETABLE t3 (f1 STRING,f2 INT)
PARTITIONED BY(ymd STRING COMMENT'年月日',h INTCOMMENT'时');-- 插入数据INSERTINTOTABLE t3 PARTITION(ymd='2022-08-08',h=8)VALUES('ef',9),('hh',13);

查看单个HIVE表分区的行数

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

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

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

分区属性说明备注所在MySQL表PART_ID分区唯一标识PARTITIONSPART_NAME分区名称例如

ymd=2022-08-08

PARTITIONSCREATE_TIME分区创建时间≤

transient_lastDdlTime

PARTITIONSnumFiles文件数PARTITION_PARAMSnumRows行数

LOAD DATE

写分区时，该值为

0

PARTITION_PARAMSrawDataSize原始数据大小

LOAD DATE

写分区时，该值为

0

PARTITION_PARAMStotalSize数据在HDFS的大小(不含副本)PARTITION_PARAMSnumFilesErasureCoded通常是

0

PARTITION_PARAMStransient_lastDdlTime最近1次DDL时间≥分区创建时间PARTITION_PARAMSCOLUMN_STATS_ACCURATE

LOAD DATE

写分区，该值为

NULL

INSERT

写分区，该值为

{"BASIC_STATS":"true"}

PARTITION_PARAMS

批量查询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(),INTERVAL1DAY),"%Y-%m-%d")))t1
INNERJOIN(-- 库名.表名（筛选外部表）SELECT CONCAT(DBS.NAME,TBLS.TBL_NAME)AS table_name,TBL_ID
  FROM DBS INNERJOIN 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
INNERJOIN(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
  GROUPBY PART_ID
  -- HAVING column_stats_accurate IS NOT NULL)t2 ON t1.PART_ID=t2.PART_ID;

table_namepartition_namecreate_timenum_filesnum_rowsraw_data_sizetotal_sizetransient_last_ddl_timecolumn_stats_accuratedefault.t3ymd=2022-08-08/h=82022/8/29 10:44129112022/8/29 10:44{“BASIC_STATS”:“true”}

补充

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

车手 - Cyndi Wang