Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Insert based on whether string contains


+
Dave Houston 2012-01-04, 13:11
+
Bejoy Ks 2012-01-04, 13:45
+
Tucker, Matt 2012-01-04, 13:56
Copy link to this message
-
Re: Insert based on whether string contains
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" <[EMAIL PROTECTED]>
Date: Wed, 4 Jan 2012 08:56:44
To: [EMAIL PROTECTED]<[EMAIL PROTECTED]>; Bejoy Ks<[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
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
[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>

NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB