Support Questions

Find answers, ask questions, and share your expertise

All hive tables location

avatar
Expert Contributor

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

1 ACCEPTED SOLUTION

avatar
Super Collaborator

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;





View solution in original post

2 REPLIES 2

avatar
Super Collaborator

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;





avatar
Expert Contributor

Thank you for the respone @ggangadharan