Table or partition location information from Apache Hive

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:

1 2 3

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

Sample output:

1 2 3 4 5 6 7

+--------------------------+------------------------------------------------------+ | 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:

1 2 3 4 5

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:

1 2 3 4 5 6

+--------------+---------------------------------------+-------------------------------------------------------------------------------+ | 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.