The Complete Production SOP: How to Deploy and Run Apache Hive Metastore (HMS) Central Data Catalog in Autonomous AI Agents

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<configuration>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:postgresql://${env.METASTORE_DB_HOST:-localhost}:5432/${env.METASTORE_DB_NAME:-metastore_db}</value>
    <description>PostgreSQL JDBC connection URL for Hive Metastore</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>org.postgresql.Driver</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>${env.METASTORE_DB_USER:-hive}</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>${env.METASTORE_DB_PASS}</value>
  </property>
  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://localhost:9083</value>
  </property>
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>true</value>
  </property>
</configuration>

#!/usr/bin/env bash
set -euo pipefail

source .env.metastore

echo "Validating PostgreSQL connectivity for Hive Metastore..."
PGPASSWORD="${METASTORE_DB_PASS}" pg_isready \
  -h "${METASTORE_DB_HOST}" \
  -p "${METASTORE_DB_PORT}" \
  -d "${METASTORE_DB_NAME}" \
  -U "${METASTORE_DB_USER}"

echo "Database connection verified. Safe for schematool execution."

#!/usr/bin/env bash
set -euo pipefail

export HIVE_CONF_DIR="$(pwd)/conf"

echo "Checking Metastore Schema status..."
if schematool -dbType postgres -info 2>&1 | grep -q "Hive Metastore Schema is initialized"; then
  echo "Schema already initialized. Skipping initSchema."
else
  echo "Initializing Metastore Schema for PostgreSQL..."
  schematool -dbType postgres -initSchema --verbose
fi

echo "Validating schema consistency..."
schematool -dbType postgres -validateSchema

echo "HMS schema ready for thrift daemon."

-- Register external partitioned telemetry logs
CREATE DATABASE IF NOT EXISTS telemetry_lakehouse;

USE telemetry_lakehouse;

CREATE EXTERNAL TABLE IF NOT EXISTS api_events (
    event_id STRING,
    timestamp BIGINT,
    user_id STRING,
    action STRING,
    payload STRING
)
PARTITIONED BY (year STRING, month STRING, day STRING)
STORED AS PARQUET
LOCATION 's3a://production-data-lake/telemetry/api_events/'
TBLPROPERTIES ('parquet.compression'='SNAPPY');

-- Synchronize partitioned metadata with storage
MSCK REPAIR TABLE api_events;

-- Verification query
SHOW PARTITIONS api_events;