Skip to main content
snm1523
Gold ⭐️⭐️
Jun 7, 2023 3:02 AM UTC
Solved

All hive tables location

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

ggangadharan
Gold ⭐️
Jun 7, 2023 4:53 AM UTC

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;





2 replies

ggangadharan
Gold ⭐️
Gold ⭐️
Jun 7, 2023 4:53 AM UTC

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;





Translate
snm1523
snm1523Author
Gold ⭐️⭐️
Jun 8, 2023 5:51 AM UTC

Thank you for the respone @ggangadharan 

Translate