How to list table or partition location from Hive Metastore

Goal:

This article provides the SQL to list table or partition locations from Hive Metastore.

Env:

Hive metastore 0.13 on MySQL

Root Cause:

In Hive Metastore tables:
"TBLS" stores the information of Hive tables.
"PARTITIONS" stores the information of Hive table partitions.
"SDS" stores the information of storage location, input and output formats, SERDE etc.
Both "TBLS" and "PARTITIONS" have a foreign key referencing to SDS(SD_ID).

Solution:

1. To list table location:

select TBLS.TBL_NAME,SDS.LOCATION
from SDS,TBLS
where TBLS.SD_ID = SDS.SD_ID;

Sample output:

+--------------------------+------------------------------------------------------+
| TBL_NAME                 | LOCATION                                             |
+--------------------------+------------------------------------------------------+
| test1                    | maprfs:/user/hive/warehouse/test1                    |
| passwords                | maprfs:/user/hive/warehouse/passwords                |
| parquet_par              | maprfs:/user/hive/warehouse/parquet_par              |
+--------------------------+------------------------------------------------------+

2. To list table partition location:

select TBLS.TBL_NAME,PARTITIONS.PART_NAME,SDS.LOCATION
from SDS,TBLS,PARTITIONS
where PARTITIONS.SD_ID = SDS.SD_ID
and TBLS.TBL_ID=PARTITIONS.TBL_ID
order by 1,2;

Sample output:

+--------------+---------------------------------------+-------------------------------------------------------------------------------+
| TBL_NAME     | PART_NAME                             | LOCATION                                                                      |
+--------------+---------------------------------------+-------------------------------------------------------------------------------+
| partition_t  | end_date=2015-01-01/end_time=01-00-00 | maprfs:/user/hive/warehouse/partition_t/end_date=2015-01-01/end_time=01-00-00 |
| partition_t  | end_date=2015-01-02/end_time=02-00-00 | maprfs:/user/hive/warehouse/partition_t/end_date=2015-01-02/end_time=02-00-00 |
+--------------+---------------------------------------+-------------------------------------------------------------------------------+

Note: Above SQLs are based on MySQL syntax, please modify above SQLs to comply with other RDBMS syntax if needed.