hive表最后访问时间(避开权限检查)

我们在使用大数据集群时，时间越长越发现有大量的冗余数据表，删除却发现所有人的使用情况不一样，删除的话可能会影响当前正在运行的业务

于是我尝试了下面几种方法

查看表描述

desc formatted table_name

查看表描述
在图上我们可以看到这里只能看到最后一次DDL时间（也就是增删改的时间），这样并不能拿到select的时间

HDFS时间

hadoop fs -ls /user/hive/warehouse/tmp.db/regexp_test/
hadoop fs -stat /user/hive/warehouse/tmp.db/regexp_test/

hdfs时间

这个方式同样不能拿到放问时间，只能拿到修改时间

最终在大佬的知道下，学到了一个新命令

use tmp;show table extended like regexp_test

表时间
此刻我们终于拿到了我们想要的访问时间lastAccessTime,但是有另外一个问题，权限问题，我们没有权限的表不能用hive/beeline 去查看,spark-sql 无法执行该命令

hive自带黑科技--hcat

hcat 是为了让没有hive账户的人去查看hive表信息的工具
下面提供一个批量获取数据库全部表访问时间的脚本

aa=`hcat -e "show databases;"`
array=$aa
for i in ${array[@]}
do 
    dd=`show tables in $i`
    arr=${dd}
    for j in ${arr[@]}
      echo ";use ${i};show table extended like ${j}"  >> xx.sql
    done
done

hcat -f xx.sql >> result_table_info.txt

集群表越多运行时间越长，可以选择部分数据库运行

执行完是一个类型map<key,value>的文件,可以取关键行信息来操作

#1.选取我们想要的行
cat result_table_info.txt|grep -E 'location|totalFileSize|lastAccessTime'|awk -F':' '{print $NF}' >> resut_user_info.txt
#2.把没个表的信息拼成一行，两种方法均可
cat resut_user_info.txt | sed 'N;N;s/\n/,/g'   #行数-1个N；
or
cat  awk 'ORS=NR%3?" ":"\n"{print }'|sed 's/ /,/g'   # 对3取余的3目运算

最后可以吧文件到入mysql，或hive 数据库，转化时间戳（毫秒级），可以将一年内无访问的表删除（备份表除外）