You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Techy Teck <co...@gmail.com> on 2012/08/06 20:10:31 UTC

(Get the current date -1) in Hive

Is there any way to get the current date -1 in Hive means yesterdays date
always?

Re: (Get the current date -1) in Hive

Posted by Techy Teck <co...@gmail.com>.
Hi Venkatesh,

So my shell script should be like this right?

*#!/bin/bash*
*HADOOP_HOME=/home/hadoop/latest*
*export HADOOP_HOME*
*JAVA_HOME=/usr/jdk/latest*
*export JAVA_HOME*
*HIVE_OPTS="$HIVE_OPTS -hiveconf mapred.job.queue.name=hdmi-technology"*
*export HIVE_OPTS*
*DATE_YEST=`TZ=GMT+48 date +%Y%m%d`*
*echo $DATE_YEST*
*hive -S -e "SELECT * FROM REALTIME WHERE dt='$DATE_YEST' LIMIT 5"*





On Tue, Aug 7, 2012 at 1:28 PM, Venkatesh Kavuluri <vk...@outlook.com>wrote:

> Since the UDF unix_timestamp() is a non-deterministic function, Hive query
> planner doesn't run partition pruning based the 'dt' column value. If your
> table is partitioned by 'dt' column, the query would end up scanning entire
> table.
>
> It is ideal to compute the required date value dynamically in a shell
> script if you are working against partitioned columns. If your version of
> Hive doesn't support variable substitution based on hiveconf, you can
> achieve the same as shown below
>
> hive -e "SELECT .... FROM <table> where dt=$date_var;"
>
> Thanks,
> Venkatesh
>
>
> ------------------------------
> From: carla.staeben@nokia.com
> To: user@hive.apache.org
> Subject: RE: (Get the current date -1) in Hive
> Date: Mon, 6 Aug 2012 23:26:46 +0000
>
>
>  In the case here it literally is taking the UNIX timestamp, formatting
> it in yyyy-mm-dd format and then subtracting the specified integer (in this
> case 1)
>
> Sent from my Lumia 900
>  ------------------------------
> From: ext Techy Teck
> Sent: 8/6/2012 3:37 PM
> To: user@hive.apache.org
> Subject: Re: (Get the current date -1) in Hive
>
> Thanks Carla for the suggestion, I am currently using Hive 0.6 and that
> Hive version doesn't supports variable substitution with hiveconf variable,
> so that is the reason I was looking for some other alternative-
>
>  So you are saying basically, If I add your suggestion in my query like
> below-
>
>  *select * from lip_data_quality where dt =
> 'date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)';*
> *
> *
> Then the above query will be interpreted as like below-
>
>  *select * from lip_data_quality where dt = '2012-08-05';*
> *
> *
> *
> *
> Am I right? And what does *date_sub* do here? I am not familiar with that.
>
>  Correct me if I am wrong.
>
>
>
>  *
> *
>
>
> On Mon, Aug 6, 2012 at 12:23 PM, <ca...@nokia.com> wrote:
>
> If you are just using it in a query, you can do this:
> date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)
>
> I generally do my date calculations in a shell script and pass them in
> with a hiveconf variable.
>
> Carla
>
> -----Original Message-----
> From: ext Yue Guan [mailto:pipehappy@gmail.com]
> Sent: Monday, August 06, 2012 15:00
> To: user@hive.apache.org
> Subject: Re: (Get the current date -1) in Hive
>
> guess you can use sub_date, but you have to get today by some outside
> script.
>
> On 08/06/2012 02:10 PM, Techy Teck wrote:
> > Is there any way to get the current date -1 in Hive means yesterdays
> > date always?
> >
> >
> >
>
>
>

RE: (Get the current date -1) in Hive

Posted by Venkatesh Kavuluri <vk...@outlook.com>.
Since the UDF unix_timestamp() is a non-deterministic function, Hive query planner doesn't run partition pruning based the 'dt' column value. If your table is partitioned by 'dt' column, the query would end up scanning entire table.
It is ideal to compute the required date value dynamically in a shell script if you are working against partitioned columns. If your version of Hive doesn't support variable substitution based on hiveconf, you can achieve the same as shown below
hive -e "SELECT .... FROM <table> where dt=$date_var;"
Thanks,Venkatesh

From: carla.staeben@nokia.com
To: user@hive.apache.org
Subject: RE: (Get the current date -1) in Hive
Date: Mon, 6 Aug 2012 23:26:46 +0000







In the case here it literally is taking the UNIX timestamp, formatting it in yyyy-mm-dd format and then subtracting the specified integer (in this case 1)




Sent from my Lumia 900




From:
ext Techy Teck

Sent:
8/6/2012 3:37 PM

To:
user@hive.apache.org

Subject:
Re: (Get the current date -1) in Hive



Thanks Carla for the suggestion, I am currently using Hive 0.6 and that Hive version doesn't supports variable substitution with hiveconf variable, so that is the reason I was looking for some other alternative-



