Ram
Teacher
How to find out list of all hive external tables and hdfs paths from Hive Metastore ?
Hello all,
As part of my project requirement i need to find out list of all hive external tables from all databases, like suppose if my hive metastore contain 10 databases and each databases having more than 100 tables,at the end my output will be
Database name,Table name,Table Type(External) and HDFS Location of hive External tables.
Can someone please help me on this ?
Share
srilakshmi
Hello Ashok,
As per your requirement you want list of all external hive tables along with HDFS path location.
Database name,Table name,Table Type(External) and HDFS Location of hive External tables.
Use 3 tables TBLS,DBS and SDS tables , on top of this 3 tables we can apply joins on DB_ID and SD_ID .
select d.NAME,t_TBL_NAME,t.TBL_TYPE,s.LOCATION from TBLS t JOIN DBS d ON t.DB_ID=d.DB_ID JOIN SDS s ON t.SD_ID=s.SD_ID where t.TBL_TYPE='EXTERNAL_TABLE';nayak
Write a scala or java code and use the spark sql to fetch the details (describe table)
admin
Login to mysql metastore server
Select * from TBLS
nayak
Try with TBLS table in MySQL metastore