Mailing-List: contact hive-user-help@hadoop.apache.org; run by ezmlm
Precedence: bulk
Reply-To: hive-user@hadoop.apache.org
Received-SPF: pass (nike.apache.org: domain of saurabhnanda@gmail.com
 designates 209.85.221.188 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=nNh6Av5khEN89M76Ec4UX/MiCG505iBZevjIuqEEyl+X3OHGcinffVjYS8o8vC7A6V
         G48ykOpCacj8K04ZkMZ7VnmUxYZ4HtuMyTrBw3uBSR5MwRcIcteYs1IpP+zYIrtuDw+A
         B3Z9OXHgPMX1+R0qVBxRaCQYnvsCbBnqWxJWs=
MIME-Version: 1.0
In-Reply-To: <1248719723.6541.113.camel@minmax-laptop>
References: <1248719723.6541.113.camel@minmax-laptop>
Date: Tue, 28 Jul 2009 00:29:14 +0530
Message-ID: <794f042d0907271159j7f8c0335j132eb23985773ee9@mail.gmail.com>
Subject: Re: counting different regexes in a single pass
From: Saurabh Nanda <saurabhnanda@gmail.com>
To: hive-user@hadoop.apache.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

I think you can do that with regex replace and GROUP BY. Something
like this- select replaced_col, count(1) from (select
regex_replace(original_col, '.*(text1|text2|text3).*', '$1') as
replaced_col from table)

On 7/28/09, Andraz Tori <andraz@zemanta.com> wrote:
> Hi,
> I am a beginner at Hive's SQL so I am sorry if this question is answered
> somewhere else. I tried to find the answer in Wiki, but no luck.
>
> I have a dataset in which one of the columns is text. I need to count
> number of records that match certain regex on that column. There are a
> number of different regexes that I need to count records for and it
> seems that there should be a way to do it in one pass through the data
> with hive.
>
> So how do I do it?
>
> This doesn't seem to work (on Hive 0.3):
> select sum(textcolumn LIKE "%%sometext%%") as CountA, sum(textcolumn
> LIKE "%%othertext%%") as CountB from sometable;
>
>
> Thank you for your help in advance!
>
>
> [unrelated: We've written a simple AWS S3 log format deserializer for
> Hive. It needs some polishing - if anyone needs it or wants to polish it
> up for inclusion, let me know]
>
> --
> Andraz Tori, CTO
> Zemanta Ltd, New York, London, Ljubljana
> www.zemanta.com
> mail: andraz@zemanta.com
> tel: +386 41 515 767
> =EF=BB=BFtwitter: andraz, skype: minmax_test
>
>
>
>


--=20
http://nandz.blogspot.com
http://foodieforlife.blogspot.com