Hive. How to see the table created in the metastore?

Question

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

eg. Here are the 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> 

Then I sign up to the machine where MySQL is installed, and this MySQL is used as a metastart of the hive. I am using the metastore database. But if I want to list the tables, I do not see the table or the information about the table that I created in Hive.

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

Answer 1

First, find the MySql database that contains the metastor. This will be in your hive-site.conf url. Then, as soon as 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 find other tables to which a particular column belongs:

 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;

Learn more about setting up MySQL metastatistics to store metadata for Hive and checking and viewing saved metadata here .

Answer 4

* When configuring Hadoop services, any other services are used (this is also necessary), administrators use relational databases in most scenarios to store metadata information about services such as hive and oozi.

So, find your bush in the database (mysql, postgresql, sqlserver, etc.) and you can see the metadata information in the TBLS table. *

When updating your hive, you need to backup these TBLS.