So you are saying basically, If I add your suggestion in my query like below-



select * from lip_data_quality where dt = 'date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)';



Then the above query will be interpreted as like below-




select * from lip_data_quality where dt = '2012-08-05';






Am I right? And what does date_sub do here? I am not familiar with that.



Correct me if I am wrong.















On Mon, Aug 6, 2012 at 12:23 PM, <ca...@nokia.com> wrote:


If you are just using it in a query, you can do this:

date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)



I generally do my date calculations in a shell script and pass them in with a hiveconf variable.



Carla




-----Original Message-----

From: ext Yue Guan [mailto:pipehappy@gmail.com]

Sent: Monday, August 06, 2012 15:00

To: user@hive.apache.org

Subject: Re: (Get the current date -1) in Hive



guess you can use sub_date, but you have to get today by some outside script.



On 08/06/2012 02:10 PM, Techy Teck wrote:

> Is there any way to get the current date -1 in Hive means yesterdays

> date always?

>

>

>










 		 	   		  

RE: (Get the current date -1) in Hive

Posted by ca...@nokia.com.
In the case here it literally is taking the UNIX timestamp, formatting it in yyyy-mm-dd format and then subtracting the specified integer (in this case 1)

Sent from my Lumia 900
________________________________
From: ext Techy Teck
Sent: 8/6/2012 3:37 PM
To: user@hive.apache.org
Subject: Re: (Get the current date -1) in Hive

Thanks Carla for the suggestion, I am currently using Hive 0.6 and that Hive version doesn't supports variable substitution with hiveconf variable, so that is the reason I was looking for some other alternative-

So you are saying basically, If I add your suggestion in my query like below-

select * from lip_data_quality where dt = 'date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)';

Then the above query will be interpreted as like below-

select * from lip_data_quality where dt = '2012-08-05';


Am I right? And what does date_sub do here? I am not familiar with that.

Correct me if I am wrong.






On Mon, Aug 6, 2012 at 12:23 PM, <ca...@nokia.com>> wrote:
If you are just using it in a query, you can do this:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)

I generally do my date calculations in a shell script and pass them in with a hiveconf variable.

Carla

-----Original Message-----
From: ext Yue Guan [mailto:pipehappy@gmail.com<ma...@gmail.com>]
Sent: Monday, August 06, 2012 15:00
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Re: (Get the current date -1) in Hive

guess you can use sub_date, but you have to get today by some outside script.

On 08/06/2012 02:10 PM, Techy Teck wrote:
> Is there any way to get the current date -1 in Hive means yesterdays
> date always?
>
>
>



Re: (Get the current date -1) in Hive

Posted by Techy Teck <co...@gmail.com>.
Thanks Carla for the suggestion, I am currently using Hive 0.6 and that
Hive version doesn't supports variable substitution with hiveconf variable,
so that is the reason I was looking for some other alternative-

So you are saying basically, If I add your suggestion in my query like
below-

*select * from lip_data_quality where dt =
'date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)';*
*
*
Then the above query will be interpreted as like below-

*select * from lip_data_quality where dt = '2012-08-05';*
*
*
*
*
Am I right? And what does *date_sub* do here? I am not familiar with that.

Correct me if I am wrong.



*
*


On Mon, Aug 6, 2012 at 12:23 PM, <ca...@nokia.com> wrote:

> If you are just using it in a query, you can do this:
> date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)
>
> I generally do my date calculations in a shell script and pass them in
> with a hiveconf variable.
>
> Carla
>
> -----Original Message-----
> From: ext Yue Guan [mailto:pipehappy@gmail.com]
> Sent: Monday, August 06, 2012 15:00
> To: user@hive.apache.org
> Subject: Re: (Get the current date -1) in Hive
>
> guess you can use sub_date, but you have to get today by some outside
> script.
>
> On 08/06/2012 02:10 PM, Techy Teck wrote:
> > Is there any way to get the current date -1 in Hive means yesterdays
> > date always?
> >
> >
> >
>
>

RE: (Get the current date -1) in Hive

Posted by ca...@nokia.com.
If you are just using it in a query, you can do this:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)

I generally do my date calculations in a shell script and pass them in with a hiveconf variable.

Carla

-----Original Message-----
From: ext Yue Guan [mailto:pipehappy@gmail.com] 
Sent: Monday, August 06, 2012 15:00
To: user@hive.apache.org
Subject: Re: (Get the current date -1) in Hive

guess you can use sub_date, but you have to get today by some outside script.

On 08/06/2012 02:10 PM, Techy Teck wrote:
> Is there any way to get the current date -1 in Hive means yesterdays 
> date always?
>
>
>


Re: (Get the current date -1) in Hive

Posted by Yue Guan <pi...@gmail.com>.
guess you can use sub_date, but you have to get today by some outside 
script.

On 08/06/2012 02:10 PM, Techy Teck wrote:
> Is there any way to get the current date -1 in Hive means yesterdays 
> date always?
>
>
>