alibabacloud-migration-lhm-inspect-hive-metastore

name	alibabacloud-migration-lhm-inspect-hive-metastore
description	执行 Hive 数据表探查任务，支持全量探查和增量探查，提供两种连接方式：DB 直连（通过 Metastore MySQL/PostgreSQL 数据库）和 Thrift 直连（通过 HMS Thrift API 端口 9083）。全量探查导出所有表的元数据、存储大小和 DDL；增量探查识别指定时间节点之后的表创建、结构变更、数据变更和分区变更。使用场景：用户提到"Hive 探查"、"全量探查"、"增量探查"、"Hive 元数据"、"hive_dive"、"metastore 变更"、"Thrift 探查"、"migration-lhm-inspect-hive-metastore"时调用此 skill。不适用于数据迁移执行、DDL 转换、数据写入等场景，这些功能由 migration-lhm-migrate-hive-to-paimon 等下游 skill 承担。

Hive Metadata Exploration

This skill targets AI agent platforms that support terminal command execution (e.g. Claude Code, Qoder).

Safety Red Lines

• Database passwords in the config file must never be printed to the terminal or logs.
• Only read-only exploration operations are performed; Hive metadata is never modified.
• Exploration output files contain no sensitive credentials.
• Never echo the plaintext password from config.ini back to the user.

Tooling Overview

This skill depends on no MCP tools. It connects to the Hive Metastore directly via local Python/Bash scripts.

Prerequisites

• Python >= 3.7
• pip install PyMySQL>=1.0.0 — MySQL Metastore driver
• pip install psycopg2-binary>=2.9 — PostgreSQL Metastore driver
• pip install hmsclient>=0.1.1 — base dependency for Thrift mode
• pip install thrift_sasl gssapi — Kerberos authentication (optional)
• hadoop CLI — only needed in size_source=hadoop mode
• hive CLI — only needed for hive_dive.sh full exploration

Required Permissions

DB direct-connect mode

• MySQL/PostgreSQL read-only: SELECT on TBLS, DBS, SDS, TABLE_PARAMS, PARTITIONS, PARTITION_KEYS, PARTITION_PARAMS

Thrift mode

• HMS Thrift API read access: get_all_databases, get_all_tables, get_table, get_partitions

Storage size retrieval (optional)

• hadoop fs -du -s access (HDFS path read permission)

---

Overview

Mode	Connection	Script	Key output
Full exploration	DB direct	hive_dive.sh	summary_report.csv + per-table DDL files
Full exploration	Thrift direct	hive_dive_thrift.py	summary_report.csv + per-table DDL files
Incremental	DB direct	get_metastore_changes.py	metastore_delta.csv
Incremental	Thrift direct	get_metastore_changes_thrift.py	metastore_delta.csv

Choosing a connection method:

• DB direct: connects straight to the Metastore MySQL/PostgreSQL DB + hadoop/hive CLI; suitable when you have DB access.
• Thrift direct: uses the HMS Thrift API (port 9083); needs only Python, no hadoop/hive CLI; suitable for remote or container environments.

---

Full Exploration (hive_dive.sh)

How It Works

1. Connect to the Hive Metastore database (MySQL) and query TBLS / DBS / SDS to get all managed and external tables.
2. Run hadoop fs -du -s <location> per table to get storage size.
3. Run hive -e "SHOW CREATE TABLE" to export DDL into individual .sql files.
4. Aggregate into summary_report.csv.

Configuration

Edit the variables at the top of the script:

MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
MYSQL_DATABASE="hive"   # Metastore database name

Commands

# Explore all databases → output dir: hive_explore_all_dbs_YYYYMMDD/
bash hive_dive.sh

# Explore specific databases → output dir: hive_explore_batch_YYYYMMDD/
bash hive_dive.sh db1 db2 db3

Output Layout

hive_explore_<all_dbs|batch>_YYYYMMDD/
├── summary_report.csv     # columns: db_name, tbl_name, tbl_location, total_size_bytes, total_size_human, ddl_file_path
├── ddl_files/
│   └── <db>.<table>.sql   # SHOW CREATE TABLE result per table
└── error.log              # hadoop/hive command error log

Notes

• set -e is enabled; if the MySQL connection fails, the script exits immediately.
• When DDL export fails, the file content is -- FAILED TO GET DDL for <db>.<table>, without affecting the overall flow.
• Views are out of scope (the WHERE clause filters for MANAGED_TABLE / EXTERNAL_TABLE).

