Back

HIVE METASTORE

Used to store all metadata of hive objects.
Very useful for querying how many object and tables you have, as well as data types.

There are 2 useful tables, SDS and TBLS.
SDS contains the hive object details. TBLS contains the table details.
By joining the tables you can find the detailed information about each hive table.

Probably best to run each query from a sql script;

Launcher file for each sql query

#!/bin/sh
#launcher.sh
mysql -u root -p < [sqlquery.sql]

List all hive data formats

/*
 * Extract all unique data formats
 */
SELECT DISTINCT OUTPUT_FORMAT FROM SDS;

List object quantity of each data format

/*
 * Extract number of objects of each data format
 */
SELECT OUTPUT_FORMAT, count(*)
FROM SDS
GROUP BY OUTPUT_FORMAT;

List table quantity of each data format

/*
 * Extract number of tables of each data format
 */
SELECT OUTPUT_FORMAT, count(*)
FROM TBLS
LEFT JOIN SDS ON TBLS.SD_ID = SDS.SD_ID
GROUP BY OUTPUT_FORMAT;

Search location of all tables

/*
 * Extract location of each table
 */
SELECT TBLS.TBL_NAME,SDS.LOCATION
FROM SDS,TBLS
WHERE TBLS.SD_ID = SDS.SD_ID;