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).
"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 commentsAluminium Section Door Window Fabricator in ahmedabad
ReplyAluminium 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.
This information really helped me a lot. It was very informative.
ReplyGCP Managed Services