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

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.

Answer 2

*While setting up Hadoop services are any other services(this is mandatory too), admins use a relational databases in most of the scenarios to store the metadata information of the services like hive and oozie.

So, find which database(mysql,postgresql,sqlserver etc) your hive is backed up by, and you can see the metadata information in the TBLS table.*

While upgrading your hive, you have to take backup of these TBLS.

Answer 3

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 4

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