You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Vijay <te...@gmail.com> on 2010/10/15 08:36:25 UTC
Help with last 30 day unique user query
Hi, I need help with this scenario. We have a table of events which has
columns date, event (not important for this discussion), and user_id. It is
obviously easy to find number of unique users for each day. I also need to
find number of unique users in the last 30 days for each day. This is also
quite simple to do for one day. However, I cannot figure out how to do this
for a range of days. Something like this is pretty straightforward in most
RDBMS but with HiveQL has I'm finding this hard. I might be missing
something simple though. Any help is appreciated. Ideally the query should
also be as optimized as possible as this table could be huge.
Thanks,
Vijay
Re: Help with last 30 day unique user query
Posted by Ning Zhang <nz...@facebook.com>.
Sorry I don't understand your question. I thought you were referring to the lack of DATE type in Hive. HiveQL has the similar syntax with SQL like count(distinct col). Your regular SQL query should work together with the help of UDFs I mentioned.
On Oct 15, 2010, at 9:43 AM, Vijay wrote:
Thanks, Ning! Finding the date which is 30 days before/later was easy enough but my problem is beyond that. I need to find unique users based on these last 30 days for a range of days. Does that make sense?
On Fri, Oct 15, 2010 at 12:10 AM, Ning Zhang <nz...@facebook.com>> wrote:
There are some UDFs that convert a string to epoch time and back to a string. e.g.,
select from_unixtime(unix_timestamp('2010-10-10', 'yyyy-MM-dd') + 60*60*24*30, 'yyyy-MM-dd') from src limit 1;
will given you the date which is 30 days later than 2010-10-10.
On Oct 14, 2010, at 11:36 PM, Vijay wrote:
> Hi, I need help with this scenario. We have a table of events which has columns date, event (not important for this discussion), and user_id. It is obviously easy to find number of unique users for each day. I also need to find number of unique users in the last 30 days for each day. This is also quite simple to do for one day. However, I cannot figure out how to do this for a range of days. Something like this is pretty straightforward in most RDBMS but with HiveQL has I'm finding this hard. I might be missing something simple though. Any help is appreciated. Ideally the query should also be as optimized as possible as this table could be huge.
>
> Thanks,
> Vijay
>
Re: Help with last 30 day unique user query
Posted by Vijay <te...@gmail.com>.
Thanks, Ning! Finding the date which is 30 days before/later was easy enough
but my problem is beyond that. I need to find unique users based on these
last 30 days for a range of days. Does that make sense?
On Fri, Oct 15, 2010 at 12:10 AM, Ning Zhang <nz...@facebook.com> wrote:
> There are some UDFs that convert a string to epoch time and back to a
> string. e.g.,
>
> select from_unixtime(unix_timestamp('2010-10-10', 'yyyy-MM-dd') +
> 60*60*24*30, 'yyyy-MM-dd') from src limit 1;
>
> will given you the date which is 30 days later than 2010-10-10.
>
> On Oct 14, 2010, at 11:36 PM, Vijay wrote:
>
> > Hi, I need help with this scenario. We have a table of events which has
> columns date, event (not important for this discussion), and user_id. It is
> obviously easy to find number of unique users for each day. I also need to
> find number of unique users in the last 30 days for each day. This is also
> quite simple to do for one day. However, I cannot figure out how to do this
> for a range of days. Something like this is pretty straightforward in most
> RDBMS but with HiveQL has I'm finding this hard. I might be missing
> something simple though. Any help is appreciated. Ideally the query should
> also be as optimized as possible as this table could be huge.
> >
> > Thanks,
> > Vijay
> >
>
>
Re: Help with last 30 day unique user query
Posted by Ning Zhang <nz...@facebook.com>.
There are some UDFs that convert a string to epoch time and back to a string. e.g.,
select from_unixtime(unix_timestamp('2010-10-10', 'yyyy-MM-dd') + 60*60*24*30, 'yyyy-MM-dd') from src limit 1;
will given you the date which is 30 days later than 2010-10-10.
On Oct 14, 2010, at 11:36 PM, Vijay wrote:
> Hi, I need help with this scenario. We have a table of events which has columns date, event (not important for this discussion), and user_id. It is obviously easy to find number of unique users for each day. I also need to find number of unique users in the last 30 days for each day. This is also quite simple to do for one day. However, I cannot figure out how to do this for a range of days. Something like this is pretty straightforward in most RDBMS but with HiveQL has I'm finding this hard. I might be missing something simple though. Any help is appreciated. Ideally the query should also be as optimized as possible as this table could be huge.
>
> Thanks,
> Vijay
>
Re: Help with last 30 day unique user query
Posted by Vijay <te...@gmail.com>.
Thanks Alex! That is exactly what I thought was the limitation but wanted to
make sure I'm not missing anything.
On Fri, Oct 15, 2010 at 10:51 AM, Alex Boisvert <al...@bizo.com> wrote:
> As far as I know, Hive has no built-in support for sliding-window
> analytics. There is an enhancement request here:
> https://issues.apache.org/jira/browse/HIVE-896
>
> <https://issues.apache.org/jira/browse/HIVE-896>Without such support, the
> brute force way of doing things is,
>
> SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date > start_date
> and event_date <= end_date;
>
> (repeated N times to cover each day of your time window).
>
> alex
>
> On Thu, Oct 14, 2010 at 11:36 PM, Vijay <te...@gmail.com> wrote:
>
>> Hi, I need help with this scenario. We have a table of events which has
>> columns date, event (not important for this discussion), and user_id. It is
>> obviously easy to find number of unique users for each day. I also need to
>> find number of unique users in the last 30 days for each day. This is also
>> quite simple to do for one day. However, I cannot figure out how to do this
>> for a range of days. Something like this is pretty straightforward in most
>> RDBMS but with HiveQL has I'm finding this hard. I might be missing
>> something simple though. Any help is appreciated. Ideally the query should
>> also be as optimized as possible as this table could be huge.
>>
>> Thanks,
>> Vijay
>>
>>
>
Re: Help with last 30 day unique user query
Posted by Alex Boisvert <al...@bizo.com>.
As far as I know, Hive has no built-in support for sliding-window analytics.
There is an enhancement request here:
https://issues.apache.org/jira/browse/HIVE-896
<https://issues.apache.org/jira/browse/HIVE-896>Without such support, the
brute force way of doing things is,
SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date > start_date
and event_date <= end_date;
(repeated N times to cover each day of your time window).
alex
On Thu, Oct 14, 2010 at 11:36 PM, Vijay <te...@gmail.com> wrote:
> Hi, I need help with this scenario. We have a table of events which has
> columns date, event (not important for this discussion), and user_id. It is
> obviously easy to find number of unique users for each day. I also need to
> find number of unique users in the last 30 days for each day. This is also
> quite simple to do for one day. However, I cannot figure out how to do this
> for a range of days. Something like this is pretty straightforward in most
> RDBMS but with HiveQL has I'm finding this hard. I might be missing
> something simple though. Any help is appreciated. Ideally the query should
> also be as optimized as possible as this table could be huge.
>
> Thanks,
> Vijay
>
>