Storage sizes in the exploration result are based on a Metastore metadata snapshot and may differ from the actual runtime state; cross-verify critical data.

---

Incremental Exploration (get_metastore_changes.py)

How It Works

Queries the Hive Metastore DB directly to detect changes after a given time point, with dedup priority: TABLE_CREATE > TABLE_MODIFIED > DATA_MODIFIED. Partition changes of newly created tables are filtered automatically.

Change Types

type	Meaning
TABLE_CREATE	New table
TABLE_MODIFIED	Schema change (existing table)
DATA_MODIFIED	Data change of a non-partitioned table (no schema change)
PARTITION_CREATE	New partition (not a new table)
PARTITION_MODIFIED	Partition data change (not a new partition)

Environment Setup

# MySQL Metastore
pip install PyMySQL

# PostgreSQL Metastore
pip install psycopg2-binary

Config File (config.ini)

Only the [metastore_db] section is needed (incremental exploration does not involve rclone/paimon):

[metastore_db]
db_type = mysql          # or postgres
host = your_host
port = 3306
user = your_user
password = your_password
database = hivemeta      # Metastore database name

Security tip: in production, reference the password via an environment variable, e.g. password = ${HIVE_METASTORE_PWD}, to avoid plaintext storage.

Command

# Run incremental metadata query only (does not generate rclone/paimon statements)
python get_metastore_changes.py \
  -c config.ini \
  -s "2026-01-12 18:00:00" \
  -o metastore_delta.csv

Output Format (metastore_delta.csv)

type,db_name,table_name,is_partitioned,partition_keys,partition_values,location,change_time
TABLE_CREATE,default,new_table,1,p_dt,,hdfs://.../new_table,2026-01-15 14:33:00
PARTITION_MODIFIED,default,orders,1,dt,2026-01-12,hdfs://.../orders/dt=2026-01-12,2026-01-15 15:00:00

Field notes:

• is_partitioned: 1 = partitioned table, 0 = non-partitioned.
• partition_keys: partition key names (comma-separated).
• partition_values: partition values (/-separated, key names removed).
• location: HDFS/OSS path.

---

Troubleshooting

Issue	Diagnostic direction
Full-exploration MySQL connection fails	Check MYSQL_HOST/USER/PASSWORD/DATABASE and network connectivity
hadoop fs -du errors	Check error.log; confirm HADOOP_HOME and cluster connectivity
hive DDL export fails	Check error.log; confirm the hive command works and table names have no special characters
Incremental query returns empty	Confirm -s time format is YYYY-MM-DD HH:MM:SS; confirm the timezone matches the Metastore
psycopg2 / PyMySQL not installed	Install the corresponding driver per the environment-setup steps

---

Unified Entry Point (hive_explore.py) — recommended

hive_explore.py is the recommended way to use this skill, integrating config management, connection preflight, automatic fallback, and profile saving.

Subcommands

python hive_explore.py <subcommand> [options]

Subcommands:
  full      Full exploration (Thrift mode)
  incr      Incremental exploration (DB or Thrift)
  test      Connection test (connectivity check only)
  compare   Compare mode (run DB + Thrift together, produce a diff report)

Common Examples

# connection test
python hive_explore.py test --host 10.0.1.100 --thrift-port 9083

# Thrift full exploration
python hive_explore.py full --host 10.0.1.100 --mode thrift db1 db2

# DB incremental exploration
python hive_explore.py incr --mode db --host 10.0.1.100 --user root --password xxx \
  -s "2026-01-12 18:00:00"

# Thrift incremental exploration (interactive config completion)
python hive_explore.py incr --mode thrift -s "2026-01-12 18:00:00"

# compare mode
python hive_explore.py compare --host 10.0.1.100 --user root --password xxx \
  -s "2026-01-12 18:00:00"

# use/save a profile
python hive_explore.py incr --profile prod -s "2026-01-12 18:00:00"
python hive_explore.py full --host 10.0.1.100 --save-profile prod

Global Parameters

