在处理数据时,遇到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)的情况