Querying Hive Metastore
Querying hive metastore tables can provide more in depth details on the tables sitting in Hive. This article is a collection of queries that probes Hive metastore configured with mysql to get details like list of transactional tables, etc. More queries can be build to lookup tables based on hdfs location or vice versa.
Just select hive database after connecting to Hive metastore in mysql and before running below queries.
Get list of transactional tables
select DBS.NAME `Database`, TBLS.TBL_NAME `Table`, TBLS.OWNER `Table Owner`, DBS.OWNER_NAME `Database Owner`, DBS.DB_LOCATION_URI `Database Location`, PARAM_KEY, PARAM_VALUE from TABLE_PARAMS join TBLS ON TBLS.TBL_ID = TABLE_PARAMS.TBL_ID join DBS on DBS.DB_ID = TBLS.DB_ID where param_key='transactional' ;