Namit: wont he have to use some attribute to join on. Looks like he wants to figure out which pattern (b.ip) a given ip (a.client_ip) matches and get the attributes associated with that pattern (from b).
On 12/15/09 3:12 PM, "Namit Jain" <[email protected]> wrote: > 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
