Hi Jan,

I have date in different format= also, so that is the reason I was thinking to do by this approach. How can= I make sure this will work on the selected partition only and it will not = scan the entire table. I will add your suggestion in my UDF as deterministi= c thing.

My simple question here is- How to get the Yesterdays d= ate which I can use on the Date Partition I cannot use hiveconf here as I a= m working with Hive 0.6

Raihan Jamal

On Tue, Aug 7, 2012 at 10:37 AM, Jan Dol= in=E1r <dolik.rce@gmail.com> wrote:

I'm afraid that =A0he query

SELECT * FROM REALTIME where d= t=3D yesterdaydate('yyyyMMdd') LIMIT 10;

will scan entire table, because the functions is evaluated a= t runtime, so Hive doesn't know what the value is when it decides which= files to scan. I am not 100% sure though, you should try it.=A0

Also, you might want to try to add annotation to your UDF saying= that the function is deterministic:
@UDFType(deterministic=3Dfalse<= /em>)<= /div>

I think Hive might be clever enough to evaluate it earl= y enough to use the partition pruning correctly, since it operates on const= ant expression. But again, I'm not really sure, maybe someone with deep= er knowledge of Hive optimizations will tell us more. It is actually quite = interesting question.

Another way to help Hive with the optimizations might b= e to skip passing the format string argument, if you have all dates in same= format, you can call the function just like 'yesterdaydate()' and = hardcode the format in the function.

Jan

=

On Tue, Aug 7, 2012 at 7:20 PM, R= aihan Jamal <jamalraihan@gmail.com> wrote:

Hi Jan,

=A0
I figured that out, it is working fine for me now.=A0The only question I have is, if I am doing like this-

=A0

SELECT * FROM REALTIME wh= ere dt=3D yesterdaydate('yyyyMMdd') LIMIT 10;

=A0

Then the above quer= y will be evaluated as below right?

=A0

SELECT * FROM REALTIME wh= ere dt=3D =9120120806=92 LIMIT 10;

=A0

So that means it will loo= k for data in the corresponding dt partition (20120806) only right as above table is partitioned on dt = column ? And it will not scan the whole table right?

Raihan Jamal

On Mon, Aug 6, 2012 a= t 10:56 PM, Jan Dolin=E1r <dolik.rce@gmail.com> wrote:

Hi Jamal,

Check if the function really returns what it s= hould and that your data are really in yyyyMMdd format. You can do this by = simple query like this:

SELECT dt,=A0yesterdaydate('yyyyM= Mdd')=A0FROM REALTIME LIMIT 1;

I don't see anything wrong with the function itself= , it works well for me (although I tested it in hive 0.7.1). The only thing= I would change about it would be to optimize it by calling 'new' o= nly at the time of construction and reusing the object when the function is= called, but that should not affect the functionality at all.=

Best regards,

Ja= n

On Tue, Aug 7, 2012 at 3:39 AM, Raihan Jamal <<= a href=3D"mailto:jamalraihan@gmail.com" target=3D"_blank">jamalraihan@gmail= .com> wrote:

Problem

I created the below UserD= efinedFunction to get the yesterday's day in the format I wanted as I will be passing the format into this below method from the query.

=A0

public final class Yes= terdayDate extends UDF {

=A0

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 public String evaluate(final String format) {

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 DateFormat dateFormat =3D new SimpleDateFormat(format);

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Calendar cal =3D Calendar.getInstance();

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 cal.add(Calendar.DATE, -1);=A0=A0=A0=A0

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 return dateFormat.format(cal.getTime()).toString();

=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 }

}

=A0

=A0

So whenever I try to run = the query like below by adding the jar to classpath and creating the temporary function yesterdaydate, I always ge= t zero result back-

=A0

hive> create temporary= function yesterdaydate as 'com.example.hive.udf.YesterdayDate';

OK

Time taken: 0.512 seconds=

=A0

Below is the query I am r= unning-

=A0

hive> SELECT * FROM= REALTIME where dt=3D yesterdaydate('yyyyMMdd') LIMIT 10;

OK

=A0

And I always get zero res= ult back but the data is there in that table for Aug 5^th.
=A0

What wrong I am doing? An= y suggestions will be appreciated.

=A0

=A0

NOTE:- As I am working wi= th Hive 0.6 so it doesn=92t support variable substitution thing, so I cannot use hiveconf here and the above ta= ble has been partitioned on dt(date) column.