Query Hive Meta Store

Question

I want to know how to query the hive metastore database and grab a few key properties including:

• create date
• last update date
• last access date

I just learned a hive command show table extend like <tablename>

0   tableName:<tablename>
1   owner:<userid>
2   location:hdfs://NameService-705/user/hive/warehouse/<username>.db/<tablename>
3   inputformat:org.apache.hadoop.mapred.TextInputFormat
4   outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
5   columns:struct columns { i64 ingestts, i64 ingestdate .... map<string,string> params}
6   partitioned:false
7   partitionColumns:
8   totalNumberFiles:1
9   totalFileSize:0
10  maxFileSize:0
11  minFileSize:0
12  lastAccessTime:1453767099408   ->  Mon Jan 25 2016 17:11:39 GMT-0700 (MST)
13  lastUpdateTime:1432218969243   ->  Thu May 21 2015 08:36:09 GMT-0600 (MDT)

I know that field0, 1, 12 and 13 are exactly the information and I am wondering how could I query the hive meta store to get those information directly instead of run show table extend a thousand time. Something like:

select tableName, owner, lastAccessTime, lastUpdateTime from <metatable>

Thanks!

Answer 1

From Java code you can query the Hive Metastore directly - cf. JavaDoc

Or, if you like quick-and-dirty tricks, you can connect to the Metastore database (typically a MySQL instance) and run some SQL queries directly against it. Not much public documentation about the data model, unfortunately.

Answer 2

select TBL_NAME, OWNER, CREATE_TIME, LAST_ACCESS_TIM
from TBLS
limit 11;