一个hive遇到的问题（两表关联，两表中关联字段唯一，关联结果表中不唯一）

记录一个hive中遇到的很有意思的问题，首先我建了两张临时表，最终要把两张表关联起来的时候，发现：两张表中的关联字段都是唯一的，关联之后关联字段竟然不唯一了！关联方法用的left join。检验sql如下：

SELECT project_id, COUNT(1) AS num
FROM tmp.remote_monitor_5
GROUP BY project_id
HAVING num > 1

无结果

SELECT projectid, COUNT(1) AS num
FROM tmp.remote_monitor_res
GROUP BY projectid
HAVING num > 1

无结果

SELECT x.projectid, COUNT(1) AS num
FROM (
    SELECT a.projectid
    FROM tmp.remote_monitor_res a
        LEFT JOIN tmp.remote_monitor_5 z ON a.projectid = z.project_id
) x
GROUP BY x.projectid
HAVING num > 1

有23条结果

 

这种结果是我很不理解的，我把结果明细数据查询出来，发现是z表中一个id对应有多条记录（group by却不在一个分组中，看来group by这种方式确定唯一性不可靠！需要注意！）（20200601更新一下，并不是group by不可靠，因为关联key本来就不一样，所以group by结果没问题，而是join结果有问题，唯一的join key不应该产生多对多join。），因为一个projectid对应z表中获取的记录有多个，我想到了加上trim()，防止关联字段有空格的干扰，做了以后不唯一的条数减少了，但是依然存在！

 

而且，把不唯一的记录的project_id在a、z中查询都没有重复的记录。然后我想到用模糊查询试一试，果然！project_id字段使用trim处理之后仍然有空白符（使用trim只能把标准英文空格删掉！）。于是我使用regexp_replace(NVL(project_id,''),'[\\s]','')，问题终于得以解决！