如何在Hive所有表中查找指定列名？含报错及解决方案问询

Ah, I see the issue here—Hive doesn’t use the same direct [database].columns system table structure that traditional SQL databases like MySQL or PostgreSQL rely on. That’s exactly why your initial queries are throwing the "Table not found" error. Let’s walk through the current, working methods to locate tables with your target columns in Hive:

1. Use Hive's information_schema (Standard & Recommended) #

Hive supports the standard information_schema database, which houses metadata tables you can query just like in traditional SQL. This is the most straightforward and maintainable approach:

To find columns containing 'emp' in the retail database: #

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'retail' 
  AND column_name LIKE '%emp%';

To find distinct tables in default with an exact 'emp' column: #

SELECT DISTINCT table_name
FROM information_schema.columns
WHERE table_schema = 'default' 
  AND column_name = 'emp';

Note: Most Hive setups grant regular users access to information_schema by default, but if you hit permission issues, check with your cluster admin.

2. Query Hive's Metastore Directly (For Advanced Users) #

If you have access to Hive’s underlying metastore database (usually MySQL, PostgreSQL, or Derby), you can query the raw metadata tables directly. This is useful if information_schema isn’t available or you need more granular control over metadata:

First, connect to your metastore database (using tools like mysql or psql), then run these joined queries:

For the retail database: #

SELECT t.tbl_name AS table_name, c.column_name
FROM dbs d
JOIN tbls t ON d.db_id = t.db_id
JOIN columns_v2 c ON t.tbl_id = c.cd_id
WHERE d.name = 'retail' 
  AND c.column_name LIKE '%emp%';

For the default database: #

SELECT DISTINCT t.tbl_name AS table_name
FROM dbs d
JOIN tbls t ON d.db_id = t.db_id
JOIN columns_v2 c ON t.tbl_id = c.cd_id
WHERE d.name = 'default' 
  AND c.column_name = 'emp';

3. Batch Scripting (For Ad-Hoc Checks) #

If you prefer using the Hive CLI and need a quick, manual way to check tables, you can write a simple bash script to iterate through tables and validate their columns:

# Check for columns containing 'emp' in the retail database
hive -e "use retail; show tables;" | while read table; do
  # Skip header lines if present
  if [[ "$table" != "tab_name" ]]; then
    column_match=$(hive -e "use retail; describe $table;" | grep -i "emp")
    if [[ -n "$column_match" ]]; then
      echo "Found match: Table '$table' has column(s) with 'emp':"
      echo "$column_match"
      echo "------------------------"
    fi
  fi
done

This script loops through all tables in retail, describes each one, and prints out any tables that have columns containing 'emp'.

Why Your Initial Queries Failed #

Traditional SQL databases expose column metadata via a columns table under each database, but Hive doesn’t follow this pattern. Instead, it centralizes metadata in either the standard information_schema (for user-friendly access) or its internal metastore tables (for direct database-level access).

内容的提问来源于stack exchange，提问作者Mohit.kc