基于MapReduce运行Hive查询时任务停滞的问题排查求助

Hey there, let's walk through the most likely reasons your Hive query is getting stuck or running at a crawl, based on the join logic and tables you shared. I’ve dealt with tons of these scenarios on MapReduce-backed Hive clusters, so here’s what to check first:

1. Data Skew (The #1 Culprit for Stuck Jobs) #

MapReduce jobs often grind to a halt when one reducer ends up handling way more data than others—this is data skew. With your left outer joins, watch out for:

• Null values in join keys: If A.TICKET_NO or A.COMPANY_ID has lots of nulls, all those rows will get sent to the same reducer, overwhelming it.
• High-cardinality or low-cardinality keys: For example, if most rows in CUSTOMER have the same COMPANY_ID, that reducer will be swamped.

Fixes to try: #

• Add skew hints to Hive:

  SET hive.optimize.skewjoin = true;
  SET hive.skewjoin.key = 100000; -- Adjust based on your data size
  

• Split skew keys manually: Filter out nulls first (if business logic allows) or split the join into two parts—one for skew keys, one for the rest.
• Use MAPJOIN for small tables: If TICKET_R or TICKET_X is small enough to fit in memory, force a map-side join to avoid reducer bottlenecks:

  SET hive.auto.convert.join = true;
  SET hive.mapjoin.smalltable.filesize = 25000000; -- 25MB, adjust as needed
  

2. Missing or Outdated Table Statistics #

Hive relies on table statistics to generate an optimal execution plan. If it doesn’t know how big your tables are or the distribution of join keys, it might pick a terrible join strategy (like sending all data to a single reducer).

Fixes: #

• Update stats for all involved tables:

  ANALYZE TABLE CUSTOMER COMPUTE STATISTICS;
  ANALYZE TABLE TICKET_R COMPUTE STATISTICS;
  ANALYZE TABLE TICKET_X COMPUTE STATISTICS;
  -- For column-level stats (even better for join keys)
  ANALYZE TABLE CUSTOMER COMPUTE STATISTICS FOR COLUMNS TICKET_NO, COMPANY_ID;
  

3. Suboptimal Join Order #

Hive’s query optimizer might not always pick the best join order, especially with multiple left outer joins. Since you’re doing CUSTOMER LEFT JOIN TICKET_R LEFT JOIN TICKET_X, make sure:

• The largest table is the leftmost one (which it seems like CUSTOMER is, since it’s the base for left joins)—this helps MapReduce process data more efficiently.
• If TICKET_X is small, try joining it first with CUSTOMER before joining TICKET_R (test if this aligns with your business logic).

4. Insufficient MapReduce Resources #

If your cluster doesn’t have enough allocated memory or CPU for MapReduce containers, tasks will queue up or run extremely slowly.

Check and adjust these settings: #

-- Increase container memory (adjust based on your cluster's capacity)
SET mapreduce.map.memory.mb = 4096;
SET mapreduce.reduce.memory.mb = 8192;
-- Increase number of reducers (avoid too many, but enough to parallelize)
SET mapreduce.job.reduces = 20; -- Start with 10-20, adjust based on data size

5. Poor Table Storage Format/Compression #

If your tables are stored as uncompressed TextFile, MapReduce has to process way more data than necessary. Columnar formats like Parquet or ORC with compression drastically reduce I/O time.

Quick fix for future queries: #

• Convert tables to ORC with compression:

  ALTER TABLE CUSTOMER SET FILEFORMAT ORC;
  ALTER TABLE CUSTOMER SET TBLPROPERTIES ("orc.compress"="SNAPPY");
  

6. WHERE Clause Placement Issue #

Wait a second—since you’re using left outer joins, if your WHERE condition filters on columns from TICKET_R or TICKET_X, you might accidentally turn the left join into an inner join. For example, if you have WHERE B.STATUS = 'ACTIVE', any rows from CUSTOMER that don’t match TICKET_R will get filtered out. If that’s not intended, move those conditions into the ON clause instead:

LEFT OUTER JOIN TICKET_R AS B ON A.TICKET_NO = B.TICKET_NO AND B.STATUS = 'ACTIVE'

内容的提问来源于stack exchange，提问作者sshah