hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bejoy...@yahoo.com
Subject Re: Insert based on whether string contains
Date Wed, 04 Jan 2012 14:05:56 GMT
I agree with Matt on that aspect. The solution proposed by me was purely based on the sample
data provided where there were  3 digit comma separated values. If there are chances of 4
digit values as well in event_list you may need to revisit the solution.

Regards
Bejoy K S

-----Original Message-----
From: "Tucker, Matt" <Matt.Tucker@disney.com>
Date: Wed, 4 Jan 2012 08:56:44 
To: user@hive.apache.org<user@hive.apache.org>; Bejoy Ks<bejoy_ks@yahoo.com>
Reply-To: user@hive.apache.org
Subject: Re: Insert based on whether string contains 

The find_in_set() UDF is a safer choice for doing a search for that value, as %239% could
also match 2390, which has a different meaning in Omniture logs.



On Jan 4, 2012, at 8:46 AM, "Bejoy Ks" <bejoy_ks@yahoo.com<mailto:bejoy_ks@yahoo.com>>
wrote:

Hi Dave

       If I get your requirement correct, you need to load data into video_plays_for_sept
 table FROM omniture table only if omniture.event_list contain the string 239.

Try the following query, it should work fine.

INSERT OVERWRITE TABLE video_plays_for_sept
SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region FROM omniture WHERE
event_list LIKE  ‘%239%’;

Hope it helps!..

Regards,
Bejoy.K.S

________________________________
From: Dave Houston <root@crankyadmin.net<mailto:root@crankyadmin.net>>
To: user@hive.apache.org<mailto:user@hive.apache.org>
Sent: Wednesday, January 4, 2012 6:41 PM
Subject: Insert based on whether string contains

Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want
to insert into another table if 239 is in the string.

INSERT OVERWRITE TABLE video_plays_for_sept

SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where
regexp_extract(event_list, '\d+') = "239";

is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept


Many thanks

Dave Houston
root@crankyadmin.net<mailto:root@crankyadmin.net>





Mime
View raw message