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 Threaded View
Hive >> mail # user >> Some questions on hive SELECT/UNION - how to do multiple counts in one query?


Copy link to this message
-
Some questions on hive SELECT/UNION - how to do multiple counts in one query?
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
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