hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ning Zhang <nzh...@facebook.com>
Subject Re: Help with last 30 day unique user query
Date Fri, 15 Oct 2010 17:18:46 GMT
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 <nzhang@facebook.com<mailto:nzhang@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
>




Mime
View raw message