Hive - How to see the table created in metastore?

Question

Here is our setup - We have Hive that uses MySQL on another machine as a metastore. I can start the Hive command line shell and create a table and describe it. But when I log on to the other machine where MySQL is used as metastore, I cannot see the Hive table details on the MySQL.

e.g. Here are hive commands -

hive> create table student(name STRING, id INT);
OK
Time taken: 7.464 seconds
hive> describe student;
OK
name    string
id      int
Time taken: 0.408 seconds
hive>

Next, I log on to the machine where MySQL is installed and this MySQL is used as Hive metastore. I use the "metastore" database. But if I want to list the tables, I cannot see the table or the table info I have created in Hive.

How can I see the Hive table information in the metastore?

Answer 1

First, find what MySql database the metastore is stored in. This is going to be in your hive-site.conf - connection URL. Then, once you connect to MySql you can

use metastore; 
show tables; 
select * from TBLS; <-- this will give you list of your hive tables

Answer 2

Another useful query if you want to search what other tables a particular column belongs to:

SELECT c.column_name, tbl_name, c.comment, c.type_name, c.integer_idx,
 tbl_id, create_time, owner, retention, t.sd_id, tbl_type, input_format, is_compressed, location,
 num_buckets, output_format, serde_id, s.cd_id
FROM TBLS t, SDS s, COLUMNS_V2 c
-- WHERE tbl_name = 'my_table'
WHERE t.SD_ID = s.SD_ID
AND s.cd_id = c.cd_id
AND c.column_name = 'my_col'
order by create_time

Answer 3

You can query the metastore schema in your MySQL database. Something like:

mysql> select * from TBLS;

More details on how to configure a MySQL metastore to store metadata for Hive and verify and see the stored metadata here.