Parameter	Description
-c, --config	Config file path (default: config.ini)
--profile NAME	Load a saved profile
--save-profile NAME	Save the current config as a profile
--mode {db,thrift,both}	Connection mode (default: thrift)
--host	Host address (shared by DB and Thrift)
--thrift-host	Thrift-specific host
--port	DB port
--thrift-port	Thrift port
--user / --password	DB authentication
--database	Metastore database name (auto-detected if empty)
--db-type {mysql,postgres}	DB type
--auth {NOSASL,KERBEROS}	Thrift authentication method
--fallback-host	Fallback host
--no-interactive	Disable interactive prompts

Features

• Connection preflight: checks port connectivity before running to surface network issues fast.
• Automatic fallback: prompts to switch to Thrift when the DB is unreachable.
• Database-name auto-detection: auto-detects when --database is omitted in DB mode.
• Fallback host: configure internal/external dual addresses via --fallback-host.
• Automatic retry: retries on network jitter (exponential backoff, up to 3 times).
• Config profiles: save/load common environment configs to avoid repeated input.
• Friendly error messages: classifies errors and gives specific diagnostic suggestions.

---

Thrift Direct-Connect Mode

When to Use

• Cannot access the Hive Metastore MySQL/PostgreSQL database directly.
• No hadoop / hive CLI in the environment (e.g. a remote host or container).
• Can only reach the HMS Thrift Service over the network (port 9083).

Environment Setup

# base dependency (unauthenticated NOSASL mode)
pip install hmsclient

# for Kerberos authentication
pip install hmsclient thrift_sasl gssapi

Config File (config.ini)

Fill in the [general] and [thrift] sections:

[general]
connection_mode = thrift    # 'db' or 'thrift'

[thrift]
host = your_hms_host        # HMS Thrift service address
port = 9083                 # Thrift port
auth = NOSASL               # NOSASL or KERBEROS
kerberos_principal = hive/[e-mail]  # only for KERBEROS mode
timeout = 60                # connection timeout (seconds)
size_source = params        # table-size source: params / hadoop / skip

size_source explained:

Value	Behavior	Requirement
params	Read from the Hive table parameter totalSize (requires prior ANALYZE TABLE)	No external dependency
hadoop	Get actual size via hadoop fs -du -s	Requires hadoop CLI
skip	Always show N/A	None

Kerberos authentication: obtain a ticket before running the script:

# using a password
kinit [e-mail]

# using a keytab
kinit -kt /path/to/keytab [e-mail]

Full Exploration (hive_dive_thrift.py)

How It Works

1. Connect to HMS via the Thrift API, calling get_all_databases() / get_all_tables() / get_table() for metadata.
2. Get storage size from table parameters or the hadoop command.
3. Rebuild DDL from the Thrift Table object (via ddl_builder.py).
4. Aggregate into summary_report.csv.

Commands

# explore all databases
python hive_dive_thrift.py -c config.ini

# explore specific databases
python hive_dive_thrift.py -c config.ini db1 db2 db3

Output Layout

Identical to hive_dive.sh:

hive_explore_<all_dbs|batch>_YYYYMMDD/
├── summary_report.csv     # columns: db_name, tbl_name, tbl_location, total_size_bytes, total_size_human, ddl_file_path
├── ddl_files/
│   └── <db>.<table>.sql   # DDL rebuilt from Thrift metadata
└── error.log              # error log

Notes

• DDL is rebuilt from Thrift metadata and may differ in formatting from SHOW CREATE TABLE output (property ordering, whitespace), but is semantically equivalent.
• With size_source = params, size may show N/A if the table has never run ANALYZE TABLE.
• Downstream tools generate_rclone_script.py / generate_paimon_statements.py can consume the output directly.

Incremental Exploration (get_metastore_changes_thrift.py)

How It Works

Iterates all tables and partitions via the Thrift API, checking timestamp parameters, implementing the same five-stage dedup logic as the DB-direct version: TABLE_CREATE > TABLE_MODIFIED > DATA_MODIFIED > PARTITION_CREATE > PARTITION_MODIFIED

Commands

# scan all databases
python get_metastore_changes_thrift.py \
  -c config.ini \
  -s "2026-01-12 18:00:00" \
  -o metastore_delta.csv

# limit scan scope (improves performance on large clusters)
python get_metastore_changes_thrift.py \
  -c config.ini \
  -s "2026-01-12 18:00:00" \
  -o metastore_delta.csv \
  --databases db1 db2

Output Format

Identical to get_metastore_changes.py:

