监控hive db下面的表是不是每天都在更新

背景：
公司有很多打点数据，有时候并不知道哪些表停止更新了，这时候如果我们的脚本每天还在更新数据的话，就很浪费资源。我们是不是可以监控一下某个库下面表的数据量或者最新更新时间。如果没量或者更新时间还是很久之前的话，那我们关于这个表的更新脚本就可以停了，节省集群资源。

1.首先将某个库下面的表全部load出来

${hive} -e "use ${hive_db};show tables;" > ${local_dir}/tables.csv
tables_name=`awk '{print $1}'  ${local_dir}/tables.csv`
arr_tables_name=(${tables_name})

2.循环判断这个库下面表的数据量，这个时候就会有一个问题，这个库下面并不一定都是分区表，如果不是分区表我们就不能按照分区表的方式来统计
那我们怎么判断呢？
这时候有一个分关键的命令：

desc formatted ${hive_db}.${table_name};

我们来看看会展示哪些信息：

hive> desc formatted ods_center.adplatform_xm_ceshi;
OK
# col_name            	data_type           	comment             
	 	 
hisid               	string              	                    
userid              	string              	                    
username            	string              	                    
companyname         	string              	                    
	 	 
# Detailed Table Information	 	 
Database:           	ods_center          	 
Owner:              	hadoop              	 
CreateTime:         	Wed Dec 11 14:47:58 CST 2019	 
LastAccessTime:     	UNKNOWN             	 
Retention:          	0                   	 
Location:           	hdfs://HDFS3/usr/hive/warehouse/ods_center.db/adplatform_xm_ceshi	 
Table Type:         	EXTERNAL_TABLE      	 
Table Parameters:	 	 
	EXTERNAL            	TRUE                
	numFiles            	1                   
	numRows             	0                   
	rawDataSize         	0                   
	totalSize           	185676              
	transient_lastDdlTime	1638258908          
	 	 
# Storage Information	 	 
SerDe Library:      	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	 
InputFormat:        	org.apache.hadoop.mapred.TextInputFormat	 
OutputFormat:       	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	 
Compressed:         	No                  	 
Num Buckets:        	-1                  	 
Bucket Columns:     	[]                  	 
Sort Columns:       	[]                  	 
Storage Desc Params:	 	 
	field.delim         	\t                  
	serialization.format	\t          

其中有一条信息：transient_lastDdlTime 1638258908最后一次更新时间。有没有注意到这个表没有分区有关的信息，那么这个表就不是一个分区表。我们可以通过这个判断是不是分区表。

#判断是不是分区表
partition_data=`${hive} -e "desc formatted ${hive_db}.${table_name};" | grep Partition | wc -l`
#判断非分区表的最后一次更新时间
seconds_new=`${hive} -e "desc formatted ${hive_db}.${table_name};" | grep transient_lastDdlTime |awk -F '\t' '{print $3}'`
#判断分区表的最后一次更新时间，也可以根据上面的命令来获取
max_dt=`${spark_sql} --conf spark.port.maxRetries=100  -e "show partitions ${hive_db}.${table_name};" | tail -1 | cut -d '=' -f2`

3.统计完每个表的数据量、最后一次更新时间，最后将数据插入到一张表，进行后期的分析。数据质量检测等等一系列的分析。

最后的脚本在这：

#!/bin/bash

spark_sql="/usr/local/service/spark/bin/spark-sql"
hive="/usr/local/service/hive/bin/hive"

vardate=`date -d '1 day ago' +%Y%m%d`
hive_db="ods_center"
local_dir="/home/hadoop/cp/test"


${hive} -e "use ${hive_db};show tables;" > ${local_dir}/tables.csv
tables_name=`awk '{print $1}'  ${local_dir}/tables.csv`
arr_tables_name=(${tables_name})

num=`cat ${local_dir}/tables.csv | wc -l`

for((i=0;i<num;i++));
do 
  table_name=${arr_tables_name[i]}
  partition_data=`${hive} -e "desc formatted ${hive_db}.${table_name};" | grep Partition | wc -l`
  echo $i
  echo ${table_name}

#partition_data是否有分区信息，等于0为不是分区表，否则是分区表
if [[ partition_data -eq 0 ]];then
  counts=`${spark_sql} --conf spark.port.maxRetries=100  -e "select count(1) from ${hive_db}.${table_name}"`
  #不是分区表，查看最后的更新时间
  seconds_new=`${hive} -e "desc formatted ${hive_db}.${table_name};" | grep transient_lastDdlTime |awk -F '\t' '{print $3}'`
  max_dt=`date -d @$seconds_new "+%Y%m%d"`
  is_partition=0
else 
  counts=`${spark_sql} --conf spark.port.maxRetries=100  -e "select count(1) from ${hive_db}.${table_name} where dt='${vardate}'"`
  #分区表查看最后一个分区，不能用昨日分区作为最大分区，有些表可能某一天就没数据了，那么就要根据最后一个分区作为最大分区
  max_dt=`${spark_sql} --conf spark.port.maxRetries=100  -e "show partitions ${hive_db}.${table_name};" | tail -1 | cut -d '=' -f2`
  is_partition=1
fi

#第一条记录插入
if [[ i -eq 0 ]];then
  echo "set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrick;
insert overwrite table dw_center.monitor_table_data partition(dt) select '${hive_db}' as hive_db,'${table_name}' as hive_table,${is_partition} as is_partition,${counts} as hive_table_num,'${max_dt}' as hive_table_max_dt,${vardate} as dt" > ${local_dir}/query.sql
#最后一条记录加一个分号
elif [[ i -eq num-1 ]];then
  echo "union all select '${hive_db}' as hive_db,'${table_name}' as hive_table,${is_partition} as is_partition,${counts} as hive_table_num,'${max_dt}' as hive_table_max_dt,${vardate} as dt;" >> ${local_dir}/query.sql
else 
  echo "union all select '${hive_db}' as hive_db,'${table_name}' as hive_table,${is_partition} as is_partition,${counts} as hive_table_num,'${max_dt}' as hive_table_max_dt,${vardate} as dt" >> ${local_dir}/query.sql
fi
done

#插入表监控结果数据
${spark_sql} --conf spark.port.maxRetries=100  -f ${local_dir}/query.sql

注：
1.统计表的数据量时候，可以用presto，spark-sql启动太耗时
2.统计的表结果数据，尽量用insert into，太多union all会报错，也可以一条条加载到文件中，再load到表里面进行映射

4.简单的邮件如下：

可以进行昨日比、7日比等等来查看我们的ods表是否数据异常等信息。