hadoop - 配置数据类型的配置单元查询失败

Question

我写了以下配置单元查询。在这里，我尝试在我的join子句中使用Map数据类型的列(msg)。

select p.p_id, count(*) from prod_json n 
inner join res_pan p on n.msg["mid"] = p.id
where n.cat='XYX'
group by p.p_id limit 10;

此查询始终失败，并显示错误消息

    [Error getting row data with exception java.lang.ClassCastException: 
java.lang.String cannot be cast to org.openx.data.jsonserde.json.JSONObject at 
org.openx.data.jsonserde.objectinspector.JsonMapObjectInspector.getMap(Json
MapObjectInspector.java:40) at 
org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:317) at 
org.apache.hadoop.hive.serde2.SerDeUtils.buildJSONString(SerDeUtils.java:353) at 
org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:197) at 
org.apache.hadoop.hive.serde2.SerDeUtils.getJSONString(SerDeUtils.java:183) at 
org.apache.hadoop.hive.ql.exec.MapOperator.toErrorMessage(MapOperator.java:
529) at 
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:502) at 
org.apache.hadoop.hive.ql.exec.mr.ExecMapper.map(ExecMapper.java:170) at 
org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54) at 
org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453) at 

Answer 1

我认为我能够解决问题。我将查询重写为

select t2.p_id, count(*)
from (select cat, msg["mid"] as mid from prod_json) t1
join (select id, p_id from res_pan) t2
on t1.mid = t2.id
where t1.cat = 'XYZ'
group by t2.p_id

因此，基本上不要尝试直接使用map列，而是先使用内部查询将 map 放入各列中，然后再加入这些列。