type,db_name,table_name,is_partitioned,partition_keys,partition_values,location,change_time
TABLE_CREATE,default,new_table,1,p_dt,,hdfs://.../new_table,2026-01-15 14:33:00
PARTITION_MODIFIED,default,orders,1,dt,2026-01-12,hdfs://.../orders/dt=2026-01-12,2026-01-15 15:00:00

Performance Notes

Thrift incremental exploration iterates all tables and partitions to check timestamps, which can be slow on large clusters. Limit the scan scope with --databases.

Orchestrator Integration

main_metastore_changes.py supports Thrift mode and switches automatically via connection_mode in config.ini:

# after setting connection_mode = thrift in config.ini
python main_metastore_changes.py -c config.ini -s "2026-01-12 18:00:00"

---

Thrift Mode Troubleshooting

Issue	Diagnostic direction
Thrift connection timeout	Check whether the HMS service is running, port 9083 is reachable, and firewall rules
Kerberos authentication fails	Confirm kinit was run, check ticket validity with klist, verify the principal config
ImportError: hmsclient	Run pip install hmsclient
ImportError: thrift_sasl	Kerberos mode requires: pip install thrift_sasl gssapi
DDL rebuild incomplete	A few special tables may lack a StorageDescriptor; check error.log
Table size shows N/A	With size_source = params, run ANALYZE TABLE first; or switch to hadoop mode
Incremental scan too slow	Limit scan scope with --databases

---

Known Limitations

• Thrift incremental exploration must iterate all tables/partitions; performance is slow on large clusters (>10000 tables). Limit scope with --databases.
• size_source=params depends on the table having run ANALYZE TABLE; otherwise size shows N/A.
• DDL is rebuilt from Thrift metadata and may differ in formatting from SHOW CREATE TABLE (property ordering, whitespace), but is semantically equivalent.
• Hive view exploration is not supported (VIRTUAL_VIEW / MATERIALIZED_VIEW are filtered).
• DB direct mode depends on the MySQL/PostgreSQL client library; Thrift mode depends on hmsclient.

---

File Structure

scripts/
├── hive_explore.py                    # unified entry point (recommended)
├── connection_utils.py                # connection utils (preflight/retry/fallback/error classification)
├── config_manager.py                  # config management (profile/interactive completion)
├── hive_dive.sh                       # full exploration (DB direct, Bash)
├── hive_dive_thrift.py                # full exploration (Thrift direct)
├── get_metastore_changes.py           # incremental exploration (DB direct)
├── get_metastore_changes_thrift.py    # incremental exploration (Thrift direct)
├── thrift_client.py                   # Thrift connection factory (NOSASL/Kerberos)
├── ddl_builder.py                     # DDL rebuild engine
├── main_metastore_changes.py          # full-pipeline orchestrator
├── generate_rclone_script.py          # rclone migration-script generation
└── generate_paimon_statements.py      # Paimon sync-SQL generation

config.ini                             # unified config file (skill root)

---

Suite Relationship

This skill is part of the lakehouse migration suite; its exploration output can be used directly as input to downstream tools (-e <explore_dir>).

Related tools:

• migration-lhm-migrate-hive-to-paimon: uses this skill's exploration result to run an end-to-end Hive → Paimon migration.
• migration-lhm-migrate-hive-to-maxcompute: uses this skill's exploration result to run an end-to-end Hive → MaxCompute migration.
• migration-lhm-migrate-sqlserver-to-maxcompute-ddl: SQL Server → MaxCompute DDL migration.
• migration-lhm-manage-maxcompute-mms: MaxCompute MMS migration-service monitoring and management.

Downstream toolchain:

1. rclone data migration (generate_rclone_script.py): generates HDFS → S3 rclone copy scripts.
2. Paimon sync (generate_paimon_statements.py): generates Paimon external-table creation and data-sync SQL.
3. Full-pipeline orchestration (main_metastore_changes.py): chains incremental query → rclone script → Paimon SQL.

The output format is identical for both connection methods, so downstream tools need not distinguish the data source.

---

Termination and Summary

After exploration completes, the agent should report to the user:

1. Output directory path and key file locations (summary_report.csv or metastore_delta.csv).
2. Exploration stats: number of databases and tables explored (full), or number of change records (incremental).
3. Error notice: if any errors occurred, guide the user to check error.log.
4. Next-step suggestion: note that the exploration result can be passed to downstream migration tools.

Exploration results are based on a Metastore metadata snapshot; actual storage sizes may differ from the runtime state. Cross-verify critical data.