Hive解决SQL的join or

清疚 2022-01-30 00:09 614阅读 0赞

在处理数据时，遇到join on的条件有多个，然而hive不支持on or，因此问了度娘，找了google才发现这东西还涉及hive优化，吭哧了一下午终于弄出来，心情豁然开朗，希望本文能帮到遇到难题的你们。   
我的一个表是有关电话号码的，另一个表是解析这个电话号码的，分析号码的省，市，服务商，手机号就取前7位获得省市服务商，固话就取前3/4位，所以问题就来了，join 后面的条件有多个，要是sql是这样的  
select call\_bill.bill\_id,call\_bill.caller,phonearea.province,phonearea.city ,phonearea.isp   
from call\_bill left outer join phonearea  
on (substr(call\_bill .caller,1,7) = phonearea\_test.prefix  
or substr(call\_bill .caller,1,3)=phonearea.code);  
or substr(call\_bill .caller,1,4)=phonearea.code;

到hive上你会发现有错误   
尝试多种方法，应该用union all 这样

select id,caller,province,city,isp   
from ( select bi.bill\_id id,bi.caller caller,ph.province province,ph.city city,ph.isp isp   
            from  call\_bill bi left outer join phonearea ph on substr(bi.caller,1,7) = ph.prefix   
        UNION ALL select bi.bill\_id id,bi.caller caller,ph.province province,ph.city city,ph.isp isp   
                from  call\_bill bi left outer join phonearea ph on substr(bi.caller,1,3)=ph.code   
        UNION ALL select bi.bill\_id id,bi.caller caller,ph.province province,ph.city city,ph.isp isp   
                from  call\_bill bi left outer join phonearea ph on substr(bi.caller,1,4)=ph.code  
) tmp\_tmp GROUP BY id,caller,called,province,city,isp;

hive sql不支持 join on 的条件后跟or，使用union all，并去重插入表  
INSERT INTO TABLE tableName1 PARTITION (stat\_month='$\{hiveconf:stat\_month\}' , stat\_date='$\{hiveconf:stat\_date\}' , stat\_hour='$\{hiveconf:stat\_hour\}')  
       SELECT   
          COLLECT\_SET(字段名称)\[0\],COLLECT\_SET(字段名称)\[0\],COLLECT\_SET(字段名称)\[0\],.............COLLECT\_SET(字段名称)\[0\]  
 FROM(

SELECT A.字段,B.字段  
              FROM tableNameA A  
               LEFT JOIN tableNameB B ON  A.字段名称 =B.字段名称a  
               WHERE  stat\_month='$\{hiveconf:stat\_month\}'  and AND stat\_date='$\{hiveconf:stat\_date\}'  AND..............

UNION ALL

SELECT A.字段,B.字段  
              FROM tableNameA A  
               LEFT JOIN tableNameB B ON  A.字段名称 =B.字段名称b  
               WHERE  stat\_month='$\{hiveconf:stat\_month\}'  and AND stat\_date='$\{hiveconf:stat\_date\}'  AND..............  
      UNION ALL  
 SELECT A.字段,B.字段  
              FROM tableNameA A  
               LEFT JOIN tableNameB B ON  A.字段名称 =B.字段名称c  
               WHERE  stat\_month='$\{hiveconf:stat\_month\}'  and AND stat\_date='$\{hiveconf:stat\_date\}'  AND..............             
  )  newTableName GROUP BY 去重字段名称;

总结：

（1）group by 语句中出现的非聚合函数需要跟在group by后面。否则会报错

（2）可使用COLLECT\_SET()函数来封装不需要group by的字段【COLLECT\_SET(a\_id)\[0\]】可解决（1）的情况