Sorry table A has around 3,000,000 records not 200,000. Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________ From: Namit Jain [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:39 PM To: [email protected] Subject: RE: LIKE operator Can you partition the data in A and B and then join the partitions and then union all them. From: Sagi, Lee [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:35 PM To: [email protected] Subject: RE: LIKE operator Yes, currently I create the table first than insert (just wanted to have less code/scripts). And yes, this query/join is painfully slow, but I need to flag records that match an IP pattern...I'm open for suggestions. Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947 ________________________________ From: Namit Jain [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:27 PM To: [email protected] Subject: RE: LIKE operator I should clarify that this is not the most efficient way of doing it - since we are doing a Cartesian product first (which will go to 1 reducer). We do support CREATE TABLE AS SELECT in hive trunk, but it is not part of 0.4 You can create the table and then insert into that table. -namit From: Sagi, Lee [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:18 PM To: [email protected] Subject: RE: LIKE operator Thanks your re-write works perfectly. Allow me to piggy-back and ask a follow-up question, how can I create a table as the result of this query? I tired the obvious CREATE TABLE AAA AS SELECT .... with not success. Thanks. Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947 ________________________________ From: Namit Jain [mailto:[email protected]] Sent: Tuesday, December 15, 2009 3:12 PM To: [email protected] Subject: RE: LIKE operator 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
