Some questions on hive SELECT/UNION - how to do multiple counts in one query?

[Vijay <te...@gmail.com>]

Hi,

I have some questions about using SELECT with UNION. I have a number of
access log files that I want to process to select counts per day matching a
specific pattern in the URL. This is very simple with a select:

SELECT day, count(1) FROM accesslogs WHERE request like '%foo%' group by day
order by day;

However, when I need to count multiple patterns (count of requests like
'%foo%', like '%bar%', etc), I'm not able to find the right query. I made
some attempts with UNION ALL and such but none of them really worked.
Ultimately I'm trying to get some numbers like this:

<day>, <count matching %foo%>, <count matching %bar%>

Is there a way of doing this with a single query? I know I can do it with
multiple queries and such but that seems like such a waste of time and
resources.

I hope my question is clear.

Thanks,
Vijay

Re: Some questions on hive SELECT/UNION - how to do multiple counts in one query?

[Vijay <te...@gmail.com>]

Thank you so much Zheng! You made my day!! I should've spent some time
brushing up on SQL :)

On Thu, Aug 13, 2009 at 4:48 PM, Zheng Shao <zs...@gmail.com> wrote:

> SELECT day,
>  SUM(IF(request like '%foo%', 1, 0)),
>  SUM(IF(request like '%bar%', 1, 0))
> FROM accesslogs
> group by day
> order by day;
>
>
> On Thu, Aug 13, 2009 at 4:42 PM, Vijay<te...@gmail.com> wrote:
> > Hi,
> >
> > I have some questions about using SELECT with UNION. I have a number of
> > access log files that I want to process to select counts per day matching
> a
> > specific pattern in the URL. This is very simple with a select:
> >
> > SELECT day, count(1) FROM accesslogs WHERE request like '%foo%' group by
> day
> > order by day;
> >
> > However, when I need to count multiple patterns (count of requests like
> > '%foo%', like '%bar%', etc), I'm not able to find the right query. I made
> > some attempts with UNION ALL and such but none of them really worked.
> > Ultimately I'm trying to get some numbers like this:
> >
> > <day>, <count matching %foo%>, <count matching %bar%>
> >
> > Is there a way of doing this with a single query? I know I can do it with
> > multiple queries and such but that seems like such a waste of time and
> > resources.
> >
> > I hope my question is clear.
> >
> > Thanks,
> > Vijay
> >
> >
>
>
>
> --
> Yours,
> Zheng
>

Re: Some questions on hive SELECT/UNION - how to do multiple counts in one query?

[Zheng Shao <zs...@gmail.com>]

SELECT day,
  SUM(IF(request like '%foo%', 1, 0)),
  SUM(IF(request like '%bar%', 1, 0))
FROM accesslogs
group by day
order by day;


On Thu, Aug 13, 2009 at 4:42 PM, Vijay<te...@gmail.com> wrote:
> Hi,
>
> I have some questions about using SELECT with UNION. I have a number of
> access log files that I want to process to select counts per day matching a
> specific pattern in the URL. This is very simple with a select:
>
> SELECT day, count(1) FROM accesslogs WHERE request like '%foo%' group by day
> order by day;
>
> However, when I need to count multiple patterns (count of requests like
> '%foo%', like '%bar%', etc), I'm not able to find the right query. I made
> some attempts with UNION ALL and such but none of them really worked.
> Ultimately I'm trying to get some numbers like this:
>
> <day>, <count matching %foo%>, <count matching %bar%>
>
> Is there a way of doing this with a single query? I know I can do it with
> multiple queries and such but that seems like such a waste of time and
> resources.
>
> I hope my question is clear.
>
> Thanks,
> Vijay
>
>



-- 
Yours,
Zheng