hive对有特殊值null的数据倾斜处理
对有特殊值的数据倾斜处理
SET hive.map.aggr=TRUE;
SET hive.groupby.skewindata=TRUE;
SET hive.optimize.skewjoin=TRUE;
set hive.auto.convert.join=false;
INSERT OVERWRITE TABLE HIS_RFD_SYMID_RESULT
PARTITION(tid='wcj01')
SELECT aa.MAC,aa.phoneNo,bb.member_type,bb.xx_mid
FROM
( SELECT mr.MAC,phoneNo FROM MAC_RFD_RESULT_VALUES mr WHERE mr.tid = 'wcj01') aa
LEFT OUTER JOIN
( SELECT mac,mobile,member_type,xx_mid
FROM member m
WHERE m.id >= 'wcj01' AND m.id <= 'wcj01}' AND m.mobile IS NOT NULL ) bb
ON CASE WHEN aa.phoneNo = 'null' THEN CONCAT(RAND(),'hive') WHEN aa.phoneNo IS NULL THEN CONCAT(RAND(),'hive') WHEN length(aa.phoneNo)= 0 THEN CONCAT(RAND(),'hive') ELSE aa.phoneNo END = bb.mobile;
<!--
set hive.fetch.task.conversion=more;
-->