Hive0.14关联表替换行程上下车ID为名称仅返回ID相等行问题

使用受场景限制无法升级的Hive 0.14版本分析纽约Uber出行数据，涉及两张核心表：

• yellowtaxi22：Uber行程明细表，本次分析仅使用pickupid（上车点ID）、dropoffid（下车点ID）字段
• zoneinfo：区域维度表，存储点位ID与对应区域名称的映射关系
  前期已完成高频Uber行程统计，基于统计结果创建了commontrip视图，当前需求是将视图中的上下车点ID关联替换为对应区域名称。
  执行关联查询后出现异常：仅返回pulocationid与dolocationid相等的2条记录，不符合预期（预期所有行程的上下车ID均可正确映射区域名称，包含上下车点不同的行程）。
  当前使用的查询语句如下：

select /*+ Mapjoin(commontrip)*/ zoneinfo.zone as pickup, zoneinfo.zone as dropoff, commontrip.trips 
from zoneinfo join commontrip on
(zoneinfo.locationid=commontrip.pulocationid and zoneinfo.locationid=commontrip.dolocationid);

关联逻辑存在本质错误：

1. JOIN条件要求zoneinfo.locationid同时等于上车点ID和下车点ID，等价于强制筛选pulocationid = dolocationid的记录，自然只能返回上下车点完全相同的行程
2. 单次关联维度表无法同时完成两个独立ID字段的映射，上车点、下车点是两个独立的点位，需要分别关联维度表取对应的区域名称
3. MapJoin提示使用错误：zoneinfo作为数据量极小的维度表（纽约出租车区域仅260+个）才是适合放入内存做MapJoin的表，原语句将统计结果视图commontrip作为MapJoin表，既不符合优化逻辑，大表加载进内存还可能触发内存溢出。

对维度表zoneinfo做两次独立关联，分别起别名区分上车点、下车点的映射，同时修正MapJoin提示，使用LEFT JOIN避免维度表ID缺失时丢数：

select /*+ MAPJOIN(z1, z2) */
  z1.zone as pickup,
  z2.zone as dropoff,
  commontrip.trips
from commontrip
left join zoneinfo z1 on commontrip.pulocationid = z1.locationid
left join zoneinfo z2 on commontrip.dolocationid = z2.locationid;

如果确认所有上下车点ID都在zoneinfo表中存在匹配记录，也可以将LEFT JOIN替换为INNER JOIN，效果一致。

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