通过hive元数据查询hive指定库和表的总条数

一、整库下，总条数

1、指定库的表总数

查看ods层的总表数

select count(TBL_NAME)  
from TBLS t   
left join DBS d  
on t.DB_ID = d.DB_ID  
where d.NAME like 'ods_%'  and t.TBL_NAME like "ods_%";

2、非分区表的总条数

查看ods库的总条数

select FORMAT(sum(tb.PARAM_VALUE),0)  
from TBLS t   
left join DBS d  
on t.DB_ID = d.DB_ID  
left join TABLE_PARAMS tb 
on t.TBL_ID = tb.TBL_ID   
where d.NAME like 'ods_%' and tb.PARAM_KEY='numRows' and t.TBL_NAME like "ods_%";  

3、查询含有分区表的总条数

查看ods库的总条数

select FORMAT(sum(a.PARAM_VALUE),0)  
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 d.NAME like 'ods_%' and a.PARAM_KEY='numRows' and t.TBL_NAME like "ods_%"  

4、查询某天含有分区表的总条数

查看ods库的总条数

select FORMAT(sum(a.PARAM_VALUE),0)  
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 d.NAME like 'ods_%' and a.PARAM_KEY='numRows' and t.TBL_NAME like "ods_%"  
and  date(from_unixtime(p.create_time)) =  '2023-01-20' ;