Hive only supports equality joins right now: INSERT OVERWRITE TABLE ZZ SELECT VOTF_REQUEST_ID , THRESHOLD_VALUE , THRESHOLD_MET , BRAND_ID FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP) WHERE date_key = '2009121315';
Can be rewritten as: INSERT OVERWRITE TABLE ZZ SELECT VOTF_REQUEST_ID , THRESHOLD_VALUE , THRESHOLD_MET , BRAND_ID FROM A LEFT OUTER JOIN B WHERE date_key = '2009121315' and A.CLIENT_IP LIKE B.IP; From: Sagi, Lee [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:09 PM To: [email protected] Subject: LIKE operator I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B: INSERT OVERWRITE TABLE ZZ SELECT VOTF_REQUEST_ID , THRESHOLD_VALUE , THRESHOLD_MET , BRAND_ID FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP) WHERE date_key = '2009121315'; B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc. I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP Any ideas? Thanks. Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
