一个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]','')，问题终于得以解决！