Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 36 ยท Architecture and Metastore

The Hive Metastore

Learn what the Hive metastore stores, how its relational schema is organized, the embedded, local and remote deployments, and why Spark and Trino share it.

If the data files are the body of a Hive table, the metastore is its memory. Without it, a folder of Parquet files is just a folder: no column names, no types, no idea which partitions exist. The metastore is the catalog that turns files into tables, and it has become the most reused part of Hive.

The metastore is a service plus a database, so it cannot run in a web page. Hive statements and the metastore schema are shown read-only. The runnable editors build a tiny catalog of our own in SQLite as a labelled stand-in, to show how metadata is modelled.

What the metastore stores

  • Databases: name, description, location, owner.
  • Tables: name, owner, type (managed, external, view), location, creation time, parameters.
  • Columns: name, type, comment, position.
  • Storage descriptors: InputFormat, OutputFormat, SerDe class and its properties, bucketing columns, sort columns.
  • Partitions: every partition's key values and its own location.
  • Statistics: row counts, sizes, per-column min/max/distinct estimates.
  • Privileges, transactions and locks (for ACID tables), and functions registered as UDFs.

What it does not store is the data. A table with ten petabytes has a metastore entry of a few kilobytes plus one row per partition.

The backing database

The metastore uses a relational database through an ORM called DataNucleus. The main tables are named in capitals, for example:

Metastore table Contents
DBS databases
TBLS tables, with a link to its database and storage descriptor
SDS storage descriptors (location, formats)
COLUMNS_V2 columns
PARTITIONS, PARTITION_KEYS, PARTITION_KEY_VALS partition definitions
TAB_COL_STATS, PART_COL_STATS column statistics

You can query these tables directly in the backing MySQL or PostgreSQL, but that is only for diagnosis. Never write to them by hand: an inconsistent metastore can make tables unreadable. Use Hive DDL instead.

SQL
-- read-only diagnostic, run in the metastore's MySQL, not in Hive
SELECT d.NAME AS db, t.TBL_NAME, t.TBL_TYPE
FROM TBLS t JOIN DBS d ON t.DB_ID = d.DB_ID
ORDER BY d.NAME, t.TBL_NAME;

Modeling a catalog (stand-in)

The metastore is relational at heart: tables point to databases, columns point to tables. This stand-in builds a three-table mini catalog in SQLite and joins it, the way the diagnostic query above joins TBLS and DBS.

SQL Stand-in: a mini catalog (SQLite, not the real metastore)

Press Run (or Ctrl + Enter) to see the result here.

Deployment options

As covered in Installing Hive, the metastore can be embedded, local or remote. In production, use a remote Hive Metastore Service (HMS):

flowchart LR
    A["HiveServer2"] -->|Thrift 9083| M["HMS<br/>(2 or more instances)"]
    B["Spark"] -->|Thrift| M
    C["Trino / Presto"] -->|Thrift| M
    D["Flink / Impala"] -->|Thrift| M
    M --> DB[("MySQL or PostgreSQL<br/>with backups")]

One catalog shared by many engines: define a table once, query it anywhere.

Key properties: hive.metastore.uris=thrift://hms1:9083,thrift://hms2:9083 on clients, and the JDBC settings on the service. The database behind it is a single point of failure, so use replication and backups; the metastore database is among the most important things to back up in a Hive deployment.

Hive 3: standalone metastore

From Hive 3 the metastore can run as its own project artifact without the rest of Hive. This is how Trino, Spark and cloud services use it: the HMS binary plus a database, with no HiveServer2 at all. Managed cloud catalogs, such as AWS Glue, implement the same interface so tools can treat them as a metastore.

Typical metastore commands

You rarely talk to the metastore directly, but a few Hive statements read or repair it:

SQL
SHOW PARTITIONS sales.orders;
DESCRIBE FORMATTED sales.orders;
MSCK REPAIR TABLE sales.orders;           -- register folders that exist in storage but not in the metastore
ANALYZE TABLE sales.orders COMPUTE STATISTICS;

MSCK REPAIR TABLE is the standard fix when somebody wrote partition folders to storage with a tool other than Hive. The files exist but the metastore does not know about them, so queries return nothing.

Performance and scaling

  • Tables with hundreds of thousands of partitions make metastore calls slow. Partition less finely (see Partitioned Tables).
  • Put a connection pool in front of the database, and keep it close to the service for low latency.
  • Enable the metastore cache on clients where your version supports it.
  • Remove old statistics and unused tables with DROP rather than letting them accumulate.

Common problems

  • Metastore connection errors: the service is down or hive.metastore.uris is wrong.
  • Schema version mismatch: you upgraded Hive but did not run schematool -upgradeSchema.
  • Table exists in HDFS but is empty in Hive: partitions are missing from the metastore; run MSCK REPAIR TABLE.
  • Locks left after a crash: show them with SHOW LOCKS and clean up.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the Hive metastore NOT store?

    The metastore holds metadata only; data lives in HDFS or object storage.
  2. 2. Why is a remote metastore service preferred in production?

    A remote HMS hides the backing database and is the shared catalog for Hive, Spark, Trino and others.
  3. 3. Which statement registers partition folders that exist in storage but are missing from the metastore?

    MSCK REPAIR TABLE compares storage with the metastore and adds the missing partitions.
  4. 4. What should you do if you upgrade Hive and the metastore schema is outdated?

    schematool upgrades the schema safely; manual edits risk corrupting the catalog.

Summary

  • The metastore is the catalog of databases, tables, columns, partitions, formats and statistics; data stays in storage.
  • It runs on a relational database; production uses a remote, shared Hive Metastore Service.
  • Spark, Trino, Flink and others reuse it, so Hive table definitions work across engines.
  • Never edit its tables by hand; use DDL, MSCK REPAIR TABLE and schematool.

Next: Execution Engines: MapReduce, Tez and Spark