All hive tables location

[#1]

Hello,

Is there a way get the hdfs locations of all tables in CDP 7.1.7 SP1?

I tried running a for loop against a list of tables on below command, however, it will take ages to get the list as I am looking forward to 66k+ tables.

hive -e “desc formatted <table>;” | grep Location

Thought of to get from HMS DB directly, however, I can only see DB location URI, but nothing specific to tables.

Kindly help as this is urgent.

Thanks

snm1523

[#2 · Accepted solution]

Use the below Query to fetch the table location from HMS .

select "DBS"."NAME" as DB_NAME, "TBLS"."TBL_NAME", "SDS"."LOCATION" from "DBS" join "TBLS" on "DBS"."DB_ID" = "TBLS"."DB_ID" AND "TBLS"."TBL_TYPE" != 'VIRTUAL_VIEW' join "SDS" on "TBLS"."SD_ID" = "SDS"."SD_ID";

 
To query the same from hive, Would recommend to use JDBC Storage Handler.  In CDP by default in sysdb this tables has been created. you can use the same. 

Query 

select dbs.name as db_name , tbls.tbl_name , sds.location from dbs join tbls on dbs.db_id = tbls.db_id and tbls.tbl_type != 'VIRTUAL_VIEW' join sds on tbls.sd_id = sds.sd_id;

[#3]

Thank you for the respone @ggangadharan