question Re: All hive tables location in Support Questions

All hive tables location

snm1523 — Tue, 21 Apr 2026 07:08:13 GMT

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

Re: All hive tables location

ggangadharan — Wed, 07 Jun 2023 13:24:21 GMT

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;

Re: All hive tables location

snm1523 — Thu, 08 Jun 2023 05:51:53 GMT

Thank you for the respone @ggangadharan