Two questions:

1. How would one group by week (instead of date or time)?

My first idea was the following:

INSERT OVERWRITE TABLE platforms_weekly select platform, count(distinct
apikey), count(1), date_add(pdate, datediff('2007-12-31', pdate) / 7)
from shairlogs group by platform, datediff('2007-12-31', pdate) / 7
but the issue is that Hive can't know there's a subcalculation that is
the same in both group part and output part.

I tried to use AS statement with group expression, but no luck there [or
maybe I didn't know how].
2. When grouping by months there seems to be a problem of getting the
proper 'yyyy-mm' as part of output columns.

Here my idea was:
INSERT OVERWRITE TABLE platforms_monthly select platform, count(distinct
apikey), count(1), concat(concat(CAST(year(pdate) as STRING), '-'),
CAST(month(pdate) as STRING)) from shairlogs group by platform,
year(pdate), month(pdate)"

But the issue is that output yyyy-mm strings are not zero-padded for
months and lexical sort then breaks on them...

--
Andraz Tori, CTO
Zemanta Ltd, New York, London, Ljubljana
www.zemanta.com
mail: [EMAIL PROTECTED]
tel: +386 41 515 767
twitter: andraz, skype: minmax_test

Hive expects the expressions in the group by clause to be top level expressions in the select list

so

select platform, datediff('2007-12-31', pdate)/7, count....
from
group by platform, datediff('2007-12-31', pdate)/7

should work. You could also put the select list into a subquery and then do a group by on top of it.

For getting numeric sorting you can do an order by CAST(month(pdate) AS INT). That will probably work for you?

Ashish
________________________________________
From: Andraz Tori [[EMAIL PROTECTED]]
Sent: Friday, July 31, 2009 4:18 AM
To: [EMAIL PROTECTED]
Subject: Group by week?

Two questions:

1. How would one group by week (instead of date or time)?

My first idea was the following:

INSERT OVERWRITE TABLE platforms_weekly select platform, count(distinct
apikey), count(1), date_add(pdate, datediff('2007-12-31', pdate) / 7)
from shairlogs group by platform, datediff('2007-12-31', pdate) / 7
but the issue is that Hive can't know there's a subcalculation that is
the same in both group part and output part.

I tried to use AS statement with group expression, but no luck there [or
maybe I didn't know how].
2. When grouping by months there seems to be a problem of getting the
proper 'yyyy-mm' as part of output columns.

Here my idea was:
INSERT OVERWRITE TABLE platforms_monthly select platform, count(distinct
apikey), count(1), concat(concat(CAST(year(pdate) as STRING), '-'),
CAST(month(pdate) as STRING)) from shairlogs group by platform,
year(pdate), month(pdate)"

But the issue is that output yyyy-mm strings are not zero-padded for
months and lexical sort then breaks on them...

--
Andraz Tori, CTO
Zemanta Ltd, New York, London, Ljubljana
www.zemanta.com
mail: [EMAIL PROTECTED]
tel: +386 41 515 767
twitter: andraz, skype: minmax_test

Hi Ashish, thanks for help!

1) regarding the group-by-week, it was entirely my fault... I miswrote
the function... to get grouping by week one needs to have
FIELD: date_add('2007-12-31', (datediff(date_field, '2007-12-31') / 7) *
7)
GROUP BY: datediff(date_field, '2007-12-31') / 7

This then works and you get both the grouping and the first day of the
month as the field value

2) However this is not so simple for the month:

> For getting numeric sorting you can do an order by CAST(month(pdate)
AS INT). That will probably work for you?

This won't work since pdate by itself is a date string, not a number,
and even if it was then the month dates would be:
2008-4-20 which comes after 2008-10-20 when sorting lexically
Therefore the question is how to get zero padding with hive?
I have this expression:
concat(concat(concat(CAST(year(date_field) as STRING), '-'),
CAST(month(date_field) as STRING)), '-1')

which produces dates like:
2008-4-1
but I want zero-padding, which means
2008-04-1

How does one do that with hive?

Is there a function that would take separate year,month and day values
and return properly formated date?

[btw from my experience support for handling dates in hive is extremely
weak, and code becomes heavily convoluted to do it properly]
--
Andraz Tori, CTO
Zemanta Ltd, New York, London, Ljubljana
www.zemanta.com
mail: [EMAIL PROTECTED]
tel: +386 41 515 767
twitter: andraz, skype: minmax_test

We don't have such a udf right now, but it is extremely easy to write one.

Take a look at this (and search for UDF):
http://www.slideshare.net/ragho/hive-user-meeting-august-2009-facebook

Let us know if you have any questions.

Zheng

On Tue, Aug 4, 2009 at 7:21 AM, Andraz Tori<[EMAIL PROTECTED]> wrote:
> Hi Ashish, thanks for help!
>
> 1) regarding the group-by-week, it was entirely my fault... I miswrote the
> function... to get grouping by week one needs to have
> FIELD: date_add('2007-12-31', (datediff(date_field, '2007-12-31') / 7) * 7)
> GROUP BY: datediff(date_field, '2007-12-31') / 7
>
> This then works and you get both the grouping and the first day of the month
> as the field value
>
> 2) However this is not so simple for the month:
>
>> For getting numeric sorting you can do an order by CAST(month(pdate) AS
>> INT). That will probably work for you?
>
> This won't work since pdate by itself is a date string, not a number, and
> even if it was then the month dates would be:
> 2008-4-20 which comes after 2008-10-20 when sorting lexically
> Therefore the question is how to get zero padding with hive?
> I have this expression:
> concat(concat(concat(CAST(year(date_field) as STRING), '-'),
> CAST(month(date_field) as STRING)), '-1')
>
> which produces dates like:
> 2008-4-1
> but I want zero-padding, which means
> 2008-04-1
>
> How does one do that with hive?
>
> Is there a function that would take separate year,month and day values and
> return properly formated date?
>
> [btw from my experience support for handling dates in hive is extremely
> weak, and code becomes heavily convoluted to do it properly]
>
>
> --
> Andraz Tori, CTO
> Zemanta Ltd, New York, London, Ljubljana
> www.zemanta.com
> mail: [EMAIL PROTECTED]
> tel: +386 41 515 767
> twitter: andraz, skype: minmax_test
>
>
>

--
Yours,
Zheng

> [btw from my experience support for handling dates in hive is extremely
> weak, and code becomes heavily convoluted to do it properly]
>
That's the first thing I noticed -- lack of native date format. However, if
you mange the zero-padding while importing the data, storing dates as
strings works just fine.

I would recommend that instead of jumping hoops each time you need to run a
query.

Saurabh.
--
http://nandz.blogspot.com
http://foodieforlife.blogspot.com

Totally, if your input is sql date strings like 2009-08-04 while a generic
to_char with format string input could be added substr(pdate,1,7) is simple
means of extracting month -- tho instead of another udf it could be cool if
that could just be aliased to e.g. month_format() via config but I don't
think such is possible.

On Aug 4, 2009 8:47 PM, "Saurabh Nanda" <[EMAIL PROTECTED]> wrote:
> [btw from my experience support for handling dates in hive is extremely
weak, and code becomes hea...
That's the first thing I noticed -- lack of native date format. However, if
you mange the zero-padding while importing the data, storing dates as
strings works just fine.

I would recommend that instead of jumping hoops each time you need to run a
query.

Saurabh.
--
http://nandz.blogspot.com
http://foodieforlife.blogspot.com