Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Query to access all managed tables of a given database in Hive Metastore

avatar
New Contributor

how to see metadata information for all tables available in a particular databases as DESCRIBE FORMATTED table_name is extracting information of a single table and i have 200 tables to be exected. please suggest if it is possible

4 REPLIES 4

avatar
Master Guru

@Rohini Mathur

Using Shell script:

one way of doing this would be using Shell script and to get all tables from the database

show tables from <db_name>;

then store all the tables into a variable and then loop through the each variable and execute

show create table <table_name>;

command.


Using Spark:

Another way would be

Using spark.catalog.listtables("<db_name>") to list out all the tables from database then filter out only the managed tables and execute show create table on the list of managed tables..


Using Hive metastore db:

Hive stores all the table information in metastore like mysql..etc you can also get information about tables from metastore also.

avatar
Master Guru

@Rohini Mathur

Please check this and this link to get location of the hive table.

avatar
New Contributor

@Shu: Thanks but i am trying to pull information from command describe formatted demo_table to hive table, where columns are col_name|data_type|comment|Database|Owner|CreateTime|Table Parameters|COLUMN_STATS_ACCURATE|numFiles|numRows|. Please suggest is there any way to do this

avatar
New Contributor

Hello @rohinimathursjc 

 

The best way to get the list of Hive external tables and Managed tables is using hive metastore. yes by applying some quires on hive metastore tables we can divide both Hive external tables and Managed tables . please follow the below link to get the queries 

https://askdoubts.com/question/list-of-hive-internal-tables-from-metadata/#comment-20