You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Dave Houston <ro...@crankyadmin.net> on 2012/01/04 14:11:41 UTC
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
Re: Insert based on whether string contains
Posted by be...@yahoo.com.
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" <Ma...@disney.com>
Date: Wed, 4 Jan 2012 08:56:44
To: user@hive.apache.org<us...@hive.apache.org>; Bejoy Ks<be...@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" <be...@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 <ro...@crankyadmin.net>>
To: user@hive.apache.org<ma...@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<ma...@crankyadmin.net>
Re: Insert based on whether string contains
Posted by "Tucker, Matt" <Ma...@disney.com>.
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" <be...@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 <ro...@crankyadmin.net>>
To: user@hive.apache.org<ma...@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<ma...@crankyadmin.net>
Re: Insert based on whether string contains
Posted by Bejoy Ks <be...@yahoo.com>.
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 <ro...@crankyadmin.net>
To: 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