【备忘】统计hive事务表数量

因为一些原因，要清点一下线上hive事务表有多少
几千个表一个个去看得累死人
灵机一动可以通过元数据来查嘛

这里记录一下：

#事务表的库名、表名、创建时间
SELECT d.`NAME` db_name,t.TBL_NAME,FROM_UNIXTIME(t.CREATE_TIME) create_time
 FROM `TABLE_PARAMS` tp
INNER JOIN TBLS t on tp.TBL_ID=t.TBL_ID
INNER JOIN DBS d on t.DB_ID=d.DB_ID
WHERE PARAM_KEY='transactional' and PARAM_VALUE='true'

#库名、事务表数量
SELECT d.`NAME` db_name,count(1) FROM `TABLE_PARAMS` tp
INNER JOIN TBLS t on  tp.TBL_ID=t.TBL_ID
INNER JOIN DBS d on t.DB_ID=d.DB_ID
 WHERE PARAM_KEY='transactional' and PARAM_VALUE='true'
 GROUP BY db_name

#事务表的库名、表名、创建时间、ddl修改时间
select a.db_name,a.TBL_NAME,a.createTime,b.ddl_time
from
(
SELECT d.`NAME` db_name,t.TBL_ID,t.TBL_NAME,FROM_UNIXTIME(t.CREATE_TIME) createTime FROM `TABLE_PARAMS` tp
INNER JOIN TBLS t on tp.TBL_ID=t.TBL_ID
INNER JOIN DBS d on t.DB_ID=d.DB_ID
WHERE PARAM_KEY='transactional' and PARAM_VALUE='true'
) a
INNER JOIN
(
SELECT TBL_ID,FROM_UNIXTIME(PARAM_VALUE) ddl_time FROM `TABLE_PARAMS` tp
WHERE PARAM_KEY='transient_lastDdlTime'
) b
on a.TBL_ID=b.TBL_ID