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

Highlighted

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

4 REPLIES 4
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.

Highlighted

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

Highlighted

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

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

 

Don't have an account?
Coming from Hortonworks? Activate your account here