CODEWITHSUNDEEP
hivemetadata
asds_asds
asds_asds

Reputation: 1062

What do the various tables in Hive Metastore contain?

I went through the Hive Metastore database and found that it contains a lot of tables (for example TBLS, DBS, etc).

I want to know what data do these various tables store?

I tried to find some sort of documentation for the meaning of these individual tables, and their columns but couldn't find any.

Upvotes: 1

Views: 3249

Answers (1)

gupta_hemant
gupta_hemant

Reputation: 136

There are many tables present in Hive metastore each one for a specific purpose. Hive uses this metastore to store its metadata (Database names, table names, columns, data types, etc.) For example, the TBLS table contains data related to Hive tables such as table name, table owner, created time, Database ID, etc.

These tables are related to each other with foreign keys and useful information can be retrieved by querying them with joins.

A sample query to find all the database names and their corresponding tables with column name and type is shown below.

SELECT DBS.NAME, TBLS.TBL_NAME, COLUMNS_V2.COLUMN_NAME, COLUMNS_V2.TYPE_NAME FROM TBLS, COLUMNS_V2, SDS, DBS WHERE TBLS.SD_ID=SDS.SD_ID AND COLUMNS_V2.CD_ID=SDS.CD_ID AND TBLS.DB_ID=DBS.DB_ID

ER diagram of the metastore - https://datacadamia.com/_media/db/hive/hive_metastore_er_diagram.png

Some useful queries can be found here - https://analyticsanvil.wordpress.com/2016/08/21/useful-queries-for-the-hive-metastore

I also didn't find any specific documentation on the same but I think this could help!!

Upvotes: 1

Related Questions