We’ll look at how to get the table’s storage location in this article. We don’t always want to put everything in the default warehouse; sometimes it’s better to store it somewhere else, and we can query the metastore to get that information.
Before we proceed to the query, note the following:
DBS: This table has all the details of databases/schemas.
TBLS: This table has all the details of the tables.
COLUMNS_V2: This table has all the details about the columns.
SDS: This table has all the details about storage/location.
TABLE_PARAMS: This table has all the details about table parameters.
Now, let’s see the query.
SELECT s.* FROM hive.TBLS t
JOIN hive.DBS d ON t.DB_ID = d.DB_ID
JOIN hive.SDS s ON t.SD_ID = s.SD_ID
WHERE TBL_NAME = ‘Employee’ AND d.NAME=’emp’;
In this example, “emp” refers to the database, while “Employee” refers to the table. Change it to meet your requirements.
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 