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 zshao9@gmail.com designates
 209.85.211.187 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;
        b=lhn1Xy0zQB5ji0dzLx8rVRW+MxT5l8WZoqtS3uvVd8Hhw70U97s/aBvEUTmMCdSw2w
         tIzh5ZplbcXBjiNCy2ErrtZbLxFa4hY1YefTyg9I0U++CzOUb+ZKKd9wopjdXrz2eGx3
         vm3kmXSvC/hsyEOb0rKVWm8/OQPd50C3RoS9k=
MIME-Version: 1.0
In-Reply-To: <6acdaf2a0910101142k568b0be4t2e2a505ab8f8ac4b@mail.gmail.com>
References: <6acdaf2a0910101142k568b0be4t2e2a505ab8f8ac4b@mail.gmail.com>
Date: Sat, 10 Oct 2009 11:47:57 -0700
Message-ID: <34fd060d0910101147s643f1a1am59573ef8a4fdac51@mail.gmail.com>
Subject: Re: Multiple aggregated metrics in one query
From: Zheng Shao <zshao9@gmail.com>
To: hive-user@hadoop.apache.org
Content-Type: multipart/alternative; boundary=001636ed65f7916fe50475992296

--001636ed65f7916fe50475992296
Content-Type: text/plain; charset=ISO-8859-1

Yes, we can do this:

SELECT ip, SUM(IF(action = 'action1', 1, 0)), SUM(IF(action = 'action2', 1,
0)), SUM(IF(action = 'action3', 1, 0))
FROM mytable
GROUP BY ip;

For more details on IF, please refer to:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

Zheng

On Sat, Oct 10, 2009 at 11:42 AM, Ryan LeCompte <lecompte@gmail.com> wrote:

> Hello all,
>
> Very newto Hive (haven't even installed it yet!), but I had a use case that
> I didn't see demonstrated in any of the tutorial/documentation that I've
> read thus far.
>
> Let's say that I have apache logs that I want to process with Hadoop/Hive.
> Of course there may be different types of log records all tying back to the
> same user or IP address or other log attribute. Is there a way to submit a
> SINGLE Hive query to get back results that may look like:
>
>
> IP Action1Count Action2Count Action3Count
>
> .. where the different actions correspond to different log events for that
> IP address.
>
> Do I have to submit 3 different Hive queries here or can I submit a single
> Hive query? In a regular Java-based map/reduce job, I would have written a
> custom Writable that would record counts for each of the different actions,
> and submit it to the reducer using output.collect(IP, customWritable). Here
> I wouldn't have to submit multiple map/reduce jobs, just 1.
>
> Thanks
> Ryan
>
>


-- 
Yours,
Zheng

--001636ed65f7916fe50475992296
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Yes, we can do this:<br><br>SELECT ip, SUM(IF(action =3D &#39;action1&#39;,=
 1, 0)), SUM(IF(action =3D &#39;action2&#39;, 1, 0)), SUM(IF(action =3D =
9;action3&#39;, 1, 0))<br>FROM mytable<br>GROUP BY ip;<br><br>For more deta=
ils on IF, please refer to: <a href=3D"http://dev.mysql.com/doc/refman/5.0/=
en/control-flow-functions.html#function_if">http://dev.mysql.com/doc/refman=
/5.0/en/control-flow-functions.html#function_if</a><br>
<br>Zheng<br><br><div class=3D"gmail_quote">On Sat, Oct 10, 2009 at 11:42 A=
M, Ryan LeCompte <span dir=3D"ltr">&lt;<a href=3D"mailto:lecompte@gmail.com=
">lecompte@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_qu=
ote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0p=
t 0.8ex; padding-left: 1ex;">
Hello all,<br><br>Very newto Hive (haven&#39;t even installed it yet!), but=
 I had a use case that I didn&#39;t see demonstrated in any of the tutorial=
/documentation that I&#39;ve read thus far.<br><br>Let&#39;s say that I hav=
e apache logs that I want to process with Hadoop/Hive. Of course there may =
be different types of log records all tying back to the same user or IP add=
ress or other log attribute. Is there a way to submit a SINGLE Hive query t=
o get back results that may look like:<br>

<br><br>IP Action1Count Action2Count Action3Count<br><br>.. where the diffe=
rent actions correspond to different log events for that IP address.<br><br=
>Do I have to submit 3 different Hive queries here or can I submit a single=
 Hive query? In a regular Java-based map/reduce job, I would have written a=
 custom Writable that would record counts for each of the different actions=
, and submit it to the reducer using output.collect(IP, customWritable). He=
re I wouldn&#39;t have to submit multiple map/reduce jobs, just 1.<br>

<br>Thanks<br><font color=3D"#888888">Ryan<br><br>
</font></blockquote></div><br><br clear=3D"all"><br>-- <br>Yours,<br>Zheng<=
br>

--001636ed65f7916fe50475992296--