如何编写Hive查询找出含指定列的表及实现关联表查询

Hey there! Let's break down your problem into two clear, actionable parts: first tracking down all tables in your Hive database that contain your target column, then using those tables to build the join query you need.

1. Find All Tables with a Specified Column Name #

Hive keeps metadata about your tables and columns in the information_schema database, which makes cross-table column searches straightforward. Here's a simple, reliable query to get the job done:

SELECT DISTINCT table_name
FROM information_schema.columns
WHERE table_schema = 'your_hive_database_name'  -- Replace with your actual database name
  AND column_name = 'your_target_column_name'; -- Replace with the column you're searching for

Quick Notes: #

• Use DISTINCT to avoid duplicate table names (though rare, this handles cases where the same column appears multiple times in one table).
• If you’re on an older Hive version that doesn’t support information_schema, you can query the underlying Hive metastore tables directly (usually stored in a MySQL/PostgreSQL instance). For example:

  SELECT t.tbl_name
  FROM hive_metastore.tbls t
  JOIN hive_metastore.columns_v2 c ON t.tbl_id = c.cd_id
  JOIN hive_metastore.sds s ON t.sd_id = s.sd_id
  WHERE s.db_name = 'your_hive_database_name'
    AND c.column_name = 'your_target_column_name';
  

2. Build the Join Query #

Once you have your list of matching tables, you can construct a join between your known table and the relevant candidate(s). Let’s define some placeholders to make this concrete:

• Your known table: known_table
• A matching table you found: matching_table
• The shared column linking them: target_column

Here’s a basic inner join to fetch specific data from both tables:

SELECT 
  kt.column1, kt.column2,  -- Pick columns you need from your known table
  mt.specific_column       -- Pick columns you need from the matching table
FROM your_hive_database_name.known_table kt
INNER JOIN your_hive_database_name.matching_table mt
  ON kt.target_column = mt.target_column
-- Add a WHERE clause to filter for exactly the data you want
WHERE kt.some_filter_column = 'desired_value';

Tips for Smooth Joins: #

• Pick the right join type: Use LEFT JOIN if you want all records from your known table (even if there’s no match in the other table), or INNER JOIN to only keep rows with matches across both tables.
• Test with small datasets first: For large tables, run a quick count to verify the join logic makes sense before fetching full results:

  SELECT COUNT(*)
  FROM your_hive_database_name.known_table kt
  JOIN your_hive_database_name.matching_table mt
    ON kt.target_column = mt.target_column;
  

• Optimize performance: If one table is much smaller than the other, use Hive’s MAPJOIN hint to speed things up:

  SELECT /*+ MAPJOIN(mt) */
    kt.*, mt.specific_column
  FROM your_hive_database_name.known_table kt
  JOIN your_hive_database_name.matching_table mt
    ON kt.target_column = mt.target_column;
  

内容的提问来源于stack exchange，提问作者Tom J Muthirenthi