Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

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

3 REPLIES 3
Highlighted

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

Super 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.

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

Super Guru

@Rohini Mathur

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

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

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