How to list table or partition location from Hive Metastore

Document created by Hao Zhu Employee on Feb 18, 2016
Version 1Show Document
  • View in full screen mode

Author: Hao Zhu

Original Publication Date: April 22, 2015

 

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:

  • "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.

Attachments

    Outcomes