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

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' ;