Translate

Monday, 9 March 2020

Shahed

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.

2 comments

Write comments
11 September 2021 at 01:48 delete

Aluminium Section Door Window Fabricator in ahmedabad
Aluminium Section Partition in ahmedabad
False Ceiling Work in ahmedabad
Toughned Glass Door Partition Work in ahmedabad
Aluminium Section Door Window Fabricator in ahmedabad is a relatively simple process that can even be conducted right from Brahmani Aluminium.Aluminium Section Door Window Fabricator in ahmedabad, Upvc Door Window Manufacture in ahmedabad, False Ceiling Work in ahmedabad, Aluminium Section Partition in ahmedabad. Brahmani Aluminium company is engaged in the manufacturing of False Ceiling that is manufactured using high-grade steel, glass, and plywood. Manufactured under the strict vigils of experts and professionals, the range of laminated kitchens offered by us is highly renowned among the client-base we have accomplished to gain. This offered kitchen is highly popular for its perfect finish. Toughned Glass Door Partition Work Glass Railing or tempered glass is a sort of laminated glass processed by controlled thermal or chemical treatments.

Reply
avatar
Jake Mathews
AUTHOR
27 October 2021 at 00:17 delete

This information really helped me a lot. It was very informative.
GCP Managed Services

Reply
avatar



About Authors

Shahed Munir

Krishna Udathu

Shahed and Krishna are Oracle / Big Data Experts