q64 Suspended During Remote Deployment of Hive MetaStore

Symptom

When the Hive MetaStore is deployed remotely, running the 1TB ORC TPCDS-99 benchmark causes query 64 to hang, since it actually results in a Cartesian product. The system continuously performs the join operation. As a result, the task is suspended.

Key Process and Cause Analysis

This problem may also occur when Hive executes open-source SQL statements. That is, when q44 and q64 are executed in sequence, the MetaStore cache causes the q64 execution plan to change, resulting in a Cartesian product and a large amount of data.

Conclusion and Solution

Solution 1: Choose local deployment instead to prevent changes in the SQL statement execution plan caused by the cache.

Solution 2: When the remote mode is used, execute q64 first and adjust the execution sequence so that q64 comes before q44.