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
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>;
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.
@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
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